texts: pt-table-sync fails "Turning off AutoCommit"

texts:
While trying to use pt-table-sync with the following options for a list of slaves:

pt-table-sync --print --replicate schema.checksum_table --sync-to-master slave-001

I receive the following error message on some of the slaves:

DBD::mysql::db STORE failed: Turning off AutoCommit failed at /usr/lib/perl5/DBI.pm line 723.

The MySQL version for the slave is 5.6.17 and for the master 5.6.15. Other slaves for which this does work also have MySQL 5.6.15 version.
I am able to turn off session autocommit if I log in to the slave directly using MySQL client and the same user being used for pt-table-sync.
The checksum table in use was created by previously running pt-table-checksum.

DBI.pm version:

perl -MDBI -e 'print "$DBI::VERSION\n";'
1.63

Has anyone encountered this issue before?

Please let me know if you need me to provide more information.


texts:
I'm trying to group two columns in my MySQL database and show counts for them respectively (as separate columns). Is there a way of doing this or do I have to join two sub-queries?

I'm aware of WITH ROLLUP but can't produce the right query.

This gives the right result:

SELECT A.mid, B.pid, A.count AS mid_count, B.count as pid_count
  FROM (Select mid , COUNT(*) AS count FROM test GROUP BY mid) AS A,
  (Select mid, pid, COUNT(*) AS count FROM test GROUP BY mid, pid) AS B
  WHERE A.mid = B.mid ORDER BY mid_count DESC

This is the expected output

+-----+-----+-----------+-----------+
| mid | pid | mid_count | pid_count |
+-----+-----+-----------+-----------+
|   3 |  12 |         5 |         2 |
|   3 |  14 |         5 |         1 |
|   3 |  15 |         5 |         2 |
|   4 |  12 |         2 |         1 |
|   4 |  17 |         2 |         1 |
+-----+-----+-----------+-----------+

I've created a fiddle at: http://sqlfiddle.com/#!9/e3ebe1/12


texts: What's the difference between mysql-community-server and mysql-community-server-minimal?

texts:
I just downloaded the first GA release of MySQL 5.7, and noticed that the RPM bundle comes with two versions of the MySQL server package:

mysql-community-server-5.7.9-1.el7.x86_64.rpm
mysql-community-server-minimal-5.7.9-1.el7.x86_64.rpm

What's the difference between the normal and the "minimal" server packages?

I could not find this information on the MySQL website.


texts: Are SQL Server in-place upgrades as ill advised as they used to be?

texts:
I have been working with SQL server on and off since SQL Server 6.5, the old advice that still rings in my head was never to do an in-place upgrade.

I'm currently upgrading my 2008 R2 DEV and TEST systems to SQL Server 2012 and need to use the same hardware. The thought of not having to restore my Reporting services configuration is very attractive and i'm really up against the wall time wise. There is no analysis services involved or anything unusual or non-standard - only the database engine and reporting services are installed.

Has anyone experienced serious issues with in-place upgrades? Or should I reevaluate my position about in-place upgrades?


texts: Restore database from backup file of different version / edition

texts:
I read that it's possible to restore a database in SQL Server as long as you're restoring from an older version to a newer version, for backwards compatibility reasons.

Does anyone know off hand if you can restore a database from a *.bak file for different editions of SQL Server?  We are moving a very large database via FTP that will take a couple days, so we'd rather only do this once. If nobody responds by the time we transfer the database via FTP, we will obviously try this out and see if it works by testing, and answer our own question.

Below is a query to get version details of SQL Server.  The productversion is in the format {major revision}.{minor revision}.{release revision}.{build number}.  In my case, the {release revision} has a value of 5500 for the source and 5512 for the target.  So that looks okay. However, the edition is different.

Query:

SELECT
  SERVERPROPERTY('productversion'),
  SERVERPROPERTY('productlevel'),
  SERVERPROPERTY('edition')

Source database:

10.0.5500.0
SP3
Developer Edition (64-bit)

Target database:

10.0.5512.0
SP3
Enterprise Edition (64-bit)


texts: How to prevent regular users to backup database, in Postgresql

texts:
How can I prevent/restrict regular users, that do have select rights granted on tables etc, to perform backup of Postgres database?

Is there a way to enable users to connect, select, update etc. tables in the database, but to disable them to backup database (e.g. via pgAdmin4)?


texts:
i have a mysql 5.6.19 on linux server, and it takes about 8 Gb of memory. That is much more than i expect.

There are about 150 db, each with 700 tables. All of them are innodb. We do not care about their safety.

SHOW ENGINE INNODB STATUS - Total memory allocated 4395630592.
So i can't understand why mysql takes additional 3.5 Gb, any suggestions ?

my.cnf
innodb_log_buffer_size = 100M
innodb_log_file_size=1000M
innodb_flush_log_at_trx_commit=0
innodb_open_files=50000
innodb_flush_method=O_DIRECT_NO_FSYNC
innodb_force_recovery=0
innodb_buffer_pool_size=4G
innodb_flush_log_at_timeout=5
innodb_stats_on_metadata=OFF
innodb_checksums=OFF
innodb_thread_concurrency=0
innodb_read_io_threads=64
innodb_write_io_threads=64
innodb_file_per_table=0
innodb_sort_buffer_size=2M
max_connections=400
tmp_table_size=20M
max_heap_table_size=20M
innodb_ft_total_cache_size=64000000
innodb_ft_result_cache_limit=100000000

interactive_timeout=1500
wait_timeout = 300

open_files_limit=50000
table_open_cache=20000
table_definition_cache=20000


texts: Create window (PARTITION BY?) out of column values

texts:
I have a table structured as:

| date       | key | value |
|------------|-----|-------|
| 2019-01-02 |S1   |20     |
| 2019-02-04 |S1   |30     |
| 2019-03-10 |S2   |15     |
| 2019-04-07 |S1   |0      |
| 2019-04-13 |S2   |35     |
| 2019-04-19 |S1   |10     |
| 2019-05-01 |S1   |30     |
| 2019-05-15 |S1   |40     |
| 2019-06-21 |S1   |0      |

I want to retrieve the date of the first record associated with each key and date, when the given key is 0. If there are multiple 0 associated with each key, I want to partition by 0 and compute that for each partition.

Using the table above as an example, below is the result for S1:

The first partition is as follows:

| 2019-01-02 |S1   |20     | <- Output this
| 2019-02-04 |S1   |30     |
| 2019-04-07 |S1   |0      | <- And this

and it's output should be

| date       | date_of_zero |
|------------|--------------|
| 2019-01-02 | 2019-04-07   |

The second partition will be

| 2019-04-19 |S1   |10     | <- Output this
| 2019-05-01 |S1   |30     |
| 2019-05-15 |S1   |40     |
| 2019-06-21 |S1   |0      | <- And this

and it's output will be

| date       | date_of_zero |
|------------|--------------|
| 2019-04-19 | 2019-06-21   |

The overall result expected:

| key | date       | date_of_zero |
|-----|------------|--------------|
| s1  | 2019-01-02 | 2019-04-07   |
| s1  | 2019-04-19 | 2019-06-21   |

I have tried to come up with solutions using PARTITION BY and LATERAL JOIN but I do not even know how to get started, and partition by value = 0 as WHERE clause do not work with partition expressions.

I am wondering if this is something solvable in SQL (within reasonable query complexity) or one is better off fetching the rows and doing windowing at the application layer?


texts: Spinning multiple environments from single backup file

texts: I am having a unique situation where my production database size 5TB (SQLSrv) which needs to be replicated across 4 servers daily. I am looking for an option where all 4 environments can use a single backup file as the source and continue to perform the write operation in isolation thereafter. It will save a lot of disk space and restoration time (3hours per env)


texts: I created a DB schema for a project relating to collecting comments for web pages, is relational the way to go and how are my relationships?

texts: I'm curious if relational DB is the way to go, if the current relationships in the diagram make sense, and whether it will bode well for coding.


texts: sqlcmd gives no sign of failure yet doesn't work

texts:
I am running sqlcmd from a linux bash script. My target server is an Azure SQL.  I execute an sql file with a lot of sql statements (around 2500). These are Insert/update/delete statements. I used the following command

sqlcmd -i $SQL_SCRIPT_FILE -m-1 -p

Initially there were individual sql statements in the file. When executing that, a lot of statements were being missed out (I found that by checking the database, as some of the insert statements does not reflect in db).

Then I put all the statements in try catch and transaction. I am writing the exceptions in an ErrorLog table.
Now when I execute the file, nothing happen. I do not see any error in ErrorLog table, the sqlcmd output also not display anything specific. The output is:

(0 rows affected)
Network packet size (bytes): 4096
1 xact[s]:
Clock Time (ms.): total        23  avg   23.0 (43.5 xacts per sec.)


texts: We need to do encryption in transit in mongodb so which one is recommended ssl or tsl ? and what is the difference between ssl and tls?

texts: We are using MongoDB 4.0 community version with 1 primary 2 secondary replicaset, we need to do encryption in transit so which one is recommended ssl or tsl ? and what is the difference between ssl and tls ?


texts: UPDATE column without FK fires other FK triggers constraint check

texts:
I'm using PostgreSQL 9.6 and i have noticed that when i execute an UPDATE on a single column without foreign key it will fire all triggers for other columns that have a value and a foreign key.
I see this thanks to EXPLAIN ANALYZE that gives me "Trigger for constraint" for each one of those other columns.
With massive updates those triggers takes 50% of the execution time

EDIT:

CREATE TABLE parent (
    id INTEGER,
    PRIMARY KEY(id)
);

CREATE TABLE child (
    id INTEGER,
    is_enabled BOOLEAN DEFAULT FALSE,
    parent_id INTEGER references parent(id),
    PRIMARY KEY(id)
);

INSERT INTO parent (id) VALUES (1);
INSERT INTO child (id, is_enabled, parent_id) VALUES (1, FALSE, 1);

UPDATE child SET is_enabled = FALSE WHERE id = 1;
EXPLAIN ANALYZE
UPDATE child SET is_enabled = TRUE WHERE id = 1;

OUTPUT:

Update on child  (cost=0.16..8.17 rows=1 width=15) (actual time=0.017..0.017 rows=0 loops=1)
  ->  Index Scan using child_pkey on child  (cost=0.16..8.17 rows=1 width=15) (actual time=0.004..0.005 rows=1 loops=1)
        Index Cond: (id = 1)
Planning time: 0.033 ms
Trigger for constraint child_parent_id_fkey: time=0.168 calls=1
Execution time: 0.199 ms

Notice Trigger for constraint child_parent_id_fkey even if i haven't changed parent_id

EDIT 2:
It happens when issuing 2 update statements, same transaction, that covers the same row even if nothing is changed and no FK column is used.
Here is a dbfiddle by joanolo

Is this an expected behavior? Should i fill a bug report?
Now i'm trying to avoid to touch same rows twice in the same transaction


