MySQL: LEFT OUTER JOIN within reason What I'm attempting to do is LEFT OUTER JOIN a table, but I only want the last inserted record of the joined table to affect the results. Currently each table row within the joined table seems to be affecting the results of my query.

My current LEFT OUTER JOIN is similar to:

LEFT OUTER JOIN tablethree AS t3 ON t3.sid = t2.sid

Inside of tablethree I have two different results. Both of them are appearing to affect my results, but what I would like is for the most recently entered result to affect my end results.

My full query looks similar to (only using * here to save time):

SELECT t1.*
FROM tableone AS t1
INNER JOIN tabletwo AS t2
  ON t1.cid = t2.id
LEFT OUTER JOIN tablethree AS t3
  ON t3.sid = t2.sid
WHERE t1.fieldone = 1
  && t1.odate NOT BETWEEN t3.startdate AND t3.enddate

The tablethree start date and end date that I want reflect 03-01-2014 and 03-31-2014, but the second result in that table is 04-01-2014 and 04-30-2014 and this appears to be affecting the results of the query.

My Question is: How can I change the LEFT OUTER JOIN to only use the most recently entered result and not each result found to affect my final outcome of results?

First consider a query that computes which rows are actually relevant from tablethree. With the assumption that with "most recently entered result" you mean "most recent enddate" the following query would gather the appropriate rows:

SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid

Now you can build a join to retrieve not only sid, but all of the data of tablethree:

SELECT a.*
FROM tablethree a
INNER JOIN (
  SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
) b
ON a.sid = b.sid AND a.enddate = b.enddate

This is the result set you actually want to "left join in". You have to insert this into your original query:

SELECT t1.*
FROM tableone AS t1
INNER JOIN tabletwo AS t2
  ON t1.cid = t2.id
LEFT OUTER JOIN (
  SELECT a.*
  FROM tablethree a
  INNER JOIN (
    SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
  ) b
  ON a.sid = b.sid AND a.enddate = b.enddate
) AS t3
  ON t3.sid = t2.sid
WHERE t1.fieldone = 1
  AND t1.odate NOT BETWEEN t3.startdate AND t3.enddate

What should also work is the following:

SELECT t1.*
FROM tableone AS t1
INNER JOIN tabletwo AS t2
  ON t1.cid = t2.id
LEFT OUTER JOIN tablethree AS t3
  ON t3.sid = t2.sid
LEFT OUTER JOIN (
  SELECT sid, MAX(enddate) FROM `tablethree` GROUP BY sid
) mostrecent
  ON t3.sid = mostrecent.sid AND t3.enddate = mostrecent.enddate

WHERE t1.fieldone = 1
  AND t1.odate NOT BETWEEN t3.startdate AND t3.enddate
  AND mostrecent.enddate IS NULL

This includes both tablethree and the new SELECT as left joins, and sorts out the rows where mostrecent.enddate IS NULL (meaning those rows which are actually not most recent). This should lead to the same result, but MySQL may be able to compute this result a little faster. EXPLAIN on both queries should reveal possible differences in computation.


MYSQL 5.7 Innodb bufferpool settings I have set  innodb_buffer_pool_size=100G and instances to 8, but I am getting following output

mysql> show engine innodb status \G
---BUFFER POOL 6
Buffer pool size   835482

I have been reading online that bufferpool= innodb_buffer_pool_chunk_size* innodb_buffer_pool_instances.

This is confusing, should we set the default chunk size to higher values and increasing the number of instances do not cause contention anymore.

Can anyone advise on correct parameters for 100GB bufferpool size?

There is no problem, just confusing notation.  It is saying that there are 835482 blocks in instance #6.

Here is a buffer_pool of 3G and 8 instances:

mysql> SHOW VARIABLES LIKE 'innodb_buffer_pool%';
...
| innodb_buffer_pool_instances        | 8              |
| innodb_buffer_pool_size             | 3145728000     |  -- 3GB

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
...
---BUFFER POOL 7
Buffer pool size        23999       -- 3G / 8 / 16K
Buffer pool size, bytes 393199616   -- 3G / 8

In your case, 835482 = 100G / 8 / 16K


Convert UNION query to JOIN. Is it actually needed for high performance? I have the following table structure in MySql.

To get the list of roles for a particular user, I use the following union Query,

SELECT r.ID, r.ROLE_NAME, r.TENANT_DOMAIN, r.PROVIDER_ID
FROM  `T_ROLE` AS r
JOIN T_ROLE_USER AS ru ON r.id = ru.ROLE_ID
WHERE ru.user_id ="100"
UNION SELECT r.ID, r.ROLE_NAME, r.TENANT_DOMAIN, r.PROVIDER_ID
FROM  `T_ROLE` AS r
JOIN T_ROLE_GROUP AS rg ON rg.ROLE_ID = r.id
JOIN T_USER_GROUP AS ug ON ug.GROUP_ID = rg.GROUP_ID
WHERE ug.user_id ="100"
LIMIT 0 , 30

Explain of the above query,

and to get the list of permissions for a user I wrote the following query,

SELECT p.ID,p.TENANT_DOMAIN,p.PROVIDER_ID,p.RESOURCE_NAME,p.ACTION
FROM  `T_PERMISSION` AS p
JOIN T_ROLE_PERMISSION AS rp ON rp.PERMISSION_ID = p.ID
JOIN T_ROLE AS r ON r.ID = rp.ROLE_ID
JOIN T_ROLE_USER AS ru ON ru.ROLE_ID = r.ID
WHERE ru.USER_ID ="100"
UNION
SELECT  p.ID,p.TENANT_DOMAIN,p.PROVIDER_ID,p.RESOURCE_NAME,p.ACTION
FROM  `T_PERMISSION` AS p
JOIN T_ROLE_PERMISSION AS rp ON rp.PERMISSION_ID = p.ID
JOIN T_ROLE AS r ON r.ID = rp.ROLE_ID
JOIN T_ROLE_GROUP AS rg ON rg.ROLE_ID = r.ID
JOIN T_USER_GROUP AS ug ON ug.GROUP_ID = rg.GROUP_ID
WHERE ug.USER_ID ="100"

Explain results of the above query is,

Is it possible to convert the above two queries to use JOIN instead of UNION ? If can why? Will there be any issues on huge data with this query. I see that all the data are indexed properly.

You use LIMIT without ORDER BY - that may return different 30 rows each time it is executed.

Other than that if you combine the two unioned queries into one join, you will have OR in WHERE which will check columns from different tables - that is probably going to be much slower than what you have now, union is good for this. You might want to use UNION ALL instead if you know that there will be no duplicates or that they are no problem to you as it does not have to check and eliminate them and can be even faster that UNION.


Unexpected behaviour for GROUP BY query CREATE TABLE public.temp (
   id integer,
   a integer,
   b integer,
   x integer
);

select x, a, b
    from temp
group by id, a, b;

ends up with expected error:

ERROR:  column "temp.x" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select x, a, b
               ^

However, after adding primary key:
ALTER TABLE ONLY temp ADD  PRIMARY KEY (id);

... the previous SELECT query runs without error.
Why can column x now be used without aggregation?
Is the behaviour documented?

GROUP BY by primary key column causes each row to become a separate group. In this case the column not included into grouping / aggregating has only one value within the group, so this value may be returned without ambiguity.
If there is no primary column / expression as a part of grouping expression then server must consider that there may be more than one value for a group, and it generates error because of possible ambiguity.
In general the fact that the error is not generated is an extension it seems...
If PK is a part of grouping expression than formally all another parts are excess. I.e. if id is PK then group by id, group by id,a,b,x, group by id,a,b, group by id,b,x, group by id,a,x, ... will have the same meaning and effect.
But the best practice is to use an aggregate function for each non-groupby column / expression in any case.


Can only specific SQL users be forced to use TLS? My school would like to develop custom software for some embedded devices. As part of this, we may need the devices to connect to an SQL Server instance.

Is it possible to force specific SQL users to use encryption when connecting to the server? We don't want to force it for the entire instance because of some important legacy software.

We're also evaluating other (frankly, better) solutions, but I want to know what's possible.

The option for forcing encryption from the server side is either all or nothing.  There is no way on the server side to configure it so that it only forces certain logins or connections to encrypt.

Clients, however, can be configured to either encrypt the connection or not.  For example, adding encrypt=true to a Microsoft JDBC connection string, or SSL=require   to a jTDS connection string will configure that client connection to be encrypted.  If you're using an ODBC DSN, the configuration typically has an option for encryption as well.  You should be able to quickly learn how to configure a given SQL client for encryption by doing a bit of searching on the Internet.

One note of caution is that you may need to update SQL Server and the SQL client that is being used if you are forcing TLS 1.2 (see TLS 1.2 support for Microsoft SQL Server).  The latest release of the jTDS driver has a defect that causes it to fail with TLS 1.2, but there is a developer build available in the forums with a fix.  The last official release of jTDS was in 2013, so you'll have to use an unreleased build if you'll be forcing TLS 1.2.


Compare last data and second last data sql server based on id How do I create a view to find the last 2 records for a company id and to display the last value along with the difference between the last and the second last?

Here is some example data:

CREATE TABLE #161689 (
Dt DATE NULL,
Value INTEGER NULL,
CompanyId INTEGER NULL
);

INSERT INTO #161689
VALUES
('12/01/2010', 10, 10),
('11/01/2010', 5, 10),
('10/01/2010', 2, 10),
('08/10/2010', 1, 10),
('12/01/2010', 7, 20),
('10/01/2010', 3, 20),
('09/01/2010', 2, 20),
('08/10/2010', 2, 20);

Here is the expected result:

Date - Value - CompanyId - Diff

12.1.2010 - 10 - 10 - 5

12.1.2010 - 7 - 20 - 4

You have what we call a "greatest-n-per-group" problem. Plenty of community members from this stackexchange wrote up generic solutions for that problem here.

One approach for your problem is to use the ROW_NUMBER() window function to assign a number to all of the rows and to pivot the results from columns into rows. One implementation is as follows:

SELECT
  CompanyId
, MAX(CASE WHEN RN = 1 THEN Value ELSE NULL END) Last_Value
, MAX(CASE WHEN RN = 1 THEN Dt ELSE NULL END) Last_Date
, MAX(CASE WHEN RN = 1 THEN Value ELSE NULL END) - COALESCE(MAX(CASE WHEN RN = 2 THEN Value ELSE NULL END), 0) DIFF
FROM
(
    SELECT
      CompanyId
    , Value
    , Dt
    , ROW_NUMBER() OVER (PARTITION BY CompanyId ORDER BY Dt DESC) RN
    FROM #161689
) t1
GROUP BY CompanyId;

To break it down step by step, the t1 derived table returns the following results:

╔═══════════╦═══════╦════════════╦════╗
║ CompanyId ║ Value ║     Dt     ║ RN ║
╠═══════════╬═══════╬════════════╬════╣
║        10 ║    10 ║ 2010-12-01 ║  1 ║
║        10 ║     5 ║ 2010-11-01 ║  2 ║
║        10 ║     2 ║ 2010-10-01 ║  3 ║
║        10 ║     1 ║ 2010-08-10 ║  4 ║
║        20 ║     7 ║ 2010-12-01 ║  1 ║
║        20 ║     3 ║ 2010-10-01 ║  2 ║
║        20 ║     2 ║ 2010-09-01 ║  3 ║
║        20 ║     2 ║ 2010-08-10 ║  4 ║
╚═══════════╩═══════╩════════════╩════╝

