texts: Synchronization between MS-SQL on premise database and azure database

texts:
Is there any stable way to synchronize a table between a MS SQL Server on premise database and a table in an Azure database?

So far i have found the following possibilities, but they are not stable:

SQL Data Sync
Transactional Replication
Self written TSQL Script with Elastic Database Transactions(T-SQL is planned for a later time and .Net is not stable)

I use SQL Server 2012 Standard Edition. My problem with the first two methods is that they are not recommended for production use.
I expect suggestions from experienced people.


texts: How to migrate external procedure from Oracle Listener to Postgres

texts:
Consider that I'm using C or java external procedure called from my Oracle 12.2 Listener (EXTPROC listener parameter).
That Oracle database will be migrated to Postgres.

My question is how to migrate the C or java external procedure?

Should I install C or java extension for Postgres, and then compile these procedures inside my Postgres database?


texts: Time Series data for controllers connected to other devices

texts: We have a relatively large number of controllers deployed to the field (4500). Each of these controllers have multiple other devices connected to them. These controllers are now going to be able to start sending us time series data. The data is not so much from the controllers as it is of each device that is connected to it. The data is sent every 15 minutes and includes all the parameters of the connected devices. Some of these controllers will have one device connected and some will have 10 devices connected. The controllers also have the ability to add devices down the line and devices can be removed.Each of the devices send data in a JSON format. How should we design SQL tables to handle this? We think since the time series data will grow over time, we can created one table for every controller and store associated data. We are looking for recommendations on how to store the dynamic number of device data that is sent. Since each controller can have a maximum of 40 devices connected, should we create 40 columns for each table? Also if a new device were to be added to a controller, how would we detect it from a database perspective? Thanks all for your help.


texts: How to add spatial indexes through rockmongo

texts:
In rockmongo we got fields for the field name and whether we should sort by ascending or descending.

How to add spatial indexes then?


texts: Read-Scale Availability Group between a Transactional subscriber and another machine

texts:
I am trying to configure Read-Scale replica between a transactional subscriber and a 3rd machine. I could create the group. But, when i attempted to add the DB (which is a transactional - subscriber) to be part of my read scale replica group, I am getting the following message and not able to complete my read scale replica. My replication is already configured and working fine. Configured the AG as well. All i am doing is, trying to add the DB to the AG.
Here is the message:
Special considerations exist when adding a replication Subscriber database to an availability group. For more information, see "Replication and Availability Groups" in SQL Server Books Online.
But the BOL is not saying anything about AG on a subscriber. Any help on this would be highly appreciated.


texts: Update queries slower after enabling SQL Server Full-Text Index

texts:
I have an asp.net website with many insert, update, and delete queries running against my database.

A few days ago I create a Full-Text Index on two columns of one of tables.
After that I realized that when the website runs update queries on that table,  memory and disk usage of the SQL Server Process is jumping and updates are slower. The queries ran without any performance issues before creating the Full-Text index.

I have also realized that update queries that were very simple before, are now complicated, since the execution plan now has things such as Full Text index update. This is a part of a new execution plan that became complicated after enabling Full-Text:

In some hours when I update site contents, I ran 5000 update queries and I think full text indexing process is done each time for each row.

Should I disable full text scanning on start of updating rows and then re enable it (as in this related question)?

Can I tell SQL Server to stop full text indexing for 5 minutes and then start indexing new data?

Is there any better alternative? I am using SQL Server 2012.


texts:
I have tried installing MariaDB audit plugin for MySQL 5.7.24.
I have downloaded the 'server_audit.so' from  MariaDB-10.3.11 and I think it is compatible with MySQL 5.7.24.
But I have encountered this error while running command

  INSTALL PLUGIN server_audit SONAME 'server_audit';

  ERROR 1126 (HY000): Can't open shared library
  '/usr/lib/mysql/plugin/server_audit' (errno: 2
  /usr/lib/mysql/plugin/server_audit: cannot open shared object file: No
  such file or directory)

thanks for any help


texts: Altering same table across multiple databases

texts:
I have multiple databases on the same instance and I am in the process of updating a table schema that must propagate across all the databases.

I am not sure I know what the right procedure for this is but I think it should go a little something like this?

DO $$DECLARE r record;
BEGIN
  FOR r IN SELECT datname FROM pg_database WHERE datistemplate = false
  LOOP
    EXECUTE 'ALTER TABLE public.' || quote_ident(r) || ' ALTER VARCHAR(200);';
    EXECUTE ...
  END LOOP;
END;
$$;

Any thoughts?


texts: datatypes in sql server , difference between similar dataypes, numeric, money , decimal ,float

texts:
What datatypes could be given to this value --> 45.58

Could these be given:

numeric
money
decimal
float

What's the difference then? Which one would be the best to use?


texts:
I have one master RDS instance of 50 GB storage and created a read replica of the master DB with the same configuration.

I use this read replica only for SELECT operations nothing else. But suddenly I got the storage full problem with the read replica DB. Master Db is working properly, how is it possible if the size is same but replica DB's size is full?

Getting below error when executing the complex query(inner, join, group).
ERROR 3 (HY000): Error writing file '/rdsdbdata/tmp/MY7U2XRf' (Errcode: 28 - No space left on device)

In AWS Console, The slave DB status is "Storage-full" and event log message is :
The free storage capacity for DB Instance: example-slave is low at 1% of the provisioned storage [Provisioned Storage: 49.07 GB, Free Storage: 527.80 MB]. You may want to increase the provisioned storage to address this issue.

But, I checked the free size with below command of both Master and replica Db instances, it's almost the same.

SELECT table_schema,
       ROUND(SUM(data_length+index_length)/1024/1024/1024,2) "size in GB"
FROM information_schema.tables
GROUP BY 1
ORDER BY 2 DESC;

Master DB's space is used 23.86 GB out of 50 GB and Slave DB's space is used 24 GB out of 50 GB.

Please help me.


texts: What happens when the same application from different server tries to connect to the always on primary server

texts:
In our environment we have an always on availability group and the reporting server is acting as a secondary replica with Synchronous Mode, manual failover. XSLT report editor that is a vendor application will connect to the application server which in turn connects to the database server. The application server (separate server)  connects to the listener to serve client connections. My question is if I set up a separate application server just for the XSLT report which in turn connects to the reporting, now will there be any problems if the reporting server for some disaster recovery becomes the primary where the Client application and the XSLT application will both  connect, both the application is same  that is installed on separate server one for client another for the xslt report.

I am not comfortable to set up transactional replication which is an additional overhead. Any advice will be greatly appreciated.


texts: ERROR: column "FLAT" does not exit. Line 1. SELECT UPPER(FLAT). Kindly fix the trigger

texts:
CREATE OR REPLACE FUNCTION trigger_fct_tri_sv_tab_temp()
RETURNS trigger
AS
$BODY$
BEGIN
  n.FLAT := UPPER(n.FLAT);
  RETURN NEW;
end
$BODY$
 LANGUAGE plpgsql
 SECURITY DEFINER;

CREATE TRIGGER tri_sv_tab_temp
    BEFORE INSERT OR UPDATE ON sv_tab_temp FOR EACH ROW
EXECUTE PROCEDURE trigger_fct_tri_sv_tab_temp();


texts: how to design database table record in monthly for Evaluate System

texts:
I'm creating a system which is staff can submit their evaluate marks of the maintenance work of golf course in monthly. The evaluation in each hole.Marks will insert in 1 to 5 marks in each hole.

The column are the category of job, job of work, frequency,hole1,hole2, hole3, hole4.

Moreover, the category of job, job of work, frequency can insert new and can be updated by user if needed.

After evaluating marks need to calculate each hole job have been done in percentage. I decide total all the marks in divide how many jobs have.

However, my issue is with the DB Design, I don't know how to design it to make it work.

So far my Idea is:

(No. varchar(5), JobCategory varchar(15), JobLocation Varchar(15), Hole1 int, Hole2 int, Hole3 int, Hole4 int, Hole5 int, Marks int)

but how do I record in monthly?

Could anyone help please?

Thanks


texts:
I'm building an SSAS OLAP cube on Meteorological data. Some of this data can be totaled (eg rainfall) and some can't (like wind direction).

I want totals, but only on data sources where it makes sense. (Where the interpolation method = 3.)

I've tried adding SCOPE with this:

SCOPE ([Measures].[Total]);
SUM([Data Source].[Interpolation Method].&[3],[Measures].[Total]);
END SCOPE;

And get warnings:

  Invalid query syntax. A valid MDX or DAX query was expected

and:

  One or more errors were encountered in the MDX script

What am i missing?


texts: Oracle returns different results when executing a sql with bind variable or with a fix value

texts:
The following two statements (strongly simplyfied) returning a different result:

with a fix value for rownmun:

select * from table
 where rownum <= 12;

with bind variable for rownum:

select * from table
 where rownum <= :maxsize;

I know, Orcale is using a different execution plan due to internal statistics, but imho even if the choosen plans are different , the result should be the same.
I'm using a Oracle 10g.

My dba skills are limited, therefore I would appreciate any hint or advice, that helps me to understand this strange behaviour.


texts: sp_execute expects parameter '@handle' of type 'int'

texts:
I'm trying to check in a stored procedure if a table exists in my destination database.  If it does not then I will create the table useing the information_schema tables from the source database.  However when I use sp_execute to try and bring back if the table exists I'm receiving the error
Procedure expects parameter '@handle' of type 'int'.

I'm not using an @handle parameter.  Can someone tell me what this error means and why I'm receiving it?  The relevent portion of my code is below.

DECLARE @SQL NVARCHAR(MAX),
        @Parameters NVARCHAR(4000),
        @TableNotExists INT,
        @SourceTable NVARCHAR(200),
        @DestDB NVARCHAR(200)

BEGIN

SET @SourceTable = 'table'
SET @DestDB = 'database'
SET @Parameters = N'@SourceTableIN NVARCHAR(200), @TableNotExistsOut INT OUTPUT'
SET @SQL = N'USE [' + @DestDB + '] IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = ''BASE TABLE'' AND TABLE_NAME = @SourceTableIN)
BEGIN SET @TableNotExistOUT = 1 END'

EXEC sp_Execute @SQL, @Parameters, @SourceTableIN = @SourceTable, @TableNotExistsOUt = @TableNotExists OUTPUT

END


texts: PostgreSQL read-only user for pg_dumpall

texts:
I'm running a PostgreSQL instance which is used by several related applications, each of which has its own database. For backups I want to create snapshots of the complete data in the instance and I plan on using pg_dumpall to do that. The backups are done from another host and should use a separate PostgreSQL user for security reasons.

I am therefore trying to create a PostgreSQL user backup who

can read all data in the instance, both in existing databases/tables and in databases/tables that may be added in the future by other users
cannot modify anything

