texts: How can I create a recursive query over a column that has gaps?

texts:
I've adapted the recursive query from here in order to calculate ELO ratings (like in chess) from a list of game outcomes on-the-fly.

Given a table of games like:

id | home_player | away_player | home_score | away_score |
|--|-------------|-------------|------------|------------|
1  | 1           | 2           | 21         | 18         |
2  | 2           | 3           | 14         | 21         |
3  | 1           | 3           | 12         | 21         |
5  | 3           | 2           | 21         | 8          |

I want to construct a table of ELO ratings for each player after each game has been played, like this:

| current_game_number | player_id | previous_elo | new_elo |
|---------------------|-----------|--------------|---------|
| 0                   | 1         | 1000         | 1000    |
| 0                   | 2         | 1000         | 1000    |
| 0                   | 3         | 1000         | 1000    |
| 1                   | 3         | 1000         | 1000    |
| 1                   | 2         | 1000         | 984     |
| 1                   | 1         | 1000         | 1016    |
| 2                   | 1         | 1016         | 1016    |
| 2                   | 2         | 984          | 969     |
| 2                   | 3         | 1000         | 1015    |
| 3                   | 3         | 1015         | 1031    |
| 3                   | 2         | 969          | 969     |
| 3                   | 1         | 1016         | 1000    |

I can get most of the way there with this recursive query:

WITH RECURSIVE p(current_game_number) AS (
  SELECT
    0               AS id,
    id as player_id,
    1000.0 :: FLOAT AS previous_elo,
    1000.0 :: FLOAT AS new_elo
  FROM players
  UNION ALL
  (
    WITH previous_elos AS (
        SELECT *
        FROM p
    )
    SELECT
      games.id,
      player_id,
      previous_elos.new_elo AS previous_elo,
      round(CASE WHEN player_id NOT IN (home_player, away_player)
        THEN previous_elos.new_elo
            WHEN player_id = home_player
              THEN previous_elos.new_elo + 32.0 * ((CASE WHEN home_score > away_score THEN 1 ELSE 0 END) - (r1 / (r1 + r2)))
            ELSE previous_elos.new_elo + 32.0 * ((CASE WHEN away_score > home_score THEN 1 ELSE 0 END) - (r2 / (r1 + r2))) END)
    FROM games
      JOIN previous_elos
        ON current_game_number = games.id - 1
      JOIN LATERAL (
           SELECT
             pow(10.0, (SELECT new_elo
                        FROM previous_elos
                        WHERE current_game_number = games.id - 1 AND player_id = home_player) / 400.0) AS r1,
             pow(10.0, (SELECT new_elo
                        FROM previous_elos
                        WHERE current_game_number = games.id - 1 AND player_id = away_player) / 400.0) AS r2
           ) r
        ON TRUE
  )
) SELECT * FROM p;

However, If there is a gap in the game ID sequence, as in the example table above, the query stops at the gap and doesn't continue to calculate ELOs. I would like to support gaps so that games can be deleted and concurrent inserts are supported.

I'm lost as to how to extend the query to "skip gaps" in the id sequence. My hunch is it would require changing the recursive join to skip the gaps:

FROM games
  JOIN previous_elos
    ON current_game_number = games.id - 1

Does anyone have a suggestion to fix this? Any help is very appreciated!

Please see this SQLFiddle which is populated with the schema and sample data.


texts:
I have got a question about the fastest way to copy a large number of records (around 10 million) from 1 table to another in MySQL.

The target table has 1 primary key, 4 indexes and 3 foreign key constraints. The source table has the same primary key, 3 equal indexes, no foreign keys.
Before copying I can truncate the target table, so it's not necessary to any duplicate key check (source table has the same primary key after all).

On my local development machine I have experimented with insert into, insert ignore into, insert into ... on duplicate key update, replace into.  The fastest result so far has been around 75 minutes (equal results for replace into and insert ignore into). Is there a faster way to do this?


texts:
I have a large partitioned time series table where each partition is stored in it's own file. Since it's time series based partitioning divided by week and I'm only storing data that is new I have never more than 2 partitions I'm actively writing to at once. I have a FILLFACTOR set to 75 on the index since the data is coming in somewhat randomly.

The old data partitions are taking up quite a bit of space, utilizing only about 60-70% of that space. Is it possible to automatically change the FILLFACTOR on said partitions, rebuild the indexes and shrink the files? Since I'm not writing to those partitions anymore they shouldn't grow back in size.


texts: Move data from a table to another without losing data

texts:
I have a log table which is currently holding millions of records. I want to enable partitioning on that table, so what I did for now is:

Created a partition function and a partition scheme.
Created an empty table with the same structure on that partition scheme.
Copied data from the current log table from this point in time (let's call it T1) going backwards to the new partitioned table.

The next steps would be to copy the last remaining records from T1 to Tnow and rename the two tables so that the application starts to write to the new partitioned table.

Of course the log table is frequently accessed, so my question is:

How can I make sure that I don't lose any data during this process? Can i make it so that the users don't notice anything or do I necessarily need to stop the application for this brief time? Or can I just block that table so that the application keeps running? If so, how?


texts: Search and Replace in multiple MySQL databases

texts:
I have multiple MySQL databases(50+) and I want to replace a string in all of them, is there anyway to search and replace in all the databases?

Server is CentOS and I have SSH root access.


texts:
How to do this ?

Change the Hosts files on both SQL nodes to show 127.0.0.1 LocalHost


texts:
I have created a user say A. I want to export some of my schemas using A user.

For example, I have B and C users. I want to export B schemas using A user. Which grant do I have to use?

I am using this statement:

EXP FILE = B.DMP OWNER(B)

But it is not working. A user has only select grant on B schemas. There is no other grant A has.

Which grant do I have to use?


texts: Help wanted: Special case of changing columns and rows MySQL

texts:
Im beginner in MySQL and want to convert this table

Any help? Thanks


texts: Can I make something similar to a makefile for psql?

texts:
Basically I have a handful of .sql files that I regularly modify in structure that need to be run in a certain order:

CREATE.sql,
INSERT.sql,
PLPSGSQL.sql,
TEST.sql,
DROP.sql

Since every structural modification requires a full re-run of this sequence I would like to save myself the trouble of tapping throgh my command history and compile them into a makefile-esque structure; e.g.

$ Makepsql
$ psql
> \i CREATE.sql
> \i INSERT.sql
> \i PLPSGSQL.sql
> \i TEST.sql
> \i DROP.sql
> \q

Is there something like this?


texts: Configure unconstrained delegation for BULK INSERT

texts:
I have a pair of Microsoft SQL Server 2016 nodes in an Always On Availability Group. I am trying to perform a BULK INSERT (using a SQL Server 2016 Management Studio query) on a file located on a Windows Server 2016 File Server Failover Cluster, but I get the following error:

  Msg 4861, Level 16, State 1
  Cannot bulk load because the file "\nas2.my.domain\Microsoft SQL Server 2016 Enterprise\test.txt" could not be opened. Operating system error code 5(Access is denied.).

This will occur regardless if I use the active node name (nas2.my.domain) or the failover cluster listener (nas.my.domain).

After looking around I found out this was due to the SQL Server being unable to impersonate the user account I am connected with due to nuances with BULK INSERT.

If you connect to the SQL Server using Windows Authentication, the SQL Server service account attempts to impersonate your user account when connecting to the file server. If you connect using SQL Server authentication, it will connect to the file server as the SQL Server service account.

If delegation and impersonation are not configured properly (the default state), the SQL Server service will not be able to impersonate your user account and will fall back to trying to connect to the file server as an anonymous user.

This can be confirmed by looking through the security event log on the file server. These facts along with a guide on configuring unconstrained and constrained delegation is documented in these links:

How To: SQL Server Bulk Insert with Constrained Delegation (Access is Denied)
Bulk Insert and Kerberos

I've tried following the instructions in thesqldude's guide but it still isnt working.

The database I am trying to BULK INSERT to is not part of the availability group so only the MSSQL1 node should be relevant. The File Server was active on the NAS2 node. Checking the event log on the file server does show that it is still suffering from this problem and the SQL Server is trying to authenticate to the file server as an anonymous user rather than impersonating my user account.

Does anyone know what is going wrong? Or if something changed in SQL Server 2016 to make these guides obsolete?

File Server Security Event Log Entry
Service Account Delegation
Service Account SPNs
SQL Server #1 Computer Account Delegation
File Server #2 Computer Account SPNs
Group Policy Objects
sys.dm_exec_connections - Kerberos

I can confirm that this GPO has been applied to MSSQL1 via gpresult.exe /R, and both the SQL and File Server nodes were rebooted afterwards to ensure any caches have been flushed.


texts:
I have the problem:

  Find all the customers that have an order that was handled by employee number 129 and count all the orders they made.

What I tried:

select c.id, c.fname, count(*)
from customer c inner join sales_order so
on c.id = so.cust_id
group by c.id, c.fname
having so.sales_rep = 129;

But it isn't correct and the answer is:

select c.id, c.fname, count(*)
from customer c inner join sales_order so
where c.id = so.cust_id and c.id in (
select s.cust_id
from sales_order s
where s.sales_rep = 129)
group by c.id, c.fname;

Isn't the keyword having in SQL like a condition that runs on each group? Then why won't all the groups that have so.sales_rep = 129 come up?

These are the outputs: https://pastebin.com/J9LD1L9L


texts: Can SQL Server Always Encrypted be used for temporary tables?

texts:
I'm trying to determine if I can use Always Encrypted on a column in a temporary table (SQL Server 2016 SP1 Enterprise on Windows 2016).  I've tried as follows:

CREATE TABLE #listofdates (compare_dt datetime2(7) encrypted with (
    COLUMN_ENCRYPTION_KEY = [ColEnc1]
    , ENCRYPTION_TYPE = Deterministic
    , ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
    ) NOT NULL
)

However, I get the error:

  Msg 33293, Level 16, State 8, Line 3
  Cannot find the column encryption key "ColEnc1" because it does not exist or you do not have permissions.

I think this error is misleading, because the statement works if I simply replace table name #listofdates with another name without the leading # (therefore switching from a temporary table to an ordinary table).

The section on the MS site that lists limits/restrictions on Always Encrypted doesn't mention temporary tables.


texts:
I have three tables: items, members, subscriptions.
One item can be get by 1 or more members (one member can get more items), so subscriptions represents the many-to-many relationship between items and members.
So we have:

items(id, name)
members(id, name, email)
subscriptions(*id_item*, *id_member*)

And now my problem: I have a quite comlicated query which manage items and outputs items data, I want to add to this output a column that contains the number of members who subscribed that item.
I wrote this query but I don't want to use sub select statement

SELECT
(SELECT count(*) AS current_sub from subscriptions AS s where s.id_item = i.id) AS current_members,
  -- ..... other code....
FROM items AS i
WHERE
// ....other code...

I use MySQL 5.5. Thanks in advance.


texts: I've created a view for a report, it now takes 30 minutes to run - Any ideas?

texts:
View the actual execution plan here

The plan is showing what happens when a stored procedure is called to get the payments that are associated with a client.