You're interested in the rows that have an RN value of 1 or 2. You need to combine the relevant two rows for each CompanyId into a single row. This can be accomplished with GROUP BY. I use MAX to get the latest or second latest as needed. The use of MAX is a bit misleading, since there's always just one row that matches. However, I need to use some aggregate function because this is a GROUP BY query so MAX works fine.

The final results are:

╔═══════════╦════════════╦════════════╦══════╗
║ CompanyId ║ Last_Value ║ Last_Value ║ DIFF ║
╠═══════════╬════════════╬════════════╬══════╣
║        10 ║         10 ║ 2010-12-01 ║    5 ║
║        20 ║          7 ║ 2010-12-01 ║    4 ║
╚═══════════╩════════════╩════════════╩══════╝


Data Migration With Different Schemas I have a database DBv1 on a SQL Server, and I want to migrate the data in it to DBv2 (on the same box, save server). DBv2 has very similar schema. The only difference in schema is that DBv2 has one less table, because it is de-normalized.

What would be the most practical way to migrate the data from DBv1 to DBv2 given that change in schema, and the need for some transformation?

Is there a tool that would do that for me? Or do I have to script that manually?

For clarification: this is a one-time load only since it is just a migration. By schema here I do not refer to the SQL object with the same name. Rather, I am referring to the structure of the tables. Due to de-normalization, one table is removed, and a new column is added to the parent table that would have the data in the deleted table.


Schema replication Postgresql AWS Is there a way to replicate Postgres Schemas in AWS into one location for reporting purposes?

I have 3 postgres databases (up to date with current version) running in AWS that I would like to replicate into one location to build an Operation Data Store (ODS).  I require the ability to query cross databases - I've primarily only used MSSQL - and I believe that you can only cross query schemas.

Or do I have to put this into another amazon product to be able to achieve this result?

I don't know how these things apply to RDS in particular, but regarding community PostgreSQL at large, there are a few options:

Use logical replication to replicate data from the other servers into a single PostgreSQL database.  A major caveat here is that the tables on the central server need to have the exact same table name, including schema name, as they do on the outlying servers.  So if the outlying servers have tables with the same table name and schema name as each other, that will be a problem.  If you are starting from scratch, it is pretty easy to solve this by having each server include the server descriptions in all of its schema names so that all schema names are unique (i.e. "london_accounts", "chicago_accounts", rather than just "accounts" on both servers).  But if you are adapting existing systems which didn't abstract away the schema names, it can be a major pain to have to change everything to fit this pattern.

Or, you could just leave the data where it is, and use the foreign data wrapper postgres_fdw to query it from the central location.  Caveats here are that all the outlying servers have to be online for this to work, the high ping time and possible low network throughput to them can be major performance problem, and FDW has higher overhead and often comes up with worse plans than than native data.

You can overcome the uptime, ping, and throughput issues by using physical replication of each outlying servers to the central location (e.g. three database instances running on three different port numbers of the same piece of hardware), and then use FDW from a 4th server to each replica to tie those replicas together. You might still suffer from some overhead and planning issues.


Move data from one oracle db to another db using toad I am new to oracle and this might be a simple question for many.

I need to take data from one db and insert it into other db.  The databases are on the same version and edition.  I am currently using Toad.

All I have been able to find out is to use export and import utilities, which I am not able to execute from my toad client. I'm hoping for a query to retrieve data in a file and then import this file into another database.

If you have Toad I assume you can connect to both databases from it.

Select the origin table in the schema browser in Toad, right click, select "Create in another schema" or "Copy Data to another schems".
A dialog box appears, select your options as required.
then a "Destination" dialog box appears.  Select the connection to the destination database and schema of your choice
Click on execute.

Toad will show a failure or success message.

Job done!


Wrong results on queries with join for some MariaDB versions I found that on some versions of MariaDB server some simple queries return wrong results for certain data.

I have 2 tables:

CREATE TABLE `tab_parent` (
  `id` bigint(12) unsigned NOT NULL AUTO_INCREMENT,
  `f_date` date DEFAULT NULL,
  `f_value` int(11) NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
CREATE TABLE `tab_child` (
  `id` bigint(12) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` bigint(12) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),KEY `parent_id` (`parent_id`),
  CONSTRAINT `fk_2` FOREIGN KEY (`parent_id`) REFERENCES `tab_parent` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

And a view which counts how many children has a parent:

CREATE VIEW `tab_parent_view` AS
  SELECT `tab_parent`.*,
  COUNT(`tab_child`.`id`) AS `child_count`
  FROM (`tab_parent` LEFT JOIN `tab_child` ON(`tab_child`.`parent_id` = `tab_parent`.`id`))
GROUP BY `tab_parent`.`id`;

With a sample data like here: https://dbfiddle.uk/?rdbms=mariadb_10.2&fiddle=0a89cb2a4bec635766313c3c59cd923c the 2 queries do not return the same number of records for versions 10.2.27 and 10.4.8 of MariaDB. But the results are correct for versions 10.1.43, 10.3.15, 10.3.16 and 10.4.12.

SELECT * FROM tab_parent_view WHERE f_date>='2020-02-07' AND f_date<'2020-02-08';
SELECT * FROM tab_parent      WHERE f_date>='2020-02-07' AND f_date<'2020-02-08';

For the view only 2 records are returned, but for the table all the 4 existing ones:

Is this a known bug in MariaDB? I would like to know what causes this and on which versions is it fixed. For MySQL I did not found this problem.

I don't think it's a bug. You use GROUP BY and at that moment server leaves only one random value for f_date in each row. After that you apply your filter for results returned by the query inside the view.
According to MariaDB documentation:

  You can use any of the grouping functions in your select expression.
  Their values will be calculated based on all the rows that have been
  grouped together for each result row. If you select a non-grouped
  column or a value computed from a non-grouped column, it is undefined
  which row the returned value is taken from. This is not permitted if
  the ONLY_FULL_GROUP_BY SQL_MODE is used.
  https://mariadb.com/kb/en/group-by/

There is no guarantee that value for non-grouped column will be returned from the same group of rows.
I would recommend you to enable ONLY_FULL_GROUP_BY SQL_MODE and add all non-grouped columns to GROUP BY part or apply grouping functions for them (for example, you could use MAX(f_date)).


Filtering a MySQL table on multiple columns, including one with spatial data I have a MySQL table with linestrings:

CREATE TABLE edges (
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
  geometry linestring NOT NULL,
  type_key varchar(255) NOT NULL,
  created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  deleted_at datetime DEFAULT NULL,
  PRIMARY KEY (id),
  KEY type_key (type_key, deleted_at),
  SPATIAL KEY geometry (geometry)
) ENGINE=InnoDB AUTO_INCREMENT=130966 DEFAULT CHARSET=utf8mb4;

I want to plot a subset of them on a map. To do so, I run the following query (the ? are variables):

SELECT *
FROM edges
WHERE deleted_at IS NULL
  AND type_key = ?
  AND MBRIntersects(geometry, LineString(Point(?, ?), Point(?, ?)))

The issue I'm facing, is that MySQL does not allow for multi-column indices that include spatial columns (so the geometry column). It therefore has to choose: either use the type_key index, or use the spatial geometry index.

Which one is more efficient depends on the type_key chosen and the minimum bounding rectangle of the linestring constructed in the query. And in quite some cases, it's not efficient at all to only be able to use one of them: with about 100k rows in this table, it might very well take MySQL more than 500ms to compute that one such query results in 0 rows.

I googled quite a bit on this topic, but could not find any good solutions. Interesting results were:

partitioning (see eg. http://mysql.rjweb.org/doc.php/latlng), but the number of partitions you should make is quite limited (< 100, I read)
not using a spatial column at all: the geometry can be represented by a bounding box with four points. I could add all four as separate columns and add them to the index. That will help on one of them (as they are all inequality constraints), but will still be quite inefficient (because of the other 3)
migrating to other database software, eg. Postgres. I read that Postgres is able to use multiple indices in the same query (https://devcenter.heroku.com/articles/postgresql-indexes), but it's not clear to me how efficient that is. I'm not clear on the alternative: whether a spatial column and other columns can be combined in a single index. I spent some time in setting up a test environment with the same data, but gave up after an hour or so, as it took me too much time.

My question therefore is: how are others solving this issue?

You are saying that some type_keys are associated with thousands of rows; some with only a few?

An off-the-wall thought:

if ( SELECT id FROM edges
         WHERE type_key = ? AND deleted IS NULL
         LIMIT 100, 1 ) is NOT NULL
then
   SELECT * FROM edges ... FORCE INDEX(type_key) ...
else
   SELECT * FROM edges ... IGNORE INDEX(type_key) ...

Caveats:

The IF statements require either using a Stored Procedure or moving the IFs into your app code.
I don't know if the idea will work.
The choice of 100 needs tuning.


Can MySql nested SP be a bottleneck? We have this MySQL SP, which calls a nested SP. It seems it does NOT perform well under load.

It is possible that this SP becomes slow under load because it calls a nested SP and uses temporary tables to pass the data to main SP?

DELIMITER $$

drop procedure if exists `GeoAreaFlattened_Select`;

create procedure `GeoAreaFlattened_Select`(
    _areas MEDIUMTEXT,
    _comparisonGroup varchar(21844),
    _parentArea varchar(21844),
    _areaType varchar(21844)
)
begin

drop temporary table if exists areas;

-- areas
call CreateAreas(_areas, _comparisonGroup, _parentArea, _areaType);

SELECT
    areas.ID,
    areas.Code,
    areas.Name,
    areas.LevelId,
    GeoAreaLevel.Name AS AreaTypeLabel,
    GeoAreaLevel.Identifier AS AreaTypeIdentifier
FROM
    areas
INNER JOIN
    GeoAreaLevel
ON
    areas.levelid = GeoAreaLevel.id
ORDER BY areas.name ASC;

drop temporary table areas;

end

The nested SP:

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
drop procedure if exists `CreateAreas`;

DELIMITER $$

CREATE PROCEDURE `CreateAreas`(
    _areas varchar(21844),
    _comparisonGroup varchar(21844),
    _parentArea varchar(21844),
    _areaType varchar(21844)
)
BEGIN

    -- create temporary table "areas"
    -- fill with area ids

    create temporary table areas (
        id int not null,
        code varchar(30),
        name varchar(100),
        shortName varchar(100),
        levelid int not null,
        sortOrder int not null,
        key (id)
    );

    -- assumes that only one of the 3 options is valid, areas, comparison group, bounded comparison group

    if (_areas is not null) then

        set @sql = concat('insert into areas (id, code, name, shortName, levelid, sortOrder) select id, Code, Name, ShortName, LevelID, 0 from GeoArea where Code in (''', replace(_areas, ',', ''','''), ''')');
        prepare stmt from @sql;
        execute stmt;
        deallocate prepare stmt;

    elseif (_comparisonGroup is not null) then

        -- might not be the most efficient way, but is consistent with the approach above, and we do not expect the list to be long
        insert into areas (id, code, name, shortName, levelid, sortOrder)
        select GeoAreaID, GeoArea.Code, GeoArea.Name, GeoArea.ShortName, GeoArea.LevelID, SortOrder
        from ComparisonGroupGeoAreaLink
        INNER JOIN
        GeoArea
        ON GeoArea.ID = GeoAreaID
        where ComparisonGroupID = (select id from ComparisonGroup where Identifier = _comparisonGroup)
        and IsMember = 1;

    elseif (_parentArea is not null and _areaType is not null) then

        -- might not be the most efficient way, but is consistent with the approach above, and we do not expect the list to be long
        insert into areas (id, code, name, shortName, levelid, sortOrder)
    select a.ID, a.Code, a.Name, a.ShortName, a.LevelID, 0
        from (select id from GeoArea where Code = _parentArea) as t
        INNER JOIN
        GeoAreaLinkCache c
        ON
        c.ParentAreaID = t.id
        inner join GeoArea a
        on c.ChildAreaID = a.ID
        INNER JOIN
        (select id from GeoAreaLevel where Identifier = _areaType) as l
        ON
        a.LevelID = l.id;

    elseif (_areaType is not null) then

        -- might not be the most efficient way, but is consistent with the approach above, and we do not expect the list to be long
        set @sql = concat('insert into areas (id, code, name, shortName, levelid, sortOrder)
        select a.ID, a.Code, a.Name, a.ShortName, a.LevelID, 0
        from
        (select id from GeoAreaLevel where Identifier in (''', replace(_areaType, ',', ''','''), ''')) l
        INNER JOIN
        GeoArea a
        ON
        a.LevelID = l.id');
        prepare stmt from @sql;
        execute stmt;
        deallocate prepare stmt;

    end if;

END

There's nothing inherently wrong with what you are doing, assuming the strings you're splitting up into pieces to form the IN sets are coming from a safe source, but I would have these suggestions for optimization:

There's not an obvious reason to make the call to a separate procedure, as this could all be accomplished in a single procedure, and everything requires some finite amount of time to be done, so writing it as a single proc would save some small amount of time, which helps when you're doing a large amount of the same thing... though I don't think that's the biggest issue, by far.

I suspect the big time killer here is the repeated creation of the temporary table.  On my unimpressive dual 1GHz Opteron test machine, I see dramatically different creation times for temporary tables:

Query OK, 0 rows affected (0.24 sec) # ENGINE=InnoDB (default)
Query OK, 0 rows affected (0.02 sec) # ENGINE=MyISAM
Query OK, 0 rows affected (0.00 sec) # ENGINE=MEMORY

Since you are not using the temporary table to directly update another table, you should be free to use the most lightweight engine that fits the application, even if that means you're mixing transactional and non-transactional storage engines, because you don't need subsequent queries based on the temporary table to actually replicate correctly to downstream slaves and there's nothing here to roll back.  You're creating, inserting, selecting, dropping, and this should not have any impact on replication (otherwise, it might).

You should also evaluate the EXPLAIN output from the queries in the nested proc to be sure they are optimal.  Using derived tables, e.g., SELECT ... FROM (SELECT...) do not really appear to be necessary and may not be the most optimal way to write those queries.

For example:

insert into areas (id, code, name, shortName, levelid, sortOrder)
select a.ID, a.Code, a.Name, a.ShortName, a.LevelID, 0
    from (select id from GeoArea where Code = _parentArea) as t
    INNER JOIN
    GeoAreaLinkCache c
    ON
    c.ParentAreaID = t.id
    inner join GeoArea a
    on c.ChildAreaID = a.ID
    INNER JOIN
    (select id from GeoAreaLevel where Identifier = _areaType) as l
    ON
    a.LevelID = l.id

...would likely be much better written this way (assuming I've parsed it correctly):

INSERT INTO areas (id, code, name, shortName, levelid, sortOrder)
SELECT a.ID, a.Code, a.Name, a.ShortName, a.LevelID, 0
  FROM GeoArea t
 INNER JOIN GeoAreaLinkCache c ON c.ParentAreaID = t.id
 INNER JOIN GeoArea a on c.ChildAreaID = a.ID
 INNER JOIN GeoAreaLevel l ON l.id = a.LevelID
 WHERE l.Identifier = _areaType
   AND t.Code = _parentArea;

Avoiding the derived tables potentially gives the optimizer a lot more options when formulating the query plan because derived tables generally limit the usability of indexes.  They may be logically equivalent but aren't equivalent in execution.


Delete Old Archivelogs from missing database We have deleted a database but the archive logs remain. We need to delete these as they take a lot of space:

Since the database is deleted, it is not in the rman catalogue so I cannot use delete archivelog expired in Rman to delete these files.

I read about asmcmd but I'm afraid to put out of sync the Enterprise Manager's records with the actual disk content going that way.

What is the cleanest way to proceed?

We finally deleted the files manually and OEM is fine.

We ran this query to get the delete scripts:

SELECT 'alter diskgroup '
       || gname
       || ' drop file ''+'
       || gname
       || Sys_connect_by_path(aname, '/')
       || ''';' a
FROM   (SELECT b.name            gname,
               a.parent_index    pindex,
               a.name            aname,
               a.reference_index rindex,
               a.system_created,
               a.alias_directory
        FROM   v$asm_alias a,
               v$asm_diskgroup b
        WHERE  a.group_number = b.group_number)
WHERE  alias_directory = 'N'
       AND system_created = 'Y'
START WITH ( MOD(pindex, Power(2, 24)) ) = 0
           AND rindex IN (SELECT a.reference_index
                          FROM   v$asm_alias a,
                                 v$asm_diskgroup b
                          WHERE  a.group_number = b.group_number
                                 AND ( MOD(a.parent_index, Power(2, 24)) ) = 0
                                 AND a.name = '&DATABASENAME')
CONNECT BY PRIOR rindex = pindex;

Replace &DATABASENAME with your old database name and then run the scripts.

Important note: you would normally not do this. I'm sure my database has been deleted and that the remaining files are completely useless. If your intent is to clean obsolete archive logs, use RMAN instead. It's safe and really easy.


Use previous state of data I am trying to carry forward the previous state of data wherever data is not available. For example, I have following data;

Month
Order
Amount

1
Item1
100

1
Item2
200

1
Item3
300

2
NULL
NULL

3
Item4
100

4
NULL
NULL

5
NULL
NULL

6
Item1
100

So in the above example I want to use 1st month's information to be carry forwarded as it is for 2nd month. Also since 3rd month's information is available then use it for the month 4 and 5. Hence the output will be following:

Month
Order
Amount

1
Item1
100

1
Item2
200

1
Item3
300

2
Item1
100

2
Item2
200

2
Item3
300

3
Item4
100

4
Item4
100

5
Item4
100

6
Item1
100

WITH
cte AS ( SELECT DISTINCT
                Month,
                MAX(CASE WHEN [Order] IS NOT NULL
                         THEN Month
                         END) OVER (ORDER BY Month
                                    ROWS BETWEEN UNBOUNDED PRECEDING
                                             AND 1 PRECEDING) prev
         FROM test )
SELECT Month, [Order], Amount
FROM test
WHERE [Order] IS NOT NULL
UNION ALL
SELECT t1.Month, t2.[Order], t2.Amount
FROM test t1
JOIN cte ON t1.Month = cte.Month
JOIN test t2 ON t2.Month = cte.prev
WHERE t1.[Order] IS NULL
ORDER BY Month, [Order];

fiddle with some explanations


Postgres connection access denied on IPv6 address Installed PostgreSQL 9.1 x64 on Windows, set up a listen address, but when connecting with pgAdmin I get the following error. Not sure why PostgreSQL is seeing my IPv6 address and not my regular IP address:

To get authentication working, based on the error message, I updated pg_hba.conf with this:

host    all         all fe80::c5d2:XXXX:XXXX:3bc0/12          trust

That worked, but it's ugly, and too specific. I tried the following based on PostgreSQL docs, but none worked, I get the same 'access denied' error:

local all all trust
host all all 0.0.0.0/12 trust

I got this one working, which covers the entire IPv6 address space, but how can I specify an IPv6 range for more restriction?

host  mydb  myuser  ::/0   trust

Questions

Why does pgAdmin pick up my IPv6 address and not my normal IP?
How do I specify a range in IPv6 without resorting to ::/0?

The IPv6 addresses starting with fe80: are link-local addresses. They cannot be routed across different subnets or the internet. They are for communication between machines connected to the same LAN only. The link-local range is defined as fe80::/10. If you trust everybody on your local LAN then you could do

host  all  all  fe80::/10  trust

If you don't trust everybody on your local LAN then you probably want to use username+password protection:

host  all  all  fe80::/10  md5

When specifying md5 you are still using username+password protection, but the password is not transmitted in a readable format anymore. Since you're not trusting people on your local LAN you probably shouldn't send a snoopable password over the wire either.


SQL Server Always on Three node Cluster We have two data centers DC1 and DC2

The distance between two DC's are about 200 miles.
DC1 - has two nodes
DC2 - has 1 node with SQL 2012 Always-On AG and windows 2008R2
Node majority cluster
Two nodes on DC1 are primary.
Failovers are always between the two nodes on DC1.
DC2 is only for backups.

There is intermittent network issue between two DC1 and DC2, whenever there is network glitch causes the databases on DC2 to restart which kills the full backup job that was running.

One of our DBA suggesting to create a file share witness and remove vote to the node on DC2, then change quorum settings from node majority to node and file share majority.

My Question is..

If we create file share witness and change quorum setting will fix our backup issue? I am not clear how the full backup job continue running when there is network glitch and the all the nodes are in cluster
Can any one advise how do i over come from this situation. Network team said they do not have any fix from their end.

I researched on this and found below link which looks similar but i can not correlate with my issue.

SQL Server Always On Node and File Share Majority

Regardless of the quorum model, if a SQL Server 2012 secondary becomes disconnected, it will go offline.

This was changed in SQL Server 2014.

  When disconnected from the primary replica or during cluster quorum
  loss, readable secondary replicas now remain available for read
  workloads.

What's New for SQL Server 2014


Explaining 2NF vs 3NF with an example I have a problem with second normal form (2NF) and I have been unable to solve it by using Google. It is making me crazy because I am a teacher and I don't want to teach wrong stuff to my students.

Let's have a table with 5 fields.

Gradings = {StudentName, SubjectCode, SubjectName, #Exam, Grade}

Dependencies are this way:

StudentName, SubjectCode, #Exam -> Grade

SubjectCode -> SubjectName

SubjectName -> SubjectCode

Therefore, candidate key 1 is {StudentName, SubjectCode, #Exam} and candidate key 2 is {StudentName, SubjectName, #Exam}.

Prime attributes are {StudentName, SubjectCode, SubjectName, #Exam} and non-prime attributes is Grade

According to the definition of second normal form, a non-prime attribute cannot depend on a part of a candidate key. The only non-prime attribute (Grade) does not depend on a part of a candidate key so this table seems in 2NF .

The problem is that I think something is amiss (and I could be wrong). I think subjects should have their own table.

Gradings = {StudentName, Subject Code, #Exam, Grade}

Subjects = {Subject Code, SubjectName}

But 2NF does not produce this. 3NF is about dependencies between non-prime attributes so it does not produce this either. But it seems to me that this is the right outcome, because it has no redundancy.

I guess if non-prime attribute was defined as "attribute that is not a candidate key", 2NF would produce the desired result. But I have checked this again and again and non-prime attribute is defined as "attribute that does not BELONG to a candidate key".

What am I doing wrong?

Your relation is in 3NF, (and not only in 2NF), since as you say the only non prime attribute is Grade, which only appears on the right hand side of your FDs.

The relation is not in BCNF, because the left hand side of the two small FDs is not a superkey.

You can, however, losslessly decompose the relation to
(SubjectCode, SubjectName) and either
(StudentName, SubjectCode, #Exam, Grade)
 or
(StudentName, SubjectName, #Exam, Grade)

This decomposition gives you two BCNF relations and preserves all functional dependencies. This isn't always possible (you can always decompose a relation to 3NF, but not necessarily to BCNF).

2NF

If you want an example of 2NF (and not 3NF), your relation needs to contain transitive dependencies.

For instance, say you have a Score column. Intuitively Score->Grade since all exams with the same score should get the same grade (it would be rather unfair otherwise), but note that we cannot say Grade->Score since several scores can have the same grade (11% and 12% would likely be "Fail", for instance).

Now your relation is:

Gradings(StudentName, SubjectCode, SubjectName, #Exam, Score, Grade)

and you have a new form of redundancy since every time you enter a result with the same score as another Gradings record you also have to repeat the corresponding Grade. To get to 3NF you could therefore decompose to

ScoreGrades(Score,Grade)

with Score as the key, and

Scores(StudentName, SubjectCode, SubjectName, #Exam, Score)


Retreive the list of Up/Down status of Oracle database using Shell Script I am trying to create a shell script for retrieving the list of Oracle database with status UP/DOWN.

Are all the databases running on one server?  If so, start with

/home/mark > ps -fu oracle | grep pmon
oracle   15060     1  0  2019 ?        00:28:00 ora_pmon_test01
oracle   53410     1  0 Jan08 ?        00:15:33 ora_pmon_rcatn

And if that output matches what seems reasonable, pipe it through cut:
/home/mark > ps -fu oracle | grep pmon | cut -d_ -f3
test01
rcatn

But that really doesn't verify if the database is fully up and running; you'll likely need something like Jon mentioned in his comments.  And anything beyond what I listed above, gets complex quickly.
The PMON process is (to oversymplify) the main watchdog of an Oracle database, so if a PMON process is running, the database might be available and online.  99.9% of the time, yes, but you want to also know if it is accessible.


Powershell runs locally but fails when put in a job - login failed, login is from an untrusted domain I have a powershell script that queries from the local server for a list of servers and then iterates through that list of servers, connects to them, queries some data, and then connects and inserts that data locally.

The powershell works perfectly fine if I run it in Powershell ISE, but when I put it into a powershell step in a SQL Agent job it fails.  I have the job using a proxy sql agent account that I set up that uses a windows account, let's call it domain\PowershellAgent.  I have granted the account PowershellAgent sufficient permissions on the server as far as I can tell, but I still get the error.

The full error is:

  Login failed.  The login is from an untrusted domain and cannot be
  used with Windows authentication.

What can I do to get this working?

Example of a connection:

$sqlConnection = new-object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server='$server'; Database=DBADatabase;Trusted_Connection=True;Connect Timeout=5; MultiSubnetFailover=Yes"
$sqlConnection.Open()

$sqlCommand = new-object System.Data.SqlClient.SqlCommand
$sqlCommand.Connection = $sqlConnection
$sqlCommand.CommandText = $Query
$sqlCommand.CommandType = [System.Data.CommandType]::Text
$sqlCommand.CommandTimeout = 60
$sqlCommand.Parameters.Add("@id", [System.Data.SqlDbType]::Int).value = $id

$DataReader = $sqlCommand.ExecuteReader()

$Datatable.Load($DataReader)

This doesn't 100% answer the question as to WHY I was getting the answer, but a workaround I figured out was moving the entire job to a different server where it worked.


Subqueries are not allowed in this context. Only scalar expressions are allowed My customer is running MS SQL Server 2000.
I completed a project, but I failed to realize that MS SQL Server 2000 will not allow a select inside insert into values thus giving the error:

Subqueries are not allowed in this context. Only scalar expressions are allowed.

Th

insert into table_one (greeting_column, name_column)
values (
         'hello',
         (select column_1 from table_to where name = 'bob')
       )

I'm calling this query from coldfusion10.
I've already realized a solution utilizing coldFusion10 for this problem by replacing the select statement calling the query and storing the results in a coldFusion list variable, then iterate through a loop that inserts the contents of the CF list variable to its respective record, but this takes much more processing then a simple SQL statement. I've found solution like this:

CREATE PROC whatever
@REC int,
@ChangedIP varchar(15),
@ChangedBY varchar(30)
AS
INSERT INTO table_LOG
SELECT *, GETDATE(), @ChangedID, @ChangedBy FROM table WHERE record = @REC

But I don't think coldFusion will allow this in a query (will try after the weekend)
Is there a way to re-write not using Transact-SQL Variables?

You could use this code instead - just use the SELECT to provide the values, including a fixed literal:

INSERT INTO dbo.table_one (greeting_column, name_column)
   SELECT
      'hello', column_1
   FROM
      dbo.table_to
   WHERE
      name = 'bob'

You cannot mix the VALUES() and the SELECT methods of providing values for an INSERT - either you can provide literals and SQL variables for a VALUES(.....) list of values - or then you need to use the SELECT instead


Union probably not right for this I have a table witrh 3 columns:

id price created_at
1    0      ts
2    5      ts
3    0      ts
4    3      ts

I'd like to have a result table that contains 2 rows, like this:

id timestamp
4  ts
2  ts

To get the two rows above, I am using two queries.
Here is how I am currently selecting the first row:

select id, to_timestamp(created_at)::timestamp as timestamp
from (select * from web.orders where total > 0) as q
order by id desc limit 1

Here is how I am selecting the second:

select id, to_timestamp(created_at)::timestamp as timestamp
from (select * from web.orders where total = 0) as q
order by id desc limit 1

I tried to create the desired result table with UNION ALL of the above queries but it is not working. Any ideas?

If I correctly understood, this should give the desired result:

create table weborders (id int, total int , ts timestamp);
insert into weborders values
(1, 0, '20200101'),
(2, 5, '20200102'),
(3, 0, '20200103'),
(4, 3, '20200104');

with ct as
(
  select   0 as row_no, id, ts
  from     weborders
  where    total > 0
  order by id desc limit 1
)
, ct2 as
(
  select   1 as row_no, id, ts
  from     weborders
  where    total = 0
  order by id desc limit 1
)
select id, ts
from
(
  select row_no, id, ts from ct
  union
  select row_no, id, ts from ct2
) a
order by row_no;

or alternatively:

select id, ts
from
(
 select 0 as row_no, id, ts
 from   weborders
 where id = (select max(id) from weborders where total > 0)
 union
 select 1 as row_no, id, ts
 from   weborders
 where id = (select max(id) from weborders where total = 0)
) a
order by row_no;

id | ts
-: | :------------------
 4 | 2020-01-04 00:00:00
 3 | 2020-01-03 00:00:00

db<>fiddle here

And another smart solution, thanks to @ypercubeᵀᴹ for this.

select distinct on (total > 0)
         id, ts
from     weborders
order by (total > 0), id desc ;

db<>fiddle here


How to use OR in query with priority? In a simple query like

SELECT *
FROM table
WHERE id='x' OR id='y'

How to give a priority to get only one of the WHERE clauses?

I mean getting rows with id='x'. Only if there is no row, get row with id='y'.

In other words, do not use id='y' if there is a row for id='x'.

SELECT *
FROM   table
WHERE  id IN ('x', 'y')
AND    (id='x' OR NOT EXISTS(SELECT id FROM table WHERE id='x'))

should do the trick (caveat: I'm an MSSQL guy and haven't tried that in mySQL, but I suspect that syntax is accepted just fine there too) though the efficiency will depend on how many rows matching the first filtering clause there are (for every row matching id='x' OR id='y' he subquery in the EXISTS part will probably be run unless the query planner is bright enough to see that it depends on nothing in the outer query so can be run once and the result reused - if you are only expecting at most two (which with a column name like id I suspect is the case) this should not be an issue)


How to fix errors reported by DBCC CHECKDB I am getting a lot of errors from DBCC CHECKDB, including:

  Msg 8939, Level 16, State 98, Line 1
  Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 3667181342891245568 (type Unknown), page (7791:-1694668604). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -12.

  Msg 8928, Level 16, State 1, Line 1
  Object ID 405576483, index ID 73, partition ID 72057594049200128, alloc unit ID 72057594054246400 (type In-row data): Page (1:194923) could not be processed.  See other errors for details.

  Msg 8976, Level 16, State 1, Line 1
  Table error: Object ID 405576483, index ID 73, partition ID 72057594049200128, alloc unit ID 72057594054246400 (type In-row data). Page (1:194923) was not seen in the scan although its parent (1:186194) and previous (1:194922) refer to it. Check any previous errors.

  Msg 8980, Level 16, State 1, Line 1
  Table error: Object ID 405576483, index ID 73, partition ID 72057594049200128, alloc unit ID 72057594054246400 (type In-row data). Index node page (1:186194), slot 103 refers to child page (1:194924) and previous child (1:194923), but they were not encountered.

  Msg 8978, Level 16, State 1, Line 1
  Table error: Object ID 405576483, index ID 73, partition ID 72057594049200128, alloc unit ID 72057594054246400 (type In-row data). Page (1:194932) is missing a reference from previous page (1:194931). Possible chain linkage problem.

Most importantly, at the end, the following is relayed:

  CHECKDB found 0 allocation errors and 28 consistency errors in database 'houseme'.

  repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (houseme).

What do I do?

The output of DBCC CHECKDB has told you exactly what to do:

USE [master];
GO

ALTER DATABASE houseme SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB(N'houseme', REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE houseme SET MULTI_USER;

In case the name of the option isn't obvious enough, this can lead to data loss - but without backups, you don't really have any other choice.

If this doesn't work, and you don't have backups, well, you may have just learned a very expensive lesson about why you should have backups. You can see if Paul Randal has some other ideas, or this idea from Steve Stedman. No guarantees, of course.

If you still have consistency errors after multiple tries, it might be time to create a new database, migrate as much consistent data as you can, and cut your losses.


Does anybody know what this tool is called? Productive work has crawled to a stop while we try and figure out what the red thing on the cover of Microsoft SQL Server 2012 Internals (2013, 0735658560) is. We think it's a surgical or electrical tool of some sort?

Thoughts?

It's a pair of canvas pliers. They're used to stretch canvas over braces, used in painting prep.

Answer source: Matt Velic, data professional and noted art graduate.


How compiled are prepared statements? I found this explanation of how prepared statements are compiled, but I'm still unsure.

What exactly is "compiled" in the context of a prepared statement?  Is a C compiler executed to create a custom executable?

If so, is that limited by prepareThreshold?

If not, what exactly is it compiled into, and are there any projects for converting prepared statements into executables?

The post you found is from 2007. Rather start with the current manual:

  When the PREPARE statement is executed, the specified statement is
  parsed, analyzed, and rewritten. When an EXECUTE command is
  subsequently issued, the prepared statement is planned and executed.
  This division of labor avoids repetitive parse analysis work, while
  allowing the execution plan to depend on the specific parameter values supplied.

And:

  If a prepared statement is executed enough times, the server may
  eventually decide to save and re-use a generic plan rather than
  re-planning each time.

Bold emphasis mine. Read more details in the manual.

I wouldn't know of projects trying to convert prepared statements into executables.


how to set mogodb in upstart service with authentication in mongodb way I set username and password in MongoDB every time I have to run this command in terminal:

sudo mongod --auth --port 27017 --dbpath /var/lib/mongodb

As soon as I close terminal, MongoDB db also closes so how will I set MongoDB in startup service?

You need to use sudo service to start MongoDB as a service, check the documentation for further details:

sudo service mongod --auth --port 27017 --dbpath /var/lib/mongodb


Normalization of a table I am trying to normalize the following table but having some difficulties

Mem_id mem_name phone email Book_num Book_titl Author_nam publication edition
100     smith    12443 eml1    200     physics   john         MACGROW   1ST
100     smith    12443 eml1    201     math      martin,tina  MACGROW   2ND
101     hena     1020  eml2    200     physics   john         MACGROW   2ND
102     moon     2020  eml3    204     new java  korth,chira  ALPHA     4TH

1NF:

Member:

Mem_id mem_name phone email

100     smith    12443 eml1

101     hena     1020  eml2

102     moon     2020  eml3

Book:

Mem_id Book_num  Book_titl  Author_name  publication edition

100     200       physics      john        MACGROW      1ST

100     201       math       martin,tina   MACGROW      2ND

101     200       physics      john        MACGROW      2ND

102     204       new java   korth,chira   ALPHA        4TH

2NF:

Member:

Mem_id mem_name phone email

100     smith    12443 eml1

101     hena     1020  eml2

102     moon     2020  eml3

Issue:

Mem_id Book_num

100     200

100     201

101     200

102     204

Book:

Book_num  Book_titl  Author_name  publication edition

200       physics      john        MACGROW      1ST

201       math       martin,tina   MACGROW      2ND

204       new java   korth,chira   ALPHA        4TH

Now i am having difficulty here.Am i on the right way to the normalization ?Is it possible to normalize the book table to 3NF OR it is already in 3NF ?

Actually, you could go a little further and take the Author out and having a new table named Authors and you could be more rigid and take publication as well.

Book:

Book_num  Book_titl  Author_id  publication edition

200       physics      1        MACGROW      1ST

201       math         2        MACGROW      2ND

204       new java     3        ALPHA        4TH

Author:

Author_id Author_name

   1      john

   2      martin,tina

   3      korth,chira

Still, remember that going full onto normalization has drawbacks on performance as well.

It all depends on the business and how you are trying to manage the data.


Upper or Lower Case Optimisation on MS SQL Server Does anyone know if MS SQL Server text queries are optimised for upper or lower case strings?

I read somewhere the it was uppercase, but cant seem to find the reference to it again.  Of course if this is entierly wrong and no optimisation takes place, this would also be useful to know.

Thanks for your help.

CM

I would say it depends on your install of SQL server. The default ones that I have seen are case insensitive.

Run select serverproperty('SQLsortorderName') if you get back nocase_iso it would mean that your searches are case insensitive. So using UPPER would give you the same results, but it would add a bit more work on the CPU.


Conditional MS SQL replication I have an issue that I want to do conditional replication to one of my enterprise level client databases. This database is very large: approximately 300 GB MDF file and a 160 GB LDF file because of archiving and accountability.

My primary DB has lots of accountability data and I want to transfer them to the secondary replicated DB for reference purposes. Once the accountability data has been deleted by a job on the primary server, when the replication job runs again
the secondary DB should retain all the accountability data without overwriting and/or deleting anything.

Does anybody have a suggestion on the best way to do this?

Reference:

Transactional Articles - Specify How Changes Are Propagated
sp_addarticle (Transact-SQL)
Transactional Replication insert only new Records

Yes it is possible with transactional replication.  I am assuming initially you want all data to be replicated to secondary. For that you will do a reinitialize and snapshot. From there on if your article is set up I mentioned below only new inserts will be replicated. For update you can do the same if you chose to.

When using sp_addarticle set @del_cmd  value to NONE.

If you do it from GUI it will look something like this.


Insert if NOT EXISTS not working I am trying to copy data from table to another mysql table from two different databases
First I get all data from first table and stored them into php variable
then I loop on that var to insert data to the other table
using this query

INSERT INTO `users` (`nUserId`, `vLoginName`, `vFirstName`, `vLastName`, `vEmail`)
    VALUES ('$entity_id','$name','$firstname','$lastname','$email')
    WHERE NOT EXISTS (SELECT `nUserId`,`vEmail`
                      FROM `users`
                      WHERE `nUserId`='$entity_id'
                      AND   `vEmail` = '$email')

but it's not working

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE NOT EXISTS (SELECT `nUserId`,`vEmail` FROM users '

What the correct syntax to make this work ?

I think the problem might be VALUES and I would write the statement as the following:

INSERT INTO `users`
    (`nUserId`, `vLoginName`, `vFirstName`, `vLastName`, `vEmail`)
SELECT
    '$entity_id', '$name', '$firstname', '$lastname', '$email'
FROM dual
WHERE NOT EXISTS (SELECT *
                    FROM `users`
                    WHERE `nUserId`='$entity_id'
                    AND   `vEmail` = '$email')


Table partitioning filegroup Can I use the same filegroup for multiple tables when partitioning a table? I partitioned a items table by year. It created five filegroups. Can I use the same file group for orders table or will I need to create separate filegroup?

Technically you can assign any existing/new filegroup to any new partition scheme (for other table).

However, you need to understand the existing partition function and scheme design, and more importantly the purpose of the existing partition. It could be partitioned for improved scalability and performance reason by having filegroup on different disks. Or it could be partitioned to archive older data on slower and cheaper disk. Or partitioned on a same/different disk for quicker subset access like during data loading process. So, the idea is to find out if the purpose of the new table partition inline with existing partition to determine if existing or new filegroup should be used.


how to design movies with show times database structure how to design movies with show times database structure

I'm building simple local cinemas guide website using Yii PHP framework.

I need Correct and improve this database, specially for showtimes

Cinema / done
City / done
Country / done

Movie
  id
  title
  year
  imdb_rate
  cast
  director
  summary
  views
  featured
  available_status

Genre
  id
  name
  movie_id

movie_showing
  id
  available_status
  show_time
20:00

22:00

  movie_id
  cinema_id

movie_video
  id
  youtube_uri
  movie_id
  trailer

movie_photo
  id
  photo_uri
  poster 0 1
  movie_id

I would also suggest the following:

Add a movie genres table - as a movie can have one or more genres
Split up the movie_showing table as follows:

movie_showing - id, movie_id, cinema_id, details and restrictions at cinema level (this means the movie is showing at a specific cinema)
movie_showing_times - movie_showing_id, availability_status, showdate, showtime,  show details (prices, cinema showing, 3D or not, other offers for example half price etc)


MySQL find_in_set function not work as expect Now I am using this command to query user_id not in collections, this is my sql:
select *
from report_user_foundation
where !FIND_IN_SET(user_id ,(
    select GROUP_CONCAT(login_user_ids) as bet_user_ids
    from report_summary rs
    where statistic_time >= 1606752000000)
)

I query user id from table report_user_foundation that not in report_summary table login_user_ids column. but when the using the result user id to query in report_summary that still exists.
select login_user_ids
from report_summary rs
where login_user_ids like '%4685%'

Am I sql is not correct? Am I missing something?

FIND_IN_SET ( GROUP_CONCAT ) -- cleaver, but clunky.  Instead of
select *
from report_user_foundation
where !FIND_IN_SET(user_id ,(
    select GROUP_CONCAT(login_user_ids) as bet_user_ids
    from report_summary rs
    where statistic_time >= 1606752000000)
)

do
SELECT *
    FROM report_user_foundation AS rsf
    WHERE NOT EXISTS(
            SELECT 1 FROM report_summary
                   ON rsf.user_id = login_user_ids
                  AND statistic_time >= 1606752000000
                    )
   /* AND login_user_ids like '%4685%' */

or, alternatively,
SELECT rsf.*
    FROM report_user_foundation AS rsf
    LEFT JOIN report_summary rs
                   ON rsf.user_id = rs.login_user_ids
                  AND rs.statistic_time >= 1606752000000
    WHERE rs.id IS NULL
   /* AND login_user_ids like '%4685%' */

Alas, I don't understand your comment about where login_user_ids like '%4685%'.  Perhaps it goes where I put the comments.
If these tables get big, this composite index may help:
report_summary:  INDEX(login_user_ids, statistic_time)


How can I use user defined variables and `USE`? I have some schemas, all with the same format. The format is: schema name, underscore, single digit (example: database_1).
How can I loop through all of these schemas easily? I tried SET @num = 1; USE database_@num; But it says

  Unknown database 'database_@num'

I'm using MariaDB 10.1.30

The particular statement you want to run - USE - is not supported by dynamic SQL. I therefore suspect what you want to do is not currently possible.

If dynamic SQL did support the USE statement, you could have done:

MariaDB 10.1:

SET @sql = CONCAT('USE database_', @num);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;`

MariaDB 10.3:

EXECUTE IMMEDIATE CONCAT('USE database_', @num);

Both result in:

  ERROR 1295 (HY000): This command is not supported in the prepared
  statement protocol yet

The MariaDB knowledge base has a list of permitted statements that you can PREPARE. Effectively, this is a list of statements that can be used in dynamic SQL.

EDIT:

So instead I suppose you're left with options such as:

Generate your statements as text -- un-roll your loop to a long list of statements, dump them to a .sql file and then SOURCE that file.
Use a scripting language such as Python, bash, PHP etcetera. Do the loop in the scripting language. (Do not use prepared statements for the USE statement, obviously.)


SSMS 2016 CTP3.1 November Preview (v13.0.800.111) bug? properties error: missing column federated_service_account Using SSMS 2016 CTP3.1 November Preview (v13.0.800.111) on my laptop, whenever I right click any database object (the database, a table, etc.) I get the below error.

  TITLE: Microsoft SQL Server Management Studio

  Cannot show requested dialog.

  ADDITIONAL INFORMATION:

  Cannot show requested dialog. (SqlMgmt)

  Attempt to retrieve data for object failed for Server ''.  >(Microsoft.SqlServer.Smo)

  For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=13.0.800.111+((SQL_Main).151121-1556)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attempt+to+retrieve+data+for+object+Server&LinkId=20476

  An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

  Invalid column name 'federated_service_account'.
  Invalid column name 'federated_service_account'. (Microsoft SQL Server, Error: 207)

  For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=13.00.0700&EvtSrc=MSSQLServer&EvtID=207&LinkId=20476

  BUTTONS:

  OK

I was able to run a Profiler trace to determine the query that's being run to throw this error:

SELECT
eds.location AS [RemoteDataArchiveEndpoint],
eds.name AS [RemoteDataArchiveLinkedServer],
rdad.remote_database_name AS [RemoteDatabaseName],
rdad.federated_service_account AS [RemoteDataArchiveUseFederatedServiceAccount],
case when rdad.federated_service_account = 1 then null else cred.name end AS [RemoteDataArchiveCredential]
FROM
sys.remote_data_archive_databases rdad
INNER JOIN sys.external_data_sources eds ON rdad.data_source_id = eds.data_source_id
LEFT OUTER JOIN sys.database_scoped_credentials cred ON eds.credential_id = cred.credential_id

Anyone recognize this query? Where is it coming from, what's the source? Anyone else experiencing a similar error? Is this an SSMS bug?

Some interesting repro notes:

This error happens while connecting from my laptop to the database that's on a VM in my organization.
While RDP'd in to the VM and running SSMS locally on the VM and connecting to localhost, the error does not repro.
The SSMS version on the VM (v13.0.700.242) is lower than the SSMS version on my laptop (v13.0.800.111). Hence, I'm afraid to update the SSMS on the VM to latest version as this error might throw there too.
Running the profiler trace directly on the VM and doing the repro steps does not generate the same SQL that the newer SSMS version seems to generate from my laptop.

Updating the VM's engine to CTP 3.1 resolved the issue. I no longer get the error on my laptop's SSMS.


Problem with the function UNIX_TIMESTAMP Query 1

SELECT SessionInfo.IVRSessionInfoID
FROM   SessionInfo
WHERE  SessionCallTime BETWEEN UNIX_TIMESTAMP('2013-08-01 00:00:00')
                           AND UNIX_TIMESTAMP('2013-08-01 23:59:59')
ORDER  BY SessionInfo.SessionCallTime DESC;

Query 2

SELECT SessionInfo.IVRSessionInfoID
FROM   SessionInfo
WHERE  ( SessionInfo.SessionCallTime BETWEEN '2013-08-01 00:00:00'
                                         AND '2013-08-01 23:59:59' )
ORDER  BY SessionInfo.SessionCallTime DESC;

What is the difference? Why does the first query give 0 rows and the second give records?

In this table there are 20000 rows between these two dates.

My guess:

From: http://dev.mysql.com/doc/refman/5.6/en/date-and-time-functions.html#function_unix-timestamp

  UNIX_TIMESTAMP(), UNIX_TIMESTAMP(date)

  If called with no argument, returns a Unix timestamp (seconds since
  '1970-01-01 00:00:00' UTC) as an unsigned integer. If UNIX_TIMESTAMP()
  is called with a date argument, it returns the value of the argument
  as seconds since '1970-01-01 00:00:00' UTC

In the first query, you're comparing your date to the number of seconds 2013-08-01 00:00:00 and 2013-08-01 23:59:00 are after 1970-01-01, meaning you're comparing your dates to seconds.

In the second query, you're actually comparing date to other dates.


Debuging import of a large dump file to MySQL I'm trying to import a 50GB dump file to AWS RDS instance and have an issue. I get the following error:

ERROR 2013 (HY000) at line 15850: Lost connection to MySQL server during query

In the beaning i got this error after a few minutes, Then i increased the parameter max_allowed_packet to 1G. Now i get this error after 2.5 hours. What could be the issue? Is there any way to debug this issue? I couldn't find any relevant logs.

Here are some of the relevant DB paramameters:

mysql> SHOW VARIABLES like '%innodb_bu%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_dump_at_shutdown | OFF            |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 8              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | OFF            |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 50503614464    |
+-------------------------------------+----------------+
8 rows in set (0.00 sec)

mysql>  SHOW VARIABLES LIKE '%_timeout';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 3600     |
| wait_timeout                | 28800    |
+-----------------------------+----------+
12 rows in set (0.00 sec)

mysql> SHOW VARIABLES like '%max_all%';
+--------------------------+------------+
| Variable_name            | Value      |
+--------------------------+------------+
| max_allowed_packet       | 1048576000 |
| slave_max_allowed_packet | 1073741824 |
+--------------------------+------------+

Please advise.

There are two possibilities for this issue.

Wait timeout
Low resource on the server where you are trying to restore the DB.

Increase the lock_wait_timeout and wait_timeout to 300 seconds.
Try to restore the Database where you have enough resource to export the file(generally if you are using a t2.nano or t2.micro instances(where you are executing the restore command and keep the Backup file), then it'll kill the restore query due to low resource)


Restore Latest Copy of Full and Differential database From Azure Blob Container I'm trying to work out how to Restore the Latest Backup of a Database that is held on Azure Storage Blobs. This container contains other backups from different databases on a server but I'm only interested in Restoring the Latest Full and Diff Backup for a particular Database

I need to do this automatically via powershell or something and schedule it from a job.

I've looked at:
Restore-SqlDatabase but can't see how it will use Azure credentials
DBATools.io - Again nothing to pass in Azure credentials
AzCopy - How do you get the latest Backup?
Azure PowerShell - Again as above.

Any pointers would be greatly appreciated.

Thanks In Advance.

Restore-DbaDatabase does have -AzureCredential as parameter.

Suggest you to download the latest version of dbatools and try it.

Alternatively, you can try ps_restoregene - which is powershell based script written by Paul brewer. (I have used it and its a life saver :-) )


what is oracle shiphome? I am trying to install oracle grid, One of the Oracle installation steps says,

  set the variable ORA_SHIPS to the path location where you are going to
  extract the 11.2.0.4 shiphomes

Please explain what is shiphomes ?

shiphome is nothing but a specified location in Oracle grid  installation environment , Which identify that your runInstaller meets all Oracle installation  requirements. If your installing the grid file , which meet the prerequisite installation required privilege like ( Oracle Home, Central inventory,Meta-data files ,operating system certification & location of file ) then installation  will success. If during installation Verifying environment and performing prerequisite checks if any such specified path not meet then shiphome Error comes.

If you are getting the shiphome error during installation.

Please be sure about that  some points like

1) oracle grid installation has stop before oracle grid patch installtion
2) Before installation of Oracle grid service, sure about the specified node-list path is correct or not.