So far I have managed the following:

-- Revoke default privileges
REVOKE ALL ON SCHEMA public FROM PUBLIC;

-- Allow user to connect and use database
GRANT CONNECT ON DATABASE db TO backup;
GRANT USAGE ON SCHEMA public TO backup;

-- Grant read privileges for existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO backup;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO backup;

-- Grant read privileges when new tables are created
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO backup;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO backup;

This works, but only for a single, fixed database. Is there a way to do this for all existing databases and databases that might be created in the future?

I'm currently on PostgreSQL 9.4 and I'm not using any schemas aside from public.


texts:
Even though I'm (apparently) setting the search_path correctly I still have to prefix everything with the schema. What's wrong here?  How do I get a default schema from the search_path?

psql blip
psql (9.4.1, server 9.4.4)
Type "help" for help.

blip=> \dt messages

no matching relations found.
blip=> \dt blip_schema.messages
            List of relations
   Schema    |   Name   | Type  | Owner
-------------+----------+-------+--------
 blip_schema | messages | table | dbuser
(1 row)
blip=> set search_path to "blip_schema,public";
SET
blip=> show search_path;
     search_path
----------------------
 "blip_schema,public"
(1 row)

blip=> \dt messages
No matching relations found.
blip=> \dt blip_schema.messages
            List of relations
   Schema    |   Name   | Type  | Owner
-------------+----------+-------+--------
 blip_schema | messages | table | dbuser
(1 row)


texts:
I searched for Assert and found general explanations:

Assertion (software development)

  In computer programming, an assertion is a statement that a predicate
  (Boolean-valued function, a true–false expression) is expected to
  always be true at that point in the code. If an assertion evaluates to
  false at run time, an assertion failure results, which typically
  causes the program to crash, or to throw an assertion exception.

and

What does the “assert” keyword do?

  Assertions are generally used primarily as a means of checking the
  program's expected behavior. It should lead to a crash in most cases,
  since the programmer's assumptions about the state of the program are
  false. This is where the debugging aspect of assertions come in. They
  create a checkpoint that we simply can't ignore if we would like to
  have correct behavior.

As a database operatior I only saw asserts when data files got corrupt (repair needed, sometimes data loss). Any more examples and definition specific for database systems (both SQL and NoSQL)?


texts:
we have system that quite a lot and often asks database about:
SELECT TABLE_NAME, TABLE_SCHEMA, COLUMN_NAME, ORDINAL_POSITION, COLUMN_DEFAULT, NUMERIC_PRECISION, CHARACTER_MAXIMUM_LENGTH, NUMERIC_SCALE, DATA_TYPE, IS_NULLABLE
FROM "information_schema"."columns"
WHERE TABLE_NAME = 'some_instances'
 AND TABLE_SCHEMA = 'public'
ORDER BY ORDINAL_POSITION

Time to execute this query is 10s+, and it ruins overall performance
"information_schema"."columns" contains more than 5m records
Postgres version is 9.6
How to speed up such query?
UPD. cant paste here execution plan as it's off stackexchange limitation.
here is the link: https://github.com/mkdel/code_pile/blob/main/EXPLAIN_ANALYZE_BUFFERS


texts: How do I allow non sysadmin accounts the ability to create/query linked servers in SQL Sever 2014?

texts:
I want to allow a non-admin account the ability to query a linked server.

If I create a linked server with the following under a sysadmin account like below.

EXEC master.dbo.sp_addlinkedserver @server = N'LINKEDSERVER', @srvproduct=N'', @provider=N'IhOLEDB.iHistorian', @datasrc=N'linkedservername'
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LINKEDSERVER',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

When I run SELECT * FROM OPENQUERY(LINKEDSERVER, 'SELECT  * FROM ihTags') from a sysadmin account (no matter what account it is), it works.

If I run the query from a non-sysadmin account I get the following error.

  Msg 7416, Level 16, State 2, Line 2
   Access to the remote server is denied because no login-mapping exists.


texts: What happens if you lose the connection during a huge insert?

texts:
What happens when you lose your connection with the DB during a huge insert. Will part of the data be inserted? Will nothing be inserted? What is the reason for this?

The example statement I am picturing is:

INSERT INTO user
  (variable)
values
  (?),
  (?),
  (?),
  ...,
  (?)

I am specifically interested in MySQL or SQL Server.


texts: MySQL - Can I have quick queries in a table with 200 million rows?

texts:
THE PROBLEM
I have following trivial SELECT statement:
select * from `web_notifications` where `user_id` = 123456 order by `created_at` limit 500;

The query is pretty simple (no join statements, no aggregate functions). The query is being executed on a high-class AWS RDS Aurora machine (db.r5.4xlarge). Yet, the query executes for 0.70 seconds the first time and for 0.13 seconds afterwards constantly.
CONTEXT
Here is my EXPLAIN result (as you can see I am using an index):
| id | select_type | table             | partitions | type | possible_keys                                                            | key                                   | key_len | ref   | rows | filtered | Extra                 |
| -- | ----------- | ----------------- | ---------- | ---- | ------------------------------------------------------------------------ | ------------------------------------- | ------- | ----- | ---- | -------- | --------------------- |
|  1 | SIMPLE      | web_notifications | NULL       | ref  | web_notification_users_seen_at_idx,web_notification_users_created_at_idx | web_notification_users_created_at_idx | 4       | const |  912 |   100.00 | Using index condition |

Here is the create table statement:
CREATE TABLE `web_notifications` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`text` varchar(255) NOT NULL,
`url` varchar(255) NOT NULL,
`seen_at` timestamp NULL DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`deleted_at` timestamp NULL DEFAULT NULL,
`user_id` int(10) unsigned NOT NULL,
`operation` varchar(64) DEFAULT NULL,
`start_year` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `web_notification_users_seen_at_idx` (`user_id`,`seen_at`),
KEY `web_notification_users_created_at_idx` (`user_id`,`created_at`),
CONSTRAINT `fk_web_notification_users1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=2000000000171904234 DEFAULT CHARSET=utf8

The table has 170 million rows.
Am I doing something wrong or I should look for an alternative storage solution (probably, noSQL) for such highly-loaded and highly-intensive table (as my table web_notifications)? 0.70 is absolutely unacceptable execution time for such straightforward query.
P.S. I removed the order by phrase and the query time didn't fell down significantly.


texts: Where does SQL Server store the SA password and SQL Server password policy?

texts:
On Linux, where does SQL Server store the "SQL Server password policy" and the SA user's password? I get it, "password" isn't secure.

  ERROR: Unable to set system administrator password: Password validation failed. The password does not meet SQL Server password policy requirements because it is too short. The password must be at least 8 characters..

and then,

  ERROR: Unable to set system administrator password: Password validation failed. The password does not meet SQL Server password policy requirements because it is not complex enough. The password must be at least 8 characters long and contain characters from three of the following four sets: Uppercase letters, Lowercase letters, Base 10 digits, and Symbols..

I'm guessing the policy is actually compiled into the database? And the password gets stored in an obscure location?


texts: Does restoring database clear buffer cache and stored proc cache

texts:
on our development server, we refresh database (restore from production backup) every week. my question is does restore db clear its buffer cache/stored procedure cache from sql memory? Does it have the same/similar effect of  DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS

Thanks.


texts: We've been seeing some deadlocks occur but have not been able to reproduce.  Is there any way to determine, after the fact, what queries were running, etc to figure out why deadlock occurred?


texts:
Following a reboot, two of our databases were marked as “Recovery Pending” and could not be accessed.  These databases are both using FILESTREAM.  In trying to fix them, we attempted the following:

Take the database offline (“could not take database offline”)
Detach the database (Database cannot be accessed because it is in recovery)
After several hours, tried restoring the database.  It refused because the database was in use, so we dropped the database, and tried to restore.

It then told us that FILESTREAM was not enabled on the instance.  Sure enough, it was gone.  The problem is, we can’t turn it back on.

Every time we do, we get the following dialog message:

  There was an unknown error applying the FILESTREAM settings.
  Check the parameters are valid. (0x80070002)

In the Event log, we have this message:

  FILESTREAM feature configuration might be inconsistent.  To reset the configuration, use the sp_configure stored procedure.

The following succeeds…

exec sp_configure 'filestream access level', 2;
reconfigure;

…but, the value doesn’t change.

name                    minimum maximum config_value    run_value
filestream access level 0             2            2            0


texts: Using SQL Server, how can I obtain the restore date for a particular database?


texts: After I replaced master mdf/ldf files, can no longer log in

texts:
Today I couldn't start SQL Server process anymore. Reason:

The log scan number (213:18:1) passed to log scan in database 'master' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

Ok, then I searched the internet looking for a solution for this strange problem and I found a blog post suggesting that I should replace master.mdf and mastlog.ldf files with the ones located in C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\Template Data and so I did.
Now I can start the process but I can't log in with neither my 2 previous login accounts. I thought that after replacing master I would at least be able to login with my Windows account, but I can't.
These are the login failed event logs:

Login failed for user 'andre-PC\andre'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: ]
Login failed for user 'andrerpena'. Reason: Could not find a login matching the name provided. [CLIENT: ]

How can I work around this?


texts: How do DTUs in Standard and Premium performance tiers in SQL Azure compare?

texts:
We recently observed a serious performance degradation in a SQL Azure database running in Standard3 performance tier - CPU utilization went from ten percent to fifty percent to almost one hundred percent in just one hour. So we changed the performance tier to Premium2 and CPU utilization immediately dropped to about eight percent.

Standard3 is claimed to offer 100 DTUs and Premium2 is claimed to offer 250 DTUs. Which means that eight percent of P2 is just twenty DTUs which is very far from using all of 100 DTUs in Standard3.

Are those DTUs different? Otherwise how is this sudden drop of utilization possible when switching from a 100 DTUs performance tier to a 250 DTUs performance tier?


texts: How to Reorganize Microsoft Access Table

texts:
I have just taken over a retiring DBA's position and is responsible for migrating the current access database to SQL Server  or PostgreSQL. Right now, the table structure is a mess. Instead of properly establishing relationships between tables, multiple fields and parameters are cramped in a single table.

I have broken up the table and plan to regroup the parameters into separate individual tables and link them together. My question is, the unorganized tables contain many rows of data, with over 40 different parameters, what is the best way to break up the table and still have all the data repopulated in the new tables linked?


texts: Linked Server: An error occurred during decryption

texts:
We're running a SQL Server instance on AWS RDS. I believe this is a 2017 instance. We also have a couple of Linux EC2 instances running SQL Server (not sure the version, but I assume 2017)

Our RDS instance has linked servers set up to both of the Linux instances. What I've found is that after creating the linked servers, I can query them and they work fine.

This is a QA environment and, to save money, we take all the servers down at night. Then they are started on demand. The problem I'm seeing is that once the servers come back up, I get the following error when trying to query the linked servers:

  System.Data.SqlClient.SqlException (0x80131904): An error occurred during decryption.

My best guess is that when the RDS instance comes back up, it appears to be different hardware and I'm guessing there is a decryption key based on that hardware?

I've tested a couple things to eliminate possibilities:

I tested leaving the Linux instances up and just stop/start (not restarted) the RDS instance. This seemed to reproduce the problem. I haven't yet tried the reverse (leave RDS running and stop/start a Linux instance).
I tried dropping and recreating the linked server when I get this error. This seems to fix the problem.

Theoretically, I guess I could use this sp_procoption procedure I've been reading about to run a query at server startup that would drop and recreate all the linked servers. But that seems like the wrong way to fix this.

We could leave our servers running 24x7, though our budget is pretty lean so we'd rather not. And besides, if we had some other reason to stop/start our RDS instance, we still have this problem.

Is there any way to make this work other than just running a drop/create script on the linked servers at server startup?


texts: Speed up SELECT with WINDOW, (PARTITION ... ORDER) with compound index

texts:
I have the following table:

CREATE TABLE ranking_points  (
  id serial NOT NULL,
  store_id integer,
  book_id integer,
  list_name character varying(255),
  rank integer,
  created_at timestamp without time zone,
  updated_at timestamp without time zone,
  storewide boolean,
  yesterday_delta integer,
  best_ever_rank integer,
  best_ever_date date,
  CONSTRAINT ranking_points_pkey PRIMARY KEY (id)
);

CREATE INDEX index_ranking_points_on_book_id_and_created_at
  ON ranking_points (book_id, created_at);

CREATE INDEX index_ranking_points_on_book_id_list_name_and_created_at_desc
  ON ranking_points (book_id, list_name, created_at DESC);

CREATE INDEX index_ranking_points_on_store_id_and_created_at
  ON ranking_points (store_id, created_at);`