Unfortunately there is no other way to get this data than by inner joining a whole bunch of tables together.

Objec5.Index11 is on the Payments table (nothing sensitive, just a record from the gateway to confirm the transaction). This is 457mb of data according to SQL Sentry Plan Explorer. The rest of the clustered index seeks following on from the inner joins are all roughly the same amount of data equating to somewhere between 10% and 13% of the execution cost of this query.

My questions are:

What is the best approach to getting data from multiple tables? I've joined them together via inner joins and dropped them into a view
Should the view have a clustered index? Can this only be achieved with schemabinding? If so, will this prevent field level changes to all tables within the view?
Would having better estimated row values help SQL Server run this query more efficiently? Or is the approach so fubar that this just is not efficient by design?
Is there anything i can do in the short term to boost the performance of this query?

Any help would be appreciated.

I've provided responses to each of the questions below:

SET STATISTICS IO ON output here
All of the tables have a clustered index on them on the primary key
The query consistently performs poorly. However, modifying the query to join on the tables themselves as opposed to views speeds the time the stored proc takes to run quite significantly (from 20 mins to 6 mins) but it still is not fast enough for production
The query used to run within 1 - 2 minutes. Now the fastest is 6 minutes
Index11 is the payments table, there are 1,570,473 rows according to the execution plan actual rows. This is the same table where there were over 4 million logical reads and 1.4 million scans - This does not seem normal to me or conducive to performance? I could be wrong ...


texts:
I am getting a syntax error for my MySQL (5.5.24) query.

The error is:

  #1064 - 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 ''topics'( 'topicid' int unsigned not null auto_increment primary
  key, 'creator' at line 1

The query is:

create table 'topics'(
  'topicid' int unsigned not null auto_increment primary key,
  'creator' varchar(255) not null,
  'createtime' datetime not null,
  'content' text not null
);

What's wrong?


texts: Get a list of databases that have a certain table

texts:
I'm trying to get a list of databases from a Postgresql instance that contains a certain table.  I've started using the system catalog tables to try and figure this out.  I was trying to use pg_database, pg_class, pg_tables but I couldn't seem to find a way to link pg_class/pg_table back to pg_database to get the database for a given table.

If anyone has any ideas please let me know.  It's probably just something stupid that I'm misising.

Thanks in advance.


texts: Conditional Query selection on the basis of paarameters

texts:
I have two sql:

1st:

Select emp.id from Employee
where '&BI'=I
UNION ALL
Select emp.id from Employee
where '&BI'<>I

2nd:same as first one but addition of few more tables and more union all

(
Select emp.id,sal.salary from Employee emp, Salary sal
where '&BI'=I AND emp.id=100 AND '&BI' Is not null
UNION ALL
Select emp.id,sal.salary from Employee emp, Salary sal
where '&BI'<>I AND emp.id=100 AND '&BI' Is not null
)
UNION ALL
(
Select emp.id,sal.salary from Employee emp, Salary sal
where '&BI'=I AND emp.id=sal.id AND '&BI' Is not null
UNION ALL
Select emp.id,sal.salary from Employee emp, Salary sal
where '&BI'<>I AND emp.id=sal.id AND '&BI' Is not null
)

want to combine both 1st and 2nd in to one select statement and run it dynamically.
Please help!!


texts: How to capture error message from one step in another step of an Agent job?

texts:
Is it possible to capture error message from one step in another step of a SQL Server Agent job?

I have below scenario:

Step 1: Transact-SQL script (TSQL)
-- On Success action: Go to step 2
-- On Failure action: Go to step 3
Step 2: Transact-SQL script (Send success email)
Step 3: Transact-SQL script (Send failure email)

What I need is, if step 1 fails, I need to capture error message from step 1 in step 3 and send the same in an email notification.

I tried to use below token in Job step, but no success.

declare @err_msg as nvarchar(4000) = $(ESCAPE_SQUOTE(A-MSG))


texts: Data Corruption - ERROR: could not open segment 2 of relation

texts:
I am getting the below error for a production database. I am new to PostgreSQL databases. I need help to get this fixed.

2015-10-28 10:25:04,154 [monitor-thread-pool1-thread-6] ERROR [org.hibernate.util.JDBCExceptionReporter] ERROR: could not open segment 2 of relation 1663/98913/98947 (target block 427542): No such file or
directory

server1=>  select oid::regclass from pg_class where relfilenode =98947;
          oid
------------------------
 alert_instance_history
(1 row)

server1=>  select oid::regclass from pg_class where relfilenode =98913;
 oid
-----
(0 rows)

server1=> select * from pg_database where oid=1663;
 datname | datdba | encoding | datistemplate | datallowconn | datconnlimit | datlastsysoid | datvacuumxid | datfrozenxid | dattablespace | datconfig | datacl
---------+--------+----------+---------------+--------------+--------------+---------------+--------------+--------------+---------------+-----------+--------
(0 rows)

1. server1=> select version();
                                          version
--------------------------------------------------------------------------------------------
 PostgreSQL 8.1.4 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5 (Debian 1:3.3.5-13)
(1 row)

Error says 2 relations but I can only find 98947. How can I check the other segment ?
I just did a select on this table alert_instance_history, I can see records from this table. Do I need to check anything else?

server1=> select min(eventtimestamp) from alert_instance_history;

WARNING:  could not write block 427542 of 1663/98913/98947
DETAIL:  Multiple failures --- write error may be permanent.
ERROR:  could not open segment 2 of relation 1663/98913/98947 (target block 427542): No such file or directory
CONTEXT:  writing block 427542 of relation 1663/98913/98947
server1=>
server1=> reindex table alert_instance_history;
WARNING:  could not write block 427542 of 1663/98913/98947
DETAIL:  Multiple failures --- write error may be permanent.
ERROR:  could not open segment 2 of relation 1663/98913/98947 (target block 427542): No such file or directory
CONTEXT:  writing block 427542 of relation 1663/98913/98947

What's the possible options I have to restore, Can I restore just one table?
Backup of this database is done using pg_dump utility.
I verified random tables all of them are producing the same error.

cmproddb=> select min(CREATEDATE) from checkpointdata;
WARNING:  could not write block 427542 of 1663/98913/98947
DETAIL:  Multiple failures --- write error may be permanent.
ERROR:  could not open segment 2 of relation 1663/98913/98947 (target block 427542): No such file or directory
CONTEXT:  writing block 427542 of relation 1663/98913/98947


texts:
I need to make a simple referral system and I have a database table set out as follows:

mem_id | date | refer_email | accepted

And the meaning of such columns is the following:

mem_id: The member unique identifier who is making the referral.
date: The timestamp value of the request.
refer_email: The contact email address owned by the person being referred.
accepted: A CHAR value of Y or N that indicates if the referred person received acceptance.

I am not sure if this is best structure for this kind of scenario. I will be required to tally the number_of_users referred by each person mainly, and I expect a few more simple queries on the horizon.


texts: Database structure for a large amount of data with different types

texts:
Can I ask you for give me tips how to design efficient database to store large amounts of data with different types. I would like to use SQL Server 2016 as the database management system.

I have to store device id (int), timestamp and value itself. Value could be bit, int, float or even string. It's SCADA application so I have to store parameters like speed, temperature, status, product name, etc...

The end users select data portions without doing math on server side - so it's simply select by id and timestamp.

My questions are: how to design the relevant tables and what datatypes should I use to make it work as efficiently as possible?

Current options

My idea is to keep it in one table with values as VARBINARY. I use Entity Framework Core 2, since I am familiar with it, but maybe you can suggest me a better approach.


texts: data compression does not show when scripting index on ssms

texts:
when I right click on SSMS and choose to script the create index:

I get this script, without the data_compression:

USE [APCore]
GO

/****** Object:  Index [i_agencyId]    Script Date: 07/11/2019 12:18:13 ******/
CREATE UNIQUE NONCLUSTERED INDEX [i_agencyId] ON [agy].[agency]
(
    [agencyId] ASC
)
INCLUDE (   [countryCode]) WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [NONCLUSTERED_INDEXES]
GO

But when specifically checking for the data compression using teh query below

use APCore
go

SELECT i.name, p.data_compression_desc
 FROM sys.partitions P
 INNER JOIN sys.indexes I ON I.object_id = P.object_id AND I.index_id = P.index_id
 WHERE P.data_compression > 0 and I.name = 'i_agencyId';

I get the correct information.

I am using this SSMS :

Is there a way to make ssms include data compression on the index definition when scripting?


texts: How can I maintain a "virtual column" which is composed of data from 2 tables?

texts:
I have these two tables:

CREATE TABLE events (
    id integer NOT NULL,
    user_id integer NOT NULL,
    start_time time without time zone,
    end_time time without time zone
);

CREATE TABLE users (
    id integer NOT NULL,
    timezone_string character varying
);

The users change their timezone from time to time, but maintain the same schedule relative to the timezone where they are.

I want to be able to run this query:

select * from events where start_time >= '12:00' and end_time < '12:00';

where '12:00' is UTC.

But I want start_time and end_time to be adjust for each user's individual timezone. I can join with the users table and run a function to make the adjustment...

select * from events inner join users on (user_id = users.id)
where
  start_time::time at time zone 'UTC' at time zone timezone_string >= '12:00'
  and
  end_time::time at time zone 'UTC' at time zone timezone_string < '12:00';

...but that means each time I do this query I have to iterate over the entirety of both tables.

What are the ways I can precalculate the timezone adjustment, and auto-update the adjustment when data in either table changes? A materialized view needs to be completely rebuilt on each refresh, so that's out of the question. The best idea I have so far is to simply make a cache table that holds the adjusted data, and put triggers on the appropriate schedule and user columns to maintain the cache table.


texts: DELETE rows which are not referenced in other table

texts:
I have two tables in a PostgreSQL 9.3 database: Table link_reply has a foreign key named which_group pointing to table link_group.

I want to delete all rows from link_group where no related row in link_reply exists. Sounds basic enough but I've been struggling with it.

Will it be something simple like this (not working)?

DELETE FROM link_group WHERE link_reply = NULL;


texts: I have this weird issue with my SQL Server it always stop every night at 2 or 3 am Singapore time and I wonder why it's happening. Then in order for me to start it back again I have to restart the whole VPS for it to start back again. I have tried to find answer over the google but I don't have it.. the services I put automatic start. Is there possibility that SQL Server has its own timer to shutdown by itself?


texts:
As internal applications evolve over a number of years, you occasionally find there are a number of tables that people believe are no longer relevant and want to cull. What are the practical methods for identifying database dependencies, both within the SQL environment, and maybe onward into things like SSIS?

I've worked places where fairly brutal options have been taken such as:

Drop first, ask questions later (can kill a data warehouse build if it tries to extract a table that no longer exists)
Remove permissions first, and wait for the errors to be reported (can cause silent bugs, if the failiure isn't handled correctly)

I appreciate that SQL Server comes with tools for tracking dependencies within that instance, but these seem to struggle if you have databases on different instances. Are there options that make it easier to query dependencies, maybe answering questions like "Where is this column used?" with answers like "Over on this other server in this stored procedure" or "Over in this SSIS package"?


texts:
I'm newbie trying to wrap my head around OLAP, and I have a few questions.

Question 1: Can an OLAP cube store medians, modes, percentiles?
Question 2: Can an user-written MDX query return a summary of row-level data? (ex: % transactions > $100). Or must the cube designer add this to the cube?
Question 3: Do any OLAP products now provide mechanisms for accessing row level data? Which?

Our IT Department are looking for feedback on what sort of issues we're having with a particular MS Analsis Services ROLAP cube. We don't have access to the relational database behind it and need to perform calculations that aren't currently available as measures in the cube.

Let me see if I have this right.

A cube can provide statistics for counts, means, proportions, standard deviations.
If a particular statistic hasn't been catered for in a measure provided by the cube designer, can we write an MDX query to get it? Or do they need to change the cube in order to pre-calculate it from the row level data?
A cube cannot provide statistics likes medians, modes or percentiles, beacuse these statistics don't aggregrate properly.

I'm reading Leland Wilkinson's The Grammar of Graphics and in his chapter on Data Mining and OLAP, he says

  These [cube operations] work well with statistics like counts, means,
  proportions, and standard deviations. Simple aggregations over
  subclasses can be computed by operating on sums, sums of squares, and
  other terms that are combined in linear functions to produce basic
  summary statistics.

  They do not work properly with statistics like the median, mode and
  percentiles because the aggregate of these statistics is not the
  statistic of their aggregates. The median of medians is not the median
  of the aggregate, for example.

He goes on to add:

  A more sophisticated ROLAP model has emerged recently, however. It is
  possible, through several technologies, to give statistical algorithms
  access to raw data through the relational model in real time.
  This approach is more promising than the rigid aggregations offered by
  structures such as data cubes.

  In the most elegant form of this
  architecture, applications can request remote connections to provide
  information about their data-handling methods and take suitable action
  depending on the returned information. In this form, component
  architecture can achieve the real promise of distributed computing:
  design and execution that are independent of site, operating system,
  or language.

That was written circa 2005. Is anyone aware of products employing this methodology to allow for row-level data access?


texts: Mapped drive for Microsoft SQL Server 2008

texts:
I've noticed strange issue after this weekend. Maybe someone could explain this situation.
I've got SQL Server 2008 placed on Windows Server 2008 with read/write permission to catalog ( let say Workgroup ) on network drive ( it is used to make backups )

To make catalog Workgroup visible for SQL Server, I've used this syntax:

  xp_cmdshell 'net use Y: \\network-disk-server\Workgroup [password here] /[user here] /persistent:no'

And everything was fine. I could make backups on Workgroup. But after Sunday SQL Server 2008 couldn't see mapped drive anymore. After making net use syntax again, I get error:

  Multiple connections to a server or shared resource by the same user, using more than one user name, are not allowed. Disconnect all previous connections to the server or shared resource and try again.

So I think that this mapped drive still exists, but somehow it is not seen by SQL Server anymore.

I am using Domain user service with read/write permissions to workgroup folder and sysadmin fixed role on MSSQL.


texts: Log Shipping failover when Primary is not accessible

texts:
Log Shipping Fail-over

I have set up log Shipping with Cluster. In this scenario I want to simulate a situation when the whole cluster becomes inaccessible.
My Steps for fail-over will be

Copy and restore T-logs (WITH RECOVERY) that are not applied, to put the database online
Since the Primary in not accessible so I cannot take a T-log and put it in restoring state
Disable the above two jobs
Change the application connection strings to the new primary.

My confusion arises what should I do when the Old Primary comes live again:

Should I take the T-log back up and put the database in restoring state so that I can set it up as a secondary and then fail-over to make it primary.

If that’s the case then wont the log shipping fail if I want to configure without initializing (since I have the database in restoring state) as it will look for the last T-log which I Have not applied to my new secondary as the primary was not accessible during fail-over.

Thanks in advance


texts: Oracle 9i Low performance on Windows Server Enterprise 2003

texts:
I'm facing some problems with performance with my Oracle database running on a virtual machine with Microsoft Windows Server Enterprise 2003 with 8GB RAM.

It works pretty well after the database is initialized, but after some time it starts slowing down.
I've tried to run statistics, rebuild all indexes, export and reimport the dump file, uninstalling/reinstalling Oracle, increase the sga size.. but none of these worked so far.
The strangest thing is that if I import this dump into a virtual machine running Microsoft Windows Server STANDARD 2003 with 4GB RAM it works perfect without any performance issue.

Question: Is there a different configuration that must be done on Oracle or on Windows Server Enterprise 2003 to avoid this problem?
I'm a not a DBA, so my point of view is that the machine with 8GB should run better than the other one, am I right?

Thanks in advance.


texts:
I am designing a postgresql database for an online card game. I want to provide users with the option to access their play history - users can see log of their games.

The data I want to save has the following structure:

Player Ids | game_data
-----------|--------------
A,B,C,D    | ..game log..
A,D,E,F    | ..game log..
D,C,A      | ..game log..
D,A        | ..game log..

Each game can have up to 22 participants, so Max number of players is 22, Min number of players is 2.

So far I have about 100M records. Every day I add about 500K records. I have about 500K Players. Player-ID is a 32 byte string (MD5).

I want players to be able to access their game_data, so I want to select game_log by Player-Id. I need to do it as fast as possible. What would be the best way to do it with Postgres? I would prefer to save all these data in a single table.

So far I am considering two approaches:

Approach 1

Make a field of JSON type and save all Player's in in JSON-Array and query JSON in a SELECT statement.

Approach 2

Make 22 fields in a table for each player (If there is no player field is NULL) and make an ugly query over all fields.

So far I don't like any of these approaches. Is there a better method for this?


texts: Calling a function in remote database inside a stored procedure

texts:
There are 2 Oracle databases with pseudo names Remote and Local. I have a function in Remote called FUS.F_Return_10 which simply returns 10 for testing purposes, where FUS is a schema name. In Local I want to create a procedure that will call the above function. Here's the PL/SQL:

CREATE OR REPLACE PROCEDURE TEST
(
 V_COUNT OUT NUMBER
)
AS
 V_FOO NUMBER(2,0);
BEGIN
 V_FOO:= FUS.F_Return_10@PER_ACC;
  SELECT COUNT(*) INTO v_count FROM persons WHERE DEPID=V_FOO;
   EXCEPTION
    WHEN NO_DATA_FOUND THEN
    v_count :=0;
END;

There's a Public Database Link called PER_ACC in Local. When I try to create this procedure I get: Encountered symbol "@" when expecting one of the following: .(*%&................

I'm executing this code in SQL Developer

Can you tell me where my mistake is?


texts: How to remove duplicates leaving only one

texts:
I want to add a unique constraint on a table, eg

ALTER TABLE report ADD UNIQUE (date, scholar_id);

Each scholar should have only one report per day and the application checks whether there is an existing report for the scholar for the date but due to having a cluster of servers I occasionally do get duplicates.

Now I need to remove aproximately 700 items from the table.

I run a query like

SELECT date, scholar_id, count(*)
FROM report
GROUP BY date, scholar_id
HAVING count(*) > 1;

And see about 548 rows.  Some with counts = 3 or 4, most with count = 2.

These "duplicates" cause the ALTER TABLE to fail.

I am hoping to automate deleting these duplicates (all except 1 per scholar_id/date pair).

I imagine some way to get the pairs, and then per pair get all the rowids, and then DELETE FROM report WHERE id = any(...) AND NOT id = any(... limit 1);

Note: it is not important which one of the copies are removed.  There are other columns and the other columns might not have identical values.

How to do this is the question...


texts: Removing AG and clustering - make nodes stand-alone

texts:
I am wanting to make a 2-node Always On cluster no longer a cluster (make them (2) stand-alone machines).  I am trying to upgrade Sql and the wizard keeps getting stopped before it does it checks because it is detecting it is a cluster and the two machines no longer are able to talk to each other.  I have already deleted the AG and removed the Always-On check from the properties of the instance. The application is already coded to connect to the Primary so this is not an issue.  I am now thinking I will need to break (delete) the cluster and remove it but I want to keep one of the nodes from being affected (they are prod servers) and up and running with no outage.  Again, this is a 2-node Always-On cluster without shared storage (each server has it own local attached).

Has anyone ever done this?  After the cluster is deleted, do the individual machines stay okay (up) and functional during it (deleting the cluster)?

Thanks


texts: Merging the sysdb database (a dependency due to conversion from Oracle) into my SQL Server 2014 Express Database

texts:
I am looking to convert my SQL Server 2014 Express Database to Azure SQL Database.

My SQL Server database was automatically converted from Oracle a number of years ago, and needs the sysdb database (provided by Microsoft) as a dependency to provide user defined functions that emulate Oracle functions.

I ran the Data Migration Assistant to check for compatibility, and the main problem seems to be that "Cross-database references not supported in Azure SQL Database". This relates to the dependency on the sysdb database.

The two recommendations of the Data Migration Assistant are either:

Move the dependent datasets from other databases into the database that is being migrated. OR
Migrate the dependent database(s) to Azure and use 'Elastic Database Query' functionality to query across Azure SQL databases.

I would eventually like to get rid of all Oracle related references, although I do not have the resources to do that right now. I feel that the best thing to do is to merge the sysdb database with my database (so that it would then work with Azure SQL Database) and then as a long-term project slowly rid myself of Oracle dependencies.

Any ideas about how to achieve this? (Or a better idea?)


texts: SQL Server 2012 CPU usage spikes to 100%

texts:
I am using SQL Server 2014 in AWS and the AWS image configuration is 4 cores and 16GB RAM(m3.xlarge). I am running the following simple query,

SELECT * FROM user_table WHERE user_id = '10'

user_table contains 1000k records, user_id is primary key. When the above simple query is executed from my application through EJB hibernate, the CPU spikes to 10% for a moment and again it backs to normal.

So my use case is, 100 users will concurrently try to hit the application, so in a fraction of second 100 times the above query will try to execute in a fraction of second. So the CPU usage spikes to 100%. Once all the query execution is completed the CPU usage is back to normal at 1%.

Why it so? whether I need to increase my AWS instance type?
What should I have to do in-order to make SQL server to handle 100 or more concurrent hits without making high CPU usage? If my query is so complex then there might have a chance to get spike but my query is simple and straight forward.
Is there is any bench mark metrics available for SQL server 2014?
Any solution to make support for concurrent hits with SQL server by low CPU usage consumption?
One more information my data file size is around 32.2GB and log
file size is around 894mb for my database.
My DB has the isolation level of READ_COMMITTED_SNAPSHOT is set to ON.
But when I tried by setting READ_COMMITTED_SNAPSHOT to OFF, there is a difference of 20% performance improvement but not that much considerable performance improvements.
Similarly I faced another issue I posted over here. Consider this too for analysis(Some hint may arise from the question in the link).


texts: Access Macro: ApplyFilter w/values from 2 combo boxes

texts:
I'm getting started with databases in Access for a work project (technically a work-learn for my non-CompSci degree). I have experience using multiple filters on multiple columns through Access/Excel, but for my work learn I'm trying to design an interface with some "quick filter" functionality built into the UI. Since I'm pretty green, I'm using macros instead of VBA which seems to be doing the job fine... But I've hit a snag, and was hoping y'all might be able to help?

I have a split-view form with top containing record details and quick search options, and the bottom containing the table itself as filtered (which serves as a list of results). What I'm trying to do is filter a column by the selections from two dropdowns on a form.

I was able to do this by doing:

ApplyFilter WHERE Table!Articles!IntCodes LIKE "*"IntCombo1 & "*" & IntCombo2 "*"

But then I realized that while that will find entries where IntCombo1 and IntCombo2 appear in order, (e.g. Dog, Cat) it won't show ones where they appear reversed (Cat, Dog).

I would think that the way to fix that would be one of:

ApplyFilter WHERE Table!Articles!IntCodes LIKE "*"IntCombo1 & "*" & IntCombo2 "*" OR "*"IntCombo2 & "*" & IntCombo1 "*"

or

ApplyFilter WHERE Table!Articles!IntCodes LIKE ("*"IntCombo1 & "*" & IntCombo2 "*") OR ("*"IntCombo2 & "*" & IntCombo1 "*")

or

 ApplyFilter WHERE Table!Articles!IntCodes LIKE ("*"IntCombo1 & "*" & IntCombo2 "*" OR "*"IntCombo2 & "*" & IntCombo1 "*")

But none of these work, and I'm not sure why because they all seem pretty boolean-ly sound. Can someone tell me what I'm doing wrong?

(Other perhaps relevant info is that the entries on the table I'm filtering are short text words/phrases separated by semicolons ("Dog; Cat; Mouse; Horse", "Mouse", "Cat; Dog", etc...). I totally own that this should probably be converted into a table (long term goal), but the dataset was created over 5 years in an Excel spreadsheet, and therefore is a beast so I haven't gotten around to doing that yet.)


texts: why stored procedure performance is very high compared to plain select query

texts:
I have a table with more than 2 million rows, with needed indexes.

When I run a simple query it takes so much time so I decide to stop executing it; while when I wrap the query in a stored procedure, it runs very quickly (<100 ms)!

Also sp_executesql for the same query acts like stored procedure and runs very fast.

I just want to no why the plain query is slow? Is this normal?

I've indexed my table, so I do not think this is because of my lack of indexes

This is plain query:

SELECT  [t1].[Id]
    FROM    ( SELECT TOP ( 201 )
                        [t0].[Id]
              FROM      [dbo].[Logs] AS [t0]
              WHERE     [t0].[DeviceId] = 40
                        AND ArrivalDateTime >= '2012-10-12'
              ORDER BY  [t0].[Id] ASC
            ) AS [t1]
    ORDER BY [t1].[Id] ASC

This is procedure:

CREATE PROC TestProc
(
  @datetime DATETIME2(0) ,
  @deviceId INT
)
AS
BEGIN
        DECLARE @dataTimeLocal DATETIME2(2);
        SET @deviceIdLocal = @datetime

    DECLARE @did INT= @deviceId;
    SELECT  [t1].[Id]
    FROM    ( SELECT TOP ( 201 )
                        [t0].[Id]
              FROM      [dbo].[Logs] AS [t0]
              WHERE     [t0].[DeviceId] = @deviceIdLocal
                        AND ArrivalDateTime >= @dataTimeLocal
              ORDER BY  [t0].[Id] ASC
            ) AS [t1]
    ORDER BY [t1].[Id] ASC
END

and Logs table:

CREATE TABLE [dbo].[Logs](
[Id] [int] IDENTITY(1001,1) NOT NULL,
[DeviceId] [int] NOT NULL,
[TypeId] [tinyint] NOT NULL,
...
other non related fields
...
[YearFa] [smallint] NULL,
[MonthFa] [tinyint] NULL,
[DayFa] [tinyint] NULL,
[Year] [smallint] NULL,
[Month] [tinyint] NULL,
[Day] [tinyint] NULL,
[Hour] [tinyint] NULL,
[DeviceLogNumber] [int] NULL,

CONSTRAINT [PK_Logs] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

SET ANSI_PADDING ON
GO

ALTER TABLE [dbo].[Logs] ADD  CONSTRAINT [DF_LogsGs828S_DeviceId]  DEFAULT ((1)) FOR [DeviceId]
GO

ALTER TABLE [dbo].[Logs] ADD  CONSTRAINT [DF_Logs_TypeId]  DEFAULT ((1)) FOR [TypeId]
GO

ALTER TABLE [dbo].[Logs] ADD  CONSTRAINT [DF_LogsGs828S_ArrivalDateTime]  DEFAULT (getdate()) FOR [ArrivalDateTime]
GO

ALTER TABLE [dbo].[Logs]  WITH NOCHECK ADD  CONSTRAINT [FK_Logs_Devices] FOREIGN KEY([DeviceId])
REFERENCES [dbo].[Devices] ([Id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

Update1:

For some dates like '2010/10/10' in where clause query runs immediately but for some dates like '2012/01/10' it takes so long (19 secs)!!

This is execution plan of the query:


texts: T-SQL How to Create a new column as INT from Datetime column as YYMM or YYYYMM

texts:
which is the simpliest way to cretae new column and keep old datetime ?
Use FORMAT?

Result like 201805, 201806 or 1805, 1806 etc from column datetime "2018-05-15 09:45:00.000"


texts: Can we enable tls 1.0 on MSSQL Server 2019 on database level? if yes then how?

texts:
i am running sql server 2019 on Linux box and trying to connect using a Windows server 2016 client, how can we enable TLS 1.0 for SQL Server 2019 on the Database/instance level.
TIA.


texts: Capturing change deltas when database replication or CDC breaks

texts:
The company I work for is in the process of implementing change data capture on a Microsoft BI stack for the purpose of capturing incremental changes that need to be loaded into the data warehouse. So far this has worked great for us, we have the database replication setup and table level CDC setup on the replicated database for the specific tables and columns we need to track. The problem that we are running into is that both replication and CDC have the potential to break. When this happens any changes that occur from the time it breaks to the time it is resolved will not be captured and thus cannot be passed to the data warehouse.

I'm wondering what the best practice is to identify all the deltas and get them loaded into the data warehouse? In theory you can rebuild your entire data warehouse but then what is the best way to keep historical Type 2 data?


texts:
I want to configure the snapshot replication for all the articles. The problem iam facing is first time all the articles got sync on the subscriber. But later on let suppose if there is a schema change (added new table) in the publisher that newly created table is not getting replicated on the subscriber.

So every time if there a schema changes in publisher i have to manually update the selected articles on the publisher to get it replicated on the subscriber.

Is there a way to automate this step that whenever there is a schema changes at the publisher (added new articles) it should be automatically replicated on the subscriber ? Thanks


texts: Mongo: mongodump and the config database error

texts:
I've setup a backup script on my Ubuntu box using mongodump. However this currently doesn't work because of an error when attempting to dump the built in config database:

(Apologies for the image, I couldn't copy paste from the terminal I used)

How can I fix this? I either need to add the right permissions to my user, or exclude the config database. Ideally I'd like mongodump to ignore errors on any particular database so I still get a useful backup.

Here is the user I'm using to login as, as defined in the admin database:

{
    "_id" : "admin.admin",
    "userId" : UUID("b1928c55-fbaa-4196-9e7c-93e9139501ed"),
    "user" : "admin",
    "db" : "admin",
    "roles" : [
        {
            "role" : "userAdminAnyDatabase",
            "db" : "admin"
        },
        {
            "role" : "dbAdminAnyDatabase",
            "db" : "admin"
        },
        {
            "role" : "readWriteAnyDatabase",
            "db" : "admin"
        }
    ],
    "mechanisms" : [
        "SCRAM-SHA-1",
        "SCRAM-SHA-256"
    ]
}


texts: How do I resolve "ORA-23293: Cannot rename a column which is part of a join index?"

texts:
Disclaimer: I am a developer, not a DBA, but this question seems more appropriate for DBAs than for StackOverflow. Now to my question:

We have a table in an Oracle Exadata schema that uses a numeric, sequence-generated PK simply called ID. We would like to rename this column to [TABLENAME]_ID so that its name is consistent with its table name (and therefore consistent with other naming conventions used at the company).

When I attempt to rename this column with ALTER TABLE TABLENAME RENAME COLUMN ID TO TABLENAME_ID;, I'm slapped with the message SQL Error: ORA-23293: Cannot rename a column which is part of a join index. The obvious suggestion from Oracle is to drop the join index so that I can rename the column, but that's the fun part: I (nor any of my coworkers) have ever explicitly created such an index, nor are aware of any that exist.

At the moment, my only thought is that the index in question was auto-generated by the creation of some other object or by some unique configuration of this table. I've tried searching in user_indexes and user_objects for signs of the join index without any luck; are there other system tables I should consult to hunt this index down?

FWIW, the table has a few things about it that make it unique vs other tables where we have been able to renamed the ID column:

This table has a CLOB column in it.
This table is partitioned based on a date column. Partitions are based on the year.
Other tables in the schema are partitioned based on their foreign key references to this table.

Can anyone clue me in on whether there's some "shadow" join index created by one of the above features, and/or the correct way to find this phantom join index? Thanks!


texts: how determine the best path that satisfies a condition

texts:
declare @flat table
(
    id int not null,
    percentage int not null,
    value float not null
)

insert into @flat (id, percentage, value)
select 1,10,0.333
union all
select 1,20,0.4
union all
select 2, 50, 0.5
union all
select 2, 70, 0.67
union all
select 3, 10, 0.125
union all
select 3, 40, 0.325

having this sample data, how could i determine the rows that maximize the sum of the values ?
Having the following conditions:

one percentage per id only
the sum of the percentages must be 100

there is two possible solutions:

-- id percentage  value     sum
--  1     10      0.333    0.333
--  2     50      0.5      0.833
--  3     40      0.325    1.158

-- id percentage  value     sum
--  1     20      0.4       0.4
--  2     70      0.67      1.070
--  3     10      0.125     1.195

but it matters only the last one.


texts:
I have a SELECT request - it works fine:

select meta_value
from wp_posts v
left join wp_postmeta pm on (pm.post_id = v.id)
left join wp_posts p on (v.post_parent = p.id)
where meta_key in ('_price','_regular_price')
and v.post_type = 'product_variation'
and p.id = '1743'
limit 0,100

Request find several post's meta_value relative to post with id = 1743.

It returns me 4 (four) needed fields with values like

400
500
300
350

I need an UPDATE request for it.

I try

Update wp_posts
set meta_value = 1000
where id IN (select v.id
            from wp_posts v
            left join wp_postmeta pm on (pm.post_id = v.id)
            left join wp_posts p on (v.post_parent = p.id)
            where meta_key in ('_price','_regular_price')
                and v.post_type = 'product_variation'
                and p.id = '1743')

But it cause error

  1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use

  near 'FROM wp_posts WHERE meta_key in ( 'price' , '_regular_price' )
  and v.post' at line 2

This function as a part of UPDATE

select v.id
from wp_posts v
left join wp_postmeta pm on (pm.post_id = v.id)
left join wp_posts p on (v.post_parent = p.id)
where meta_key in ('_price','_regular_price')
and v.post_type = 'product_variation'
and p.id = '1743'

Returns me right ID'S like

1744 1744 1745 1745

But UPDATE function with this select make an error.

There is mistake??? Help me please


texts: deadlock in mariadb: with non-index & fk table

texts:
when i try to do an operation concurrently, there exists an deadlock from mariadb show engine innodb status, the deadlock log is as follows

------------------------
LATEST DETECTED DEADLOCK
------------------------
160929  7:43:35
*** (1) TRANSACTION:
TRANSACTION 151E3D3, ACTIVE 48 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 16 lock struct(s), heap size 3112, 10 row lock(s), undo log entries 9
MySQL thread id 9, OS thread handle 0x7f35529ef700, query id 9966 localhost 127.0.0.1 root update
insert into t_st_storage_adapter_host_scsi_disk (storage_adapter_id, host_scsi_disk_id) values ('e487a58a5774ddf0015774e5c43d00bc', 'e487a58a5774ddf0015774e5da2800d3')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 814 n bits 96 index `fk_storage_adapter_host_scsi_disk_storage_adapter` of table `ics`.`t_st_storage_adapter_host_scsi_disk` trx id 151E3D3 lock_mode X locks gap before rec insert intention waiting
*** (2) TRANSACTION:
TRANSACTION 151E3D0, ACTIVE 48 sec fetching rows
mysql tables in use 1, locked 1
21 lock struct(s), heap size 3112, 35 row lock(s), undo log entries 26
MySQL thread id 7, OS thread handle 0x7f355295d700, query id 10345 localhost 127.0.0.1 root updating
delete from t_st_storage_adapter_host_scsi_disk where storage_adapter_id='e487a58a5774ddf0015774e5c7b900c1'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 814 n bits 96 index `fk_storage_adapter_host_scsi_disk_storage_adapter` of table `ics`.`t_st_storage_adapter_host_scsi_disk` trx id 151E3D0 lock_mode X
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 812 n bits 96 index `GEN_CLUST_INDEX` of table `ics`.`t_st_storage_adapter_host_scsi_disk` trx id 151E3D0 lock_mode X waiting
*** WE ROLL BACK TRANSACTION (1)

the table  t_st_storage_adapter_host_scsi_disk is a relation table for t_st_storage_adapter & t_st_host_scsi_disk. non primary key or unique key is create on this table.

| t_st_storage_adapter_host_scsi_disk | CREATE TABLE `t_st_storage_adapter_host_scsi_disk` (
  `host_scsi_disk_id` varchar(36) COLLATE utf8_bin NOT NULL,
  `storage_adapter_id` varchar(36) COLLATE utf8_bin NOT NULL,
  KEY `fk_storage_adapter_host_scsi_disk_hostscsidisk` (`host_scsi_disk_id`),
  KEY `fk_storage_adapter_host_scsi_disk_storage_adapter` (`storage_adapter_id`),
  CONSTRAINT `fk_storage_adapter_host_scsi_disk_hostscsidisk` FOREIGN KEY (`host_scsi_disk_id`) REFERENCES `t_st_host_scsi_disk` (`id`),
  CONSTRAINT `fk_storage_adapter_host_scsi_disk_storage_adapter` FOREIGN KEY (`storage_adapter_id`) REFERENCES `t_st_storage_adapter` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin |

and the fk column are both uuid.

when i do a business operation, repeatable delete & insert sql(determined by orm hibernate's mechanism) on this table will be executed. when try to delete the sql is like

delete from t_st_storage_adapter_host_scsi_disk where storage_adapter_id='e482964a57d370280157d3726aba0037'

and deadlock will occur in most cases. so why will the deadlock happens and what can i do to prevent this deadlock?

the isolation level is RR, and the innodb_locks_unsafe_for_binlog is OFF.

and i have no idea on:
1. Is there exists obvious difference between the hidden clustered index generated automatically by innodb (see mysql docs) and the pk?
2. As the sql shows above, why fk lock and hidden clustered index lock will conflict? the business code operate the table t_st_storage_adapter_host_scsi_disk in different storage_adapter_id in one thread. and when i do some test, the lock won't happens in such situation.

thanks a lot in advance.

i don't know the reason but after add a pk on this table, the problem has been fixed.So as the docs from the mysql above, which difference lead to the deadlock happen between pk and the auto generate cluster_index by innodb?


texts:
I have an Oracle SQL script like this:

delete FROM activ_princ where id in
    (select ap.id FROM activ_princ ap
        inner join sect e on e.id=ap.FK_ID_SECTIUNE_e
        inner join chest ch on ch.id=e.FK_ID_CHESTIONAR
    WHERE ch.id=1661);
delete FROM activ_sec where id in
    (select asec.id FROM activ_sec asec
        inner join sect e on e.id=asec.FK_ID_SECTIUNE_e
        inner join chest ch on ch.id=e.FK_ID_CHESTIONAR
    WHERE ch.id=1661);
delete FROM sect where id in
    (select e.id FROM sect e
        inner join chest ch on ch.id=e.FK_ID_CHESTIONAR
    WHERE ch.id=1661);

how can I pass 1661 as a parameter and execute all the script changing just the id I want to delete...


texts:
I have a database with many large objects. A table have a column OID that refers to these large objects. I dump the database daily, including all large objects, with command:

pg_dump --format=c --file=/var/backups/pgsql/db-neos.pgdump \
  --compress=6 --blobs neos

and then I restore it, on a different machine, with command:

createdb neos
pg_restore -d neos /mnt/db-neos.pgdump

but, while all tables are correctly created, large objects aren't.
Instead, an error message is displayed by pg_restore:

pg_restore: [compress_io] decompressione dei dati fallita: (null)
(english: could not uncompress data)


texts:
I have the following structure:
Views

dbo.vSales
Sec.vSales

Tables

dbo.Security
Sec.Products
Sec.Countries
Sec.Users

I have 2 groups accessing my Database:

Administrators
Standard_User

The users are connecting using SQL Server Management Studio.
I want the Standard_User group to only to see the Sec schema, and only be able to do stuff in this Schema. At the moment, when they login they can see everything inside the database even if they don't have rights to execute or select.
Is this possible?


texts: Inserting rows into a temporary variable causes plan cache pollution in SQL Server

texts:
In my database I use stored procedures to insert multiple rows from a temporary table variable.  Inserting data into the temporary table variable causes a new adhoc plan to be created prior to each stored procedure execution which causes plan cache pollution.  Is there any way to stop saving these adhoc plans and eliminate the pollution in the plan cache?

USE [tempdb];
GO

DROP PROCEDURE [dbo].[uspMyTableInsert];
GO

DROP TABLE [dbo].[MyTable];
GO

DROP TYPE [dbo].MyTable;
GO

CREATE TABLE [MyTable]
    (
     [ID] INT NOT NULL
              IDENTITY(1, 1)
              CONSTRAINT [PK_MyTable_ID] PRIMARY KEY CLUSTERED,
     [FirstName] VARCHAR(50) NOT NULL
    );
GO

CREATE TYPE [MyTable] AS TABLE
(
[FirstName] VARCHAR(50) NOT NULL
);
GO

CREATE PROCEDURE [dbo].[uspMyTableInsert]
    (
     @Table MyTable READONLY
    )
AS
BEGIN
    INSERT  INTO [dbo].[MyTable]
            ([FirstName])
    SELECT
        [t].[FirstName]
    FROM
        @Table AS [t];
END;
GO

DBCC FREEPROCCACHE;
GO

DECLARE @Table MyTable

INSERT  INTO @Table
        ([FirstName])
VALUES
        ('Bugs Bunny #1'),
        ('Daffy Duck #1');

EXEC [dbo].[uspMyTableInsert] @Table = @Table;
GO


texts:
I'm working with OS Highways data, which when loaded into Postgres using ogr2ogr has a few columns that are set as arrays. Geoserver does not like this, so I need to transform the arrays into a simple list of comma separated values.

For example, currently we have:

{Buses,"Goods Vehicles Exceeding 7.5T"}

What I need instead is:

Buses, Goods Vehicles Exceeding 7.5T

And of course the data type needs to be changed to normal 'character varying' instead of the array type 'character varying[]'.

I could probably split the array into separate fields, then recombine, but I was interested to see if anyone had a more elegant/efficient solution.
Thanks!


texts: Fire trigger after the complete transaction commit

texts:
I try to call an external script with a trigger when I INSERT on my table.

This script have to get some data, especially the last row added (which one fired the trigger)

According to the documentation, CONSTRAINT TRIGGER and DEFERRABLE are executed inside the transaction so my external script can't get the last row added.

How can I fire my trigger after the transaction completely commited ?


texts: Restore / Create a database with a data file (MDF) from backup the same but with two data files (MDF and NDF)

texts:
I must restore/create a db (in a new instance) in one file(mdf) from a backup of the same but with two files of data (mdf and ndf)

How can I do it?

This is what I try... but it did not work

    RESTORE database mydb
    FROM  DISK='\\150.10.10.12\backup\mydb.bak'
    WITH  REPLACE, RECOVERY,
    MOVE 'Mydb_DATOS'       TO 'F:\MSSQL\Data\Mydb.mdf',
    MOVE 'Mydb_REGISTRO'    TO 'G:\MSSQL\LOG\Mydb_LOG.ldf'

then check the following

 RESTORE FILELISTONLY
 DISK='\\150.10.10.12\backup\mydb.bak'


texts: Where to look for a bottleneck after elimitating CPU, memory, and disk

texts: I have a database job that runs each night to create a warehouse table. Using my new database server and SAN I have gotten the process down from 4 1/2 hours to 35 minutes. I have optimized it to run in the shortest amount of time through experimentation (WITH INDEX NOLOCK, FORCE ORDER, LOOP JOIN, MAXDOP 0). I'm happy with the improvement, but I hate not knowing why a query takes as long as it does. I'm perfectly OK with bottlenecks, as long as I know where they are. When this query is running, there are significant periods of time where the obvious resources are all underutilized. What is SQL Server doing at these times?


texts: How to Revoke and Re-grant Permissions for an in-use MySQL User

texts:
We have a user which (wrongly) has global privileges (i.e. *.* rather then database specific).

We would like to restrict this user to one database, so my approach would normally be to REVOKE ALL and then GRANT per-database.

However, the account is currently being used by a live application, so we ideally need to preserve access for the required database while removing others.

My question is twofold: 1) Will it even work? and 2) will the below SQL do the job?

GRANT ALL ON mydb.* to 'myuser'@'%';
REVOKE ALL PRIVILEGES ON *.* FROM 'mysuer'@'%';
FLUSH PRIVILEGES;


texts: MS SQL: get permissions of a fixed server role

texts:
I need to get a list of MS SQL Server fixed roles and their permissions.
There is a query to get all server-level permissions:
SELECT * FROM sys.fn_builtin_permissions('SERVER') ORDER BY permission_name;

And there is a SP sys.sp_srvrolepermission which returns list of server roles and their 'permissions'. But those permissions don't really map to what the previous query returns.
There is a pretty good explanation here https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles?view=sql-server-ver15 with image, which shows roles and permissions. And permissions on the image map to the first query results.

The question is - is there a correct way (SP, query) to get server fixed roles to permissions mapping, or should I hardcode those as per image.


texts: Disable mysql_tzinfo_to_sql during MariaDB/MySQL installation

texts:
MariaDB run in docker container hangs during initial startup while mysql_tzinfo_to_sql running because of the issue.

I've disabled mysql_tzinfo_to_sql by setting MYSQL_INITDB_SKIP_TZINFO env. var to 1.

Afterwards i manually ran:

mysql_tzinfo_to_sql /usr/share/zoneinfo | sed 's/Local time zone must be set--see zic manual page/FCTY/' | mysql -uuser -ppass --database=mysql

It takes ~12 minutes and script causes errors in MariaDB error log if MariaDB cluster is set.

I'm wondering what's the purpose of running mysql_tzinfo_to_sql during MariaDB installation and consequences of disabling it.

Found this link:

  MySQL provides developers with a rich set of Date and Time functions.
  One of the functions is CONVERT_TZ which converts a datetime value
  from one time zone to another. This function can use named time zones
  such as 'MET' or 'Europe/Moscow' from the time zone information tables
  in system database called mysql.

  Named time zones can be used only if the time zone information tables
  in the mysql system database have been created and populated. The
  MySQL installation procedure creates the time zone tables in the mysql
  database, but does not load them with data. You need to load data
  manually for these time zone tables.

It does answer the question.

However, i still don't fully understand practical implications of disabling populating mysql timezone system tables.

Why does timezone system tables population may take so long during initial MariaDB startup ?


texts: Result set with multiple rows based on max date and not NULL

texts:
Staff changed their profiles as shown in this table:

The result of latest information should be like this:

I applied the technique from this question for embedded Firebird of LibreOffice Base, the query has error.


texts: reusing space after deleting lots of rows

texts:
I have a database which is 20 GB and it has history information since year 2001.
Now I started deleting data prior to year 2008. after completion of deletion how can i reuse the space that is cleared by my delete statement.
I am using SQL Server 2005.

Will Sql server automatically reuse the space if I don't shrink the datafiles?
Should we rebuild the indexes?


texts: Updating a big replicated Dimension (SQL Server PDW)

texts:
We use a SQL Server PDW appliance for our data warehouse. One of the tables in our warehouse is a replicated table with about 20 million rows. As part of our ETL process we need to expire old records from this dimension; however, we are seeing that updating a handful of records (<100) takes over 1 hour to complete. This is what I would like to improve if I can.

Naturally, one option that I thought about was changing this Dimension from Replicated to Distributed. My testing shows that it would fix the issue with the ETL process taking long (from 1.5 hours came down to 30 secs) but all the joins against the Distributed version of this dimension would be affected since the joins are almost never based on the same distribution column. When I look at the execution plan of some of these queries I usually see either a ShuffleMove or a BroadcastMove operation.

So my question to the PDW guru's here is:

Is there anything else that can be done in order to improve the performance of updating records in the replicated version of this Dimension?

Again, moving to a Distributed table doesn't seem to be the best solution since it will affect hundreds of already written SQL queries and reports developed by other people.


texts:
I have a table, lets call table a, that I allow users to enter data into. They can enter data as many times as they want, and based on some constraints, I only use the most recent entry.

My question is this: is it okay to take the entered data from table a, simplify it (take the most recent data, apply some formulas), and store it in table b?

This improves access access for my application and convert makes most of my data access queries simple selection statements. The down side is that my data is replicated, and a script must be run regularly to keep data in sync.

So, is this an acceptable practice?


texts: I am trying to understand the use of composite primary key or two primary keys in a table

texts:
Today someone asked me about composite primary key two make two column as primary key but i unable to understand the logic behind it because we have unique index option to make a column unique, and if I think about foreign key relationship then again its not required.

If anyone have good explanation with example then please clarify that.

CREATE TABLE `sample` (
  `pri_1` int(11) NOT NULL AUTO_INCREMENT,
  `pri_2` int(11) NOT NULL,
  `name` varchar(25) NOT NULL,
  `value` varchar(25) NOT NULL,
  PRIMARY KEY (`pri_1`,`pri_2`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1


texts: Effectively searching through entire 1 level nested JSONB

texts:
Let's say we need to check if a jsonb column contains a particular value matching by a substring in any of the value (non-nested, only first level).

How does one effectively optimize a query to search entire JSONB column for each value?

Is there some good alternative to doing ILIKE %val% on jsonb datatype casted to text?

jsonb_each_text(jsonb_column) ILIKE '%val%'

As an example consider this data:

SELECT '{"col1": "somevalue", "col2": 5.5, "col3": 2016-01-01, "col4": "othervalue", "col5": "yet_another_value"}'::JSONB

How would you go about optimizing a query like that when in need to search for pattern %val% in records containing different keys configuration in a column? Is there a better alternative to extracting each key-value pair to text and performing an ILIKE/POSIX search?

Mainly, I'm looking for a different alternative to unpacking a whole jsonb field into separate key rows with their values as text.


texts: When upgrading to mongodb 3.0, should I upgrade authentication schema after upgrading the storage engine to WiredTiger?

texts:
I know that, I need to upgrade the storage engine after upgrading the binaries.
But I am not very sure whether I should upgrade the authentication after upgrading the storage engine. Or it does not matter?


texts:
Looking to calculate the most suitable unit of measurement for a list of substances where the substances are given in differing (but compatible) unit volumes.

Unit Conversion Table

The unit conversion table stores various units and how those units relate:

id  unit          coefficient                 parent_id
36  "microlitre"  0.0000000010000000000000000 37
37  "millilitre"  0.0000010000000000000000000 5
 5  "centilitre"  0.0000100000000000000000000 18
18  "decilitre"   0.0001000000000000000000000 34
34  "litre"       0.0010000000000000000000000 19
19  "dekalitre"   0.0100000000000000000000000 29
29  "hectolitre"  0.1000000000000000000000000 33
33  "kilolitre"   1.0000000000000000000000000 35
35  "megalitre"   1000.0000000000000000000000 0

Sorting by the coefficient shows that the parent_id links a child unit to its numeric superior.

This table can be created in PostgreSQL using:

CREATE TABLE unit_conversion (
  id serial NOT NULL, -- Primary key.
  unit text NOT NULL, -- Unit of measurement name.
  coefficient numeric(30,25) NOT NULL DEFAULT 0, -- Conversion value.
  parent_id integer NOT NULL DEFAULT 0, -- Relates units in order of increasing measurement volume.
  CONSTRAINT pk_unit_conversion PRIMARY KEY (id)
)

There should be a foreign key from parent_id to id.

Substance Table

The Substance Table lists specific quantities of substances. For example:

 id  unit          label     quantity
 1   "microlitre"  mercury   5
 2   "millilitre"  water     500
 3   "centilitre"  water     2
 4   "microlitre"  mercury   10
 5   "millilitre"  water     600

The table might resemble:

CREATE TABLE substance (
  id bigserial NOT NULL, -- Uniquely identifies this row.
  unit text NOT NULL, -- Foreign key to unit conversion.
  label text NOT NULL, -- Name of the substance.
  quantity numeric( 10, 4 ) NOT NULL, -- Amount of the substance.
  CONSTRAINT pk_substance PRIMARY KEY (id)
)

Problem

How would you create a query that finds a measurement to represent the sum of the substances using the fewest digits that has a whole number (and optionally real component)?

For example, how would you return:

  quantity  unit        label
        15  microlitre  mercury
       112  centilitre  water

But not:

  quantity  unit        label
        15  microlitre  mercury
      1.12  litre       water

Because 112 has fewer real digits than 1.12 and 112 is smaller than 1120. Yet in certain situations using real digits is shorter -- such as 1.1 litres vs 110 centilitres.

Mostly, I'm having troubles picking the correct unit based on the recursive relation.

Source Code

So far I have (obviously non-working):

-- Normalize the quantities
select
  sum( coefficient * quantity ) AS kilolitres
from
  unit_conversion uc,
  substance s
where
  uc.unit = s.unit
group by
  s.label

Ideas

Does this require using log10 to determine the number of digits?

Constraints

The units are not all in powers of ten. For example: http://unitsofmeasure.org/ucum-essence.xml


texts:
I have built a data warehouse with multiple fact tables and dimension tables (with a MySQL server).

Now where could I build the OLAP cubes and write the MDX queries to produce results?

BTW, I have some understanding of the cube building on Apache Kylin, but I think that limited to our business type and amount of data, using Kylin is not a good solution currently now.

I know that Kylin is based on Hadoop. Maybe what we need is an OLAP server based on a RDBMS?

EDIT:
 The reason I'm asking this is because I'm the only data analyst of our team. I used to produce reports with Power BI Desktop, and load the data from our business system MySQL database directly. This worked very well.

We have 400,000 users, the largest table in our databases is about 2 million rows.

Our director think we should use OLAP cubes to speed up the report producing. After designing the fact-dimension data warehouse, I don't know what's the next step ?


texts:
Forgive me if the question is very simplistic; I am a bit of a novice when it comes to SQL. The following CREATE TABLE syntax was generated by MySQL workbench from my model; It gives me errno: 150. Anyone who can tell me what I am doing wrong gets a cookie (and my gratitude!).

CREATE TABLE IF NOT EXISTS `mealPlannerApp`.`ingredients` (
`ingredientId` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`stub` VARCHAR(40) NOT NULL,
`name` VARCHAR(40) NULL,
`photo` INT NULL,
`description` TEXT NULL,
`servingSizeInGrams` INT UNSIGNED NULL,
`cost` INT UNSIGNED NULL,
`calories` INT UNSIGNED NULL,
`protein` INT UNSIGNED NOT NULL,
`carboydrates` INT UNSIGNED NOT NULL,
`fat` INT UNSIGNED NOT NULL,
`saturatedFat` INT UNSIGNED NULL,
`transFat` INT UNSIGNED NULL COMMENT ,
`cholesterol` INT UNSIGNED NULL,
`sodium` INT UNSIGNED NULL,
`dietaryFiber` INT UNSIGNED NULL,
`sugars` INT UNSIGNED NULL,
PRIMARY KEY (`ingredientId`),
UNIQUE INDEX `id_UNIQUE` (`ingredientId` ASC),
UNIQUE INDEX `stub_UNIQUE` (`stub` ASC),
INDEX `photo` (`photo` ASC),
INDEX `name` (`name` ASC),
CONSTRAINT `photoId`
FOREIGN KEY (`photo`)
REFERENCES `mealPlannerApp`.`photos` (`photoId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB


texts: DBATools offline install - export-dbainstance was not found

texts:
I followed the instruction at https://dbatools.io/offline/:

download zip & unzip
run $env:PSModulePath to find all the paths.
copy to C:\Program Files\WindowsPowerShell\Modules.
run Get-ChildItem -Recurse   "C:\Program Files\WindowsPowerShell\Modules" | Unblock-File

When I try to run Export-DbaInstance -SqlInstance I got below error :

  The term Export-DBAInstance is not recognized".

Any help will be appreciated.


texts: why the optimizer doesn't choose the optimal plan

texts:
I know, if the statistic is not updatad, it is possible, that the optimizer doesn't choose the optimal execution plan?

Does any other reason/reasons exist?


texts: Is there any way to force MySQL use Hash Join instead of Nested Loop Join?

texts: According to the document MySQL Explain Output format, MySQL resolves all joins using a nested-loop join method. Is there any way to force MySQL to use a hash join?


texts: ORACLE Shifting from from one version to another

texts:
I have RHEL5 server with Oracle Home actual location is SAN, which is mounted on this server.

Now for few Reasons I have one more Server with RHEL 6 64Bit.

I will map those storage LUNS there.

What are the various ways to migrate and what are the advantages/disadvantages ?

Point 1: Take backup from old system and go for new install no new server ?

Point 2: Directly Map LUN and with few changes I can achieve this ?

Can I go for creating control files and copy and backup on new server and start making changes in spfile and pfile and go for changes ??


texts: creating a multi table "identity" column (faux identity)

texts:
I am currently designing a job execution engine and have run into a problem I am looking for some advice on.

I am converting some processes to a new system and need to define the table structure.  The process consists of 3 steps with different data for each step (3 different webservice calls).  Currently a flat file is generated with a RowID and the data for all 3 steps on the same row.  The new design is for each process will pull directly from tables and each step will have its own table with only the data fields needed for each step.

The problem is that the rows between the tables need to be linked so that after step 1 completes successfully we can act on step 2 with the proper data.  since none of the step data will be guaranteed to be unique (through business rules) I need to have an ID to link the tables together.  The basic table structure will be as follows:

CREATE TABLE Step1
(
BatchID INT,
BatchRowID ???,
ProcessData
)

CREATE TABLE Step2
(
BatchID INT,
BatchRowID ???,
ProcessData
)

CREATE TABLE History
(
BatchID INT,
BatchRowID ???,
StepID INT,
Result BIT --True False flag
)

I am looking for a solution to create a faux multi table IDENTIY column.  so that Process2 that uses a step 4 will have gaps where BatchRowID's are used for Process1.  The first thing that comes to mind is to use a GUID but as the clustered PK on the tables will be BatchID, BatchRowID I want to try and avoid a GUID if I can.   This is for multiple reasons, 1) Clustered GUIDs suck for inserts (however this is overcome slightly by including BatchID) and 2) The additional space used over an INT in the base table and in every index added to the table.

If any one has implimented something similar I would love to hear your solutions.

Currently the host server has not been determined so SQL Version could be 2008 or 2012.


texts: How to relate two rows in the same table

texts:
I have a table where the rows can be related to each other, and logically, the relationship goes both ways (basically, is directionless) between the two rows. (And if you're wondering, yes, this really should be one table. It is two things of the exact same logical entity/type.) I can think of a couple ways to represent this:

Store the relationship and its reverse
Store the relationship one way, constrain the database from storing it the other way, and have two indexes with opposite orders for the FKs (one index being the PK index)
Store the relationship one way with two indexes and allow the second to be inserted anyway (sounds kind of yucky, but hey, completeness)
Create some kind of grouping table and have an FK to it on the original table to it. (Raises lots of question. Grouping table would only have a number; why even have the table? Make FK NULLable or have groups with single row associated?)

What are some major pros and cons of these ways, and of course, is there some way I haven't thought of?

Here's a SQLFiddle to play with: http://sqlfiddle.com/#!12/7ee1a/1/0. (Happens to be PostgreSQL since that's what I'm using, but I don't think this question is very specific to PostgreSQL.) It currently stores both the relationship and its reverse just as an example.


texts: How to prevent Dropping of tables but allow Creating of them, within a Schema

texts:
Is it possible to, within a specific Schema, allow a user to create a table, and select from it, but not allow them to Delete or update rows after initial creation, and also prevent them from dropping it ?

Create Table as Select requires select permissions for the Select, however, the Create Table requires Create table and Alter Schema on the Schema.

The Alter Schema permission also includes permissions to drop the table, which we don't want.

We are using Azure SQL Data Warehouse, so can't use Triggers..


texts: Homebrew installed PostgreSQL could not connect, database files are incompatible with server

texts:
Here's the error message I'm getting today when I attempt to log in to my localhost PosgreSQL databases using psql:

psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

I installed PostgreSQL using Homebrew and this morning I've uninstalled and reinstalled using that utility, with no change observable.  The psql client works fine, and I can still use it to connect to databases over the network.  I only connect to my local databases once every month or two, and it worked fine before, so I'm assuming this is due to some automatic update but I don't know which.  Mac OS upgraded itself to High Sierra (10.13) recently.

The same error message popped up on here in 2014 (link) but that was a case where a guy had trouble starting the server.  In my case I believe it's running -- I've used brew services start postgresql and yes, I've restarted a few times today.  However, the file mentioned in the error, /tmp/.s.PGSQL.5432, does not exist on my system.  I think this is a case of some configuration mistake.  Anyone have a clue how to fix?

Update: I tried starting the server manually (as recommended by Evan) and I think I made a breakthrough. Here's the error I get when I try to start it manually:

FATAL: database files are incompatible with server
DETAIL: The data directory was initialized by PostgreSQL version 9.6, which is not compatible with this version 10.1.

Update 2: Ultimately I solved this problem by replacing my data directory and re-initializing it with an empty database using initdb as in the answer marked "correct".  To be fair, all three answers were helpful.  I was unable to update the database from the old version to the new one because I no longer had the binaries for the older version of Postgres.


texts: Overloaded Database - Batch Updates vs Atomic/Single Updates

texts:
We have on overloaded database which is now receiving failed transactions consequently. The proposed solution is to create a 2nd database that taskes all the (ecommerce) transactions initially and processes & persists them.  Then, eventually, the current/old database will be updated. That's the simple overview.

The question I have is - when I do the batch update from newDBServer -> OldDBServer every x minutes, won't it slow operations down on the old server during that update (assuming this isn't done overnight, etc...)?

The server is under a heavy load, so that's why I'm not certain it will really help minus the time in between updates. Is there a better way to do this?


texts: Can I remove the middle slave in a MySQL "master -> slave -> slave" setup?

texts:
Let's say I have the following setup:

Master -> Slave1 -> Slave2

I would like to remove the middle slave, Slave1, and have the following:

Master -> Slave2

My gut is that I can do the following:

Stop the Master -> Slave1 replication.
After Slave1 ->
Slave2 replication catches up, stop that replication as well.
CHANGE MASTER on Slave2 to point to Master
Turn on replication on Slave2
Enjoy new Master -> Slave2 setup.

Is this something that would work?


texts: Can I restore a database, from the same version SQL server, with different updates?

texts:
The main server ( the one I backed up the database ) is :

Microsoft SQL Server 2014 - 12.0.4100.1 (X64)
Apr 20 2015 17:29:27
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on
Windows NT 6.3  (Build 9600: )
(Hypervisor)

I'm trying to restore the database in this server:

Microsoft SQL Server 2014 (RTM-CU14) (KB3158271) - 12.0.2569.0 (X64)
May 27 2016 15:06:08  Copyright (c) Microsoft Corporation     Enterprise
Edition: Core-based Licensing (64-bit) on Windows NT 6.3  (Build
10586: )

But the restore never completes. using SP_WHOISACTIVE the complete_percentage is null ( for like, 10 minutes ).
In my own machine, I could restore the database, this is my server:

Microsoft SQL Server 2014 (SP1-CU7) (KB3162659) - 12.0.4459.0 (X64)
May 27 2016 15:33:17    Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3
 (Build 10586: ) (Hypervisor)

What can cause this problem? the database is 140GB. Is there a place where I should verify?
With verifyonly the file is valid.
I'm using T-SQL to the restore:
restore database Database
    from disk ='F:\Database.BKP'
        with
            move 'Database' to 'path\database.mdf',
            move 'Database2' to 'path\database.ndf',
            move 'Database3' to 'path\database_FULLTEXT',
            move 'Databaselog' to 'path\database_log',stats=1

One thing that I noticed was the wait type Async_io_
Is there a way to verify if the disk has a problem, but via SQL SERVER?
Oh, and when I pause the script, I got this message:

Msg 3122, Level 16, State 1, Line 4 File initialization failed.

RESTORE cannot continue. Msg 3204, Level 16, State 1, Line 4 The

backup or restore was aborted. Msg 3013, Level 16, State 1, Line 4

RESTORE DATABASE is terminating abnormally. Query was cancelled by

user.


texts: Restoring data from dumped sql file gives: mysql: unknown option '--innodb_file_per_table

texts:
I was trying to separate DB indexes from ibdata1 I took the steps in what-is-the-best-way-to-reduce-the-size-of-ibdata-in-mysql

I dumped data, dropped databases, and started mysql service.

I got the following error and now I'm stuck:

mysql -u root -p < alldb.sql
mysql: unknown option '--innodb_file_per_table'

What is the solution now?

When I remove the below lines from my.cnf everything works fine:

[mysql]
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=1G
innodb_buffer_pool_size=4G

Why innodb_file_per_table is an unknown option?

The weired thing is that I can set innodb_file_per_table dynamically and it separates .ibd file when I alter them to InnoDB engine.

EDIT:
It should be an unknown option as I've put it below [mysql] not [mysqld]. I went through the process again and now it works just fine. ibd files are in their respective database folder.

Phew...


texts: Are Cataloge and relation cache in PostgreSQL per connection or global to the server?

texts: I am trying to figure out if Postgres' relation and catalog cache are per connection or per server? I did find that plan caches are per connection. It would make sense for the cache to exist for as long as the server is running, but I can't find any documentation on it.


texts: SQL Server 2008: The target name, "package0.event_file", is invalid, or the object could not be found

texts:
What can cause the following error?

Msg 25623, Level 15, State 2, Line 1
The target name, "package0.event_file", is invalid, or the object could not be found

Given the following TSQL:

CREATE EVENT SESSION [CloudMigration]
    ON SERVER
    ADD EVENT sqlserver.sql_statement_completed
    ADD TARGET package0.event_file
    (SET
        filename = N'C:\PATH\TO\FILE',
        max_file_size = (2),
        max_rollover_files = (2)
    )
    WITH (
        MAX_MEMORY = 2048 KB,
        EVENT_RETENTION_MODE = ALLOW_MULTIPLE_EVENT_LOSS,
        MAX_DISPATCH_LATENCY = 3 SECONDS,
        MAX_EVENT_SIZE = 0 KB,
        MEMORY_PARTITION_MODE = NONE,
        TRACK_CAUSALITY = OFF,
        STARTUP_STATE = OFF
    );
GO

The TSQL came from this Microsoft article:

  https://docs.microsoft.com/en-us/sql/relational-databases/extended-events/quick-start-extended-events-in-sql-server?view=sql-server-ver15


texts:
I have a master database which would be the cloud server that consisted of different schools.

Now those schools are deployed to their corresponding school location which would be the local server.

Now what I want to happen is, to synchronize the cloud to local server on their corresponding school if something is changed. That also goes for local to cloud server.

Would anyone shed some light for me where to start off? I couldn't think of any solution that fit my problem.

I also think of using php to foreach loop all over the table and data that corresponds to current date and time. But I know that would be so bad.

I've seen some posts / articles regarding replication of master to slave.

MySql Replicate different tables to different database

Replicating from master to slaves and from slaves to master

https://dba.stackexchange.com/questions/144367/mysql-replication-master-to-multiple-slaves-with-table-filtering

The list goes on...

But yeah, I have no idea what's happening to what they are saying. I don't have any experience on replication master to slave.

The application pegs that I found are

Evernote
Todoist


texts: Neo4j service won't start with an imported database on Debian

texts:
I've been playing around with neo4j recently on my Debian 7.8 install. It's awesome!

Today I imported around 100000 nodes and 75000 relationships using neo4j-import tool. I stopped the neo4j service and copied the imported database to /var/lib/neo4j/data/graph.db - but now Neo4j service won't start

Starting Neo4j Server...WARNING: not changing user
process [4963]... waiting for server to be ready....... Failed to start within 120 seconds.
Neo4j Server may have failed to start, please check the logs.

Which is weird because it doesn't wait 120 seconds to start - 5 at most. The only log that is produced is console.log in /var/log/neo4j

2015-07-01 00:10:24.363+0000 INFO  [API] Setting startup timeout to: 120000ms based on 120000
2015-07-01 00:10:26.501+0000 INFO  [API] Successfully shutdown Neo4j Server.

which isn't very useful. When I remove the database, neo4j is able to start. I copied graph.db to my windows installation and it works well, I can search through nodes etc. so perhaps it has something to do with configuration on my Debian? I added a line in  /etc/neo4j/neo4j-server.properties

 org.neo4j.server.startup_timeout=0

but this changed nothing, service start still times out after several seconds. Any ideas?

Here's graph.db messages.log - nothing gets appended when I try to start the service

2015-07-01 01:58:12.142+0000 INFO  [org.neo4j]: Import starting
2015-07-01 01:58:12.282+0000 INFO  [org.neo4j]: Creating new db @ /var/www/ddl/scripts/./graph.db/neostore
2015-07-01 01:58:13.238+0000 INFO  [org.neo4j]: Missing counts store, rebuilding it.
2015-07-01 01:58:13.607+0000 INFO  [org.neo4j]: No lucene scan store index found, this might just be first use. Preparing to rebuild.
2015-07-01 01:58:13.663+0000 INFO  [org.neo4j]: No lucene scan store index found, this might just be first use. Preparing to rebuild.
2015-07-01 01:58:13.952+0000 INFO  [org.neo4j]: Rebuilding lucene scan store, this may take a while
2015-07-01 01:58:13.955+0000 INFO  [org.neo4j]: Lucene scan store rebuilt (roughly -1 nodes)
2015-07-01 01:58:30.337+0000 INFO  [org.neo4j]: IMPORT DONE in 18s 182ms. Imported:
  95941 nodes
  158227 relationships
  369482 properties
2015-07-01 01:58:30.341+0000 INFO  [org.neo4j]: About to rotate counts store at transaction 1 to [/var/www/ddl/scripts/./graph.db/neostore.counts.db.b], from [/var/www/ddl/scripts/./graph.db/neostore.counts.db.a].
2015-07-01 01:58:30.344+0000 INFO  [org.neo4j]: Successfully rotated counts store at transaction 1 to [/var/www/ddl/scripts/./graph.db/neostore.counts.db.b], from [/var/www/ddl/scripts/./graph.db/neostore.counts.db.a].


texts: Why can't we have multiple cascade paths?

texts:
You can see that many questions have being asked about multiple cascade paths. For example:

https://stackoverflow.com/questions/851625/foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths

https://stackoverflow.com/questions/6065501/multiple-cascade-delete-path-in-many-many-relationship-ef-4-1

https://stackoverflow.com/questions/27613117/introducing-foreign-key-constraint-may-cause-cycles-or-multiple-cascade-paths-s

However, from what I see and understand, it's totally OK that you want to delete a child record on many and not just one condition of the deletion of the  related master records.

While in a question it's said that SQL Server tries to be safe by preventing this from happening, I really don't get it that what might go wrong if we have multiple cascade paths, and what problems it prevents to make it safe?

I hope someone can explain that to me, in plain and simple words, preferably using examples of what might go wrong in case of multiple cascade paths.


texts:
I have 3 identical values:

2814|Venerdì 13  |1980   |Orrore      |1:30    |Dvd      |Italiano
2815|Venerdì 13  |1980   |Orrore      |1:30    |Dvd      |Italiano
2816|Venerdì 13  |1980   |Orrore      |1:30    |Dvd      |Italiano

I have tried to delete mutiple with this query

delete from titolo where idtitolo > '2814'  and idtitolo < '2815';

But this deleted 0 lines.
How delete two values using one command?


texts:
[5 Marks] Let R(A,B,C,D,E) be decomposed into relations with the following three set of attributes {A,B,C}, {B,C,D}, and {A,C,E}. For each of the following sets of FD's, use the chase test to tell whether the decomposition of R is lossless. For those that are not lossless, give an example of an instance of R that returns more than R when projected onto the decomposed relation and rejoined.

a. A→D, CD→E and E→D.
b. A→D, D→E and C→D.

For the question above, my work for each of the questions is below the concerns. Here are my main concerns:

Does the order in which you use the relations matter?
Can you end up with less tuples with the chase test?
Is my approach correct?

-----------------------Part A below----------------------------------
InitialTableau = T₁ ⋈ T₂ ⋈ T₂
+----+----+----+----+----+
| A  | B  | C  | D₁ | E₁ |
+----+----+----+----+----+
| A₂ | B  | C  | D  | E₂ |
+----+----+----+----+----+
| A  | B₂ | C  | D₂ | E  |
+----+----+----+----+----+

-----------------------ANSWER TO QUESTIONS START NOW-----------------------
a) After changing the initial tableau in a way that ensures that the FD's given in the question are satisfied, we get the following tableau.
+----+----+----+----+----+
| A  | B  | C  | D₁ | E  |
+----+----+----+----+----+
| A₂ | B  | C  | D  | E₂ |
+----+----+----+----+----+
| A  | B₂ | C  | D₁ | E  |
+----+----+----+----+----+

Since we do not have an unsubscribed row, this relation is lossy/not lossless.

Example of an instance R (Were going to use the final tableau):
R₁(A,B,C)
+----+----+----+
| A  | B  | C  |
+----+----+----+
| A₂ | B  | C  |
+----+----+----+
| A  | B₂ | C  |
+----+----+----+

R₂(B,C,D)
+----+----+----+
| B  | C  | D₁ |
+----+----+----+
| B  | C  | D  |
+----+----+----+
| B₂ | C  | D₁ |
+----+----+----+

R₃(A,C,E)
+----+----+----+
| A  | C  | E₂ |
+----+----+----+
| A₂ | C  | E₂ |
+----+----+----+
| A  | C  | E  |
+----+----+----+

After Joining the above relations, we get:
+----+----+----+----+----+
| A  | B  | C  | D₁ | E  |
+----+----+----+----+----+
| A  | B  | C  | D  | E  |
+----+----+----+----+----+
| A₂ | B  | C  | D₁ | E₂ |
+----+----+----+----+----+
| A₂ | B  | C  | D  | E₂ |
+----+----+----+----+----+
| A  | B₂ | C  | D  | E  |
+----+----+----+----+----+

since we have 2 more rows than the original tableau, this decomposition is not lossless.

b) After changing the initial tableau in a way that ensures that the FD's given in the question are satisfied, we get the following tableau.
+----+----+----+----+----+
| A  | B  | C  | D  | E  |
+----+----+----+----+----+
| A₂ | B  | C  | D  | E  |
+----+----+----+----+----+
| A  | B₂ | C  | D  | E  |
+----+----+----+----+----+

Since we have an unsubscribed row, this decomposition is lossless.


texts: DBCC Shrinkfile after full backup restoration but before differential restoration

texts:
My database full backup file size is 760 GB and differential would be around 20 GB but the database size is around 2TB. As it is a production database, so to apply only a minimum downtime, i want to run DBCC SHRINKFILE with TRUNCATEONLY before restoring differential backup.

Please advise if i can run the command DBCC SHRINKFILE with TRUNCATEONLY after full backup restored but before differential backup restoration.


texts:
Assume the following schema:

ID  | TENANT | TENANT_CUSTOM_ID
346 | 101    | 1
347 | 102    | 1
348 | 101    | 2
349 | 101    | 3
350 | 101    | 4
351 | 102    | 2
352 | 103    | 1

What is another name (or the proper classification) for TENANT_CUSTOM_ID? Perhaps something along the lines of a surrogate key? The purpose of this key is to make each tenant have their own numbering system instead of knowing that their records are mixed with all other tenants on the same tables.


texts: Is the limit on the number of databases in an Azure SQL managed instance a hard limit?

texts: I see the documentation for Azure SQL Managed Instance lists a limit of 100 databases per managed instance.   I would like to understand if this is a soft limit that is adjustable on request or a hard limit.


texts: Can't list columns or query in MS Access 2003 linked server in SQL Server 2008 R2 using Microsoft Jet 4.0 OLE DB Provider?

texts:
I can however list the tables...

If I run Exec sp_tables_ex 'LSTest' I get a result with a list of tables.

But if I run

Exec sp_columns_ex 'LSTest', '<one of those table names>'

I get an empty list, and if I try to query them I also get nothing.

If I go into the properties of the Linked Server I have the Be made using this security context and I use the Remote login: and With password: for the workgroup file that I would normally use in MS Access.

If I try to script a query by right clicking the table name in the linked server, instead of a select statement I get the following:

-- [LSTest]...[Tablename] contains no columns that can be selected or the current user does not have permissions on that object.


texts: parallelism repartitions, ordering, and hash matches

texts:
I am just starting to delve into query plans and recently learned that "hash matches" are bad.  I know it's rare for something to be so absolute, though, and I want to determine if I have a bad hash match.

My query, which includes two tables, uses parallelism.  It uses clustered index seeks on the two tables then it goes to the step Parallelism (Repartition Streams) with partitioning type of hash after both of these seeks.  For one table, a bitmap is created and then these two are joined (inner join) by hash match.

My understanding is if the data to be joined is ordered, a merge join would be used - and this is preferable.  The clustered indexed seek is ordered.

So... I guess I'm just looking for some information on this situation.  Do hash matches always follow repartitions of streams?  Does that repartition ignore order?  Or should I not be bothered by this "evil" hash match?

Thank you!!


texts: How to prevent logins using Windows Authentication in SQL Server?

texts: SQL Server supports creating logins using SQL, Windows, certificates and asymmetric keys. Is it possible to remove the option of creating logins with Windows authentication (and just allow SQL authentication for example)?


texts: Making the tables with db generalization

texts:
Can anyone tell me what will be the best way to make the tables for the following diagram:

I was thinking about this:

videos
------

 - id
 - title

music_videos
------------
 - id
 - video_id
 - artist
 - genre

lessons
-------
 - id
 - video_id
 - teacher

Is it right?

(i'm using MySQL if that makes any difference)