for your ref https://docs.oracle.com/cd/E11882_01/relnotes.112/e23557/toc.htm  and http://www.snapdba.com/2014/01/oracle-database-11gr2-11-2-0-4-installation-on-oracle-linux-6-4/


What could be the best DBMS to store airbnb data for price prediction I have a school project in which I need to predict the airbnb listings prices in 5 different cities.
So I have about 400,000 records each with 19 columns.
All the examples on the internet that are implemented in python use data frames.. But the goal of my project is to learn how to handle big data in a DBMS.
I was thinking about using RDBMS but I'm not sure at all if it could be good or not.
Any help or hint regarding a good DBMS for my project would be highly appreciated.

You can certainly accomplish this with any modern RDBMS, as 400,000 records is actually small data nowadays.
Since it's a school project, I assume you'll want to use a free edition of the system you choose. PostgreSQL and MySQL are free out the gate, Microsoft SQL Server also offers a free edition called "Express" edition, and I believe even Oracle offers a free edition of their database system as well.
My somewhat subjective recommendation out of those choices would be to use PostgreSQL or Microsoft SQL Server, just because of their mainstream use and plethora of documentation on how to setup, use, and query in either system. And both offer a sufficient amount of features, that I think they'll meet your data needs without a second thought.
The final criteria to help you decide on which one to use should be based off of which procedural programming language / tech stack you're most familiar with (if you need to use one as part of this project). If you're used to the Microsoft tech stack then SQL Server is a fitting choice, otherwise most other cases will plug into PostgreSQL just fine. If you're planning to use Python for the application side, it probably can plug into either database system just as easily.