I am executing the following SELECT statement:

select distinct book_id, list_name,
                first_value(created_at) over w as created_at,
                first_value(rank) over w as rank,
                first_value(id) over w as id
from ranking_points where book_id in (61,62,63)
window w as (partition by book_id, list_name order by 3 desc)

I would expect Postgres to use the index_ranking_points_on_book_id_list_name_and_created_at_desc index since it perfectly matches the partition by and order by, but instead it uses the index_ranking_points_on_book_id_and_created_at index. Here is the explain analyze:

HashAggregate  (cost=895.70..901.41 rows=571 width=42) (actual time=0.740..0.740 rows=0 loops=1)
  ->  WindowAgg  (cost=874.28..888.56 rows=571 width=42) (actual time=0.737..0.737 rows=0 loops=1)
        ->  Sort  (cost=874.28..875.71 rows=571 width=42) (actual time=0.737..0.737 rows=0 loops=1)
              Sort Key: book_id, list_name
              Sort Method: quicksort  Memory: 25kB
              ->  Bitmap Heap Scan on ranking_points  (cost=17.25..848.14 rows=571 width=42) (actual time=0.728..0.728 rows=0 loops=1)
                    Recheck Cond: (book_id = ANY ('{61,62,63}'::integer[]))
                    ->  Bitmap Index Scan on index_ranking_points_on_book_id_and_created_at  (cost=0.00..17.11 rows=571 width=0) (actual time=0.726..0.726 rows=0 loops=1)
                          Index Cond: (book_id = ANY ('{61,62,63}'::integer[]))
Total runtime: 0.811 ms

You can see that what I'm trying to do is to build a result set consisting of the grouping values and the first_value of several fields. My hope was that Postgres could do all of the selection using only the index and then read one record from each group to obtain the first_value values. Is that achievable? I have other solutions, but they generate a sequential scan of the table, so that's not very good.


texts: Granting a user read-only access on [dbo] but full access on their own schema

texts:
I created a user at the database level. I created a schema [jm] for them to use. I would like them to be able to create whatever database objects only within their own schema - but limit their abilities in [dbo] to purely read-only.

How can I accomplish that?


texts: Correct way to stop SQL Server (through automation)

texts:
Question

Is it safe to start & stop SQL's services using PowerShell's Start-Service and Stop-Service commands?
If not, is there a recommended way to start & stop SQL's services through automation?

Additional Info

We're in the process of creating scripts to bring down our systems cleanly before maintenance windows, then to bring them back up afterwards.
By cleanly I mean it stops the application's services, then stops the database services (i.e. in dependency order), setting all to DISABLED, so that the infrastructure team can apply windows updates / perform any work, safely rebooting servers as required, before running the scripts to bring the system back up in the correct (dependency aware) order.

Our DBA mentioned that the correct way to stop services is through SQL's Configuration Manager; implying that that's different to just using services.msc (or for automation, powershell's stop-service / command line's sc \\someServer stop someService.

The Note at the top of this article implies something similar: https://technet.microsoft.com/en-us/library/ms175516(v=sql.105).aspx
This article points out that SQL Server Configuration Manager has some advantages for some functions: https://msdn.microsoft.com/en-us/library/ms174212.aspx

However I've not been able to find anything explicitly saying it's incorrect to start/stop services through the regular means, or what extra protection/functionality SQL Configuration Manager gives with regards to simply starting & stopping services.


texts:
I have a /mariadb_tmp directory (as specified in my mariadb-server.cnf) which is filled with files dating back to last November.

-rw-rw---- 1 mysql mysql   1032192 Nov 19 07:47 #sql899_5d5_0.ibd
-rw-rw---- 1 mysql mysql    278528 Nov 19 09:30 #sql899_ab8f_0.ibd
-rw-rw---- 1 mysql mysql    278528 Nov 19 09:30 #sql899_ad17_0.ibd
-rw-rw---- 1 mysql mysql    638976 Nov 19 09:32 #sql899_baf3_0.ibd
-rw-rw---- 1 mysql mysql    409600 Nov 19 09:32 #sql899_bbec_0.ibd
-rw-rw---- 1 mysql mysql    262144 Nov 19 09:35 #sql899_c96a_0.ibd
...
-rw-rw---- 1 mysql mysql    704512 Jan 16 10:08 #sql899_603c0c4_1.ibd
-rw-rw---- 1 mysql mysql    540672 Jan 16 10:08 #sql899_603c123_0.ibd
-rw-rw---- 1 mysql mysql 494927872 Jan 16 10:20 #sql899_603eb7b_0.ibd
-rw-rw---- 1 mysql mysql    606208 Jan 16 10:27 #sql899_60470ff_1.ibd

Googling on 'delete old mariadb temp files' leads to advice such as:

drop table `#sql899_5d5_0`;

which results in the error 'no database selected.'  Of course, I don't know what database each tmp files comes from, so I don't know if that would work.

There are no *.frm files to go with these, and they are in the specified tmpdir directory, not the datadir.

Any idea how to clean these up?  They are taking up a bunch of space on a disk that we really can't expand (for non-technical reasons) nor should we have to keep these around that long.

Thanks,

Ed Greenberg


texts: how to force oracle 12c to create histograms

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

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

for creating extended statistics. Afterwards I gathered statistics:

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

I also queried some stuff on the respective columns like

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

or

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

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

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

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


texts:
The SQL Server 2008 R2 Web Edition product marketing site has a benefit listed on its High Availability section:

  Benefit from log shipping, which helps automatically back up data on separate servers or keep multiple-read servers online to better handle large amounts of web traffic... through one-way merge and transactional replication.

How is this scenario implemented? Is this referring to:

log shipping to multiple target instances and using snapshots and lots of T-SQL to keep them in sync
new functionality to manage multiple-read servers?
other?


texts: compare the variance of sales from one day to another on the same table


texts: Passing array from one plpgsql function to another

texts:
I have a function in PostgreSQL 9.4 that calls another function. Both are written in plpgsql. The child function takes an array argument representing IDs from a table that is used to create a new table.

Parent function:

CREATE OR REPLACE FUNCTION foo(linktable REGCLASS,
                               inttable REGCLASS,
                               verttable REGCLASS)

DECLARE
    intersection_ids INT[];

-- ... SNIP

    BEGIN
        EXECUTE format('SELECT array_agg(id) from %s',inttable) INTO intersection_ids;
        EXECUTE format('
            SELECT tdgSetTurnInfo(%L,%L,%L,%L);
            ',  linktable,
                inttable,
                verttable,
                intersection_ids);
    END;

-- SNIP ...

Child function:

CREATE OR REPLACE FUNCTION bar(linktable REGCLASS,
                               inttable REGCLASS,
                               verttable REGCLASS,
                               intersection_ids INT[])

-- ... SNIP

    EXECUTE format('
        INSERT INTO %s (int_id,
                        ref_link_id,
                        match_link_id,
                        ref_azimuth,
                        match_azimuth)
        SELECT  int.id,
                l1.id,
                l2.id,
                degrees(ST_Azimuth(ST_StartPoint(l1.geom),ST_EndPoint(l1.geom))),
                degrees(ST_Azimuth(ST_StartPoint(l2.geom),ST_EndPoint(l2.geom)))
        FROM    %s int,
                %s v1,
                %s v2,
                %s l1,
                %s l2
        WHERE   int.id ANY %L
        AND     int.id = v1.intersection_id
        AND     int.id = v2.intersection_id
        AND     l1.target_node = v1.node_id
        AND     l2.source_node = v2.node_id
        AND     l1.road_id IS NOT NULL
        AND     l2.road_id IS NOT NULL
        AND     l1.road_id != l2.road_id;
        ',  temptable,
            inttable,
            verttable,
            verttable,
            linktable,
            linktable,
            intersection_ids);

-- SNIP ...

When I run this code I get an error:

QUERY:

INSERT INTO tdggtitemptbl (int_id,
                ref_link_id,
                match_link_id,
                ref_azimuth,
                match_azimuth)
SELECT  int.id,
        l1.id,
        l2.id,
        degrees(ST_Azimuth(ST_StartPoint(l1.geom),ST_EndPoint(l1.geom))),
        degrees(ST_Azimuth(ST_StartPoint(l2.geom),ST_EndPoint(l2.geom)))
FROM    a_intersections int,
        a_net_vert v1,
        a_net_vert v2,
        a_net_link l1,
        a_net_link l2
WHERE   int.id ANY '{1,2,3,4,5,6,7,8,9,10,11,12,13}'
AND     int.id = v1.intersection_id
AND     int.id = v2.intersection_id
AND     l1.target_node = v1.node_id
AND     l2.source_node = v2.node_id
AND     l1.road_id IS NOT NULL
AND     l2.road_id IS NOT NULL
AND     l1.road_id != l2.road_id;

However, when I run similar code outside of the function and without the format() statement it runs without issues. Is there a problem with running my INT[] array through the format()? Or is it something else entirely?


texts: MySQL - Joining table with different charset in a view

texts:
I want to join tables from two different schemas. But the problem is both schemas follow different charset utf8_general_ci & utf8mb4_unicode_ci.

When i do a join on both tables, it works. But, when i create a view with same query and do a select on the view, it throws an error

Error Code: 1267. Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='


texts:
I'm trying to create a view under my DBA schema on Oracle XE. I already did that on the productive database and it worked with a user with a lot lesser privileges than the DBA role. But now I always get ORA-01031 errors.
Here is my query:

CREATE OR REPLACE VIEW usr_v_user_not_reg AS
  SELECT username "User", db_instance "Instance",
    (
      CASE
        WHEN username IN
        (
          SELECT username
          FROM sys.dba_users
          MINUS
          SELECT username
          FROM usr_t_user_reg
        ) THEN 'not registered'
        WHEN username IN
        (
          SELECT username
          FROM usr_t_user_reg
          MINUS
          SELECT username
          FROM sys.dba_users
        ) THEN 'no longer present'
      END
    ) "Status"
  FROM usr_t_user_reg
  WHERE db_instanz = 'TEST_DB'
    AND username NOT IN
    (
      SELECT username
      FROM usr_t_user_reg
      INTERSECT
      SELECT username
      FROM sys.dba_users
    )
;

And this is the output I get:

Line: 10 Column:20
SQL-Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to change the current username or password
           without the appropriate privilege. This error also occurs if
           attempting to install a database without the necessary operating
           system privileges.
           When Trusted Oracle is configure in DBMS MAC, this error may occur
           if the user was granted the necessary privilege at a higher label
           than the current login.

I even tried it with the SYS account, but with the same result. Am I missing something important?


texts: Altering a table to add a New Column vs Create table with New Column

texts:
I have Table A which i'm tasked to add a new column is_fully_complete bit(1), and i'm thinking:

Would it be better to

a.) Create a new table Table_A_new like Table_A and add the new column there before inserting the data from Table A?