texts:
The wait event “log buffer space” according to the documentation can indicate that the log buffer is too small or the I/O is slow.  We have a system seeing this event in which the redo buffer allocation retries is going up in V$SYSSTAT as observed with...

SELECT VALUE FROM V$SYSSTAT WHERE NAME = 'redo buffer allocation retries';

We recently increased the LOG_BUFFER value from 8 MB to 16 MB with only a small reduction in the retries.  It is still increasing at about 1,000 a day.

Should the LOG_BUFFER value be increased further?

Since the system is 64 bit I can take it up to 256 MB, but I don’t want to use the memory for this buffer if it won’t be beneficial and I don’t know if there are any downsides to increasing this value that significantly.


texts: Newly created jobs need to be set for failure notifications automatically

texts: Whenever a new / existing job gets failed on an instance of SQL Server, we should recieve an email about failure notification. Please help on how we can automate it?


texts: How to store high-dimensional (N > 100) vectors and index for fast lookup by cosine similarity?

texts:
I am trying to store vectors for word/doc embeddings in a PostgreSQL table, and want to be able to quickly pull the N rows with highest cosine similarity to a given query vector. The vectors I'm working with are numpy.arrays of floats with length 100 <= L <= 1000.

I looked into the cube module for similarity search, but it is limited to vectors with <= 100 dimensions. The embeddings I am using will result in vectors that are 100-dimensions minimum and often much higher (depending on settings when training word2vec/doc2vec models).

What is the most efficient way to store large dimensional vectors (numpy float arrays) in Postgres, and perform quick lookup based on cosine similarity (or other vector similarity metrics)?


texts: JDBC pooling Oracle driver Socket read timed out

texts:
I run Java EE application on Glassfish server v3 together with Oracle 12 DB on the same machine under Windows Server 2012 64bit. I use latest ojdbc7 driver.

Connection pool config:

<jdbc-connection-pool validation-table-name="DUAL" steady-pool-size="20" statement-cache-size="100" associate-with-thread="true" statement-timeout-in-seconds="30" idle-timeout-in-seconds="60" max-wait-time-in-millis="2000" validate-atmost-once-period-in-seconds="20" datasource-classname="oracle.jdbc.pool.OracleDataSource" pool-resize-quantity="5" max-pool-size="60" res-type="javax.sql.DataSource" name="dbPool" is-connection-validation-required="true">
  <property name="driverClass" value="oracle.jdbc.OracleDriver"></property>
  <property name="user" value="xxx"></property>
  <property name="url" value="jdbc:oracle:thin:@(DESCRIPTION=(ENABLE=BROKEN)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=orcl)))"></property>
  <property name="password" value="xxx"></property>
  <property name="portNumber" value="1521"></property>
  <property name="databaseName" value="orcl"></property>
  <property name="serverName" value="127.0.0.1"></property>
  <property name="oracle.jdbc.ReadTimeout" value="300000"></property>
  <property name="oracle.net.CONNECT_TIMEOUT" value="10000"></property>
</jdbc-connection-pool>

After 2 or 3 hours, when there is more than 1 user (3-5) using my application, it stops responding and I get this in glassfish logs

javax.enterprise.resource.resourceadapter.com.sun.enterprise.resource.allocator|_ThreadID=152;_ThreadName=Thread-2;|RAR5038:Unexpected exception while creating resource for pool dbPool. Exception : javax.resource.spi.ResourceAllocationException: Connection could not be allocated because: IO Error: Socket read timed out
[...]
Local Exception Stack:
Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.3.2.v20111125-r10461): org.eclipse.persistence.exceptions.DatabaseException
Internal Exception: java.sql.SQLException: Error in allocating a connection. Cause: Connection could not be allocated because: IO Error: Socket read timed out

From the database side it looks like this

Fatal NI connect error 12560, connecting to:
 (LOCAL=NO)

  VERSION INFORMATION:
    TNS for 64-bit Windows: Version 12.1.0.1.0 - Production
    Oracle Bequeath NT Protocol Adapter for 64-bit Windows: Version 12.1.0.1.0 - Production
    Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 12.1.0.1.0 - Production
  Time: 13-JUN-2014 03:14:49
  Tracing not turned on.
  Tns error struct:
    ns main err code: 12560

TNS-12560: TNS:protocol adapter error
    ns secondary err code: 0
    nt main err code: 0
    nt secondary err code: 0
    nt OS err code: 0
opiodr aborting process unknown ospid (3404) as a result of ORA-609

When I just reset db listener everything works ok for next 1-2 hours (depends on application load). So temporary solution is to run bat script from windows scheduler to reset the listener every 1h.

I tried everything I could find - applied these parameters:

- Sqlnet.ora:
SQLNET.INBOUND_CONNECT_TIMEOUT=180
SQLNET.EXPIRE_TIME=5
- Listener.ora:
INBOUND_CONNECT_TIMEOUT_LISTENER_IPC=120

But still without success


texts: SQL Server - backup / restore schema only

texts:
We have a DB on over 100 servers of which we want to backup / restore the structure of just 1 at a time to a specific UAT database. They're all called the same thing but vary in terms of schema - maybe from where someone has backed up data into another column, updated the original column and not deleted the backup column, for example.

There are also varying column sizes (i.e. column a on table b on one server may be nvarchar(7500) but same column on same table on another server may be nvarchar(max)).

Furthermore some sprocs were originally created with dependencies on views (for example) but the views have been deleted.

We've tried the following approaches:

Backup / restore entire database, delete unwanted data

UAT server doesn't have enough room for some databases, enlarging is not an option

Backup / restore small database, truncate all tables and insert into from other server

Not suitable as schema varies from one server to the next

Loop through tables, select * into <tablename> from [ip].[db].[schema].[tablename] where ...

Creates table structures fine including data types, but primary keys, triggers, sprocs etc. not created - we've tried scripting them from various sys tables but due to some views being removed some sprocs fail (same with triggers if they use removed views)

We basically just need to copy across the data for one client only onto a UAT server, run some tests against that and then move onto the next client. Forget things outside the database, these are not part of my task; my task is simply to replicate the structure of a DB from one server to another including sprocs, triggers and primary keys. We will potentially have up to 36 DBs on each of 2 UAT servers, hence the space restriction (6 per developer allowing up to 6 clients on the server per developer). We may be able to increase the number of servers and make it, say, 12 DBs per server, but to encourage those who can get these servers we will have to have a working proof of concept first, if you get what I mean.

Method 3 above gets closest but the fact that sprocs may not even exist is not satisfactory - if any program detects the cause of the error, it may act differently depending on whether the sproc is missing or failed during execution. There might be some code which executes before failure, in which case this would be executed each time the sproc is run on live but not at all on UAT.

Lastly I'm running SSMS 2005 and I believe servers vary between SQL Server 2005 and 2008.

Does anyone have any ideas on how to achieve this? Is it even possible, bearing in mind we physically cannot do a full backup / restore and cannot script all sprocs due to non-existence of views?


texts: How to display the number of days elapsed between two dates denoted in columns-specifically those exceeding 30 days?

texts:
Perhaps I'm overthinking this Oracle SQL query, but I'm attempting to display the number of days elapsed between two column data-sets attributed as "order-date" and "ship-date", which will be displayed within a new alias column, "Number of Days elapsed"- so far, I've constructed the following query:

SELECT order_id,
       (shipped_date - order_date) AS "Number of Days elapsed"
FROM orders;

Evidently, an alias column can't be sorted or filtered- so what options are available to filter the resulting output for the number of orders exceeding 30 days to ship?


texts: Concept to keep track of changes in all tables

texts:
In my current project I use about 10 MySQL tables (Doctrine as DBAL). I'd like to keep track of all changes in the databases (insertions, updates and deletions).

At the moment there are two approaches which might be the solution:

Create a table named history which stores all changes. Each time there is a change insert a new row in this table. It might have this fields:

id

target_table (name of the table)

target_id (ID of the saved entry)

created_at

data (serialized data of the saved row)
Add to each the table the following columns:

history_id (NULL: most recent entry, history_id = b.id: an old state of b, history.id pointing to nowhere: deleted object)

Are there any other approaches to keep track of all changes? Would you recommend one of my approaches?


texts:
I have a production database that's consuming a significant amount more disk space than it should be.

Using sp_spaceused (only the interesting tables), I get.

Table Name    Reserved (KBs)    Data (KBs)    Index Size (KBs)
----------    --------------    ----------    ----------------
E              149600             142992         1136
H              184968             12104        162272
M               94104             29512         62480
T               25616             13544          9464
MH              16072             10640          5208
U             5399312           2984184       2414376
Total         5869672           3192976       2654936

However, the on disk size is a whopping 16 GB database and 6 GB log file.

The culprit is mainly Table U.  Most of the data is very static with just lots of UPDATE to existing rows.  Table U is the ony with loads of data being added constantly, and being archived weekly.

Table U's definition is,

[ID]      INT                         NOT NULL,
[RD]      DATETIME                    NOT NULL,
[UN]      CHAR (7)                    NOT NULL,
[A]       BIT                         NOT NULL,
[C]       BIT                         NOT NULL,
[S]       DATETIME                    NOT NULL,
[rowguid] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL

It receives about 5 million rows per week, and archives another 5 million rows per week.

It has not been partitioned / placed into separate file groups.

The system has been arround for approximatley 6 months, and I don't believe our DBA team have ever shrunk it / defragged it!

Questions

Why is SQL using so much more disk space than sp_spaceused says?
Is the churn responsible for most of this disk space issue?
How should I use (SQL commands please) partitions and file groups to better optimise this churn?


texts: MySQL Clustered Index or Non Clustered index? (Database Design)

texts:
I know this might be a basic thing for you guys in this corner of SE but I'm having some trouble figuring out the best option for designing my DB.
I am creating a custom app where the user will be entering data into a table(lets call this table "manifest") at least 1 entry every minute, each entry will have data grabbed from the input field and when adding the new row, the current date and time is also being added to a datetime column.
This table will later be browsed by the app users on the front end at least 4 times a week when they come to do some exporting of their previously entered data.
I expect them to a lot of ordering by date, so I was planning to do an index on the date column for the first 8 characters of the MYSQL DATE = 00/00/00 (id leave out the time portion).

From what I know about indexes is that they slow down inserting and updating of data because the index needs to be rebuilt every time new data is entered.
This sounds like it would be inefficient to create an index(other than the primary key[id]) if the users will be inputting data at least once a minute for maybe 2 hours straight(per day) and the index would have to be rebuilt every minute(?) new data comes in.

I know this table would just keep growing because they need to keep track of the records for years on end, that's why I'd want to put an index on the date column to make it future proof in terms of speed. But the fact that they'd be inputting data into it so frequently and indexes would have to be rebuilt every time has me a little lost on how to proceed.
I was thinking a non-clustered index would solve the issue? I've watched several videos explaining the two and it seems that the rebuilding would still need to take place for non-clustered indexes?
Any help and performance tips would be appreciated