Achieving "sequence number", rather than dateTime, for updates; postgres specifically So a postgres database (specifically, an AmazonRDS instance fwiw).

It's easy enough to arrange things to get the last updated (or created) time for any record.

That information would be a datetime.

What I want to achieve: imagine an integer, sequenceNumber, which is atomic and global to the database.

Simply, every table would have a sequenceNumber field.

Any time any record (ie, in any table) is updated (or created), it gets the next sequenceNumber.

You know how you have programmer friends who know nothing about databases. Wave. My idiot solution would be, in the entire codebase, every time you create or modify a record, be sure to remember to update sequenceNumber on it. (Of course, just have one global system that gives the next atomic sequenceNumber.)

What's the sound solution for this?

Indeed, is it completely available already in some way I don't know, or?

As a_horse_with_no_name originally suggested in question comments:

--assuming we have tables: a, b, ...
CREATE TABLE a
(
  a_id integer NOT NULL PRIMARY KEY,
  a_name text
) ;

CREATE TABLE b
(
  b_id integer NOT NULL PRIMARY KEY,
  b_name text
) ;

First we create a global sequence and add a column in each table:

-- create one global sequence to be used by all tables
CREATE SEQUENCE global_sequence_number ;

-- add a column in each table, that uses the same sequence
ALTER TABLE a
  ADD COLUMN global_sequence_id INT NOT NULL
    DEFAULT nextval('global_sequence_number'::regclass) ;