or

b.) rely on Alter Table Syntax to add a new column.

i tried option B but on my local machine and it's taking too long for a 10-min time frame. Table A by the way has about 1.32 million rows, and is running an InnoDB engine. We're using Percona Mysql 5.5 if it helps

EDIT: i forgot to mention we're also using Ubuntu 10.04 on our servers along with Percona Mysql 5.5


texts: Access query to select multiple records with matching field

texts: I have a table with a list of people ([FirstName], [Surname], [Age]). Some of these people are in the same family. I'd like to return a list of people who have another family member in the table (same surname). However I'd like to exclude records that have no other records with the same surname. Whats the best way to do this in MS Access


texts:
SQL Server backup media set and file separation
If I am going to do such a backup plan under full recovery model for a particular database

Weekly full database backup
Daily differential backup
hourly T-log backup

Retention period: 28 days

How should I arrange the files?
Should each and every backup have a unique file name?

I tried using SMSS to do a few and ending up all backups in a single test.bak file.
I can see the content of the backup file test.bak contains a lot of backups including full backups, differential backups and log backups.

I know it wasn't right. What is the best practice for arranging backups into separate physical backup files?


texts: Huge difference in execution time of update statement with default value

texts:
Last week I tried deploying a new version of an application on an app server. Prior deploying, some database scripts needed to be executed on an Oracle 11g database.

The exact version of the database is Oracle Database 11g Release 11.2.0.1.0 - 64bit Production . Theree are no triggers on the tables.

The statement causing the headache is this one:

ALTER TABLE EMS_DATA ADD STATUS VARCHAR2(1) DEFAULT 'V' NOT NULL;

on a DDL that looks like this:

create table EMS_DATA
(
    CHANNEL_ID NUMBER not null,
    TSTAMP TIMESTAMP(3) WITH LOCAL TIME ZONE not null,
    VAL FLOAT,
    constraint EMS_DATA_PK primary key (CHANNEL_ID, TSTAMP)
)

The EMS_DATA PROD table contains around 350 million records and our ACC table contains around 280 million records. Both are Oracle 11g instances.

I ran the statement twice on the ACC table and the execution time was approx. 50ms and 300ms. The reason why it SHOULD be fast as this is the following:

  Metadata-Only DEFAULT Values

  Prior to Oracle 11g, adding a new column to an existing table required
  all rows in that table to be modified to add the new column.

  Oracle 11g introduced the concept of metadata-only default values.
  Adding a NOT NULL column with a DEFAULT clause to an existing table
  involved just a metadata change, rather than a change to all the rows
  in the table. Queries of the new column were rewritten by the
  optimizer to make sure the result was consistent with the default
  definition.

  Ref: https://oracle-base.com/articles/12c/default-values-for-table-columns-enhancements-12cr1

So yes I was fairly confident the update would cause no issue on the PROD database. Instead, this happened;

The execution of the statement took > 120mins and eventually came to a halt because what had happened was that the disk drive was full. After inspection we saw that 180GB of .ARC (Archived Redo Logs) files have been written over this period of time.

TL;DR
What can cause this alter table statement to have this HUGE difference in duration and why does one database generate a ridiculous amount of .ARC files when the total amount of data in the table isn't even close to 180GB of disk space.
Can this Metadata-Only optimisation be switched off?

It's important to note that the application server was turned off at the moment of the script execution, so no outside process could have caused this.


texts: Getting the count of same value in consecutive rows that are ordered by different field

texts:
I have a table with the following structure

finals(ID, name, result)

CREATE TABLE `finals` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(10) DEFAULT NULL,
  `result` char(4) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

Where:

ID is an auto increment key,
name is the person's name,
result is either Pass or Fail

Sample data

mysql> select * from finals;
+----+------+--------+
| id | name | result |
+----+------+--------+
|  1 | John | Pass   |
|  2 | John | Fail   |
|  3 | John | Pass   |
|  4 | Kyle | Pass   |
|  5 | John | Pass   |
|  6 | Kyle | Pass   |
|  7 | Kyle | Pass   |
|  8 | Kyle | Fail   |
+----+------+--------+
8 rows in set (0.00 sec)

Explanation

I am trying to get the names of people who have at least 3 consecutive "Pass".
So, the desired result is:
"Kyle"
Because "Kyle" has 3 cosecutive "Pass", IDs 4, 6, 7.

Note please that I don't care if this sequence is interrupted by a record of other person, but I care if it was interrupted by a record for the same person with "Fail" value in the result.

That is why "John" is not in the desired result. i.e. Although "John" has 3 "pass" in total, they are interrupted with a "Fail", so I want to exclude it from the result.

I Tried this query:

mysql> SELECT name, GROUP_CONCAT(result ORDER BY id) as result_str FROM finals GROUP BY name having result_str LIKE '%Pass,Pass,Pass%';
+------+---------------------+
| name | result_str          |
+------+---------------------+
| Kyle | Pass,Pass,Pass,Fail |
+------+---------------------+
1 row in set (0.00 sec)

It works, and this is the desired result. However, I am looking for alternative ways to

Be more Generic
avoid the constraints on the length of the result of GROUP_CONCAT.

Thank you in advance.


texts: What things we should consider when configuring Cassandra Cluster?

texts:
I'm new to Cassandra, so wanted to understand what basic things we should consider when are we going to configure Cassandra cluster in on or multiple database centers?

Thanks!


texts:
I'm new to powershell. I need to write a script which will import data for each table into separate text file.For sample table, I tried with below script:

    bcp -u sa por.dbo.audit out D:\tbackup\audit.dat

and I received below error:

Copy direction must be either 'in', 'out' or 'format'.
usage: C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\130\Tools\Binn\bcp.exe {dbtable | query} {in | out | queryout | format} datafile

  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-V file format version] [-q quoted identifier]
  [-C code page specifier]  [-t field terminator]    [-r row terminator]
  [-i inputfile]            [-o outfile]             [-a packetsize]
  [-S server name]          [-U username]            [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-x generate xml format file]
  [-d database name]        [-K application intent]  [-l login timeout]

Might be it's a silly question. But, any immediate help will be appreciated


texts: SET or VARCHAR for list of tags in a MySQL photos table?

texts:
I just discovered the SET column type for MySQL tables. I have a photos table with ARIA engine where I want to store tags/keywords for each photo.

Now I use a VARCHAR type and then find the photos with a MATCH query and a FULLTEXT INDEX, the tags are space separated like "one two three".

When I use SET, I could possibly use tags with spaces, like "a tag with spaces, another one", but how fast would this be? And can I use FIND IN SET to list photos by tag and also list all tags from a range of photos?

Which is the better choice for tags, SET, VARCHAR or even another type?


texts: Improve BLOB Writing Performance in Oracle 11g

texts:
I have an application that writes a lot of rows into a table that contains a blob column. The average size of the blob is 3k bytes.

Is there any buffer that i can resize to improve inserts into this table?
Does the lob buffer still apply for Oracle 11g ?

Thx.


texts: Print Column Names for Table in Another Database

texts:
I am in one database. How do I print out the columns names for table in another database?

create table dbo.Customer
(
     CustomerId int primary key,
     CustomerName varchar(100),
     ZipCode varchar(9)
)

Answer within Current Database:

declare @ColumnList varchar(max) =
(select STUFF((
SELECT ',
    '
+ QUOTENAME(c.name)
FROM .sys.columns c
where c.object_id = object_id('Customer')
FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,2,''))

print @ColumnList

Print columns for table in Database B

Added Database B below,

declare @ColumnList varchar(max) =
(select STUFF((
SELECT ',
    '
+ QUOTENAME(c.name)
FROM .sys.columns c
where c.object_id = object_id('DatabaseB.dbo.Customer')
FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,2,''))

print @ColumnList


texts: Defining multiple fields in a select string with hard set length values (SQL 2000)

texts:
Currently I have been tasked to modify a super old stored procedure that represents an email output in our SQL 2000 instance. With correcting a few syntax issues I wanted the original string format to be more presentable in the email. Because the fields "machine" and "Part #" have a variance of the fields data length thus the fields shift and the columns do not line up entirely. What I have been attempting is to set each field present in my SQL code to hard set values of say 15 characters that way the format of each field presented does not change/shift and looks straight to the end user. Now I have tried a few suggestions online and could not find a method to set each field. Any ideas?

 INSERT INTO ##prod_performance_txt_out2
               SELECT LEFT(machine + '             ', 9)
          + LEFT(part_no + '                              ', 30)
          + LEFT(CONVERT(varchar(7), ordered) + '       ', 7)
          + LEFT(CONVERT(varchar(7), picked)  + '       ', 7)
          + LEFT(CONVERT(varchar(7), allocated)  + '       ', 7)
          + LEFT(CONVERT(varchar(7), in_stock)  + '       ', 7)
          + LEFT(CONVERT(varchar(7), sh_bin_qty)  + '       ', 7)
          + LEFT(CONVERT(varchar(7), short)  + '       ', 7)
          + min_bin

               FROM #tempsummary2 WHERE resource = @resource AND short > 0


texts: How to search whole MySQL database for a particular string

texts:
is it possible to search a whole database tables ( row and column) to find out a particular string.

I am having a Database named A with about 35 tables,i need to search for the string named "hello" and i dont know on which table this string is saved.Is it possible?

Using MySQL

i am a linux admin and i am not familiar with databases,it would be really helpful if u can explain the query also.


texts: I can see a X lock on a RID although the query uses a non-clustered index

texts:
My initial issue was two update queries, ran in the same transaction, trying to acquire a X lock on a RID on the same table. The second query wants to update a different record in the table, but it would never complete because the first query blocks the table.

In order to fix that issue, I created a non-clustered index.

My question is: After creating that new non-clustered index, I checked the execution plan and I can see that the query uses the new non-clustered index. But, when I look at my locks, I see that the first query still acquires a X lock on a resource_type RID. Why ? I expected to see a X lock on a KEY resource_type.

Non-CLustered Index

    CREATE NONCLUSTERED INDEX [NonClusteredIndex-20170302-092518] ON [dbo].[USERS]
(
    [PARTITION] ASC,
    [DATAAREAID] ASC,
    [WORKBENCHID] ASC,
    [VALID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80)
GO

Query (acquires a X lock on the RID resource_type, updates 54 rows)

BEGIN TRANSACTION
UPDATE WORKBENCHOPENINVOICES SET VALID=1,RECVERSION=894734738
WHERE (((PARTITION=5637144576) AND (DATAAREAID=N'1010')) AND ((WORKBENCHID='WB10000887') AND (VALID=1)))
    WAITFOR DELAY '00:30:00' -- 5 minutes
ROLLBACK TRANSACTION

By the way, the second query will complete now because I have that non-clustered Index, but I still don't understand why the first query would acquire a lock on a RID instead of KEY when using the non-clustered index
Thoughts ?

Thank you


texts: sql server in docker using 15gb of disk space

texts:
I'm following the instructions here to create my own docker container containing a sql server instance with databases and data configured.

I've added two databases, a few tables containing at most hundreds of items each, and a few stored procedures.

The base sql server image is 1.57 gb . (I'm using 2019-latest).

I would expect the total size to be only slightly higher. However in actual fact the container disk space usage is 15.6GB (virtual 17.1GB).

Does anyone have any idea why this might be, and how it can be reduced?

EDIT 1:
I've just checked and one of the databases I added is 14gb according to SSMS (tasks > shrink > database > currently allocated space). However it contains very little data (It could be scripted in a 1mb file) so I don't know why this is.

EDIT 2:
Mystery Solved: For some reason the scrript I was using to create the database had allocated 11gb for the log file.


texts:
I'm importing around 44 GB (14 million rows) of information into an InnoDB MySQL database, and I'd like to do so in a reasonable timeframe. Since I'm doing this on a personal computer, I only have 8 GB of RAM available to me (and I'm using 1.5 GB for the MySQL database).

Right now, I'm using LOAD DATA INFILE. I've tweaked a lot of the InnoDB settings, including increasing the buffer pool size and buffer log size. However, the speed of this method seems to drop rapidly as the amount of information in the database increases - the first "chunk" of data (100 MB) loaded in 79 seconds, but by the time I reached the fourth chunk of data, it took 1004 seconds to load the same amount. Unfortunately, this precipitous drop in transfer rate makes it nearly impossible to load all of the data onto the MySQL server in a reasonable timeframe.

I had several questions about the scenario. Why does the transfer rate drop so drastically? I've heard that MyISAM is more efficient for loading data, so should I try to use a MyISAM engine to load this data instead? (I'll be accessing the data only locally) Are there any other techniques I can use to expedite the data loading process?


texts: How can I call a remote User Defined Function?

texts:
I have SQL server which contains a link to another server(For e.g: test) I have a function in linked server(name:getvalue(@parameter)).so this function output i want to retrieve in SQL server 2012.I am using below methodology.

DECLARE @sql nvarchar(4000);
DECLARE @somevalue nvarchar(16)

SET @sql = 'SELECT * FROM OPENQUERY(test,
                          ''SELECT @somevalue=getvalue(' +CAST('545454545' as nvarchar(10))+ ')'')';
EXEC (@sql)

I am getting error like

  OLE DB provider "OraOLEDB.Oracle" for linked server "test" returned
  message "ORA-00936: missing expression".
      Msg 7321, Level 16, State 2, Line 1
      An error occurred while preparing the query "SELECT @somevalue=getvalue(545454545)" for execution against OLE DB provider
  "OraOLEDB.Oracle" for linked server "test".

Please help me on this.Thanks in advance


texts: How to check the errorlog for important messages

texts:
I would like to receive an email every day from each of "my" SQL Servers, with information from the ErrorLog.

I would like an easy way to determine if there is any important new information, that I should not overlook.

So I've tried to categorise all the messages into "Database Backup", "Reboot", "Noise" etc. All un-categorised messages I list as "Unusual event".

I've been looking for some time for a list of messages that it is safe to ignore, but found none. So here is my list.

Am I pruning the list too hard?
Are there other messages that I should ignore?
Can you point me to other methods, that work better?

here is my code: github gist


texts: VARCHAR missing index not showing up in missing index dmv or query plan suggestions

texts:
I have a table with 307200 records, with f_column being VARCHAR(30). No missing index are shown in the missing indexes dmv or suggested in the execution plan.
SELECT f_column, f_column2 FROM t_table WHERE f_column = '23BE46F3-E9A9-4526-A2F8-3F51818025B5'

The query returns 5 results, costing 9958 logical reads. With the index below, it costs 3 logical reads.
CREATE NONCLUSTERED INDEX IX_t_Table_f_column ON t_table (f_column) INCLUDE (f_column2)

What could cause the missing indexes to not show up in both the missing indexes dmv and execution plan when adding the index would benefit the query tremendously?
I initially suspected statistics, and updated all statistics for the table, but the issue still persists. For some reason, adding or altering a column on the table will cause SQLServer to come back to its senses, and the missing indexes appear in missing indexes dmv as well as the execution plan on the query's next execution.


texts: FullText Index on VARBINARY column: how to find string with dot

texts:
On a SQL Server 2012 SP3 CU8 server instance, we have PDF files that have been inserted into a table called OBJECT_FILE created as follows:

CREATE TABLE [dbo].[OBJECT_FILE](
    [FILE_ID] [int] NOT NULL,
    [FILE_FIN] [int] NOT NULL,
    [OBJ_FILE_IDX_DOCTYPE] [varchar](3) NULL,
    [FILE_TYPE] [smallint] NOT NULL,
    [FILE_TXT] [varbinary](max) NULL,
    [FILE_TXT_SIZE] [int] NULL DEFAULT ((0)),
    CONSTRAINT [PK_DM_OBJECT_FILE] PRIMARY KEY CLUSTERED (
        [FILE_ID] ASC
    )
    WITH (
        PAD_INDEX = OFF,
        STATISTICS_NORECOMPUTE = OFF,
        IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON
    ) ON [INDEXFG]
) ON [INDEXFG] TEXTIMAGE_ON [BLOBFG]

We also created a FullText Index that is built on FILE_TXT column, which is a VARBINARY(MAX), created as follows:

CREATE FULLTEXT INDEX
ON OBJECT_FILE (
    FILE_TXT TYPE COLUMN OBJ_FILE_IDX_DOCTYPE
)
KEY INDEX PK_DM_OBJECT_FILE
WITH STOPLIST=OFF;

In that table, we are willing to insert the contents of old documents. In these contents, we will find a unique identifier inserted in the past which follows a naming convention:

CORP-MMM-<three_digits_0_to_9>.<three_digits_0_to_9>

When I try to do a lookup using CONTAINS built-in function on a particular identifier that I know it exists, it doesn't give me back any results. I use following WHERE clause:

WHERE CONTAINS(FILE_TXT,'CORP-MMM-456.245')

although a lookup with following WHERE clause provides me multiple results:

WHERE CONTAINS(FILE_TXT,'CORP-MMM-456.*')

and the record in question can be found with LIKE:

WHERE FILE_TXT LIKE '%CORP-MMM-456.245%'

My question is:

Why can't I get a specific record regarding my WHERE clause and how could I make it work ?


texts:
The built-in hierarchyid is a CLR that stores paths in an efficient binary form, and provides other useful functionality.

Unfortunately, there is limit to how deep the represented paths can be, and it's ~1427 for a binary tree. I would like to increase that limit for a complex existing application that is bound to hit this limit. I don't wish to change the interface of the type. I am not confident that I could pull off changing the interface of the type without introducing subtle bugs into all the code that would have to be changed as a result.

I could (in theory) create a "binhierarchyid" CLR UDT that implements the same interface as hierarchyid, but only supports binary trees. That should get me a depth of ~7000 while still remaining inside the 900 byte limit. Not sure how big of an undertaking that would be.

Is the source of this hierarchyid CLR available somewhere, so that I could create my own based on it that supports deeper structures?


texts:
I have a SSIS package which has the following:

Data Flow Task

OLE DB Source (select * from table1 where DateCol <= ISNULL(@DateVar,DateCol ))
OLE DB Destination

DateVar variable

And I need to have a possibility to set value for DateVar or leave it empty or NULL (by default).
So, it could be two cases: DateVar have a value and DateVar have no value

Is it possible in SSIS?

If so, how can I do this?

Thank you in advance!


texts: SSIS Package produces SQLDUMPER_ERRORLOG.log

texts:
Failing intermittently but more frequently.  Package just calls a couple stored procs and exports the results to two Excel spreadsheets.

Where to go from here:

(D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Input parameters: 4 supplied
(D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     Parameter 1: 1500
(D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     Parameter 2: 0
(D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     Parameter 3: 0:0
(D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     Parameter 4: 0011B568
(D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Parsed parameters:
(D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     ProcessID = 1500
(D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     ThreadId = 0
(D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     Flags = 0x0
(D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     MiniDumpFlags = 0x0
(D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     SqlInfoPtr = 0x0011B568
(D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     DumpDir = <NULL>
(D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     ExceptionRecordPtr = 0x00000000
(D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     ContextPtr = 0x00000000
(D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     ExtraFile = <NULL>
(D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     InstanceName = <NULL>
(D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE,     ServiceName = <NULL>
(D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 11 not used
(D08:FC8) 09/05/13 14:05:57, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 15 not used
(D08:FC8) 09/05/13 14:06:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Callback type 7 not used
(D08:FC8) 09/05/13 14:06:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, MiniDump completed: c:\Program Files (x86)\Microsoft SQL Server\100\Shared\ErrorDumps\SQLDmpr0047.mdmp
(D08:FC8) 09/05/13 14:06:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Location of module 'dbghelp.dll' : 'c:\Program Files (x86)\Microsoft SQL Server\100\Shared\dbghelp.dll'
(D08:FC8) 09/05/13 14:06:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, File version of module 'c:\Program Files (x86)\Microsoft SQL Server\100\Shared\dbghelp.dll' : '6.8:4.0'
(D08:FC8) 09/05/13 14:06:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Product version of module 'c:\Program Files (x86)\Microsoft SQL Server\100\Shared\dbghelp.dll' : '6.8:4.0'
(D08:FC8) 09/05/13 14:06:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Location of module 'sqldumper.exe' : 'c:\Program Files (x86)\Microsoft SQL Server\100\Shared\SQLDUMPER.EXE'
(D08:FC8) 09/05/13 14:06:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, File version of module 'c:\Program Files (x86)\Microsoft SQL Server\100\Shared\SQLDUMPER.EXE' : '2009.100:1600.1'
(D08:FC8) 09/05/13 14:06:00, ACTION, SQLDUMPER_UNKNOWN_APP.EXE, Product version of module 'c:\Program Files (x86)\Microsoft SQL Server\100\Shared\SQLDUMPER.EXE' : '10.50:1600.1'
(D08:FC8) 09/05/13 14:06:00, ACTION,                DTExec.exe, Watson Invoke: No


texts: any downside to CREATE STATISTICS for non-unique, multi-column indexes?

texts:
If I had the following table/index definition:
create table sku
( id     serial primary key
, color  varchar(10)
, size   varchar(10)
);

create index sku_color_size on sku(color, size);

I'm wondering if there's any downside to running
create statistics sku_color_size_stats on sku(color, size);

So that the optimizer has better knowledge of any correlation between color and size.  (Let's say for the moment that I can't tell in advance that the two might be correlated, but as things work out, I end up having a lot more "blue/small" and "red/large" than other combinations.)
The only downside that I can think of is that my vacuum (whether automatic or manual) might take longer.


texts: SQL Server table with Min Max instead of ID

texts:
To save disk space and to easy the maintenaince, I changed table from (PeriodID, Value) to (MinPeriodID, MaxPeriodID, Value). All lines, which have same Value and are only 1 period apart, are being combined into 1 line.  For example, from
(1,0),(2,0),(3,0),(5,1)
it would change to (1,3,0), (5,1). What would be recommended, to query it as the same structure as before? Currently I transformed it to

SELECT P.PeriodID, T.Value
FROM Table T
JOIN Period P ON P.PeriodID BETWEEN T.MinPeriodID AND T.MaxPeriodID

,however, Period table is being scanned many times now.

EDIT 1:
This is example in dbfiddle:

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=4fec0f1473c512fa38008c028af06606

My main goal is to have PeriodID back and query it as efficiently as before. This is is a screenshot from my actual query:

Period table is scanned a lot. Is there a way to replace this "between join"? Instead of that join, I want to cross join a row with sequence, which starts at T.MinPeriodID, ends at T.MaxPeriodID and has a step size of 1.


texts: Adding another Secondary Log Shipping location

texts:
I've seen a few variations of this question, but no clear cut answers to my situation.

I have an existing log shipping setup, and want to add a secondary server.  Here is how I understand what will happen, please correct me if I am wrong.

Restore backup from 12 hours ago to secondary, using no recovery.
Add the secondary database to log shipping which creates the copy and restore job on the secondary and the copy job copies ALL
the .trn files from shared backup location to secondary server. (no
matter if they are needed or not)
The restore job is able to look at the DB on the secondary and see what the last LSN applied is, then find the appropriate log
    backup to apply, then apply all subsequent log backups to bring the
    DB into a consistent state with the primary.

last questions is where I am not sure...  Do I need to manually apply all the log backups since the full backup myself, or will log shipping work as I described above and do it automatically?


texts:
I'm new to PostgreSQL.  Although I could access a localhost database either with PGAdminIII or psql (eg sudo -u postgres psql mydb), I've not been able to connect from Dart.  I think my difficulties are with Postgres.

Previously when I've used Postgres (Windows 10) I could start the server with

pg_ctl start -l ~/pglog.txt

Now (ubuntu 16.04), this same command gives me this error:

pg_ctl: could not open PID file
    "/var/lib/postgresql/9.5/main/postmaster.pid": Permission denied*

(PGDATA is set to /var/lib/postgresql/9.5/main)
Folder main has user/group of postgres/postgres as does it's parent, postgresql.  /var/lib has owner/group root/root.

If I run the same command as the postgres user (su -c 'pg_ctl start -l ~/pglog' postgres), after entering the password, the error message is:

su: Authentication failure

(I'm sure I have the pw correct, but is there some way to check/change/confirm that...bearing in mind that I now can't get the server running.)

The bin folder is in my path. There are postmaster.pid and postgresql.conf files in the data directory.

I have changed authentication for local users in pg_hba.conf to md5 rather than peer.

I think I installed postgresql with this command:

sudo apt-get install postgresql postgresql-contrib

ref (https://help.ubuntu.com/community/PostgreSQL).

Having changed a few permissions, then, as far as I can tell, changed them back again, the server no longer runs on bootup.  That is, I can now no longer connect with either PGAdminIII or psql.  I've not been able to find out how it would have started previously.

I feel sure that I have some permissions incorrect?  Other suggested topics on StackOverflow have not been sufficient to allow me to resolve this.  One answer is the following:

  Sounds like a permission problem on the data directory. Did you check if the postgres (operating system) user has full access to /usr/local/pgsql/data and all sub-directories?*

Another answer included:

  "Permission denied" is usually a problem with the access rights on the data directory. Make sure the postgres user account has full access to /usr/local/pgsql/data

8 March:
Thanks Roman.  Using your suggested command I get

sudo: pg_ctl: command not found

But running without sudo I got the following promising message (which is weird given that that's essentially what I was using before but with no response):

pg_ctl start -l /tmp/pglog.txt
server starting

Notwithstanding this, I still don't think the server is running - this is the message from either PGAdmin3 or psql.

Evan, thank you also. The latter gives no error and returns to the prompt after perhaps 2 seconds - so it appears to be doing something.  But trying to connect with PGAdmin3 or psql still indicates that no server is listening.  Eg:

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

I've checked postgres.conf and see that the port seems to be correct.


texts: MySQL gives me:“Can't open and lock privilege tables: Table 'host' is read only”

texts:
I am facing problem restoring a MySQL database. My primary database was MySQL 5.1 and now I am trying to copy it to MySQL 5.5. The database was backed up by using Xtrabackup.
I am using Ubuntu 12.04.3 LTS on this server, MySQL version is: 5.5.32-0ubuntu0.12.04.1-log.
I have followed all the steps to restore using Xtrabackup, this created database files, which I have copied to a tmp directory.
I have modified my.cnf to point to this tmp directory. I have changed the tmp directory permissions and changed the ownership of the files to mysql user.
drwxr-xr-x 12 mysql mysql 4096 Sep 10 10:04 base

Now when I start the MySQL server I get this error:

[ERROR] Fatal error: Can't open and lock privilege tables: Table 'host' is read only

I have given a try as follows:

Even tried installing MySQL 5.1 to see if that was the issue.

tried chcon mysql_db_t to change the context but it gives me:

can't apply partial context to unlabelled file

I have used --skip-grant to get into database, but using this I can only access InnoDB tables only, MyISAM tables throw read-only error.

After --skip-grant, I have used upgrade_mysql. This throws me errors stating that many tables are read-only.

I have removed AppArmor too. And restarted too.

I have restored a different database (5.1 to 5.5) previously on Ubuntu 12.04.2 LTS without any issues.

Can some please point me in right direction? I am not sure whats wrong with permissions.


texts:
Can someone please explain me why the following is no getting cached

use AdventureWorks2014
go
DECLARE @id INT=43865
SELECT
sp.[BusinessEntityID]
, so.salesorderid
FROM sales.salesorderheader so JOIN sales.salesperson sp
ON sp.[BusinessEntityID] = so.salespersonid
WHERE so.salesorderid = @id

Every time I execute the above query with different parameter, it create an entry in plan cache

by the way, optimize for ad hoc workloads is set

Thanks


texts:
I don't like my data files in C:\Program Files... , especially with a new system that has a smallish SSD as C: which holds mainly the operating system.

So on a recent install I put my data files into D:\apps\MSSQL\DATA

Where do you put your Sql Server data files, if you are not going with the default?

I am mainly looking for good habits to make my sql server deployments easier to use.


texts:
The postgresql documentation gives an example archive_command of
archive_command = 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'  # Unix

but adds the disclaimer saying

This is an example, not a recommendation

As someone who's about to setup a postgresql database, I'm wondering what the best practice would be for handling WAL archival? Forgive me if this is already a question that's been beaten to death, but my stackexchange search-foo is failing me. There *is a few recommendations for using pgBarman. Is it still a good direction to go?
Coming from a setup where MSSQL backups were handled by IT with a full backup taken daily in the morning with hourly transaction logs. I'm wondering what would be an equivalent setup in postgresql to give the ability of point-in-time recovery for the past week or two?


texts: How can I write a query to recursively execute with varying parameter values?

texts:
Given the following query:

DECLARE @14DayWindow Datetime = DATEADD(day, -14, GETDATE())
DECLARE @90DayWindow Datetime = DATEADD(day, -90, GETDATE())

SELECT    *
FROM      (SELECT   ID, COUNT(ID)
           FROM     SomeTable
           WHERE    SomeDate > @14DayWindow
           GROUP BY ID) as FourteenDayWindow
LEFT JOIN (SELECT   ID, COUNT(ID)
           FROM     SomeTable
           WHERE    SomeDate > @90DayWindow
           GROUP BY ID) as NinetyDayWindow
ON        FourteenDayWindow.ID = NinetyDayWindow.ID

Is there a way to re-write this query such that the SELECT subquery doesn't have to be written out for every window of time?  Ideally I would be able to supply an arbitrary number of parameters with each result being joined in sequence.  Possible?

EDIT: Output should be something like

ID | FourteenDayIDCount | NinetyDayIDCount


texts: How do you delete achieve log image copies that are already backed up?

texts:
How do you delete achieve log image copies that are already backed up ?

It seems like below command doesn't work:

delete copy of archivelog all until time 'sysdate-1' backed up 1 times to disk


texts: PostgreSQL ODBC and Microsoft Access date/time in aggregate functions

texts:
I am trying to replace our MS Access 2003 databases with PostgreSQL 9.3.  The first step is to use our MS Access front end to the upsized databases in PostgreSQL.  Everything seems to work very well but I have one problem.

We have a time clock program where a user clocks in at one time and clocks out at another.  The row in the database records this start time and end time.  The data looks like this:

The query looks like this:

SELECT emphours.id, emphours.empid, emphours.department, emphours.weekending, emphours.paytype, emphours.starttime, emphours.endtime, ([endtime]-[starttime])*24 AS hours
FROM emphours;

The starttime field and the endtime field are both timestamps without timezone in PostgreSQL.  Up to this point all is good.

If I try to make an aggregate query to sum all the hours in a week for a given employee and pay type, I lose the precision in the hours.  It not only rounds it but It truncates the decimal.  Take the following query for example:

SELECT emphours.empid, emphours.department, emphours.weekending, emphours.paytype, Sum(([endtime]-[starttime])) AS hours
FROM emphours
GROUP BY emphours.empid, emphours.department, emphours.weekending, emphours.paytyp

e

It results in:

PostgreSQL gives 47 which is definitely not the sum.  Access gives 47.8702777776634.

Any ideas?


texts: Why is pg_dumpall throwing an "OID does not exist'' error?

texts:
When I run pg_dumpall I'm now getting this:

$ pg_dumpall -f may2012
pg_dump: schema with OID 7549789 does not exist
pg_dumpall: pg_dump failed on database "dealermade", exiting

How do I resolve it?

UPDATE

Here is some of the data requested by @depesz:

select relname from pg_class where relnamespace = 7549789;
   relname
--------------
 lotdata
 lotdata_pkey
(2 rows)


texts: Upgrade postgres without messing with ports?

texts:
The standard procedure for upgrading Postgres appears to require that I temporarily run both the old and the new version. This means that I have to change the port of the old version.

This seems completely unnecessary, given how I’m telling pg_upgrade exactly where to find the data directories and it could just go and read all the data directly.

Is this really the only way to upgrade (apart from dump/restore), or am I misunderstanding something? Can I not just ask the upgrader to convert all the binary data files directly, without messing with users, passwords and ports?


texts: PostgreSQL how to create a scalable location-based query

texts:
I need suggestions because I am not good enough.

I have a DB in PostgreSQL that runs on AWS (Amazon Web Services).
I have a table "user_location" where there are stored the locations of each user and now there are more than 3 million of rows.

I have a script that runs the following query often in order to see if two users are nearby:

SELECT
    UL.id                          AS id,
    UL.user_id                     AS user_id,
    ST_X(UL.location::geometry)    AS lat,
    ST_Y(UL.location::geometry)    AS lng,
    UL.datetime                    AS datetime
FROM
    public.user_location AS UL
WHERE
    UL.user_id <> 1234567890 AND
    '1890-10-31 03:00:00 +00:00' - UL.datetime <= interval '1' minute AND
    '1890-10-31 03:00:00 +00:00' >= UL.datetime AND
    ST_DWithin(UL.location, ST_GeogFromText('POINT(54 -1)'), 5000)
ORDER BY
    UL.datetime DESC;

The problem seems to be the radius, the execution time of the query grows exponentially by increasing the radius because it needs to check among more rows.

I need of a scalable solution where by increasing the radius around a given location, the execution time is almost the same. I need to "cut horizontally" the data by using before the datetime and just after the radius in the query, how can I do?

I have also the output of the EXPLAIN ANALYZE which is:

"Sort  (cost=389.72..389.73 rows=3 width=52) (actual time=136848.985..136848.985 rows=0 loops=1)"
"  Sort Key: datetime"
"  Sort Method: quicksort  Memory: 25kB"
"  ->  Bitmap Heap Scan on user_location ul  (cost=11.00..389.70 rows=3 width=52) (actual time=136848.976..136848.976 rows=0 loops=1)"
"        Recheck Cond: (location && '0101000020E6100000C182458F29494B4095E0C3DB39E3F3BF'::geography)"
"        Filter: ((user_id <> 1234567890) AND ('1890-10-31 03:00:00 +00:00'::timestamp with time zone >= datetime) AND (('1890-10-31 03:00:00 +00:00'::timestamp with time zone - datetime) <= '00:01:00'::interval minute) AND ('0101000020E6100000C182458F29494B4095E0C3DB39E3F3BF'::geography && _st_expand(location, 5000::double precision)) AND _st_dwithin(location, '0101000020E6100000C182458F29494B4095E0C3DB39E3F3BF'::geography, 5000::double precision, true))"
"        ->  Bitmap Index Scan on users_locations_gix  (cost=0.00..11.00 rows=91 width=0) (actual time=4463.249..4463.249 rows=165622 loops=1)"
"              Index Cond: (location && '0101000020E6100000C182458F29494B4095E0C3DB39E3F3BF'::geography)"
"Total runtime: 136849.591 ms"

Thanks in advance!
Cheers


texts: Change Data Capture vs trigger for logging changes

texts:
I am going to create history table which is populated by trigger (after insert, update, delete). As only 20% of the columns are going to be updated, I decided to log only the changed values - if the values are not changed, NULL value is going to be used in the history table. For example:

The history table columns will be sparse and I am going to save a lot of space versus ordinary implementation which is logging all data (this is due to my test and my bussness cases).

As I the SQL Server 2016 SP1 standard edition supports Change Data Capture I am wondering are their any pros/cons/differences between using it and trigger-based logging?

I have check few artciles (here and here) and cannot see what more Change Data Capture can give me.


texts: Remove string after second specific character occures from left

texts:
I have this type of values in table column

154646@8@486
45465@6464@654

etc.

How can i remove everything after second @ character ? I need to display

154646@8
45465@6464

I can do it only for all @ but not for second

SELECT REPLACE(LEFT('45@Tra@lala', CHARINDEX('@','45@Tra@lala')-1),'_',' ')

returns 45 but not 45@Tra

Thank you :-)


texts: How to prove the lack of implicit order in a database?

texts:
Recently I was explaining to colleagues the importance of having a column by which to sort data in a database table if it is necessary to do so, for example for chronologically-ordered data. This proved somewhat difficult because they could simply re-run their query seemingly endlessly and it would always return the same set of rows in the same order.

I have noticed this before and all I could really do is insist that they trust me and not simply assume that a database table will behave like a traditional CSV or Excel file.

For example, executing the (PostgreSQL) query

create table mytable (
    id INTEGER PRIMARY KEY,
    data TEXT
);
INSERT INTO mytable VALUES
    (0, 'a'),
    (1, 'b'),
    (2, 'c'),
    (3, 'd'),
    (4, 'e'),
    (5, 'f'),
    (6, 'g'),
    (7, 'h'),
    (8, 'i'),
    (9, 'j');

will create a table with a clear conceptual order. Selecting that same data in the simplest way would be:

SELECT * FROM mytable;

Always gives me the following results:

 id | data
----+------
  0 | a
  1 | b
  2 | c
  3 | d
  4 | e
  5 | f
  6 | g
  7 | h
  8 | i
  9 | j
(10 rows)

I can do this over and over again and it will always return to me the same data in the same order. However I know that this implicit order can be broken, I have seen it before, particularly in large datasets, where some random value will get apparently thrown up into the "wrong" place when selected. But it has occurred to me that I don't know how this happens or how to reproduce it. I find it difficult to get results on Google because the search query tends to just return general help on sorting result sets.

So, my questions are essentially these:

How can I demonstrably and concretely prove that the return order of rows from a query without an ORDER BY statement is not reliable, preferably by causing and showing a breakdown of the implicit order even when the table in question is not updated or edited?
Does it make any difference at all if the data is only inserted once en masse and then never updated again?

I would prefer a postgres-based answer since that is the one I am most familiar with but I'm more interested in the theory itself.


texts: SQL SERVER - GROUPING and Convert Unit of measurement

texts:
I have given a task, but having a hard time on solving it. Hope you can help me with this.

Here is my scenario.

I have a sales table. the products are breads, cupcakes and cookies.

Here is the condition:

If I found 12 packs of cheesecake or certain product, I will convert it to 1 box.

So If I have

row1 product: cheesecake qty: 5 packs
row2 product: cheesecake qty: 5 packs
row3 product: cheesecake qty: 2 packs

I would get:

row 1 product: cheesecake qty: 1 box

As you can see form 3 rows, it becomes one row.

Would you help me how to do this?

Here is my code:

select
        SoldtoPtNm,
        MatCode,
        MatDesc,
        SU,
        SUM(GrossQty) as total,
        datepart(wk, BillDt) as week,
        BillDt
from tbl_Sales_test

group by
        SoldtoPtNm,
        MatCode,
        MatDesc,
        SU,
        GrossQty,
        BillDt

Right now what I get, is the quantity of the same uom. Please see screenshot for the result set.

Any hint and suggestions would be appreciated. Thanks!


texts: Moving TDE database to a new database but having problems with the cert

texts:
This is my first time working with TDE so hopefully I haven't done anything horribly wrong.

I'm trying to move my TDE database from one server to another.  I have created a master key on the master database of the new server.  I then restored my certificate from backup using the following command:

CREATE CERTIFICATE MyServerCert
FROM FILE = 'CertBackupFile'

When I created it originally I did not have a private encryption key so I just encrypted it with a password.

Now when I try to restore the backup of my TDE I'm getting the following error

Msg 15507, Level 16, State 1, Line 2
A key required by this operation appears to be corrupted.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

I've tried looking at the create statement but don't see where I can put in my original password.  Also when I look at sys.certificates on my original server the pvt_key_encryption_type_desc = ENCRYPTED_BY_MASTER_KEY but on the new one it says NO_PRIVATE_KEY.  So I assume I'm doing something wrong with the create.

Can anyone point me in the right direction to fix this?


texts: Check if database has changed to determine need to perform backup

texts:
I want to check SQL server database is modified or not. Reason to check database is modified is for backup(sql database backup) purpose. So I will be doing a full backup first and if any data inserted, modified or any structural or any changes done then I have to identify that there is some changes in databases and backup the database.

The checking for changes in database may be done based on frequency(daily, hourly etc). Modified date of the mdf or ldf file can't be used to check database is modified or not. Differential backup only backups changes since last backup but it's different and it does a backup even if there is no changes since last backup but size will be very less(in kb which you can test easily) .

In windows NTFS file system there exists USN journal for each file by using it we can check file is modified or not and then we can do backup the file if modified. Which is also implemented in most of the backup software.

So how can I check whether any changes done to database after my last backup, so that i can do backup if any chnages are detected?


texts:
Would somebody know what is that error?

This is during the impdp command and there is nothing imported.
The full error is:

Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
ORA-39006: internal error
ORA-39065: unexpected master process exception in DISPATCH
ORA-00942: table or view does not exist
ORA-06512: at "SYS.KUPU$UTILITIES_INT", line 1579
ORA-06512: at "SYS.KUPM$MCP", line 2710

ORA-39097: Data Pump job encountered unexpected error -942


texts: BCP neither gives results nor outputs anything when using valid statements but it does throw errors when passing invalid parameters

texts:
I have to use bcp command-line tool to export data from an SQL Server database to a file in a Red Hat server.
I am (apparently) using valid statements but bcp is not producing any kind of output/results.
However, when I execute statements with missing or invalid parameters it displays the respective error.
I am looking for the reason of this issue (e.g. defective installation, bad usage of bcp, lack of permissions or any other known conflict) and how to fix it.

bcp statement:

bcp fully_qualified_table_name out ./data.txt -c -S server -U user -P password

bcp usage:

usage: /opt/microsoft/bin/bcp {dbtable | query} {in | out | queryout | format} datafile
  [-m maxerrors]            [-f formatfile]          [-e errfile]
  [-F firstrow]             [-L lastrow]             [-b batchsize]
  [-n native type]          [-c character type]      [-w wide character type]
  [-N keep non-text native] [-q quoted identifier]
  [-t field terminator]     [-r row terminator]
  [-a packetsize]           [-K application intent]
  [-S server name or DSN if -D provided]             [-D treat -S as DSN]
  [-U username]             [-P password]
  [-T trusted connection]   [-v version]             [-R regional enable]
  [-k keep null values]     [-E keep identity values]
  [-h "load hints"]         [-d database name]

bcp version:

BCP - Bulk Copy Program for Microsoft SQL Server.
Copyright (C) Microsoft Corporation. All Rights Reserved.
Version: 11.0.2270.0

SQL Server version (SELECT @@VERSION):

Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
   May 14 2014 18:34:29
   Copyright (c) Microsoft Corporation
   Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

Distribution:

Red Hat Enterprise Linux 6.7 (KornShell).

Invalid statements with respective error message (examples).

bcp THAT_TUB_ACE.oh_nerd.table_name out ./data.txt -c -S sr._bear -U you_sr. -P pass_sword

    SQLState = S1T00, NativeError = 0
    Error = [unixODBC][Microsoft][ODBC Driver 11 for SQL Server]Login timeout expired
    SQLState = 08001, NativeError = 11001
    Error = [unixODBC][Microsoft][ODBC Driver 11 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
    SQLState = 08001, NativeError = 11001
    Error = [unixODBC][Microsoft][ODBC Driver 11 for SQL Server]TCP Provider: Error code 0x2AF9

...

bcp fully_qualified_table_name ./data.txt -c -S valid_server -U valid_user -P bad_word

    bcp fully_qualified_table_name out ./data.txt -c -S valid_server -U valid_user -P bad_word
    SQLState = 28000, NativeError = 18456
    Error = [unixODBC][Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Login failed for user 'valid_user'.

SUMMARY.

The objective is to generate a datafile using the following syntax (or similar):

bcp fully_qualified_table_name out ./data.txt -c -S server -U user -P password

The facts are:

When running a valid bcp statement there's nothing in the window at all (no output) and no datafile is created.
I cannot use option -T (trusted connection using integrated security) for bcp so I have to specify the server, user and password.
Tried queryout option on a very simple small table already but still no luck.
Credentials are valid, I successfully tested them using sqlcmd like the following: sqlcmd -S server -U user -P password -Q 'SELECT * FROM really_small_table'.
The bcp statements under "Invalid statements with respective error message (examples)" section of this question are just examples of invalid statements to show that bcp actually does something but giving the expected results.


texts: How to partition an Oracle table with 65 million rows?

texts:
The table that needs partitioning contains a list of Components and their Values.

So one table is tblComponents.  Another is tblFields.  Then this table, tblValues, contains the values of the Components/Fields, so it has a Component foreign key as well as a Field foreign key.  Right now this tblValues table contains 68,000,000 values.  Components table contains ~80,000 rows.  Each component has around 500-800 fields of inventory.

I was thinking about creating a partition by hash on the field id.  As the user will query, they will pick something like.. ."Show me the components where field id 5 = '03530'"  Something along those lines.

Sample queries are below that are common:

select * from tblComponents comp inner join tblValues v on comp.comp_id = v.comp_id
where v.field_id = 55 and dbms_lob.substr(v.value, 2000, 1) like '%brick%'

Also, the values are currently stored as CLOB's and can have numeric values or string values.

Any tips regarding how you would partition or index this would be great, especially for optimizing querying.


texts: Extract time portion of Datetime2(7) in hhmmssfff format

texts:
I need to extract the time portion of a datetime2(7) column in hhmmssfff format and I am doing it like this:

DECLARE @mdate AS DATETIME2(7)

SET @mdate = '2012-03-15 10:13:27.5437431'

SELECT LEFT(REPLACE(REPLACE(CONVERT(VARCHAR ,@mdate ,114),':',''),'.',''),9)

Is there a better approach than doing this ugly replaces/convert/left?

I need this to join to a DimTime dimension whose key is in hhmmssfff format.


texts:
I have executed a stored procedure in SQL Server but it took a very long time to complete.

Any SQL queries I can run to get estimated completion time?


texts: Modeling a Recursive Association for Linking an Entity Type to Itself

texts:
I am designing a simple database as follows:

The database should contains an evolving set of standards;
each standard has a library of controls;
A single control in each standard may map to one or more controls in another standard;
A control may only belong to one standard. However, a standard may have many controls;
A control must belong to a standard.

A rough sketch of the entity-relationship diagram (ERD) I have come up with is as follows:

Is this a correct conceptual schema for such a data model?


texts: Is it possible to make one column readonly?

texts:
I'm curious if it is possible to create a table with a column that can never be changed, but the other columns of the table can.

For instance I could imagine a CreatedByUser column that should never be changed.

Is there a built-in functionality in SQL Server for this, or is it only possible via triggers or something else?


texts:
I already search in here but i can't found an awnser , i have this code :

CREATE TABLE Aquario(
    ANome varchar(64) PRIMARY KEY,
    Localização varchar(8),
    Capacidade integer(15));

CREATE TABLE Habitat(
    HNome varchar(100) PRIMARY KEY,
    Iluminamento integer(10),
    Salinidade integer(2),
    pH integer(2),
    Dureza integer(3),
    Oxigenação integer(3),
    Temperatura integer(2));

CREATE TABLE contem(
    HNome varchar(64),
    ANome varchar(64),
    Percentagem_de_adequação integer(2),
    PRIMARY KEY (ANome),
    FOREIGN KEY(ANome) REFERENCES Aquario(ANome),
    FOREIGN KEY(HNome) REFERENCES Habitat(HNome));

INSERT INTO contem (Percentagem_de_adequação) VALUES (20);

INSERT INTO Aquario (ANome, Localização, Capacidade) VALUES ('Os Peixes azuis','Este',20);

INSERT INTO  Habitat (HNome, Iluminamento, Salinidade,pH,Dureza,Oxigenação,Temperatura) VALUES ('Os peixes' ,20  ,1 ,5 ,213 ,52,2);

This code don't let  me do the table " contem " why ?


texts: How to check innodb history list length?

texts: The output for show engine innodb status has the history list length as a number, but that output is hard to parse, and there's just too much data when all you want is a single value. Is the value available somewhere else that can be fetched as a row?


texts:
I have 3 servers with RHEL 6 OS and Percona server running in one of the servers in the same data centre, while the other 2 servers lie idle.

Now I need to implement some kind of on-site redundancy (high-availability) for my databases.

What is the best option we got here? I want to go for Percona cluster but I don't want to since there aren't people who cannot manage the same.

Any other options?

Master and two slaves?
Two Masters (Active-Passive) and One slave?

Need inputs here.


texts:
I have an existing table named “Orders” in our system. OrderID is the primary key in this table – it is a clustered index. I have a new table designed  as shown below for “OrderCompanyDetails”. It has a 1-to-1  relationship with Orders table. In the new table OrderID is kept as the clustered primary key.

Data gets inserted into the new table only when the Order is approved. So the OrderID getting inserted into the new table may not be in sequence. The OrderID 10 may get inserted before OrderID 5, depending on which order is approved first.

Having the clustered index on OrderID  helps my queries. But the clustered index is on a column which gets data in a random sequence. Is it a bad index design? If yes, should I add a new meaningless identity column with name OrderCompanyDetailID and make that as clustered index?

CREATE TABLE [dbo].[Orders]
(
    [OrderID] [int] IDENTITY(1,1) NOT NULL,
    [OrderType] [char](3) NOT NULL,
    [StatusCD] [char](10) NOT NULL,
    [CreatedOnDate] [datetime] NOT NULL CONSTRAINT [DF__Orders__CreatedOn]  DEFAULT (getdate()),
    CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED
    (
        [OrderID] ASC
    )
)

CREATE TABLE [dbo].[OrderCompanyDetails](
    [OrderID] [int] NOT NULL,
    [POCompanyCD] [char](4) NULL,
    [VendorNo] [varchar](9) NULL,
    [CreatedOnDate] [datetime] NOT NULL CONSTRAINT [DF_OrderCompanyDetails_CreatedOn]  DEFAULT (getdate()),
    CONSTRAINT [PK_OrderCompanyDetails] PRIMARY KEY CLUSTERED
    (
        [OrderID] ASC
    )
)

UPDATE

Recently read article Ever-increasing clustering key – the Clustered Index Debate……….again!. There is a comment in that

  Remember – narrow, static, unique, ever increasing – and usually a surrogate key instead of a natural key.


texts: MySql - Ranking so it only needs to be done once

texts:
By reading around, this seems to be a reasonable way of ranking people.

The table:

UserId (int primary key)
Name (varchar(100))
Score1 (int highest scores rank 1)
Score2 (int, if Score1s are equal, the person with the highest Score2 trumps all other equal Score1s)
Rank (int this is what I want to fill)

The Rank will only change about once a day. However, the page will likely be viewed maybe 1000 times a day. So instead of getting the database to calculate the rank for each of the 1000 visits, I thought it would be best to just fill the 'Rank' column once.

How can I include an UPDATE to fill the Rank column, using my chosen way to rank people. (Note if two people are ranked 5, then the next rank is 7.)

SELECT UserId, Name, Score, Rank FROM
    (SELECT UserId, Name, Score1, Score2
        @curRank := IF(@prevScore1 = Score1 && @prevScore2 = Score2, @curRank, @incRank) AS Rank,
        @incRank := @incRank + 1,
        @prevScore1 := Score1,
        @prevScore2 := Score2
       FROM players p,
       (SELECT @curRank :=0, @prevScore1 := NULL, @prevScore2 := NULL, @incRank := 1) r
       ORDER BY Score1 ?????
    )
s

The Questions are:

How do I UPDATE Rank in the table within this SQL statement?
What do I do in the ????? ORDER BY statement to make sure it orders by Score1 first then when these are equal it orders by Score2 second?