texts:
There is a database on a server of which I would like a copy on my local machine for testing purposes, so I created a backup in the default disk location (C:\Program Files (x86)\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup). The backup dialog says it completes successfully, but when I go to restore the .bak, it's not visible to the file selection dialog (significant file names blurred, obviously).

There are other viable backups in the same location, but the one I supposedly created does not appear. When attempting to recreate the backup, the one I created appears as a selectable option, while the others in the folder do not.

What is the issue here? I don't think it's a permissions problem because I'm able to access the other backups in the same folder.

Edit: The backup was on the server which has a similar file structure to my machine. I remoted in to the server and copied the file. Thanks for the help!


texts: SQL Server version numbers, when related to hotfixes, are they "inclusive"?

texts:
I am looking at the list of version numbers from this page: Microsoft SQL Server Version List, and here's an excerpt:

9.00.3182   2005.90.3182.0  940128 FIX: You receive error 8623 when you run a complex query in SQL Server 2005  August 3, 2007
9.00.3179   2005.90.3179.0  938243 FIX: Error message when you run a full-text query against a catalog in SQL Server 2005: "The execution of a full-text query failed. The content index is corrupt."   July 30, 2007
9.00.3178   2005.90.3178.0  938086 FIX: A SQL Server Agent job fails when you run the SQL Server Agent job in the context of a proxy account in SQL Server 2005 August 22, 2007

(note, the question is not about what I should install for SQL Server 2005, it's about that list, and the hotfix version numbers)

Since all of these 3 are hotfixes, if my SQL Server reports version 9.00.3182 (the topmost, latest hotfix, of the 3 I extracted), does that imply that the previous 2 has been installed as well?

I see that a lot of later hotfixes or patches are named "Cumulative update package". Is that just a naming convention or was the earlier ones that aren't named like that individual hotfixes?

Concrete question example. With the above 3 hotfixes, and SQL Server reporting version number 9.00.3182, which of the 3 scenarios below is the most correct one:

All three hotfixes have been applied.
Hotfix 9.00.3182 has been applied, but definitely not the other 2.
Hotfix 9.00.3182 has definitely been applied, there simply isn't enough information available to determine if the other 2 have been applied or not.

The reason I am interested in this is that I'm creating a class library for .NET for reporting what is installed on the SQL Server, since there isn't a single week where someone doesn't ask how to check for a specific .NET version, Windows version, or SQL Server version on Stack Overflow. I thought I at least, for my own part, that I should do enough research to be able to build a class library that can answer it, and thus have a definite answer to give for those questions.

But from the list on that page, given a hotfix KB940545, which SQL Server will report as 9.00.3186, my question is thus if the version number is above that, can I report that KB940545 has been installed (either specifically, or as part of later hotfixes/patches), or is it simply not possible to know this information?


texts:
Can database process make automatic checkpoint when I'm in process of copying files during

ALTER DATABASE BEGIN BACKUP;

and

ALTER DATABASE END BACKUP;

You can assume that process of copying files will last for very long time.


texts:
My customer is running MS SQL Server 2000.
I completed a project, but I failed to realize that MS SQL Server 2000 will not allow a select inside insert into values thus giving the error:

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

Th

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

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

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

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


texts: Page Number in SQL Server database is not starting from zero

texts:
I have a question on SQL Server page, I have created a new database with only one new table as below
Here is the Code:

CREATE TABLE PAGETEST
(
    ID int primary key
)

When i see my staring page number= FileID:PageNumber it is starting 1:285.
Q 1). Why my page not starting from 1:0 or 1:001?
Q 2).Will this page number have dependency from model database? Kindly find the below images


texts:
To insert massive CSV file into MySQL, there is LOAD DATA INFILE, but is there any similar command to delete massive entries with keys stored in a massive CSV file?

If not, what would you see as the fastest way to perform such operation?

EDIT: Also which one of these 2 methods would you see as the most efficient:

Multiple deletes

DELETE FROM myTable WHERE mykey=1 LIMIT 1;
DELETE FROM myTable WHERE mykey=2 LIMIT 1;
...
DELETE FROM myTable WHERE mykey=300000 LIMIT 1;

Or Batch delete

DELETE FROM myTable WHERE mykey IN (1,2,...,10000);
DELETE FROM myTable WHERE mykey IN (10001,10002,...,20000);
...
DELETE FROM myTable WHERE mykey IN (...,...,300000);

Thanks!


texts: How to use column alias to calculate other column value

texts:
My query is

SELECT
    (`house_rent`+`conveyance`+`medical`+`dearness`+`others_allowances`) AS earnings
    ,(`income_tax`+`pro_tax`+`emp_state_insu`+`absence_fine`+`others_deductions`) AS deductions
   ,(earnings - deductions) AS net_salary
FROM
    salary

and im getting an error that unknown column earnings and deductions because these are column alias not column name ..
Any solution?
Thanks in advance


texts: how to find out if it is safe to uninstall an application from a production sql server?

texts:
I noticed Microsoft Office components installed in a production SQL Server Box (which is part of an availability group - currently the principal)

OS is microsoft windows server 2012 r2 datacentre,

  Processor Intel(R) Xeon(R) CPU E5-2690 v3 @ 2.60GHz, 2597 Mhz, 4
  Core(s), 4 Logical Processor(s)

I find installed the Microsoft Office 2013 -  but the license is not activated.

I also find the following office applications:

and when I look at the windows task scheduler I see the following 3 scheduled tasks that are related to the office application.

I have already disabled the Telemetry Windows schedules.

I don't believe there will be a problem in uninstalling this office from the production server, still I would like to be clear.

QUESTION:

How can I find out if there are any dependencies from sql server to this application?

P.S.
As it is a virtual machine, VMWARE, I will create an snapshot before I do the uninstall, if any problem I have a safe place to roll back to.


texts:
I'm fairly new to the SQL Server stack, and am trying to wrap my head around how to best expose on-premise SSRS reports in websites (LAMP stack).

URL Access appears to be what I'm after, as it lets me expose a nice reporting dashboard in an iframe.

I'm stumped with authentication. The websites in question require users to authenticate (form-based), and the reports in question contain sensitive/private information (so they can't be unprotected). I need SSO (saml, oauth, etc). However, in the documentation for Authentication with the Report Server, I find:

  There is no native support for single sign-on technologies in Reporting Services. If you want to use a single sign-on technology, you must create a custom authentication extension.

Again, I'm new to the SQL Server stack, but I assume I'm not the first person to want to do this. The thought of writing a custom authentication extension to support SAML/OAUTH scares me - are there commercially available (and supported) extensions?

Am I barking up the wrong tree with SSRS here? Is there something else in the SQL Server stack that I should be looking at instead to achieve my goal of embedded reports?


texts:
I have a database accessed by around 50 clients via TDS over TCP which does not seem to be releasing log space.  The number of processes stays around the expected 50, and some of them are quite long lived (>120 days).

The database now has 40 gb in log space (it only has 14 gb data), 39 gb free.  Due to space limitations on the drive, I would like to shrink to something more reasonable (10gb-ish).  When I execute DBCC SHRINKFILE('db_log', 10000), it returns an error that the end of the log is in use.

In order to free access to the end of the log, I attempted to place the database in single user mode with the following:

ALTER DATABASE db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE db SET MULTI_USER
GO

but the script is returning the following message repeated hundreds of times:

Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.

Which leads me to believe that somewhere, I am leaving some transactions uncommitted.  I am not aware of any process that would intentionally open this many transactions at one time, so I think they must accumulate over time, never being closed.

Question: How do I locate the offending process or script or why is the log not being released?

sys.dm_tran_active_transactions is showing a reasonable 18 transactions with understandable purposes.  sp_who shows only the processes I am aware of.

SQL Server Version:

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)
Apr  2 2010 15:48:46
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

Server Version:

Windows Server 2008 R2 x64 - Datacenter
4 vCPUs, 16GB memory, Pass through disk for data and log, OS disk is VHD

on Hyper-V (Windows Server 2008 R2 SP1 x64 Datacenter)
Dual Intel X5650 (6 core, 12 thread at 2.67GHz)
72 GB memory

Hypervisor only has three VMs and does not show high resource use.  SQL Server VM shows ~40% CPU under load and 99% cache hits.


texts: How to get the last non-null value that came before the current row

texts:
Say I have the following table:
create table tab (ts integer, a integer);

insert into tab values (1, 1), (2, 2), (3, null), (4, null), (5, 3);

When i query the data there may be some nulls:
select * from tab;

+----+--------+
| ts |   a    |
+----+--------+
|  1 | 1      |
|  2 | 2      |
|  3 | (null) |
|  4 | (null) |
|  5 | 3      |
+----+--------+

Now I like to replace the null values with the first non-null value that comes before it. Like so:
+----+--------+
| ts |   a    |
+----+--------+
|  1 | 1      |
|  2 | 2      |
|  3 | 2      |
|  4 | 2      |
|  5 | 3      |
+----+--------+

I've tried using several variations of the FIRST_VALUE and LAST_VALUE window functions but they always only return first value overall, the last value overall or the current value.
SELECT
  *,
  first_value(a) OVER (
    ORDER BY CASE WHEN a IS NULL THEN NULL ELSE ts END NULLS LAST
  )
FROM
  tab
ORDER BY
  ts ASC;

-- OR

SELECT
  *,
  first_value(a) OVER (
    ORDER BY CASE WHEN a is NULL then 0 else 1 END DESC, ts
  )
FROM
  tab
ORDER BY
  ts ASC;

+----+--------+-------------+
| ts |   a    | first_value |
+----+--------+-------------+
|  1 | 1      |           1 |
|  2 | 2      |           1 |
|  3 | (null) |           1 |
|  4 | (null) |           1 |
|  5 | 3      |           1 |
+----+--------+-------------+


texts: I am running several commands, some repeatedly, from the postgresql command line tool.  In Bash, the history function keeps one command for each set of sequential identical commands, it seems that postgresql command line does not do that by default.  So for example if I run command 'a' once, then command 'b' 100 times, I have to scroll up 100 times before running command 'a' again.  This is a user-unfriendly way of going about things.  Is there an option to have alternate, bash-like functionality from the postgresql command line, in which only unique commands are remembered in the history?


texts: SSRS email Subscription Not Working but local smtp is working

texts:
SQL SERVER EDITION- 2008 ENTERPRISE EVALUATION EDITION
Operating System- Window Server 2008 R2

I've Created Local Smtp to use gmail id for Subscription of SSRS Report by Email as we don't have an Exchange Server.

Used this link to Create local SMTP for gmail

Below is the Screen Shot of rsreportserver.Config File

ERROR : on Report Manager

Below is report log on after time report was executed.