ALTER TABLE b
  ADD COLUMN global_sequence_id INT NOT NULL
    DEFAULT nextval('global_sequence_number'::regclass) ;

Create trigger function (one) and UPDATE triggers (one in each table):

-- create a trigger function to update the column
CREATE OR REPLACE FUNCTION update_global_sequence_id()
RETURNS TRIGGER AS
$$
BEGIN
   NEW.global_sequence_id = nextval('global_sequence_number'::regclass);
   RETURN NEW;
END;
$$ language 'plpgsql';

-- add a trigger, in each table
CREATE TRIGGER update_a_global_sequence_id
  BEFORE UPDATE ON a FOR EACH ROW
    EXECUTE PROCEDURE update_global_sequence_id();

CREATE TRIGGER update_b_global_sequence_id
  BEFORE UPDATE ON b FOR EACH ROW
    EXECUTE PROCEDURE  update_global_sequence_id();

And profit:

 -- example of use
INSERT INTO a (a_id, a_name)
VALUES
  (1, 'a'),  (2, 'b') ;

INSERT INTO b (b_id, b_name)
VALUES
  (3, 'c'),  (4, 'd'),  (5, 'e') ;

SELECT *
FROM a FULL JOIN b ON FALSE
ORDER BY COALESCE(a.global_sequence_id, b.global_sequence_id) ;

-- output
  1   a  1
  2   b  2
             3   c  3
             4   d  4
             5   e  5

And after some updates, to check that the triggers work:

-- lets do some updates:
UPDATE a SET a_name = 'aa' WHERE a_id = 1 ;
UPDATE b SET b_name = 'cc' WHERE b_id = 3 ;

-- and see what happened
SELECT *
FROM a FULL JOIN b ON FALSE
ORDER BY COALESCE(a.global_sequence_id, b.global_sequence_id) ;

-- output
  2   b  2
             4   d  4
             5   e  5
  1  aa  6                   -- sequence updated to 6
             3  cc  7        -- sequence updated to 7


One foreign key for two columns I have two tables, Results and Teams

Teams has the following columns:

team_id
team_name

Results has the following columns:

match_id
home_team (foreign key references teams [team_id])
away_team (foreign key references teams [team_id])
score
yellow_cards
red_cards

When I write query:

select team_name, score
FROM teams, results
WHERE results.home_team = teams.team_id
  AND results.away_team = teams.team_id

it would give me empty set.

Is it issue with the fact that I use same foreign key for two different columns in the same table?

The query results are correct, as your query finds all teams and results where a team played against itself (home_team = away_team).

You probably want to use 2 joins to teams in you query:

SELECT
    home.team_name AS home_team_name,
    away.team_name AS away_team_name,
    r.score
FROM
    results AS r
  JOIN teams AS home
    ON r.home_team = home.team_id
  JOIN teams AS away
    ON r.away_team = away.team_id ;


Missing right parenthesis after (or before) default 0, Oracle I am trying to create a table with Oracle SQL where two columns of the table should have a default value and should not be NULL.

I am getting the error you can see below, but i am not able to understand it, because the are no parenthesis to be closed there!

The Script

 CREATE TABLE ENCUENTROS (
  elocal constraint clave_extrana_equipos references equipos(code) not null enable,
  evisitante constraint clave_extrana_equipos references equipos(code) not null enable,
  fecha date,
  plocal number constraint plocal_mayor_cero check (plocal > 0) default 0 not null enable,
  pvisitante number constraint pvisitante_mayor_cero check (pvisitante > 0) default 0 not null enable);

The Error Message

plocal number constraint plocal_mayor_cero check (plocal > 0) default 0 not null enable,
                                                              *
ERROR at line 5:
ORA-00907: missing right parenthesis

* is where Oracle detected the error

I'm at loss as to why there should be an error at that position.

The problem is with the ordering of the clauses. If you move the CHECK constraint after the DEFAULT 0 clause, then the table creates:

CREATE TABLE ENCUENTROS (
  elocal constraint clave_extrana_equipos_1 references equipos(code) not null enable,
  evisitante constraint clave_extrana_equipos_2 references equipos(code) not null enable,
  fecha date,
  plocal number default 0 not null enable constraint plocal_mayor_cero check (plocal > 0),
  pvisitante number default 0 not null enable constraint pvisitante_mayor_cero check (pvisitante > 0)
);

or

CREATE TABLE ENCUENTROS (
  elocal constraint clave_extrana_equipos_1 references equipos(code) not null enable,
  evisitante constraint clave_extrana_equipos_2 references equipos(code) not null enable,
  fecha date,
  plocal number default 0 constraint plocal_mayor_cero check (plocal > 0) not null enable,
  pvisitante number default 0 constraint pvisitante_mayor_cero check (pvisitante > 0) not null enable
);

Note that I had to give different names to the two foreign key constraints.

BTW, there is a bit of a oddity in your definition: you require a value for local and pvisitante and if none is passed, then you use 0. But the check contraint requires the value to be >0. Meaning that any insert that does not provide a value will fail ... The error will be something like constraint pvisitante_mayor_cero failed validation or similar.

Maybe a simpler approach would be to remove the default clause. This will then make an insert that does not include pvisitante fail with a different error: cannot insert null into ENCUENTROS (PVISITANTE). That may be clearer to application developers.


Barman shows huge variance in WAL size I'm using barman to take backups from postgresql. This is the result of list-backup:

prod 20190831T000005 - Sat Aug 31 00:04:58 2019 - Size: 16.0 GiB - WAL Size: 654.2 MiB
prod 20190830T000005 - Fri Aug 30 00:04:41 2019 - Size: 16.1 GiB - WAL Size: 2.1 GiB
prod 20190829T000004 - Thu Aug 29 00:04:10 2019 - Size: 16.0 GiB - WAL Size: 1.3 GiB
prod 20190828T000004 - Wed Aug 28 00:03:21 2019 - Size: 15.9 GiB - WAL Size: 872.6 MiB
prod 20190827T204148 - Tue Aug 27 20:48:11 2019 - Size: 15.9 GiB - WAL Size: 129.3 MiB

As you can see, there is a huge variance in the size of WALs reported here. What is the reason? And is it ok?

barman 2.9
postgresql 9.6

It is the amount of WAL that would need to be replayed to bring one backup up to the next-in-time (previous in the order listed) backup.  If you delete the first backup, it is the amount of WAL that would also be deleted, as being no longer useful.

They are different sized because your database has seen different amounts of activity between your backups.


How to detect a running mysqldump? Here are the things I can't change:
We got a mySQL myISAM replication running a master and some slaves. One slave is being used for nightly backups using mysqldump. The backups is using heavy locks forcing it's slave to "age" compared to the master.
mysqldump is connecting as root dumping all the databases while every other connection is using one non-SUPER-user per database. The dumps are started using a simple shell script on a dedicated backup server.

Here is the problem:
I'ld like to detect if a backup is running or not while being connected as a "normal" user to one of the databases running on the server being backuped. Cronjobs should be able to react on the running backup by exiting early or processing only really important required jobs which can't wait until the backup is done.
The server running the backup script has no connections to the webservers, their only common point is the mysql server.
The backup script could do some things to flag the server as "currently running a backup".
I'd prefer to set some kind of "global server-side environment variable visible by all users" to ignore only the one slave running the backup but it would also be ok to simply know that a backup is running on that database or replication chain.
Using the database for this is no option as it's being locked during the backup.

I tried to create a temp table on backup start (existing = backup is running, not existing = no backup running), but temp tables only show up for the connection which originally created it (as expected). I googled if a non-SUPER-user could get the permission to see the SHOW PROCESSLIST of all connections including other users and root (any connection from the backup server = backup is running) - but no luck. Giving SUPER to all database users is no option.

Any suggestions?

Thanks for reading (and answering).

Your only hope in this scenario is to have the PROCESS privilege.
The SUPER privilege allows you to kill processes. Naturally, you don't want that. On the other hand, the PROCESS privilege allows you to see the processlist.
According to the MySQL Documentation on the PROCESS Privilege

The PROCESS privilege pertains to display of information about the
threads executing within the server (that is, information about the
statements being executed by sessions). The privilege enables use of
SHOW PROCESSLIST or mysqladmin processlist to see threads belonging to
other accounts; you can always see your own threads.

OK, Big Deal. The PROCESS Privilege lets you see the Process List. How does that help?
You can quickly detect a mysqldump in progress when you run SHOW PROCESSLIST; and see a pattern like this in the Info field:
SELECT /*!40001 SQL_NO_CACHE */ * FROM

If you have the PROCESS Privilege and are running MySQL 5.1+, you can run this query:
select COUNT(1) mysqldumpThreads
from information_schema.processlist
where info like 'SELECT /*!40001 SQL_NO_CACHE */%';

Here is a sample output:
mysql> select COUNT(1) mysqldumpThreads
    -> from information_schema.processlist
    -> where info like 'SELECT /*!40001 SQL_NO_CACHE */%';
+------------------+
| mysqldumpThreads |
+------------------+
|                1 |
+------------------+
1 row in set (0.00 sec)

All you need to do is query for that number. If you get a nonzero, mysqldump must be running.
Give it a Try !!!


Postgres row_to_json precision for numeric type I am using the row_to_json function to convert a table row to a json object.
There are a few columns in the table that are of type numeric which contain float values.

When row_to_json returns the json, values like 9.98 becomes 9.98000000000000043 or 6.30 becomes 6.29999999999999982 in these numeric columns.

Is there a way to impose precision at the json creation function? There are no other data issues in the table which are affecting other modules.
Changing the column type to numeric(12,2) etc. is not possible since the table has over 2 billion rows(the table would be locked for 30+hours) and would affect other production modules.

--update--

I should have made the question a little bit clearer with the use case.

row_to_json is used in a trigger to audit updates in the DB. The json result is stored in another table and this same audit function is used for  multiple tables(100+) where there may be other tables with this same issue.

One solution would be to handle it at the application level when retrieving the jsons from the audit table, but I would like to see if there is a function that can handle it in the DB itself.

You can round the values:

select row_to_json(t)
from (
  select id,
         round(value_1, 2) as value_1,
         round(value_2, 2) as value_2
  from test
) t;

If you need that more often and don't want to type it all the time, create a view that returns the rounded values.


In which context does a procedure run when called from another context? Envisage:

use databaseA;
go

exec databaseB.dbo.spProcedure();
go

Does the procedure execute against databaseA - the context from which it is called, or databaseB - the context in which it exists?

If the procedure had

insert into myTable(example) values ('example');

... and if both databases had identical tables myTable, which database will receive the new record?

It executes in the context of the database the object exists in. Example code:

CREATE DATABASE Temp1
GO
USE [temp1]
GO
CREATE TABLE Data1 (Val VARCHAR(255))
GO
INSERT INTO Temp1.dbo.Data1 (Val)
VALUES ('Test1')
GO
CREATE PROCEDURE spTemp1
AS
BEGIN
    SELECT * FROM dbo.Data1
END
GO

CREATE DATABASE Temp2
GO
USE [temp2]
GO
CREATE TABLE Data1 (Val VARCHAR(255))
GO
INSERT INTO Temp2.dbo.Data1 (Val)
VALUES ('Test2')
GO
CREATE PROCEDURE spTemp2
AS
BEGIN
    SELECT * FROM dbo.Data1
END
GO

USE [Temp1]
GO
EXEC Temp2.dbo.spTemp2
GO

USE [master]
GO
DROP DATABASE Temp1
DROP DATABASE Temp2
GO

This highlights that even though we query "dbo.Data1" which exists in both databases, the value returned is from that table in the Temp2 database because that is the context of the stored procedure.

The example is a SELECT, but the same holds true for an INSERT.


Lost SQL credentials and unable to connect to SQL via SQLCMD I have a machine running SQL 2014 but have neither the windows authentication or sa authentication details due to mismanagement of a previous support team.

Having tried logging in to SQL Management Studio using my Microsoft AD account I received the following error:

  ===Cannot connect to BACKUP-MGMT-01\CONSULT.===
  A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.) (.Net SqlClient Data Provider)

I suspect this is due to my account not having privileges.

I then found some steps online allowing you to add a new sa or windows authenticated user via SQLCMD. However, when I run either the SQLCMD -S .\BACKUP-MGMT-01\CONSULT or SQLCMD -S .\BACKUP-MGMT-01 I just receive the following error:

  C:\Users\frank>SQLCMD -S .\BACKUP-MGMT-01\CONSULT
  Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : SQL Server Network Inte
  rfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. .
  Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : Login timeout expired.
  Sqlcmd: Error: Microsoft ODBC Driver 11 for SQL Server : A network-related or in
  stance-specific error has occurred while establishing a connection to SQL Server
  . Server is not found or not accessible. Check if instance name is correct and i
  f SQL Server is configured to allow remote connections. For more information see
   SQL Server Books Online..