library!WindowsService_16!2b30!05/20/2015-13:19:05:: i INFO: Schedule 3374e590-8374-4360-b5da-d487eacb0417 executed at 05/20/2015 13:19:05.
schedule!WindowsService_16!2b30!05/20/2015-13:19:05:: Creating Time based subscription notification for subscription: ea7fc6ce-00df-4f6a-9050-e713986307ca
library!WindowsService_16!2b30!05/20/2015-13:19:05:: i INFO: Schedule 3374e590-8374-4360-b5da-d487eacb0417 execution completed at 05/20/2015 13:19:05.
library!WindowsService_16!2b30!05/20/2015-13:19:06:: i INFO: RenderForNewSession('/Subscription')
library!WindowsService_16!2b30!05/20/2015-13:19:06:: i INFO: Initializing EnableIntegratedSecurity to 'True'  as specified in Server system properties.
library!WindowsService_16!2b30!05/20/2015-13:19:06:: i INFO: Initializing SqlStreamingBufferSize to default value of '64640' Bytes because it was not specified in Server system properties.
library!ReportServer_0-24!1f08!05/20/2015-13:19:08:: Call to GetPermissionsAction(/Subscription).
library!ReportServer_0-24!2484!05/20/2015-13:19:08:: Call to GetSystemPropertiesAction().
library!ReportServer_0-24!1f08!05/20/2015-13:19:08:: Call to GetPropertiesAction(/Subscription, PathBased).
library!ReportServer_0-24!1f08!05/20/2015-13:19:08:: Call to GetSystemPermissionsAction().
library!ReportServer_0-24!2484!05/20/2015-13:19:08:: Call to ListSubscriptionsAction(/Subscription, null).
library!ReportServer_0-24!1f08!05/20/2015-13:19:08:: Call to GetPropertiesAction(/Subscription, PathBased).
library!ReportServer_0-24!2484!05/20/2015-13:19:08:: Call to GetSystemPropertiesAction().
library!WindowsService_16!2b30!05/20/2015-13:19:10:: i INFO: Initializing SnapshotCompression to 'SQL'  as specified in Server system properties.
library!WindowsService_16!2b30!05/20/2015-13:19:11:: i INFO: Initializing ChunkSegmentSize to default value of '32320' Bytes because it was not specified in Server system properties.
library!WindowsService_16!2b30!05/20/2015-13:19:11:: i INFO: Initializing ResponseBufferSizeKb to default value of '64' KB because it was not specified in Server system properties.
library!WindowsService_16!2b30!05/20/2015-13:19:11:: Using folder D:\Program Files\Microsoft SQL Server\MSRS10.SQLSVR2008_NEW\Reporting Services\RSTempFiles for temporary files.
library!WindowsService_16!2b30!05/20/2015-13:19:13:: i INFO: Initializing SessionTimeout to '600' second(s) as specified in Server system properties.
library!WindowsService_16!2b30!05/20/2015-13:19:13:: i INFO: Initializing EnableExecutionLogging to 'True'  as specified in Server system properties.
library!WindowsService_16!37e0!05/20/2015-13:19:43:: i INFO: Call to CleanBatch()
library!WindowsService_16!37e0!05/20/2015-13:19:43:: i INFO: Cleaned 0 batch records, 0 policies, 0 sessions, 0 cache entries, 0 snapshots, 0 chunks, 0 running jobs, 0 persisted streams, 0 segments, 0 segment mappings.
library!WindowsService_16!37e0!05/20/2015-13:19:43:: i INFO: Call to CleanBatch() ends

Please Suggest if there's any requirement or step i'm left with.

PS: Local SMTP is working, i.e. i am able to receive email if i place an email.text file in C:\inetpub\mailroot\pickup


texts: Why OEM RMAN backup jobs fail after changing the preferred credentials?

texts:
In an Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production, Running on Oracle Linux 6.5, we configured full RMAN backup schedule using OEM (Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0) to take full backup once a week, and incremental level 1 for the rest of the week. This setup went smoothly until we were forced by internal policy to change passwords.

We did change the host's credentials (oracle user and root) plus the main schema. sys and system were unchanged (yet), then I did update the preferred credentials in the OEM, but the next job failed!

Before retrying, I double checked that we chose to use the preferred passwords, and that I didn't misspelled when I typed them (I did test the preferred credentials, misspelled host, sysdba, and normal credentials and watched the errors for each, then entered them carefully and tested again and had a success), but the job failed again. I did change the credentials for the job so it use custom credentials and entered the new credentials manually, retried and failed again!

To get more sure, I created a new job, the same settings we chose for the old job, using the preferred credentials, and ran it immediately, and it succeeded! This made me sure that I didn't mess up the preferred credentials, but there's something in the old job that doesn't respond the way I expect it to do, which is to take the preferred credentials at runtime. It even didn't take the custom credentials when I manually specified it!

So what am I missing here? Is there any other step that I overlooked or forgot to do (or didn't know I should)? Is it the expected behavior and I should delete and recreate all the jobs whenever I change passwords?

EDIT:

OEM version (output of emctl getversion):

Enterprise Manager 11g DB Control Version 11.2.0.4.0
Enterprise Manager 10g Agent Version 10.2.0.4.0


texts:
Reading over this post it seemed to indicate this plugin allowed user authentication via LDAP

  Percona Server is bundled with the PAM plugin which opens a plethora
  of ways to authenticate to MySQL such as restricting time when users
  can connect to MySQL, authenticate via a USB key, authenticate to an
  external authentication system such as LDAP and many, many more PAM
  compatible mechanisms.

However that intro paragraph is the only mention of LDAP in the entire post.  I've read through that as well as the general Percona PAM installation post. Every setup instruction seems to be talking about authenticating against a local unix account.  No mention of specifying an LDAP server or anything.

I found one other SourceForge project and some other mentions of perhaps slapping mysql_proxy infront.  Frankly that SF project doesn't feel very production ready.

Am I missing something about this percona plugin being able to authenticate via LDAP?

If I'm completely off base with that plugin perhaps my larger questions is "How can I get users authenticated via LDAP?"


texts:
I know there are similar questions to this one, but I could not find one that answered my question.

Inside my current SSIS package...
I have a dataflow task which...
Takes output from a stored procedure (contains about 10k rows) on the db
Inserts that output into a table in the dbo

The problem is that I have 2 choices of doing this (otherwise I break primary key restrictions)

1.) Truncate the table before I insert data to it

this is problematic because what if the SP fails for some reason? Other procedures use this table and depend on it to have data.

2.)Use "Update" command to update 1 row at a time for ~10k rows

This ties up resources on our server, which is already doing some heavy lifting just to execute the stored procedure (yes, it is a poorly written SP, but I digress).
I looked into doing a row count then checking the count variable before truncating, but I couldn't figure out how to make that work inside a dataflow with data stemming from a stored procedure.

So there HAS to be a way I don't know of to easily take my data and ONLY update existing records or insert new records.

P.S. SQL Server 2014

Current SSIS:

OLE DB Source = EXEC somesp.sp

|

Count = Count rows (used later on, irrelevant to task at hand)

|

Sort = Sorts for data integrity to ensure nothing will break my primary key (data coming from the sp)

|

Destination = Place records from sp result into a destination table


texts: Scan Count of a query increased from 1 to 2,226 after creating a non-clustered index

texts:
On my query 1st time it was taking almost 39 seconds and showing me non-clustered index is missing and

  Scan count 1, logical reads 14553, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
  read-ahead reads 0

After Creating the non-clustered index the duration of the query execution comes down to 1sec and it's showing

  Scan count 2266, logical reads 4539, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
  read-ahead reads 0

Logical reads is decreased after creating the non-clustered index, but Scan count increased to 2266. So my question is the performance of the query is developed or the Scan count 2266 make my query non-optimal?


texts: Best configuration of my.cnf for make fast Magento navigation

texts:
I have a Magento 1.7 version site with 7000 configurable products.
Site navigation is slow.
From the moment I click on a site link to when the address in the url bar changes several seconds pass.
Configuring the file my.cnf I have had improvements. But I think the configuration is not optimal.
What do you suggest to edit in my my.cnf file considering that I have a VPS with the following features?

CPU: ten cores
Intel® Xeon® E5-2620v3, E5-2630v4 or 4114 processor
50 GB RAM (guaranteed)
1200 GB disk space (100% SSD)
100% SSD disk space
UNLIMITED traffic!
1 Gbit / s port
DDoS protection (details)

This is my.cnf configuration:

#query_cache_size = 50G
#table_open_cache = 2000
#innodb_adaptive_flushing = 0
#innodb_adaptive_hash_index = 0
#innodb_autoextend_increment = 1000
#innodb_buffer_pool_instances = 64
#innodb-defragment=1
open_files_limit = 2000
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64
innodb_buffer_pool_size = 5G
innodb_thread_concurrency = 20
innodb_flush_log_at_trx_commit = 2
thread_concurrency = 30
table_open_cache = 2000
thread_cache_size = 32
#table_cache = 2000
query_cache_size = 30M
query_cache_limit = 1G
join_buffer_size = 3G
tmp_table_size = 2G
key_buffer = 70M
innodb_autoextend_increment=512
max_allowed_packet = 1G
max_heap_table_size = 1G
read_buffer_size = 50M
read_rnd_buffer_size = 50M
bulk_insert_buffer_size = 50M
myisam_sort_buffer_size = 50M
myisam_max_sort_file_size = 10G
#myisam_max_extra_sort_file_size = 5
myisam_repair_threads = 1
innodb_buffer_pool_instances = 5


texts: Are there implicit indexes in InnoDB like MyISAM?

texts:
If you have (id, a, b, c, d) table with primary key (id) and another key (a, b, c), in MyISAM that means that you also have the following implicit keys:

(a)
(a, b)
(a, b, c, id)

Is this valid for InnoDB as well?

Or should I define the necessary ones explicitly?


texts: How can I handle unwanted, non-erroneous situations better?

texts:
When writing procedures, I occasionally encounter situations in which I want to abort the procedure, even if the situation didn't necessarily trigger an error.

Let's say, if I don't want John to be able to run this procedure, I'd do something like this:

IF @UserName = 'John'
BEGIN
    RAISERROR('John, get out', 16, 1);
    RETURN 1;
END

I don't really have a good reason for returning 1, it's mostly a remnant from shell scripting, and I'm open for better ways to do this.

Is there a better way of returning an error message and returning control to the caller for unwanted situations that aren't strictly speaking errors? I am not interested in reducing the amount of characters or lines of code. I just had a gut feeling saying "this is probably not the best way to solve this problem", and I was curious if there were smarter ways to do this.

One alternative could be by always using TRY/CATCH, since RAISERROR with a severity of 1-19 will hand control to the catch clause.

Example:

BEGIN TRY
    PRINT 'Before RAISERROR';
    RAISERROR('Time for errors', 16, 1);
    PRINT 'After RAISERROR'
END TRY
BEGIN CATCH
    DECLARE @Msg NVARCHAR(255) = ERROR_MESSAGE()
    PRINT 'Inside CATCH'
    RAISERROR(@Msg, 16, 1)
END CATCH

Output:

Before RAISERROR
Inside CATCH
Msg 50000, Level 16, State 1, Line 13
Time for errors

Con: Requires the presence of TRY/CATCH blocks.


texts: Database design with different user roles connected by different relationships to one organizaiton table

texts:
An user is connected to an organization by some role. Lets say there are 2 roles freelancer and employee.

One employee can only be a part of one organization and if he is an employee then he can't be a freelancer. Whereas a freelancer can be connected to multiple organization and he also can't be an employee.

In short, Freelancer and Employee both are users. Freelancer is connected to Organization via many to many relationship. Employee is connected to Organization via one to many relationship.

What I have thought:

user{
   id,
   name,
   other_fields,
}

organization{
    id,
    other_fields
}

freelancer{
    user_id,
    organization_id,
    Primary_Key(user_id, organization_id)
}

employee{
    user_id,
    organization_id,
    Primary_key(user_id),
}

There is one catch here, though. The constraint of being either a freelancer or an employee but not both has to be implemented at the application level.

What are the pros and cons of above schema?
What are other possible alternatives to design schema?
What if there are more category of users? Current schema requires addition of new table if a new role of user s created. Is it ok or bad?
Database maintenance is also a concern that making a schema which should not make maintenance a problem


texts: Recreate MSDB for SQL Server Express 2008

texts:
My SQL Server Express database's msdb file has become 'suspect'. All the other mdf files seem to be OK.

I now need to recreate the msdb file, but I do not have a backup for it (guilty as charged!). I have backups for my other DBs. This is what I found from this blog:

  If your msdb goes suspect then you have two choices, if you have
  backup of database then restore it else you have to recreate it

  If you want to recreate it here are the steps to do it

  Start the server with trace flag 3608. Detach the damaged msdb
  Navigate to the directory C:\Program Files\Microsoft SQL
  Server\MSSQL.1\MSSQL\Binn and start sqlservr.exe -c -T3608
  Move or rename the damaged msdb files
  Go to the folder C:\Program Files\Microsoft SQL
  Server\MSSQL.1\MSSQL\Install and run the instmsdb file.
  Restart SQL Server without the 3608 trace flag

Has someone done this before? I would like to get opinions or better suggestions before I break things more.

Thanks!

EDIT:
My advice to anyone is to treat SQL Express like any production DB.  Have rigorous back-up policy and make sure you back-up your system databases.


texts: How to replicate data between two SQL Server 2012?

texts:
I have two SQL Server 2012 databases running on two different Windows Server instances, I want the Server A (DB1) to be in full sync with the new Server B (DB2).

How can I make this connection between them?

Is there any way to do this through SQL management Studio??

Note: whatever I do on Server B shouldn't replicate to Server A.


texts:
I have a SQLite database with a hierarchical role-based access control (RBAC) data model.

In this model, users are granted permissions to perform certain actions against various resources either directly or by being a member of one or more roles to which the permissions are assigned. Additionally roles can be assigned to other roles such that a hierarchy of roles can be defined.

The schema is roughly like the following:

(from How to design a hierarchical role based access control system - StackOverflow)

With the exception that there is a Role_Role join table instead of a "parent" column on the Roles table, similar to the below:

(from same post as above image)

Also there is no "grant" column on the join tables; existence of a record in a join table means it is granted.

What would a query to get all of the permissions for a given user look like? I'm assuming using a recursive CTE would be involved, but I'm not sure.

Edit: I've got the following recursive CTE working for getting the roles inherited by other roles:

WITH RECURSIVE
  deep_roles(role_id, parent_id, depth) AS (
    SELECT role_id, parent_id, 1 FROM role_roles WHERE role_id = ?
    UNION ALL
    SELECT rr.role_id, rr.parent_id, dr.depth+1
      FROM deep_roles dr, role_roles AS rr
     WHERE rr.role_id=dr.parent_id
       AND dr.depth < 10
  )
SELECT DISTINCT role_id, parent_id FROM deep_roles;

Not sure if this is the only CTE I'll need but it's a start.


texts: Sudden Query Performance Degradation On A Linked Server

texts:
I have an application that uses a view on a SQL Server 2008 R2 instance. This view actually queries a linked server (SQL Server 2008) that has replicated data from our main ERP database. Yesterday, we began experiencing some timeout on the application that uses this view and we discovered that the view was taking over 1 minute to complete (before this it was a matter of seconds).

Here's what we know:

Running the query on the linked server directly returns results in a
matter of milliseconds.
Creating a view on the linked server, and then having the view on the
first server simply query the view on the linked server restores
performance to its original state. (view source), (workaround)
When running the query on the first server, I notice that there are quite a few OLEDB waits (using sp_WhoIsActive). Because of this, I decided to check the linked server while the query was running (on the first server). I noticed that the primary wait was ASYNC_NETWORK_IO.

Because of this, I am inclined to believe that there is an issue with either the network, or the configuration on my linked server. I am not sure that there is anything wrong with it, but I am posting it too.

What other things should I be looking at to help me to get to the root cause of this?

EDIT

Query Plan: http://i.imgur.com/yn6ZC.png

IO Stats: http://i.imgur.com/JNX2K.png

Query Columns: http://i.imgur.com/Coxx5.png

Top Operations: http://i.imgur.com/IROrA.png

Plan Tree: http://i.imgur.com/Wco4K.png


texts: Is a join optimized to a where clause at runtime?

texts:
When I write a query like this...

select *
from table1 t1
join table2 t2
on t1.id = t2.id

Does the SQL optimizer, not sure if that is the correct term, translate that to...

select *
from table1 t1, table2 t2
where t1.id = t2.id

Essentially, is the Join statement in SQL Server just an easier way to write sql? Or is it actually used at run-time?

Edit: I almost always, and will almost always, use the Join syntax. I am just curious what happens.


texts: Possible Pitfalls of Setting Up Kerberos to Permit Cross-SQL Database Access Via AD Trusted Connection

texts:
We are hoping to the implement Kerberos on our Active Directory (2003, Functional Level 2) so that our SQL Server 2005 databases can communicate with one another when a client is using Active Directory Trusted Connectivity.

The reason this wasn't implemented back in the day (circa 2001?) is that we were highly concerned about the holes using Kerberos left open. We haven't truly revisited this issue until now. After reading a couple articles today, namely How to Implement Kerberos Constrained Delegation with SQL Server 2008 and Understanding When Kerberos Delegation Is Needed for SQL Server we see that it's possible to limit this to SQL Server.

Questions

What are some possible pitfalls that we will run into?
What might break?
Is there a reason not to do this?

EDIT: And dang it...I can't add a Kerberos tag!


texts:
I want to install Oracle JDeveloper on my Ubuntu 18.04, and I have downloaded the installer file from here, there I download the file1 and file2 to Linux.

Here is the file name for file1, and the size is 2,0 GB:

jdev_suite_122130_linux64.bin?AuthParam=1561523342_6bfe09585a7e74ebd661dfe1d3d49427

And here is file name for file2, and the size is 432,1 MB:

jdev_suite_122130_linux64-2.zip?AuthParam=1561523346_b29a360806455b82e4a0db9e4e7767b9

I have tried to follow many tutorials how to install the JDeveloper, here is one of example tutorial that I try to follow.

There.., after I got the installer file, we should give permission access using this command:

sudo chmod +x jdev_suite*.bin

I ran the command to the file1 and file2, and here is permission:

harvest# ls -l jdev_suite_122130_linux64.bin\?AuthParam=1561523342_6bfe09585a7e74ebd661dfe1d3d49427
-rwxr-xr-x 1 tri tri 2045438518 Agu 26  2017 'jdev_suite_122130_linux64.bin?AuthParam=1561523342_6bfe09585a7e74ebd661dfe1d3d49427'
harvest# ls -l jdev_suite_122130_linux64-2.zip\?AuthParam=1561523346_b29a360806455b82e4a0db9e4e7767b9
-rwxr-xr-x 1 tri tri 432104829 Agu 26  2017 'jdev_suite_122130_linux64-2.zip?AuthParam=1561523346_b29a360806455b82e4a0db9e4e7767b9'

Then I start the installation using this command:

./jdev_suite_122130_linux64.bin?AuthParam=1561523342_6bfe09585a7e74ebd661dfe1d3d49427

I tried that command on normal user and also try as root, but I got this message after that:

Launcher log file is /tmp/OraInstall2019-06-26_02-04-26PM/launcher2019-06-26_02-04-26PM.log.
Extracting the installer . . . Done
Self extraction to /tmp/sfx_i5T36W failed.
The log is located here: /tmp/OraInstall2019-06-26_02-04-26PM/launcher2019-06-26_02-04-26PM.log.
ERROR: Installer execution failed (1).

After I looking at the log base on that message, here is the log:

harvest# cat /tmp/OraInstall2019-06-26_02-04-26PM/launcher2019-06-26_02-04-26PM.log
2019-06-26 14:04:26,534 INFO  [1] com.oracle.cie.nextgen.launcher.LogUtils - Launcher log file is /tmp/OraInstall2019-06-26_02-04-26PM/launcher2019-06-26_02-04-26PM.log.
2019-06-26 14:04:26,544 INFO  [1] com.oracle.cie.nextgen.launcher.LogUtils - Extracting the installer . . .
2019-06-26 14:04:26,545 SEVERE [1] com.oracle.cie.nextgen.launcher.Launcher -
The jar file /home/tri/Downloads/oracle/jdeveloper_12c/jdev_suite_122130_linux64.bin?AuthParam=1561523342_6bfe09585a7e74ebd661dfe1d3d4-2.zip needed for this installer is missing.
2019-06-26 14:04:26,547 SEVERE [1] com.oracle.cie.nextgen.launcher.Launcher - Self extraction to /tmp/sfx_i5T36W failed.
2019-06-26 14:04:26,547 INFO  [1] com.oracle.cie.nextgen.launcher.LogUtils - The log is located here: /tmp/OraInstall2019-06-26_02-04-26PM/launcher2019-06-26_02-04-26PM.log.

I have tried to follow this instruction to Download both File1 and File2 and place it in the same directory, but still doesn't work.

So, please help me with this.


texts: Column name naming conventions and best practices

texts:
I would like some expert opinion on best practices when it comes to column naming.

The background is that according to Wikipedia, the following syntax,

SELECT ... FROM Employees JOIN Timesheets USING (EmployeeID);

is more efficient than

SELECT ... FROM Employees JOIN Timesheets ON (Employees.EmployeeID = Timesheets.EmployeeID);

However, the JOIN ... USING syntax only works of all primary key columns have globally unique names. Thus I wonder if this is considered The Right Thing to do.

Personally, I always used to create tables with PK column id, and foreign key column othertable_id. But that way it's not possible to use USING or NATURAL JOIN.

Any links to design styles or best practice guides for table design would be appreciated, too!


texts: Refactoring database after migrating table

texts:
I'm creating a new staging database, into which I intend to migrate several tables from the old database. These tables are referenced in functions and stored procedures. If I were doing things manually, I would just migrate the tables, and then modify the functions by replacing the old table name with the new table name. I'm thinking there must be a more automated way of doing this though.