Can anybody assist?

The correct format of the parameter -S for the SQLCMD prompt is: Server\Instance.

You seem to have tried localhost\instance\database which doesn't work.

SQLCMD -S .\BACKUP-MGMT-01\CONSULT
          ^^        ^     ^   ^
          ||        |     |   +--- Database
          ||        |     +------- Separator
          ||        +------------- Instance
          |+---------------------- Separator
          +----------------------- Server (. = localhost)

In any case the error message is stating you are having issues with the server name and/or instance name.

If your server is named BACKUP-MGMT-01 and your instance is named CONSULT then you might want to try:

SQLCMD -S BACKUP-MGMT-01\CONSULT -E

If your instance is really named BACKUP-MGMT-01 then you might want to try:

SQLCMD -S .\BACKUP-MGMT-01 -E -d CONSULT

Where -d is the parameter for database and -E is for trusted connections.

Reference Reading

sqlcmd Utility (Microsoft | Docs | SQL Docs)


SQL server row count I had an activity for restoring a full database in SQL server 2008.
I had to restore the database will norecovery since I had to restore the differential backups as well which will be shared everyday.
My query is how do I check the row count of a database which is under recovery?
Thank you.

You can RESTORE the database and leave it in STANDBY mode.  Then it will be read-only, but you can still apply additional backups.  EG
drop table if exists AdventureWorks2017.dbo.foo
create table AdventureWorks2017.dbo.foo(id int identity, a int)

go
backup database AdventureWorks2017 to disk='c:\temp\aw.bak' with init, format

insert into AdventureWorks2017.dbo.foo(a) values (1)

backup database AdventureWorks2017 to disk='c:\temp\aw.diff.01.bak' with init, format, differential

insert into AdventureWorks2017.dbo.foo(a) values (1)

backup database AdventureWorks2017 to disk='c:\temp\aw.diff.02.bak' with init, format, differential

go

RESTORE DATABASE [AdventureWorks2017_copy]
FROM  DISK = N'C:\temp\aw.bak'
WITH
MOVE N'AdventureWorks2017' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2017_copy.mdf',
MOVE N'AdventureWorks2017_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2017_copy_log.ldf',
NOUNLOAD,
STATS = 5,
STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2017_standby.bak'

go
select * from AdventureWorks2017_copy.dbo.foo

go

RESTORE DATABASE [AdventureWorks2017_copy]
FROM  disk='c:\temp\aw.diff.01.bak'
WITH STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2017_standby.bak'

go

select * from AdventureWorks2017_copy.dbo.foo

go

RESTORE DATABASE [AdventureWorks2017_copy]
FROM  disk='c:\temp\aw.diff.02.bak'
WITH STANDBY = N'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\AdventureWorks2017_standby.bak'

go

select * from AdventureWorks2017_copy.dbo.foo

outputs
id          a
----------- -----------

(0 rows affected)

Processed 112 pages for database 'AdventureWorks2017_copy', file 'AdventureWorks2017' on file 1.
Processed 1 pages for database 'AdventureWorks2017_copy', file 'AdventureWorks2017_log' on file 1.
RESTORE DATABASE successfully processed 113 pages in 0.056 seconds (15.651 MB/sec).
id          a
----------- -----------
1           1

(1 row affected)

Processed 112 pages for database 'AdventureWorks2017_copy', file 'AdventureWorks2017' on file 1.
Processed 1 pages for database 'AdventureWorks2017_copy', file 'AdventureWorks2017_log' on file 1.
RESTORE DATABASE successfully processed 113 pages in 0.056 seconds (15.651 MB/sec).
id          a
----------- -----------
1           1
2           1

(2 rows affected)


GO After every T-SQL statement What is the reasoning behind using the GO statement after every SQL statement? I understand that GO signals the end of batch and/or allows the reputation of statements but what advantage does it have using it after every statement.

I am just curious as a lot of Microsoft documentation etc. have started using it after every statement  or maybe I've just started to notice.

Also what's considered best practice?

Before answering when to use it and why, it's first paramount in understanding exactly what GO is, and what it isn't.

The keyword GO is used by SQL Server Management Studio and SQLCMD in order to signify one thing and only one thing:  The end of a batch of statements.  In fact, you can even change what you use to terminate batches to something other than "GO":

That above screenshot is an option within SSMS that is configurable.

But what is a batch??  This BOL reference says it best:

  A batch is a group of one or more Transact-SQL statements sent at the same time from an application to SQL Server for execution.

Simple as that.  It's just a custom way an application (yes... an application) sends statements to SQL Server.  Let's see an application-looking example of this.  I'll use PowerShell to mimic what an application would do to send statements and batches to SQL Server:

$ConnectionString = "data source = SomeSQLInstance; initial catalog = AdventureWorks2012; trusted_connection = true; application name = BatchTesting;"

try {
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)
    $SqlCmd = New-Object System.Data.SqlClient.SqlCommand
    $SqlCmd.Connection = $SqlConnection

    # first batch of statements
    #
    $SqlCmd.CommandText = "
        select * from humanresources.department where departmentid = 1;
        select * from humanresources.department where departmentid = 2;
        select * from humanresources.department where departmentid = 3;
        select * from humanresources.department where departmentid = 4;"

    # execute the first batch
    #
    $SqlConnection.Open()
    $SqlCmd.ExecuteNonQuery()
    $SqlConnection.Close()

    # second batch of statements
    #
    $SqlCmd.CommandText = "
        select * from humanresources.department where departmentid = 5;
        select * from humanresources.department where departmentid = 6;
        select * from humanresources.department where departmentid = 7;
        select * from humanresources.department where departmentid = 8;"

    # execute the second batch
    #
    $SqlConnection.Open()
    $SqlCmd.ExecuteNonQuery()
    $SqlConnection.Close()
}
catch {
    $SqlCmd.Dispose()
    $SqlConnection.Dispose()
    Write-Error $_.Exception
}

The comments give it away, but you can see above that we're programmatically sending two batches to SQL Server.  Let's verify that, though.  My choice here is to use Extended Events:

create event session BatchTesting
on server
add event sqlserver.sql_batch_starting
(
    set
        collect_batch_text = 1
    where
    (
        sqlserver.client_app_name = N'BatchTesting'
    )
),
add event sqlserver.sql_batch_completed
(
    set
        collect_batch_text = 1
    where
    (
        sqlserver.client_app_name = N'BatchTesting'
    )
),
add event sqlserver.sql_statement_starting
(
    set
        collect_statement = 1
    where
    (
        sqlserver.client_app_name = N'BatchTesting'
    )
),
add event sqlserver.sql_statement_completed
(
    set
        collect_statement = 1
    where
    (
        sqlserver.client_app_name = N'BatchTesting'
    )
)
add target package0.event_file
(
    set
        filename = N'<MyXelLocation>\BatchTesting.xel'
);
go

alter event session BatchTesting
on server
state = start;
go

All this XEvents session is doing is capturing the statements and batches that start and complete from an application named "BatchTesting" (if you notice my connection string in my PowerShell code example, it's a quick way to look at a particular originator of events by using the "application name" connection string parameter and filtering off of that).

After I execute the PowerShell code to send those batches and statements, I see the following results:

As you can see from the screenshot, it's clear how the statements are divided up into the two different batches, also evident by the means we used to call the batches.  And if we look in the batch_text of the first occurrence of sql_batch_starting, we can see all of the statements included in that batch:

    select * from humanresources.department where departmentid = 1;
    select * from humanresources.department where departmentid = 2;
    select * from humanresources.department where departmentid = 3;
    select * from humanresources.department where departmentid = 4;

With that explanation of what a batch is, now comes the answer to your question of when to terminate batches.  The rules for batches are found on this BOL reference regarding batches:

  CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE
  SCHEMA, CREATE TRIGGER, and CREATE VIEW statements cannot be combined
  with other statements in a batch. The CREATE statement must start the
  batch. All other statements that follow in that batch will be
  interpreted as part of the definition of the first CREATE statement.

  A table cannot be changed and then the new columns referenced in the
  same batch.

  If an EXECUTE statement is the first statement in a batch, the EXECUTE
  keyword is not required. The EXECUTE keyword is required if the
  EXECUTE statement is not the first statement in the batch.

Likewise, certain runtime errors (compile errors won't allow the execution of a batch to start) that occur during a batch can cause different behaviors:  to abort the batch totally, or to continue the batch and only abort the offending statement (the above link gives two really good examples:  An arithmetic overflow error, for instance, will stop the execution of the batch, whereas a constraint violation error will only prevent the current statement from completing but the batch will continue executing).

Like many things in our profession, though, personal preference will be a huge driving force behind how you as an individual and writer of T-SQL code terminate batches.  Some people only explicitly define batches when they absolutely have to (see above for those requirements), and others terminate batches programmatically 100% of the time, even when they are only executing a single statement in a Query Window in SSMS.  Most people typically fall somewhere in the middle of those two boundaries.  For what it's worth, statement terminators have a same following with also very few enforced requirements.  A big part of all of this is code style, where it isn't enforced (in SSMS and SQLCMD).


Shrink database after drop table? I had a table with 70+ million records in a SQL Server database, I dropped that table (a one-time thing) to free up space on the disk, but it looks like the size didn't change much. I do see that I could shrink the db to a minimum.

Is that the way to do it?

The last time I did a shrink using SQL Server Management Studio, it took a few hours to complete. Is there a faster way?

Dropping a table will free up the space within the database, but will not release the space back to Windows.  That requires shrinking the database file.  However we don't want the database file full.  We want lots of free space so that as we load more data we don't have to grow the data file frequently.  This causes fragmentation of the data file on the physical disks.

As for making shrinking faster, no there's no way to make it faster.  Shrinking the database requires reading and rewriting most of the data within the database so that all the white space can be released from the database file back to the OS.  All this IO takes time, and causes a lot of fragmentation problems.


What is the available SQL Server Express database size? SQL Server Express allows the maximum of 10GB/10240MB for a database size.
I have a database which currently is ~8000 MB in size. The unallocated space is ~7000 MB.
The question is: how many megabytes are available for growth/storing data on this database from that moment on?
Is it ~2000MB or ~9000MB?
What will happen if the database .mdf file reaches the maximum of 10240MB while the unallocated space is still around 7-8 000 MB?
Will it prevent new records in the database from that moment on, or it will start using the unallocated space?
Thanks!

I haven't used SQL Express personally but from my understanding of how Microsoft's licensing works you'll be able to grow to the 10 GB limit, but then if the file tries to grow any further the operation will fail.
The limit is the allocated size of the MDF file. So technically you have about 2 GB left. Once your MDF is 10 GB, even if not all of that space is used, you're at the limit per the licensing agreement. You should still be able to use the server and fill the unused allocated space up though (without incurring any further file growths) and still be compliant.