Has anyone done something similar; what tools have you used to help refactor?


texts:
In our current system, we query the LDAP using SQL Server 2008R2 but are moving to SQL Server 2012 where we have hit a snag. According to http://msdn.microsoft.com/en-us/library/cc707782.aspx one of the discontinued features of SQL Server 2012 is the AD helper service.

Does anyone know of a way to query the AD from SQL Server 2012?


texts: Why do string functions on SQL based database servers start at position 1 instead of 0?

texts:
This has always bothered me. It seems that string functions in SQL based servers always start at position 1 (at least this is the case with MySQL, SQL Server, Oracle and Postgres). For example the following query would be used to select the first letter of a column called first_name in the names database:

SELECT SUBSTRING(first_name,1,1) FROM names;

Why does the position for string functions not start at 0 as is the norm in nearly every programming language?

I'm looking for more than just this is the ANSI standard. Why is the standard?

EDIT: Okay, so 0 is not the "norm in nearly every programming language" as has been pointed out below. 1 is also used.


texts: How do I get a run duration in minutes for each individual step in a single Job?

texts: The Run_Duration in sysjobhistory of the the complete job. Is there a way to get this? [msdb].[dbo].[sysjobactivity] only has the last step.


texts: Slow query in production, bad execution plan or bad index?

texts:
I've just fixed a production performance issue by dropping an index and recreate it.
I suspect dropping the index also dropped executions plans that used it and one of them happen to be bad.
Arguments in favor of bad execution plan :

Before dropping the index, I looked up the last update date for the statistics on the given table and they were up to date.
My DBA has put in place Hallengren's index and statistic maintenance solution
The slow query was a select statement executed from sp_executesql with dates parameters. Executing the same select statement without sp_executesql was fast, but also didn't use the same execution plan.

Arguments against bad execution plan :

Before dropping the index, we went real wild and ran the forbidden dbcc freeproccache to clear any bad plan, but this didn't fix or change the performance issue.

Note:

The slow query happen to be  using a table indexed by date. However,
there is wide differences in the amount of records for each date. In
other word, any given date range from few records to more than 100k
and it is pretty random.

The database is running under compatibility level 140 (SQL Server
2017)

Was the source of the problem a bad plan or a stale index?
If it is a bad plan, then why dbcc freeproccache didn't work in getting rid of it?
Plan before issue was solved
Plan after issue was solved
Edit :
From what I know, this looked very much like a bad plan, but somehow dbcc freeproccache didn't work. So, I'm left in the dark, confused about the whole situation.


texts: SQL Server End to End Encryption Options

texts:
I'm familiar with database encryption options for SQL Server such as TDE and ensuring backups / backup locations are encrypted and secured properly.

I'm doing some work for a new client who has asked me to detail options for end to end encryption, in other words, to ensure data sent from database server to application server remains encrypted en route and cannot be intercepted (or rather, interpreted) by any sort of packet monitoring.

I know about using SSL certificates to encrypt connections to the database, though it is something I have seldom configured. I really don't know if that is an adequate option for end to end encryption as surely this cannot be configured using SQL Server alone?

In my mind there has to be something at the application end, or on the network route that enables end to end encryption like this?

My knowledge of network security is basically limited to firewall rules and general routing, so I'm looking for options available to me to either research further or put into practice.

I wary that this question will be closed because it is opinion based, so I'm really asking for solid options within SQL Server itself (or within the remit of a DBA) to configure end to end encryption of data.


texts: Splitting row into multiple records based on row value

texts:
I have an Orders table (text customer_id, int total_orders) which contains overview stats of total orders for each customer.

What I am trying to do is generate a query output that will represent a customer as a row for each third order they've made similar to this.

The combined_key is used as the primary/unique key which is simply the customer_key and count combined as I cannot use an auto-number.

I know how I can identify which records I need to be in the query but I don't know how I can split the records as desired.

select *
from stats
where order_count / 3

I've included a fiddle, please note it's in MySQL because T-SQL is not available.


texts: Under what user does my process run on Oracle Linux?

texts:
We experience problems with directory access on some Oracle databases running on Linux.

We have two kinds of processes that need to read/write files from a directory: 1 started in a user session, 2 started by Oracle scheduled job.

It seems that on some systems these processes run under different Linux users and that permissions on the Linux file system are only granted to one of them.

Is this assumption correct and what users are these?


texts:
I need to group by month (and year) and I was thinking about:

GROUP BY CAST(YEAR(tDate) AS NVARCHAR(4)) + '-' + CAST(MONTH(tDate) AS NVARCHAR(2))

But I found in the web something like:

GROUP BY YEAR(tDate), Month(tDate)

Both are equivalent? It is better to use the second?


texts: MySQL Replication Lag Behaving Erraticly

texts:
I've got a vanilla MySQL replication, using "MIXED" mode.

The mysql slave lag calculation behaves very strangely - one moment it is 0 and the one after it it's 3630 seconds (or a similar number), then back to 0, and so on and so forth. Obviously, something is wrong in the replication configuration, since MySQL computes the lag based on the timestamps in the relay log.

I've tried checking the time of the servers, which is identical (using SELECT NOW() in MySQL). I've also checked the timezone (SELECT @@system_time_zone) which is set to CDT on both master and slave.

What else can I verify to make sure this issue is resolved? Did anyone else encounter this problem?


texts: How to import a single table in Windows environment

texts:
I am running Windows 7 with xampp. I can easily import a database with the following command:

mysql -u user -p database < backup.sql

The problem is that I have a database around 8.5 GB, which take a long long time to import. Mostly I just need to restore a single table from it.

How can i restore single table in this environment?


texts: Any system tables/views to determine query volume per table

texts: I was digging through the SQL Server system views and Windows performance counters and can't find any metrics regarding query volume PER TABLE in a given database. I am looking for any type of information to gauge the usefulness of each table in a given database. Query count/row count/data size of query results, or anything along those lines would be great. Does anyone have suggestions for how to see this data per table? I could do a Profiler trace or XE session over time, and then query those results to do some grouping, but don't want the added overhead if the data is already there somewhere.


texts:
I have metadata in a postgresql table that I need to split. So far I have been attempting to use

Select col_name from "tbl_name" split_part('QGC|WGPLNW|IR|BBC|FCPC|WGPLNW|ARME|QPC', ", 1 | 2 | 3 | etc...);

Out of a table formatted like so:

QGC 10''
QGC 10''
QGC 10''
QGC 10''
QGC 12''
QGC 12''
QGC 12''
QGC 12''
QGC 12''
QGC 10''

I need only to extract the integers and would like to preserve the acronyms if possible. I have attempted to use the split substring with regex function but haven't found much success, although this is primarily due to my lack of understanding of how to pass substrings inside of queries when dealing with a table. If someone could explain the appropriate syntax in order to perform this task they would help me greatly. Thanks in advance.

Thanks Martin, You are right - I placed this in the wrong section, thanks for your input. Also, you are correct in assuming that the quotes are holdovers from the database and I have corrected them to more accurately represent the data. How would I go about having the topic moved as not to darken the door of gis.stackexchange?


texts:
We are facing problems related to transaction logs becoming full.

  The transaction log for database is full due to 'ACTIVE_TRANSACTION'

My database logs are becoming full after every 4-5 days & after that
for sometime all queries start failing. This state remains for sometime(2-3 min max). After that log space gets freed up & everything comes back to normal again. This has become a recurring issue which happens randomly.
Initially we thought that's its a temporary issue occurred because of some temporary SQL job & did SHRINKDATABASE for 1-2 times. It worked for that time. But nowadays we are facing this issue again & again & its so random that we are not able to identify what query is causing this issue.

We tried looking for expensive queries which might have caused this issue & tried to rerun again to reproduce the same issue but it didn't worked. I tried searching for this problem but every answer is about shrinking log files. I know that doing SHRINKDATABASE every time is not the correct solution. I want to find the exact cause of this issue.

How can I find the exact query which is causing the issue? Or what approach I should adopt to get solution for this problem?

Server details :

Microsoft SQL Server 2014 - 12.0.2000.8 (X64)
Recovery Model : Simple
OS : Windows Server 2012 R2
Running on Intel Xenon CPU E5-2630 v3 @2.40GHz, 16 cores
Installed memory - 128 GB


texts: Analysis of Conditions with no results and high offset number - PostgreSQL JSONB

texts:
I am asked to record down the analysis on PostgreSQL JSONB query having Conditions with high offset number and query that provide no results considering the below.
select
    *
from
    sub_test df,
    jsonb_array_elements(json_data) jba
where
    (jba->>'name')::text = 'SubYear'
    and (jba->>'value')::numeric > '2050';

SubYear > 2200 (has no results)
Explanations about the query plan, are full scans of tables necessary with JSON?
SubYear > 1970 (has a lot of results) and offset 30k
Explanations about the query plan, are full scans of tables necessary with JSON?

Note that there are about 35k+ records in the table "sub_test" with 500+ keys in the JSONB column "json_data"
Could you help me to answer them ?


texts: How do I loop through every table and alter the PK column like so? (Postgres)

texts:
Using psql in Postgres 12, I need to alter every column in every table of my database that's:

int/bigint

not null

primary key

to:

identity column

auto-increment

with default value starting at max value of current rows

I know this should be doable using PL/PgSQL, but I'm not familiar with it.
Can someone help me out?


texts: PostgreSQL Return Joined Results of Select

texts:
I'm learning PostgreSQL and was wondering if I can put long selects in stored procedures like this one:

CREATE OR REPLACE FUNCTION api.book_list()
RETURNS TABLE (
  id         BIGINT,
  published  DATE,
  category   VARCHAR,
  author     VARCHAR,
  name       VARCHAR
) AS $$
BEGIN
  SELECT
    b.id,
    b.published,
    c.name AS category,
    b.author,
    b.name
  FROM
    schemas.book AS b
  JOIN
    schemas.category AS c
  ON
    c.id = b.category_id;
END;
$$
LANGUAGE plpgsql;

It is supposed to return select from two joined tables.

It gives the following error:

select * from api.book_list();
ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "book_list" line 3 at SQL statement


texts: Postgres Combine Summed Values from 2 Queries / Tables into Single Row

texts:
Say I had the following 2 queries, summing values from separate tables.
I would like the sum of recorded time
SELECT
    SUM(minutes) as recorded_minutes,
    SUM(hours) as recorded_hours
FROM recorded_time
WHERE
    project_id = 1

To be combined with the sum of budgeted time in a single row
SELECT
    SUM(minutes) as budgeted_minutes,
    SUM(hours) as budgeted_hours
FROM budgeted_time
WHERE
    project_id = 1

Is it possible to do this in a single query?


texts: PostgreSQL user can not connect to server after changing password

texts:
I've met this with 4 roles I created:
After changing password for a user in pgAdmin III using the GUI (1), that user can not log in any more.
pgAdmin III show error message:

An error has occurred:

Error connecting to the server: FATAL:  password authentication failed for user "sam"
FATAL:  password authentication failed for user "sam"

My system: Postgresql 9.2 on Ubuntu 12.04

Is there any way to fix this?

(1): login with account postgres, right click user in Login Roles, go to tab 'Definition' and enter password


texts: Dumps created on on secondary instance of always on

texts:
We have an two SQL server 2017 instances with CU20 that are configured in a read scale always on with Windows 2016 OS machines.
The database in these instances uses: In memory optimized tables, partitioning, and other configurations.
Lately the secondary server has started to fill up on dump files every 2 minutes or so
Every time the dump is created we get the following errors in the error log:

A user request from the session with SPID 55 generated a fatal exception. SQL Server is terminating this session. Contact Product Support Services with the dump produced in the log directory
Process ProcessID generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
Searching for a solution I found the following documentations:
https://support.microsoft.com/en-in/help/4039089/fix-exception-access-violation-error-when-you-execute-the-sys-sp-mscdc

https://support.microsoft.com/en-us/help/4131960/fix-access-violation-nested-select-query-run-against-columnstore-index
the solution that is described is updating the CU but we already have CU 20 (the fixes are in cu 7 and 1)
the server doesn't seem to be low on memory(sometime we do however have memory errors on the primary one)
integrity job was successful.
The database is 170GB, we have 320 GB of RAM.
What can we do to fix this issue? Each dump is about 3GB and its filling up the disks pretty fast.
Thanks.


texts: PostgreSQL concurrency control and Serializable transactions

texts:
Im building an application that needs to do some concurrent read/write operations on specific tables. Even after reading the official documentation (and many, many articles), the trouble persists, no one talk about the problem I have (and I think it's VERY common)
My DB:

Table accounts with a boolean column called disabled (the other columns can be ignored)

Table request, that represents some relation between 2 accounts. An account can "request" some action to another account.

The issue:

Accounts cannot request actions to disabled accounts. It is a business rule, so I don't want to put any logic related to this in the database (using triggers/constraints). The database should only guarantee things related with the data (FK's, unique indexes, etc)

Since the database will not handle this business rule, my application will. To do it, every time a new request is issued, the following steps happen:

Open a transaction
Read target account disabled value
// Concurrency issues happens here
// What happens if the target account update it's disabled value here?
If the target account is not disabled, create a new "request" row

Do you see the concurrency issue?
At first, I tried to use Explicit Locking, but it is too complexity to handle (my real case is a little bit more complex, but the above example is sufficient). It works, but its not maintainable.
After, I tried to use Serializable transactions, but the problem above persists, although everyone saying "When using Serializable transactions you don't have to think about concurrency"
What do I have to do to ensure this consistency? The explicit locking strategy is the only way?
Thanks!


texts: Violation of PRIMARY KEY constraint in SSMS import/export wizard

texts:
I have table MYTABLE with one PK constraint, generated by a third-party app:
CREATE TABLE [dbo].[mytable](
  [ID] [int] IDENTITY(1,1) NOT NULL,
  [FIELD_A] [nvarchar](255) NOT NULL,
  [FIELD_B] [tinyint] NOT NULL,

  CONSTRAINT [mytable_PK] 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],
  UNIQUE NONCLUSTERED ( [FIELD_A] ASC ) WITH (
    PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
  ) ON [PRIMARY]
) ON [PRIMARY]

And the data is pretty simple:
ID  FIELD_A     FIELD_B
1   abc         0
2   dfgh        0
3   foo         0

I'm trying to import this data into an empty copy (created with generate scripts wizard/above ddl) of this table in another database using data import, but get the following error:
"Violation of PRIMARY KEY constraint 'mytable_PK'. Cannot insert duplicate key in object 'dbo.mytable'"

I have tried the following (to verify this isn't a duplicate question):

Check that there is no actual duplicate in the destination: The destination is empty
Enabled option "Enable identity insert" (if disabled, this fails with Violation of FOREIGN KEY... instead)
Attempted with both 'Delete existing rows' and 'Append'
Disabled constraint checking: EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
Attempted to not import the surrogate ID - this fails as import wizard sets field to null if ignored instead of leaving empty.
Checked for triggers that may be creating duplicate inserts
Forced SSMS to just drop and recreate the tables

What else can I check to troubleshoot this?


texts: Difference Between a Staging and the Production DWH

texts:
I have a general question on a Data Warehouse Architecture. I have heard a lot of a Staging and a Production, so that data should be loaded via SSIS first into a staging dwh and then via SSIS to the Production environment.

But is there any difference? I mean is it only a "copy" of the production data warehouse (to do some quality checks on data before pushing to production?) or of the source data in a different model or (different schema)?

From software development perspective i know that both are identical and the only purpose is to check the software version from all stakeholders and get the release to deploy on prod. Normally in the software case the staging is same (maybe only hardware sizing is different) to production.


texts: Oracle 11g max login fail attempts workaround

texts:
My problem with database starts with situation where I cant really modify anything in database. My project specialist has limited time to help me. Here is the thing:
My user in Oracle database has older schema than actual production one. My section is working on stable and older version. After every release we are keep getting this issue, that something is set (maybe on Jenkins, maybe not) automatically to update our database to version, which we dont want. We tried to resolve it by changing password to user, but it produce new issue. Automat is trying to log in and when it gets wrong pass error, it is trying again. Oracle 11g has this limit 10 failed login attempts, after which it is locking the whole user account, which we use to connect do db by our application server.

We can not investigate this by turning on auditing failed logins, because it takes place on database space and our db-guy has not allowed us to do it, because if we exceed the space limit (which is about 11GB) the whole database will be dead (our project is not as important to do it). Another thing is that person who probably set the scripts which are our problem doesnt work anymore here.

Our workaround was to manually unlock account to get the connection by application server, and then wait a few secs to get locked again (but the connection of app server was stable). It is stupid, you must admit and the problem is when the connection drops by any reason - app server will not get it automatically, we have to do it manually which is not a solution.

I have reconsidered it all again, my db-guy has no time to help me, I have no tools and access rights to investigate where this script or whatever other problem causing thing is beeing executed, so I started to thinking: what if we set limit of failed login attempts to unlimited? Will this decrease the performance of database? Will this generate any special new problems? Maybe the solution would be change the PASSWORD_LOCK_TIME to small value?

I am asking you to some arguments that I could provide to my db-guy to convince him to use this new workarounds so I can start working again with code and not this database problems.


texts: AlwaysOn availability group and Replication - expected behaviour

texts:
I'm currently doing a bit of testing with alwayson and replication. I currently have the below:

Fully working cluster and Always on environment; DCTEST1, DCTEST2 and the listener name DCTEST_L. The cluster is healthy.

Fully working transactional replication to another server - DCREPL
All is SQL2017 Enterprise

Replication to DCREPL works regardless of which node is the primary. I make data changes on DCTEST_L and they are successfully passed to the subscriber.

The issue I'm having is when I shut down the primary (DCTEST1). The availability group fails over successfully. However, when I make data changes on the listener - DCTEST_L; they are not replicated to DCREPL.
When DCTEST1 is powered back on, then the changes are successfully replicated.
Is this expected behaviour? I would like the replication to continue should the primary ever go down, and not have to stall until the node is powered back on.

Is there a step I have missed? I have followed the microsoft documentation:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-replication-for-always-on-availability-groups-sql-server?view=sql-server-2017


texts: Why does my query end up with two seeks instead of one and how do I fix that?

texts:
I have these two tables:

CREATE TABLE [TaskItems] (
    [ItemId]             UNIQUEIDENTIFIER NOT NULL UNIQUE PRIMARY KEY,
    [LastOperationTime]  DATETIME NULL DEFAULT GETUTCDATE(),
    [OwnerId]            UNIQUEIDENTIFIER NOT NULL
    -- more columns which are irrelevant
)

CREATE INDEX [TaskItemsByOwnerIdAndLastOpTimeIndex]
     ON [TaskItems](OwnerId, LastOperationTime)

CREATE TABLE [TaskOwners] (
    [OwnerId]         UNIQUEIDENTIFIER UNIQUE NOT NULL DEFAULT NEWID() UNIQUE PRIMARY KEY
    -- more columns which are irrelevant
)

and this query:

SELECT
  COUNT( OwnerId ),
  SUM(CASE WHEN LastOperationTime > DATEADD(day, -3, GETUTCDATE()) THEN 1 ELSE 0 END),
  SUM(CASE WHEN LastOperationTime > DATEADD(day, -5, GETUTCDATE()) THEN 1 ELSE 0 END)
FROM
(SELECT
  O.OwnerId,
  MAX( LastOperationTime) as LastOperationTime
    FROM
      TaskOwners AS O
      LEFT OUTER JOIN TaskItems AS TI ON O.OwnerId = TI.OwnerId
  GROUP BY O.OwnerId) AS Whatever

Note that LastOperationTime is declared NULL which is a design error but fixing it would require recreating the index which is kind of problematic in production database. Anyway at any given moment there're no items which have LastOperationTime set to null.

TaskOwner table contains about 25 thousand rows. TaskItems contains about ten million rows and some rare "owners" have a million or so "items" owned while most of the "owners" own no "items" or some of them own hundreds or thousand "items".

So I run the query and request the actual execution plan and it has two index seeks (the two tree items in the lower right).

One of those has "object" TaskItemsByOwnerIdAndLastOpTimeIndex, "predicate" TaskObjects.LastOperationTime IS NULL and takes about 49% time. The other one has "object" TaskItemsByOwnerIdAndLastOpTimeIndex, "predicate" TaskObjects.LastOperationTime IS NOT NULL and takes 47% time. Both have "seek predicates" TaskObjects.OwnerId==TaskOwners.OwnerId and "backwards" direction in the plan tree. Both have "execution count" equal to the number of rows in the TaskOwners table.

The results of those seeks are then filtered (TOP(1)), concatenated, aggregated and fed into the JOIN which is implemented using "nested loops".

The question is why those two seeks are there (instead of one scan). Why does the query engine suddenly care so much about NULL vs NOT NULL that it initiates two separate queries? How do I get rid of those?


texts:
I have a table test:

id    number
name  varchar2(20)
ddate varchar2(20)

I want to select all row from test on specepic date and full outter join with a list of values. For example let's say this is the data:

SQL> select * from test;

id     name     ddate
---    ----     ------
1      a        20-02-18
2      b        21-02-18
3      c        21-02-18
4      d        22-02-18

I want this result:

SQL> --of course this doesn't work.
SQL> select *
from test full outter join ('b','e') list on test.name=list.name
where ddate='20-02-18';

id     name     ddate
---    ----     ------
null   e        null
2      b        21-02-18
3      c        21-02-18


texts: Is my db too big for my server? Should I go nosql or stick with relational db management?

texts:
I have a mysql database that is about 22GB in size and has multiple tables with hundreds of thousands of records, my biggest table has 1.8 million records. The Database was not professionally designed by a DB admin, it was designed by a php developer with not a lot of experience in dealing with that much data. I know its not the largest amount of data but it is giving is very slow performance, some of it is the code written.

My question is, does my system currently support the DB of that size? I have a dedicated managed server from go-daddy, 32GB of RAM, quad core processor, 1TB of storage. We have about 100 users actively using our application looking for a single record within a specific table.

I am thinking about restructuring the DB and also going into cloud instead of dedicated server and going with nosql db management rather than our current relational.

However I am not a DB admin, I am a developer. I would love to hear your thoughts.


texts: Why does MySQL fail to use an index effectively when LIMIT offset is large?

texts:
I do not understand when querying 5 rows from a table of 500,000 rows, the following query takes 0.00sec:

SELECT id, username FROM Users FORCE INDEX (PRIMARY) ORDER BY id LIMIT 20,5;

But querying the exact same rows in the reverse takes 2.50sec:

SELECT id, username FROM Users FORCE INDEX (PRIMARY) ORDER BY id DESC LIMIT 499975,5;

Furthermore, if I don't force index for the second query, MySQL will choose not to use it.

I note that there is a plan to introduce sort order for index, but that does not explain why MySQL cannot use the same index effectively when LIMIT offset is large since it knows the total row count.


texts: T SQL, Get new customer added or deleted from historical table

texts:
I have a Table containing historical data. Every day the same staff list will be added with the new dates (between 1200-12500 staff each day however only a few staff might be newly added todaysDate or they may have left the company yesterday and not showing on todays list).
I want to know each day which staff wasn't on yesterday list, it is new staff and the staff was showing yesterday but not today because they left company. I have tried the following but no joy please help
my historical table is like below and I need a script to get the rows for ID4 and ID5 only.

StaffID
StaffName
CensusDate

ID1
Staff1
02/03/2021

ID1
Staff1
03/03/2021

ID2
Staff2
02/03/2021

ID2
Staff2
03/03/2021

ID3
Staff3
02/03/2021

ID3
Staff3
03/03/2021

ID4
Staff4
02/03/2021
not showing today

ID5
Staff5
03/03/2021
is newly added today

ID6
Staff6
02/03/2021

ID6
Staff6
03/03/2021

ID7
Staff7
02/03/2021

ID7
Staff7
03/03/2021

SELECT StaffID, StaffNAME, CensusDate
FROM table1 AS A
LEFT JOIN table1 AS B

ON A.StaffID = B.StaffID AND A.date = GETDATE())  AS date)
and B.date = CAST(DATEADD(day, -1, convert(date, GETDATE())) AS date)
AND A.staffID IS NULL


texts: Attempting to cleanse old users from a SQL Server Management Studio database. Which can i delete?

texts:
So, I've recently started a placement working on a database management team. And I've been asked to cleanse an old database of users who should no longer have access/ defunct accounts of old employees or poorly named users.
However, upon inspection of some of these users, some have 'Owned Schemas' and some have login names which have to be from a select list, however, this name isn't on the list. Anyone know how to know whether I can delete them if they have owned schemas? Some seem to be schemas named after themselves. It seems to be very poorly, maintained. Hence the revamp.


texts: SQL Server AlwaysOn Availablity Group Automatic seeding

texts: I created an AlwaysOn in SQL Server 2019 and I selected Automatic seeding, but it does not create my database in secondary nodes, On the other hand, if I create my database manually I give and error in which your database already exist ! what is the solution?


texts: Oracle DB license for only non-production environments

texts:
My company is a software vendor. We develop software that is later deployed on the customer's environment, on their own machines. Our software uses various DBMS, depending on what the customer has. We do it for PostgreSQL, MySQL and so on.

Currently we have two new customers, which are using Oracle DB in their own production environments. We are obliged, however, to provide an user acceptance environments, which are ought to be 1:1 to the customer's production environment.

Hence my question - do we need to buy an Oracle DB license for ourselves, provided we're never going to have a production environment with that product? In another words - since the customer has the Oracle DB license do we also need to buy one only for testing purposes?


texts: How to Handle Adding 2nd Node to single MariaDB Galera "cluster" already in production?

texts:
I currently have a a single instance MariaDB 10.2.6 Galera instance sitting in production. For reasons, I botched the upgrade from 5.5.56 to 10.2.6 on a previously 4-node cluster.

Is there a way to minimize the downtime that will occur when adding a new MariaDB Galera node to this new cluster?

Could I mysqldump the data and have it rsync the rest of the data?

The grastate.dat on the primary node is as follows

# GALERA saved state
version: 2.1
uuid:    a75b7820-2b7c-11e3-9356-763f33730b6c
seqno:   -1
safe_to_bootstrap: 1

Or am I out of luck in terms of minimizing downtime?


texts: How to create clustered index on 100 GB table

texts:
I have a heap table that takes about 104 GB of disk space with almost 3 billion rows.  I am trying to create a clustered index on this table on the [WeekEndingDate] column.  I have about 200 gb's free in the data file and about 280 gb's free in the tempdb.

I have tried two different methods.  First was to create the index directly on the table with the following command:

CREATE CLUSTERED INDEX CX_WT_FOLD_HISTORY
ON WT_FOLD_HISTORY (WeekEndingDate ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON,
IGNORE_DUP_KEY = OFF
, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
DATA_COMPRESSION = PAGE)

I tried it both with SORT_IN_TEMPDB = ON and OFF.  When using ON it filled up the tempdb and with OFF it filled the data drive.

Other method was to create a new blank table with the needed index and then insert the records from the heap into the new table.  This failed as well after filling up the data drive.

Any other suggestions on what to do.  Most things I've read stated that I would need about 1.2 times the size of the table to be used as a workspace while create the index.  I have way more than that and it still fails.  Any suggestions would be appreciated.

Here is my original heap table structure:

CREATE TABLE [dbo].[WT_FOLD_HISTORY](
[WeekEndingDate] [varchar](50) NULL,
[Division] [varchar](50) NULL,
[Store] [varchar](50) NULL,
[SKUNumber] [varchar](50) NULL,
[UPC] [varchar](50) NULL,
[SalesUnits] [varchar](50) NULL,
[SalesCost] [varchar](50) NULL,
[SalesRetail] [varchar](50) NULL,
[InventoryUnits] [varchar](50) NULL,
[InventoryCost] [varchar](50) NULL,
[InventoryRetail] [varchar](50) NULL,
[OnOrderUnits] [varchar](50) NULL,
[OnOrderCost] [varchar](50) NULL,
[OnOrderRetail] [varchar](50) NULL,
[ReceiptUnits] [varchar](50) NULL,
[ReceiptCost] [varchar](50) NULL,
[ReceiptRetail] [varchar](50) NULL,
[PermanentMarkdowns] [varchar](50) NULL,
[ReturnsToVendor] [varchar](50) NULL,
[POSMarkdowns] [varchar](50) NULL,
[TimeFK] [smallint] NULL,
[LocationFK] [int] NULL,
[ItemFK] [int] NULL
) ON [AcademySports_DataFG1]


texts: Why am I getting an email size error with SQL Server database mail?

texts:
I am sending 6 .csv files as attachments through SQL Server Email. But Email is failing with the following message in the database mail log:

Exception Message: Cannot send mails to mail server. (Service not
available, closing transmission channel. The server response was: DATA
length exceeds 52.4MiB limit)

Then I ran the following command to increase the maxfilesize limit 2 GB
EXECUTE msdb.dbo.sysmail_configure_sp
'MaxFileSize', '2147483647';

Filesize limit was changed successfully to 2 GB, but still I am getting the same error message.
Following are my file sizes in KBs
File A    213   KB,
File B    265   KB,
File C    6246  KB,
File D    35    KB,
File E    60    KB,
File F    31371 KB
I am using mail-gun(smtp) to send an email. Email is running successfully without FILE F. But When I attached FILE F, email fails. Why my email is failing when I increased maxfile size to 2 GB. Any Idea?


texts: What service that lets you create records/data into an immutable database storage system that cannot be deleted?

texts:
I'm not sure if this exists or not, but is there a database storage system that is immutable, whereby it cannot be erased or be altered without someone knowning? Use case would be for public data that should not be manipulated or erased at all. And if there are any modifications it would be known. Also the data would have to be redundantly stored to prevent data loss or deletion.

Is there a service that lets you host any type of data/records in this decentralized manner?


texts: Database Timeout Error from VB Application

texts:
Recently we are receiving so many database timeout error from different servers

we are using vb application -- which will use ODBC connection to connect database - here no problem
when we run the application -- while saving the data it returns database timeout error

First it with one server now i am receiving more three server same issue

NOT with one application we have another vb application

I check is there any deadlocks or locking objects -- nothing is there

I checked database log,windows log and Event viewer

Before that it was working fine and superb

Suddenly from last ten to fifteen days it is giving this errors

Your help will be appreciable


texts: Combining nullif while checking if field is an integer with a case statement

texts:
As part of an ETL process, I need to copy a varchar field in table1 (staging table) which is meant to contain an integer value to a tinyint field in table2 (production table). However I am having some issues when trying to combine the following two business requirements:

If the value of the source varchar field is blank, then it will be set as NULL in the target tinyint field. For that I would normally use NULLIF.
If the field contains anything that could not be cast as a tinyint value (e.g. punctuation, letters, or a value higher than 255) then the value will be set as 255 in the target tinyint field. The rationale behind this is that the tinyint field would never have a 255 value unless there is an error -- and it wouldn't be appropriate to have errors set as NULL or 0 because these are perfectly valid values and we wouldn't be able to differentiate between a valid entry and a mistake.

The problem I have is trying to combine NULLIF with some sort of case statement. I think that perhaps this is not the right approach. Any thoughts on this?


texts: Going JavaScript to PostgreSQL and back!

texts:
In JavaScript we work with nested/complex objects.
In PostgreSQL we have composite types and JSONB.

However, converting between JS types and PG types is not straightforward.

I am currently using JSONB, however I do not like the fact that I need to also cast JSONB fields to text (using ->> or #>> operators) then cast text to the desired PG native type (e.g. ::int4).

I wish that PG supports some kind of a native arbitrary object type like JS.

Any thoughts or better ideas?

Thank you!


texts: How to do a SQL Server 2016 continuous upgrade

texts: I watched this channel9 video, and it mentioned you could now do a continuous upgrade to MS SQL Server. This allows you have a side by side install and it will keep the databases in sync until you have fully migrated all of your apps. I can't find anything else online about this process. Is this called something else or does anyone have any ideas on how to accomplish this?


texts:
CREATE TABLE public.temp (
   id integer,
   a integer,
   b integer,
   x integer
);

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

ends up with expected error:

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

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

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


texts: Tablespace utilization in db2 (percentage of tablespaces)

texts:
how much free space is there…in each tablespace, How to calculate…

i need tablespace utilization in MB's and...how can i get tablespace percentage also...used and usable