texts: SQL Server 2017 Enterprise, DB has suspect_pages, GAM and SGAM NOT ALLOCATED - am I OK?

texts:
I realize I may get some "horror!" reactions, but bear with me...

There is but one suspect_pages row, and I plugged the info into DBCC PAGE (output shown below).  When I saw that both GAM and SGAM show "NOT ALLOCATED", my theory became, "I need not care about it..."

We had the DB up to 5 TB with masses of data to verify.  At least 1TB was TRUNCATEd at the exact time when the suspect_pages row was created - all from ONE table.

Having had suspect_pages rows appear during a similar exercise on the same DB several months prior, where DBCC PAGE showed that none of the pages cited existed in the database, we moved on, deleting the suspect_pages rows.

The IndexID is -1, the ObjectID is 0.  To me, this means, "there's nothing to see here..."

In the opinion of the experts here, does it look as though it is safe to delete the current suspect_pages row?  Is there a DBCC CHECKDB REPAIR% option I should use?

With thanks, and hoping for at least minimal "nice abuse"...

Command:

DBCC TRACEON (3604);
DBCC PAGE ('DB_US', 1, 731783305, 3) WITH TABLERESULTS;
DBCC TRACEOFF (3604);
GO

Produces an error:

(43 rows affected)
Msg 2514, Level 16, State 8, Line 6
A DBCC PAGE error has occurred: Invalid page metadata - dump style 3 not possible.

DBCC PAGE OUTPUT - using output level ZERO and no WITH...:

PAGE: (1:456793585)

BUFFER:

BUF @0x000002F2DA54D680

bpage = 0x00000246815A2000          bhash = 0x0000000000000000          bpageno = (1:731783305)
bdbid = 6                           breferences = 0                     bcputicks = 0
bsampleCount = 0                    bUse1 = 56821                       bstat = 0x809
blog = 0x15ab215a                   bnext = 0x0000000000000000          bDirtyContext = 0x0000000000000000
bstat2 = 0x0

PAGE HEADER:

Page @0x00000246815A2000

m_pageId = (1:456793585)            m_headerVersion = 1                 m_type = 2
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x2200
m_objId (AllocUnitId.idObj) = 235350m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057609461825536                                Metadata: PartitionId = 0
Metadata: IndexId = -1              Metadata: ObjectId = 0              m_prevPage = (1:456793584)
m_nextPage = (1:456793586)          pminlen = 29                        m_slotCnt = 168
m_freeCnt = 32                      m_freeData = 7824                   m_reservedCnt = 0
m_lsn = (722027:450682:278)         m_xactReserved = 0                  m_xdesId = (0:0)
m_ghostRecCnt = 0                   m_tornBits = -1231952543            DB Frag ID = 1

Allocation Status

GAM (1:731572992) = NOT ALLOCATED   SGAM (1:731572993) = NOT ALLOCATED  PFS (1:731777976) = 0x0   0_PCT_FULL
DIFF (1:731572998) = CHANGED        ML (1:731572999) = NOT MIN_LOGGED


texts: How can I configure Ola Hallengren IndexOptimize script to run against a subset of tables

texts:
I'm using Ola Hallengren IndexOptimize script against a SQL 2016 database that is 7 TB in size, with over 300,000 tables.  I only have a 6 hour window each night to manage indexes.    I'm using the timelimit parameter to stop the job after 6 hours.

The problem is, every night the index job starts at the beginning of the indexes alphabetically, and only gets through about the same 4,000 tables.

What can I do to get the index job to cover all the indexes in the database?   Perhaps by creating multiple jobs, one for each night of the week that do a subset of indexes?   Or is there a way to have the job start back up the next day where it previously left off?

All the tables are in the same database schema.  This is a vendor-supplied database, I'm unable to make changes to the database schema.

Thanks in advance for any guidance.

My current job  steps are as follows:

EXECUTE [dbo].[IndexOptimize]
@Databases = 'USER_DATABASES',
@FragmentationLow = NULL,
@FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
@FragmentationLevel1 = 10,
@FragmentationLevel2 = 40,
@UpdateStatistics = 'ALL',
@OnlyModifiedStatistics = 'Y',
@PartitionLevel = 'N',
@MaxDOP = 0,
@SortInTempdb = 'Y',
@TimeLimit = 21600,
@LogToTable = 'Y'


texts: Finding reason behind high write IOPS on MySQL (RDS) server

texts:
I am having performance issues with an AWS RDS instance (running MySQL 5.6).

This is the backend db to an XMPP server called Tigase. According to Tigase Inc. this type of db instance should be able to handle at least an order of magnitude more traffic than it currently does. It's very rare that the db is the bottleneck. Usually the Tigase server becomes a bottleneck before the db does.

When looking at our monitoring data in the AWS Management Console we see that it's mainly CPU and Write IOPS that is the issue. Read IOPS is negligible.

Since I haven't written Tigase it's hard for me to know the reason behind the high load. My question is therefore: how should I go about to find the queries and/or config settings that are the root cause behind this? What tools do you recommend that I use? I have fairly extensive DBA experience but I am a newbie when it comes to MySQL.

Below is a screenshot from MySQL Workbench's performance dashboard.

Thanks!


texts: ALTER innoDB table from row to page compression

texts:
My tables have been created with InnoDB row compression (ENGINE=InnoDB ROW_FORMAT=COMPRESSED). Not I am changing them to page compression. According to the official documentation of MariaDB, enabling page compression affect the newly created tables only.
Thus, I create a replica table and use INSERT INTO SELECT.
I wonder if it is possible to ALTER a table to change the compression type (from row to page)?
We can ALTER an ordinary table to use page compression as
ALTER TABLE t1 PAGE_COMPRESSED=1;

My question is about a safe way to remove ROW_FORMAT=COMPRESSED and add PAGE_COMPRESSED=1.


texts: Postgres 9.2 - Create View Different Column Names

texts:
I want to create a view of a table with a column renamed.  I do not want to effect the source table, just the view.

I need to rename the column addr:housename to addr_housename.  We have a 3rd party piece of software that does not like ':' in field names.  We cannot just rename the field due to other software we use.

Can someone please provide an example on how to resolve my issue?

Thank you very much.


texts: Seems SQL Server couldn't optimize optional parameters

texts:
I am trying to optimize a query which has many parameters via Profiler. I tested below queries:

declare @location nvarchar(200) = null;
select count(*) from Places where @location is null or Location = @location

cpu:1249 duration:64 reads:456760

declare @location nvarchar(200) = null;
select count(*) from Places

cpu:315 duration:20 reads:15001

As you can see, both queries are identical because the @location parameter is optional and it is null, but performance metrics are showing very different results. It seems SQL Server couldn't understand both queries are exactly the same.

Why SQL Server can not optimize such a simple query?


texts: Introduce or not introduce SSIS to an ETL project

texts:
Given is an ETL process that works fine using traditional SQL.

The process is well optimized making use of ALTER TABLE permissions and performing partition switching, index creation, partition truncation and statistics updates.

Furhtermore a Scheduler is available that is up to the job.

What benefits would be gained from introducing SSIS to the project? Also would I get rid of needing ALTER TABLE permissions because SSIS interfaces at a lower layer to the database?

If additionally given that the project is small involving only one really large fact table and creating a data warehouse accessed only via SQL (no Analysis Services), does this affect the answer to the question?


texts: How to send big email via procedure sp_send_dbmail?

texts:
I need to send emails via MS SQL server. But the problem is that I have too much information to send. Let's say 50-200Kb per one mail. This is HTML formatted reports.

For sending, I run sp_send_dbmail with the following parameters:

EXEC msdb.dbo.sp_send_dbmail
        @profile_name = 'profile',
        @recipients = 'mail@server.com',
        @subject = 'subject',
        @body = 'body',
        @body_format = 'HTML', -- or TEXT
        @query = 'select * from ##t',
        @attach_query_result_as_file = 1,
        @query_attachment_filename = '1.html';

I've used the following options:

Prepared HTML in @body NVARCHAR(MAX) variable. But it truncated to 8000 symbols.
Prepared HTML inside ##t:

CREATE TABLE ##t (ID INT IDENTITY(1,1), email NVARCHAR(MAX))

and then used @query = 'select * from ##t' in sp_send_dbmail procedure.

I can send a lot of rows, but all of them will be truncated to 256 symbols.

Even If I use @query_result_width = 32767 (what is the maximum limit) - I receive truncated columns.

I wish I could get round this "256-symbols-on-column" limitation. This will be the simplest method of sending emails for me.
Prepared HTML inside ##t and used SQL Job with CMD command inside:

bcp "SELECT html FROM ##t ORDER BY Id" queryout c:\temp\report.html -c -T

This is working approach, but not so simple as I want because now I should create some folder and bother about cleaning files inside it.
I can use PowerShell script which will be called by Windows Task Scheduler
This is not so simple too.

What can you suggest in this situation?
How can I send emails with minimum effort of development and security rights changing? I need a simple solution which could be installed on any Windows server.

Thank you in advance.


texts: Database & Structure for SaaS reporting software

texts:
I'm building a SaaS application to allow users to upload data and perform reporting and analytics on it. The reporting and analytics would be quite simple like grouping and aggregating. This has presented some interesting problems.

I would expect to have multiple users. Initially a few hundred, but scaling upwards to thousands (if lucky of course).
Each user uploads multiple tables of data to analyze. I do not know the schema before hand and do not have control over number of rows, columns, or tables. This would all have to be created dynamically.
I'd expect that this should be horizontally scalable as well?
Each time a user registers on our site they'd be able to upload a CSV file along with a structure that they'd like to analyse.

I am not very experienced in databases, but these are the options I've considered:

Create a MySQL/PostgreSQL Database and create a new table each time someone wants a table. However I am concerned about the scalability of this approach. Plus I'd have to change schema each time.
Use a NoSQL database which I'm not too familiar with but am willing to learn. Don't know how I'd keep multiple different tables.


texts: PostgreSQL: Extending database storage when disk is full

texts:
I have a database that takes about 60GB in my disk storage. And it doesn't alow to insert into database anymore.
I have read the documentation about tablespaces link But It's like create space for a new table, whereas I want to continue insert into exist table. Could somebody help me out?

In addition, PostgreSQL database maximum is 64TB, so we have to connect many HDDs to achieve 64TB, then the solution could be apply to this situation (many connected HDDs or shared storage, I'm not sure how to say)

Thanks in advance!

PS: I'm working with postgreSQL 9.2 on ubuntu 12.04 LTS


texts: Can I Create a Database and Tables in One SQL Command From PHP?

texts:
I tried separating the CREATE DATABASE and CREATE TABLES commands into different mysqli_query statements. The Database gets created but I get an error with:

$create_tables = "CREATE TABLE T2T_$grouporg.Memebers LIKE T2T_Main.Members;
     CREATE TABLE T2T_$grouporg.Events LIKE T2T_Main.Events;
     CREATE TABLE T2T_$grouporg.Taskers LIKE T2T_Main.Taskers";
     $creat_tables = mysqli_query($dbconnect,$create_tables) or
     die("Create Table Error: " . mysqli_error($dbconnect) .
     "

" . $create_tables);


texts: SQL Server: modify data file size and shrink

texts:
When I try to modify the Database File Size, it shows this error.

  Specified size is less than or equal to current size

I tried this query:

Alter database [dbname] modify file
(
 name=N'data-file-name', size=(newsize=90MB)
);

Now it works when I tried to shrink it, I mean change it from 100MB to 90MB, using this query that I saw on the internet:

DBCC ShrinkFile(data_file_name,size to)

But why?

Why can't I modify the file size to a smaller size and why shrinking it works?

What happens when I shrink a file and when I modify the file size?


texts: Postgres function return table with extra column

texts:
Newbie to Postgres here..

I have a Postgres / plpgsql function that will return a table. I want to return everything from a query plus a logical value that I create and return along with it.

However, after searching around I can't seem to figure out how I can return this data along with a logical value that I generate on the fly within the query?

CREATE OR REPLACE FUNCTION public.sp_user_get_credentials_by_email(email_address character varying)
RETURNS TABLE(credential_id integer, user_id integer, password_hash character varying, password_salt character varying, created_at timestamp without time zone, last_updated_at timestamp without time zone, logical_value_return SMALLINT)
 LANGUAGE plpgsql
AS $function$

DECLARE
sproc_logical_value_return SMALLINT;

BEGIN

-- also want to return logical_value_return along with the query below
-- ex: sproc_logical_value_return = (2)::INT2; how do I add this as a column logical_value_return along with the query below?

  RETURN QUERY
    SELECT
      myapp_users_credentials.credential_id,
      myapp_users_credentials.user_id,
      myapp_users_credentials.password_hash,
      myapp_users_credentials.password_salt,
      myapp_users_credentials.created_at,
      myapp_users_credentials.last_updated_at
    FROM
      myapp_users_credentials
    JOIN myapp_contacts_assoc ON
      myapp_contacts_assoc.user_id = myapp_users_credentials.user_id AND
      myapp_users_credentials.expired_at IS NULL
    JOIN myapp_contacts ON
      myapp_contacts.contact_id = myapp_contacts_assoc.contact_id AND
      myapp_users_credentials.expired_at IS NULL
    WHERE
      myapp_contacts.value = $1 AND
      myapp_contacts.type = 1 AND
      myapp_contacts.is_primary = 1
    LIMIT 1;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'Credentials not found';
  END IF;

END
$function$


texts: SQL Server 2008 R2 sys.dm_exec_sql_text question

texts:
If i run the following query on our production server:

SELECT DISTINCT TOP 10
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time / 100000 AS MaxElapsedTime,
ISNULL(s.total_elapsed_time / s.execution_count, 0) / 100000 AS AvgElapsedTime,
s.creation_time AS LogCreatedOn,
ISNULL(s.execution_count / DATEDIFF(s, s.creation_time, GETDATE()), 0) AS FrequencyPerSec
,s.plan_handle, s.plan_generation_num
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY
s.max_elapsed_time / 100000 DESC

My top queries are "create procedure ......." does this mean the sql server recompiles this stored procedure more then once?


texts: Determine a set of id's that exist in a set of tables: options needed to speed up or parallelize the algorithm

texts:
I am trying to determine a set of id's that that exist in a set of tables, but the size of the tables are > 10 million records, the database is denormalized, normalization is not an option and the query either takes to long to complete or never completes.

I'm not sure how to partition this sort of job so that it can be run in parallel so that it takes less time and so if a step/subsection fails it can continue from where it left off.

A simplified example would be: find the customer in 3 tables whose Id matches in all tables.

The initial idea was to start looking at how sql engine inner LOOP JOIN actually works to try get ideas on how to partition it so that parts could be run in parallel. But without knowing anything about the various data science algorithms, I suspect there is some standard way to do this.

The rather cryptic original requirement was:

Compare party_id column in the three tables
to identify the customer available in three table i.e. it is AND operation between three.

SAMPLE1.PARTY_ID AND SAMPLE2.PARTY_ID AND SAMPLE3.PARTY_ID

If the operation is OR then pick all the customers available in the three tables.

SAMPLE1.PARTY_ID OR SAMPLE2.PARTY_ID OR SAMPLE3.PARTY_ID

AND / OR are used between tables then performed the comparison as required.

SAMPLE1.PARTY_ID AND SAMPLE2.PARTY_ID OR SAMPLE3.PARTY_ID

I set up some 4 test tables each with with this definition

CREATE TABLE `TABLE1` (
  `CREATED` datetime DEFAULT NULL,
  `PARTY_ID` varchar(45) NOT NULL,
  `GROUP_ID` varchar(45) NOT NULL,
  `SEQUENCE_ID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`SEQUENCE_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=978536 DEFAULT CHARSET=latin1;

Then added 1,000,000 records to each just random numbers in a range that should result in joins.

I used the following test query

SELECT `TABLE1`.`PARTY_ID` AS `pi1`, `TABLE2`.`PARTY_ID` AS `pi2`, `TABLE3`.`PARTY_ID` AS `pi3`, `TABLE4`.`PARTY_ID` AS `pi4` FROM `devt1`.`TABLE2` AS `TABLE2`, `devt1`.`TABLE1` AS `TABLE1`, `devt1`.`TABLE3` AS `TABLE3`, `devt1`.`TABLE4` AS `TABLE4` WHERE `TABLE2`.`PARTY_ID` = `TABLE1`.`PARTY_ID` AND `TABLE3`.`PARTY_ID` = `TABLE2`.`PARTY_ID` AND `TABLE4`.`PARTY_ID` = `TABLE3`.`PARTY_ID`

It's supposed to complete in under 10 min and for table sizes 10x larger.
My test query still hasn't completed and it has been running for 15 min


texts: Hundreds of millions of rows: Index by composite BIGINT, VARCHAR, or CHAR of HASH?

texts:
Given the following:

Target platform: MySQL 5.7, using InnoDB.

Scenario: Storing hundreds of millions of email addresses (plus some properties not used in queries). All queries will be done by knowing the email address beforehand.

Proposed solution:

SHA256 the email address.
Shard "the email table" by taking the first 3 bytes of the ASCII SHA256, creating 4096 tables (from 0x000 to 0xFFF) that will act as buckets for the email addresses. This tries to avoid having one single huge table.

Question: Which of the following would be a good PK to use inside each one of those 4096 buckets in terms of performance (indexing as being more important than querying)? Use of disk space might not be that important upfront (unless there are some heavy arguments to take this into account, which I'm open to know and discuss, of course).

VARCHAR(255) of the email address? This is of course the simplest.
CHAR(64) of the ASCII SHA256 hash of the email address? Long shot: I'm considering that indexing and comparing fixed length strings (CHAR) is faster than variable length strings (VARCHAR).
Split the SHA256 into 8 64bit integers, then create a composite PK of 8 BIGINT columns and index/query by those 8 BIGINT columns instead of using a VARCHAR/CHAR? Crazy idea: Perhaps using only 64bit integers for indexing and querying can provide a noticeable improvement in index and query performance (and also perhaps in disk access/storage). Although this is a composite PK of 8 BIGINT columns :\

Thanks in advance,


texts:
All,
I work at a university and having discussions with our third party ERP vendor, we found out that the vendor's table for biographical information stores all the data from different constituents. The same table stores information about candidates, students, faculty, staff, alumni, etc.

This presents a challenge to us since we will have to create views to achieve row-level security for our end users and delegate access to the department based on their need. For instance, employees in the admissions office should not be able to query the biographical information of other employees, etc

Even in the vendor's application, users in the admissions office are able to access other employee's biographical information since the software runs queries on the same table. That seems to be a huge security issues in my view. The software has been created more than 10 years ago and little progress has been made towards fixing that.

As a general rule of thumb, what are the best practices for designing a biographical table? Should there be different tables for each constituent(candidate, employee, faculty) or one table for all of them?

Thank you.


texts:
Ok, first of all, I have really limited knowledge about databases.

Problem that I have is next: I inserted few rows with data that contains Dutch characters, such as names of different countries. Some countries have at the end of their names Dutch language special characters ë, or like Ukraine have two dots on i (Servië, Oekraïne, Tunesië, Somalië, etc...).
These characters in database looks like this: ë -> A≈ and ï -> AZ.

Of course I would like that these Dutch characters shows in database.


texts: ORA-00838 error after increasing MEMORY_TARGET

texts:
I've increased MEMORY_TARGET in pfile and then create spfile. I'm getting the same problem as before:

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 3084M

The memory_target in pfile is 3084M.

I've already increased tmpfs on /dev/shm to 5Gb. And no idea what to do next?


texts: postgresql, duplicate database which is a replication of another

texts:
I have original DB-original
and have a streaming-replication DB-replica

I want a writable copy of DB-replica and did

createdb -T DB-replica DB-copy-of-replica

  createdb: database creation failed: ERROR:  cannot execute CREATE
  DATABASE in a read-only transaction

How do I copy the DB?


texts: Postgres configuration, incoming connections

texts:
Hello I'm trying to figure out the best way to allow connection from certain ips/hosts to my db instance.

I've got an api(s) pointing to the database and right now we do something like this (pg_hba.conf) :

host    prod_db   prod_user         xxx.xxx.77.xx/32        password

And since the api are getting deployed in cloud and with new instances, having new ips, we kind of change this config every time we deploy, which is not very good, cause we have to restart the db server and therefore have to put the whole eco system in the maintenance mode.

I'm just reading this :

http://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html

This bit looks appealing to me:

# Allow any user from hosts in the example.com domain to connect to
# any database if the user's password is correctly supplied.
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             .example.com            md5

So this means any incoming connection from api.example.com or api1.example.com or any from this domain will be accepted if the password is correct.

But my question is how does the db server know that it's coming from the something.example.com.

Does this mean that the server is doing some kind of reverse dns lookup before allowing connection?

Does this assume that example address 123.456.22.11 which points to api.example.com, by point I mean A record will be allowed connection from or how this works?

What do I need to do to make this work?

My ultimate goal is not to have to edit the pg_hba.conf every time there is a new api server instance booted up to add that new ip. And not having to restart postgres db instance


texts: Avoiding foreign key repetition on related tables in a relational DB

texts:
I have a simple DB schema describing certain objects that belong to a historical time period of a country and (optionally) a subdivision of that country at that period. The contrived UML class diagram is given below:

The relations are as follows:

a country can have one or more historic time periods (defined by a year interval).
object is an entity that is assigned to a historical period and may optionally be assigned to a region.
region is an optional subdivision of a period (if a country is divided into smaller pieces, i.e. Germany into West/East Germany in 1949-1989).

This schema is flawed by having a data redundancy problem: when an object is assigned to a region, it has two foreign keys pointing to region and period. The pointed region row also has a foreign key that has to be pointing to the same period. Thus, if region is moved to another period, keys have to be replaced in multiple places.

I've thought of two undesirable solutions:

by removing the region—period relationship but then region loses its logical connection to a period (by object—region relation, a region may belong to many periods, which is not desirable);
removing object—period relationship but because object's association to region is optional, object would lose it's connection to country.

Do you have any architectural ideas that would solve the data redundancy problem? I'm using Django ORM for defining my models.


texts: How to create an index to be picked by query

texts:
I have created multiple indexes one after another, but my query doesn't want to pick them :(

CREATE INDEX x
  ON gan.paid (project_id, country_iso_code, source, date(created_at));
CREATE INDEX x
  ON gan.paid (project_id, country_iso_code, text(source), date(created_at));
CREATE INDEX x
  ON gan.paid (project_id, text(country_iso_code), text(source), date(created_at));

Query Plan:

WindowAgg  (cost=46024.07..52771.64 rows=24252 width=63) (actual time=3038.104..3072.217 rows=99983 loops=1)
 ->  GroupAggregate  (cost=46024.07..52468.49 rows=24252 width=63) (actual time=2284.136..2982.067 rows=99983 loops=1)
    Group Key: key
    ->  Sort  (cost=46024.07..46535.85 rows=204709 width=63) (actual time=2284.109..2805.815 rows=198575 loops=1)
          Sort Key: key
          Sort Method: external merge  Disk: 13960kB
          ->  Seq Scan on paid gap  (cost=0.00..20265.45 rows=204709 width=63) (actual time=0.024..215.813 rows=198575 loops=1)
                Filter: ((project_id = 1) AND ((country_iso_code)::text = 'gb'::text) AND ((source)::text = 'website'::text) AND (created_at <= (now())::date) AND (created_at >= ((now())::date - 100)))
                Rows Removed by Filter: 214895
Planning time: 0.233 ms
Execution time: 3082.612 ms

Query:

SELECT sum(views) vies
     , sum(likes) likes
     , key
     , count(*) OVER() full_count
FROM gan.paid
WHERE project_id = 1
    AND country_iso_code = 'gb'
    AND source = 'website'
    AND created_at::date >= now()::date - 100
    AND created_at::date <= now()::date
GROUP BY key;

Table definition:

CREATE TABLE gan.paid (
    id               integer pk
    key              text
    project_id       integer
    source           varchar
    country_iso_code varchar
    visits           integer
    likes            integer
    created_at       date
);

How can I create an index that will speed up this query?


texts:
I am designing a workflow processing system. I have many (hundreds) of workflows that in many cases are variants of each other and have related characteristics.
In the beginning development stages I need to document these workflows in a way that less-technical personnel can interface with. They break down their usage scenario and document it all in one place, the dev team will go through later and write code to implement their descriptions.
All I need to do is make a giant spreadsheet with each row containing a single workflow specification, with columns such as "Inputs", "Outputs", "Process Description", and "Implementation Status."
The Process Description field will contain several paragraphs and ordered lists describing the potentially intricate process and any nuances/gotchyas in human readable form.
Typing all of this information into an Excel spreadsheet is tedious and people keep hitting Enter instead of Shift+Enter. Reading it requires expanding the row or column dimensions, which makes scrolling through to quickly check the status intractable. Keeping the dimensions small makes entering data hard. Cutting and pasting blocks of text to break apart and reorganize the descriptions is...you guessed it, beyond unintuitive.
Has anyone come up with a solution with a good UI that can store information like this? And has a collapse-all/expand-all toggle?
Maybe I should roll-my-own database-backed web app...Is anyone interested in working with me to develop this as a product?
Of less importance but still kind of pretty important, is the relationships. For example Workflow 302 might use the same inputs and outputs as 303, and the process description needs only a few, very critical, words to be changed.
I feel like MS Access would be along the lines of what I'm looking for to potentially support data relationships; however once again being able to type a few nicely formatted paragraphs or a page of text within one cell needs to be a painless process.
Thank you for any suggestions!


texts:
I have Oracle database 11GR2 on Windows 7.

When I want to start enterprise manager the error below is shown:

  Microsoft Windows [Version 6.1.7601]
  Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

  C:\Users\SHOJAIE>emctl start dbconsole
  Oracle Enterprise Manager 11g Database Control Release 11.2.0.1.0
  Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
  https://SHOJAIE-PC:1158/em/console/aboutApplication
  Starting Oracle Enterprise Manager 11g Database Control ...System error 5 has occurred.

  Access is denied.


texts: MySQL Replication: Seconds Behind Master super high

texts:
I've set up a slave db server for my production database, but when I checked the show slave status, I noticed a super big number in seconds behind master.

This is the output:

           Slave_IO_State: Waiting for master to send event
              Master_Host: 1.2.3.4
              Master_User: replicator
              Master_Port: 3306
            Connect_Retry: 60
          Master_Log_File: mysql-bin.000173
      Read_Master_Log_Pos: 15909435
           Relay_Log_File: mysqld-relay-bin.000079
            Relay_Log_Pos: 91173356
    Relay_Master_Log_File: mysql-bin.000093
         Slave_IO_Running: Yes
        Slave_SQL_Running: Yes
          Replicate_Do_DB:
      Replicate_Ignore_DB:
       Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
               Last_Errno: 0
               Last_Error:
             Skip_Counter: 0
      Exec_Master_Log_Pos: 91173210
          Relay_Log_Space: 8179978166
          Until_Condition: None
           Until_Log_File:
            Until_Log_Pos: 0
       Master_SSL_Allowed: No
       Master_SSL_CA_File:
       Master_SSL_CA_Path:
          Master_SSL_Cert:
        Master_SSL_Cipher:
           Master_SSL_Key:
    Seconds_Behind_Master: 486330
Master_SSL_Verify_Server_Cert: No
            Last_IO_Errno: 0
            Last_IO_Error:
           Last_SQL_Errno: 0
           Last_SQL_Error:
Replicate_Ignore_Server_Ids:
         Master_Server_Id: 1
1 row in set (0.00 sec)

ERROR:
No query specified

Then when I run SHOW PROCESSLIST, I see that the time of the thread matches the time indicated in seconds behind:

mysql> SHOW PROCESSLIST;

| 40 | system user |           | NULL | Connect |  66530 | Waiting for master to send event | NULL             |
| 41 | system user |           | NULL | Connect | 486330 | Reading event from the relay log | NULL             |
| 45 | root        | localhost | NULL | Query   |      0 | NULL                             | SHOW PROCESSLIST |

That time is dropping, slowly. Read_Master_Log_Pos,  Relay_Log_Pos, Exec_Master_Log_Pos and Relay_Log_Space are changing all the time.

I've also check the time/date and both servers are on sync.

On the Master side:

mysql> SHOW PROCESSLIST;

| 66739 | replicator | 1.2.3.5:52884 | NULL                | Binlog Dump |    65671 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL

and show slave hosts looks empty...

mysql> SHOW SLAVE HOSTS;
+-----------+------+------+-----------+
| Server_id | Host | Port | Master_id |
+-----------+------+------+-----------+
|         2 |      | 3306 |         1 |
+-----------+------+------+-----------+
1 row in set (0.00 sec)

mysql>

So what is actually going on here? Looks like the slave is actually connected and working, but very very slow? Can someone give me some hints on how to do more debug on this? Server is rather idle at 95%.


texts:
What is the recommended best practice when it comes to storing date/time for an application that needs to support multiple timezones?

Which is best ?

Use the SQL DateTime data type and insert date/time as UTC (e.g. convert local time to UTC in application business layer and pass to DB already in UTC format)
Use the SQL DateTimeOffset data type which will record the offset relative to UTC?


texts: Minimising downtime implementing merge replication on live system

texts:
Background

We have three Servers running SQL Server 2008 which all contain a Product database. These are currently kept in sync once per day by sending a copy of the MDF to the two Subscribing servers and detaching the existing DB and replacing it with the latest copy.

The two subscribers contain a Customer database which is not present on the publisher. The Customer database contains customer purchase information, so it needs to reference ProductIDs in the Product table.

We have implemented Merge Replication on a similar set up in our testing environment and are preparing to implement these changes on our live system.

The end result should look something like:

The Problem

In order to implement these changes, we will need to generate a snapshot and synchronise the data between the Publisher and the Subscribers. While this is occurring, any client application which relies on the data in the Product database will fail, and any Stored Procedures on the Customer database which make cross database joins will also fail.

We'd like to keep this downtime to a minimum or eliminate it completely.

We've had some crazy ideas thrown around which may or may not work (using synonyms to point a subscriber to another server's Product table while snapshot generation/synchronisation takes place) but they don't seem very elegant, if they'll work at all. I'm hoping someone else has had a similar issue and might have some insight :)


texts:
Is it possible to get a result as a column?

The SELECT query will only need to return one record, however, at the time of querying the database it will not know what attributes are in the table, and could get a different number of attributes back: between 6-10.


texts: MySQL replication with 5.7 master and 5.5 slave

texts:
I'm trying to setup a MySQL replica with the master on 5.7 and the slave on 5.5.

On the master I have disabled the binlog_checksum.

After import a dump of the database, change the master and start the slave, if I make a change on the master, the slave get this error:

Last_Errno: 1594
Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

And if I run mysqlbinlog to the relay log, I get this error:

# mysqlbinlog /var/lib/mysql/mysql-relay-bin.000002

...

ERROR: Error in Log_event::read_log_event(): 'Sanity check failed', data_len: 61, event_type: 34
ERROR: Could not read entry at offset 270: Error in log format or read error.
DELIMITER ;
# End of log file

...

There's any chance to get this replication working?
The upgrade process can be problematic for my client and I'm trying to avoid that.

Thanks


texts: Is it possible to log MySQL variable changes?

texts:
I'm using Percona MySQL server 5.7 and after a while my max_allowed_packet change to 1024 bytes and I don't know who or what application changes it.

I've set it in config file and after restart it sets fine:
[mysqld]
max_allowed_packet=100M

But after a while (few days later), my application throws error while inserting long text, it changed to 1024 bytes. I check it with:
SHOW variable LIKE 'max_allowed_packet'

Is it possible to log only variable changes to know who changed it when?


texts: Did user connect via AG Listener or directly to the server?

texts: How do I tell whether a SPID connected via the AG Listener name or the server name (or its IP address)?


texts:
I have a table that has a full text index defined on it.  Change tracking is set to 'Automatic' but I can see that 'Table Full-Text Pending Changes' is set to '58429', and recently added rows (for the last few days) in the table are not being picked up.  The index is enabled.

I don't understand how Change Tracking can be 'Automatic' but the index is not updated ?


texts: How To Secure Sql-Server Database From Intended Manipulation by IT mans or Database Admins or System Admins

texts:
This question rises from a dispute between "Financial Department" and "IT Department" in a Production Company.

This company has it's own IT Department witch provides Financial Software tools and all other ERP-Like tools for this company. Because the IT Department itself is part of this company actually the salary of software developers and database admin is calculated and paid through the software and databases designed and maintained by themselves. so the financial department is feared of database manipulation by the system admins. also they don't want to use other software tools and want to remain in the current software and database solutions.

Now the Question is:

How is it possible to monitor an Sql-server database to prevent any intended manipulation by database admin? the solution must be in a way that insures the system works fine but any manipulation by system admin will be traceable for financial department and system admin will not be able to erase his/her manipulation traces.

of course any simplified and tutorial based approach suggestion will be appreciated.


texts: What index to use with lots of duplicate values?

texts:
Let's make a few assumptions:

I have table that looks like this:

 a | b
---+---
 a | -1
 a | 17
  ...
 a | 21
 c | 17
 c | -3
  ...
 c | 22

Facts about my set:

Size of the whole table is ~ 1010 rows.
I have ~ 100k rows with value a in column a, similar for other values (e.g. c).
That means ~ 100k distinct values in column 'a'.
Most of my queries will read all or most of the values for a given value in a, e.g. select sum(b) from t where a = 'c'.
The table is written in such a way that consecutive values are physically close (either it's written in order, or we assume CLUSTER was used on that table and column a).
The table is rarely if ever updated, we're only concerned about read speed.
The table is relatively narrow (say ~25 bytes per tuple, + 23 bytes overhead).

Now the question is, what kind of index should I be using? My understanding is:

BTree My issue here is that the BTree index will be huge since as far as I know it will store duplicate values (it has to, since it can't assume the table is physically sorted). If the BTree is huge, I end up having to read both the index and the parts of the table that the index points to. (We can use fillfactor = 100 to decrease the size of the index a bit.)
BRIN My understanding is that I can have a small index here at the expense of reading useless pages. Using a small pages_per_range means that the index is bigger (which is a problem with BRIN since I need to read the whole index), having a big pages_per_range means that I'll read a lot of useless pages. Is there a magic formula to find a good value of pages_per_range that takes into account those trade-offs?
GIN/GiST Not sure those are relevant here since they're mostly used for full-text search, but I also hear that they're good at dealing with duplicate keys. Would either a GIN or GiST index help here?

Another question is, will Postgres use the fact that a table is CLUSTERed (assuming no updates) in the query planner (e.g. by binary searching for the relevant start/end pages)? Somewhat related, can I just store all my columns in a BTree and drop the table altogether (or achieve something equivalent, I believe those are clustered indices in SQL server)? Is there some hybrid BTree/BRIN index that would help here?

I'd rather avoid using arrays to store my values since my query will end up less readable that way (I understand this would reduce the cost of the 23 bytes per tuple overhead by reducing the number of tuples).


texts: How To Use Publication Without Precompiled Partitions

texts:
I am using SQL 2012 with merge replication. Our setup is quite complicated and thus it seems running the publication with precompiled partitions is not going to work.

The merge agent is slow to do the first sync because of calling the sp_mssetupbelongs stored procedure.

I am wondering if it is viable to run the publication without precompiled partitions?

The first sync is faster without precompiled partitions, but subsequent syncs are a bit slower than before. Just a few changes will result in a sync of about 5 minutes.

I also tried an option called 'Optimize Partitions' and would like to know what this does? Deleting a record at the publication end did not propagate to the subscribers. The subsequent syncs were faster though. Why do deletes not propagate, and is there a workaround for this?


texts: inno_db_buffer_pool_size and key efficiency

texts:
I have the following metrics from mysql workbench

After I have set innodb-buffer-pool-size to 6G, according to this answer's query on how to determine the recommended size.

While the used buffer usage was between 87% and 93% before I did the change in my.cnf (running with default values) it dramatically decreased now, so I'm wondering if 6G was too much. Especially because the key efficiency was between 97% and 99% before the change, which has lost some percents now.

Should I bother more about key efficiency or buffer usage? Should I decrease the buffer usage again?

The server specs are

Intel® Xeon® E5-1650 v3
Hexa-Core Haswell Hyper-Threading-Technologie
128 GB DDR4 ECC RAM
2 x 480 GB SATA 6 Gb/s Data Center Series SSD


texts:
We have a ETL job which is running each day at 02:00 AM.

Until last week the Job ran successfully with no issues.

The ETL runs the extract by connecting through the Linked server to the PROD server.

Checked the access to PROD server all good.

All of a sudden the job started failing with below error.

  Error: Timeout expired.  The timeout period elapsed prior to
  completion of the operation or the server is not responding.

Checked below things :

Exec updatestats on each database of this Database server where the ETL is pointed to.

I also tried setting Remote Query Timeout to 0

  NOTE: But a particular script is causing issue only during extraction.

What else should I check on as the ETL is failing only for specific script.

But the particular script is running good when executed manually (13 seconds)

There are many scripts which run for this ETL job.

But during the extraction it is failing.

P.S. I am very sorry as the code is little big I couldn't post here.

Please see linked server properties.

Kindly help me on the things I am missing.

Regards


texts: Count Assets Barcodes and Group by Rooms

texts:
I have 2 tables Asset Register and LocationRoom. I want to count the number of asset barcodes in each room.

I want the output to be the list of Asset Barcodes, Description,RoomNo and count for assets by room.

for example Room R0001 has 4 assets (01780,01781,01782,01783).

http://sqlfiddle.com/#!18/c2cb3/2


texts:
I am new to MySQL. I know that ROW_NUMBER() OVER (PARTITION) will not work in MySQL. I want to convert the code from SQL Server to MySQL:

SELECT  *,
    ROW_NUMBER() OVER (
        Partition By X.TPID,originTLA, destinationTLA
        ORDER BY packageStartDate,totalPackageSavingsPct desc
    ) AS ttl_perct_rnk
FROM TABLE

I want to convert this code to MySQL.

In Most of the cases the requirement to find rank is based on a single column. But here we have to find rank based on three columns. It is easy to find rank based on single column. But I am not sure how can we implement the rank based on three column or multiple columns.


texts: MySQL does not start - Ubuntu 14.04 LTS after migration to new disk

texts:
I have installed in a Virtualbox VM a 64-bit desktop version of Ubuntu 14.04 LTS and MySQL Server 5.6. I had some databases in the machine's main virtual hard disk but then I was going to open a SQL dump with a size of 6 GB, and the disk didn't have enough space to open it. So, I decided to create a new virtual disk (13 GB) and attach it to the VM.

After formatting the new disk I created a new folder called mysql-db-new in the new volume. Now I will give you the entire process I did to migrate the databases from the main volume to the new volume:

Stop MySQL server:

sudo /etc/init.d/mysql stop

Move MySQL data to the new drive:

sudo mv /var/lib/mysql /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/

Edit /etc/mysql/my.cnf to point the new location (initially /var/lib/mysql):

datadir = /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/mysql

Configure AppArmor with the new path:

sudo gedit /etc/apparmor.d/usr.sbin.mysqld

All lines with /var/lib/mysql were replaced with the new location /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/mysql
Change permissions and group of the new location:

sudo chgrp mysql /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/mysql

sudo chmod 755 /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/mysql

Restart MySQL:

sudo /etc/init.d/mysql start

Then after all this steps, MySQL start failed. Then I watched the /var/log/mysql/error.log and this is the output:

140904 15:25:14 mysqld_safe Starting mysqld daemon with databases from /media/juanjo/2d4aef3c-ae2e-4b30-8bb7-c5723eb5403b/mysql-db-new/mysql
140904 15:25:14 [Warning] Using unique option prefix key_buffer instead of key_buffer_size is deprecated and will be removed in a future release. Please use the full name instead.
140904 15:25:14 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
140904 15:25:14 [Note] Plugin 'FEDERATED' is disabled.
/usr/sbin/mysqld: Table 'plugin' is read only
140904 15:25:14 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it.
140904 15:25:14 InnoDB: The InnoDB memory heap is disabled
140904 15:25:14 InnoDB: Mutexes and rw_locks use GCC atomic builtins
140904 15:25:14 InnoDB: Compressed tables use zlib 1.2.8
140904 15:25:14 InnoDB: Using Linux native AIO
140904 15:25:14 InnoDB: Initializing buffer pool, size = 128.0M
140904 15:25:14 InnoDB: Completed initialization of buffer pool
140904 15:25:14 InnoDB: highest supported file format is Barracuda.
140904 15:25:14  InnoDB: Waiting for the background threads to start
140904 15:25:15 InnoDB: 5.5.38 started; log sequence number 1637757
140904 15:25:15 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
140904 15:25:15 [Note]   - '127.0.0.1' resolves to '127.0.0.1';
140904 15:25:15 [Note] Server socket created on IP: '127.0.0.1'.
140904 15:25:15 [ERROR] Fatal error: Can't open and lock privilege tables: Table 'host' is read only
140904 15:25:15 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

So I don't know what's really going on. My first thought was on the permissions but I gave enough permissions on the new MySQL database location folder.

I will appreciate your help.


texts: Best way to store multiple values from multiple daily transactions

texts: I'm currently developing a Timeclock web application which will store the date, hour, and minutes which a user logs into and out of the application via a MYSQL Database. Users can log into and out of the application multiple times per day, thus 1 user can have up to 6 transactions per day. What would be the best way to store multiple transactions such as above, in a database? I'm a web developer, and our Database Admin is currently gone for summer, so I apologize if this is a pretty easy question, but I'm confused on how to optimize the storing (and later retrieval) of multiple values in this instance.


texts: Mongodump affects app performance really bad

texts:
We have quite a big mongo instance (150 GB) with no sharding, and our regular backup (mongodump) has a very significant effect on the app performance. Worse than that, due to the heavy use of mongo by the app, the backup lasts more than 10 hours.

I know that we need sharding, and we have plans to move to ElasticSearch, so I'm looking for some short-term solution.

Is there something I can do to improve this, like limiting number of queries per second for mongodump or anything?

We have a standalone mongo on a 32-core 190 GB RAM server that shares it with nginx, rabbitmq and some small things. Not the cleanest setup ever, I know :)


texts: Importing bulk csv file to oracle database

texts:
I am going to import a bulk csv file to oracle database. This file contains around 200m records. I was wondering what is the best way of importing this file? I check the performance of SQL developer and SQL Loader script but both of them was poor in comparison to MS SQL server. In MS SQL server the import wizard takes less than 1 hour for this file but in Oracle I am unable to do that in less than 6-7 hours! So how can I speed up the importing procedure?

SQL Loader command:

sqlldr CONTROL=244105_result_20140331_12_07_05_20140331_12_07_05.ctl   skip=1

SQL Loader script:

load data
infile '244105_result_20140331_12_07_05_20140331_12_07_05.csv' "str '\n'"
append
into table CDR2
fields terminated by ', '
OPTIONALLY ENCLOSED BY '"' AND '"'
trailing nullcols
           ( anum CHAR(4000),
             bnum CHAR(4000),
             call_type CHAR(4000),
             call_date CHAR(4000),
             call_time CHAR(4000),
             call_duration CHAR(4000),
             amount CHAR(4000)
           )


texts: PostreSQL execute a default query when no row are returned

texts:
I need to calculate the average score of a list of userId.

I have a table "user_place_score" (user_id / place_id/ score).
Sometimes I do not have the score for a user_id so I need to fetch a "default" score in a the "default_place_score" table (place_id / score).

Is is possible in PostreSQL to do this in one query ? For a a given list of users and one place_id select all their score in the user_place_score and if now row found take the score found if the default_place_score table ?

Basically I want to avoid doing this kind of stuff:

for (Integer userId : userIds) {
  score = (query to fetch the score for this userId)
  if (score == null)
    defaultScore = (query to fetch the default score for this PlaceId)

  total = total + defaultScore
}

//return the average
return total/numberOfUser

Thank you!


texts: Batch Requests/sec reported by DMV millions of times larger than Activity Monitor

texts:
I have verified through SQL Server Profiler that my request per-second are around 30 request/s as is corroborated by SSMS Activity Monitor but sys.dm_os_performance_counters is reporting hundreds of millions/s.

Any idea what might be causing this gross discrepancy?

Query:

SELECT
    RTrim(LTrim(object_name)) as object_name,
    RTrim(LTrim(counter_name)) as counter_name,
    cntr_value
FROM
    sys.dm_os_performance_counters
WHERE
    instance_name IN ('', '_Total')
    and counter_name IN (
        N'Batch Requests/sec'
        , N'SQL Compilations/sec'
        , N'SQL Re-Compilations/sec'
        , N'Transactions/sec')

Results:

object_name counter_name    cntr_value
SQLServer:Databases Transactions/sec    191721399
SQLServer:SQL Statistics    Batch Requests/sec  242955426
SQLServer:SQL Statistics    SQL Compilations/sec    42048371
SQLServer:SQL Statistics    SQL Re-Compilations/sec 1200947


texts: How often does the slow_log table get updated in MySQL?

texts:
I've changed the long_query_time from 10 secs to 5 secs. I haven't seen any queries logged for a few hours now. Am I supposed to tweak my database for the slow_log table to gets updated more often?

There were slow queries that took longer than 10 seconds and I've already dealt with them. However, the first byte time for my WordPress website is still > 3 seconds according to tools.pingdom.com.

Updates:

I've just changed the long_query_time to 1 sec and still, I haven't seen any slow queries logged yet. Could it be possible that I now have no queries that takes longer than 1 sec to execute (that seems too good to be true, doesn't it?)?

I've seen queries that took longer than 1 sec now so everything is working fine for me - I see the slow queries that I need to see.

*How I got the slow queries to be logged in the slow_log table in mysql database?*
I'm using Amazon RDS MySQL, so as soon as I changed slow_query_log to 1, the slow queries appear in the slow_log table.


texts: SQL Server Transactional Replication: Adding an existing column to an existing publication article

texts:
We are using Transactional replication and I've been fighting with it over the past few days trying to add an existing column to an existing article that we now need replicated to the subscriber.
I do not want to reinitialize all of the articles as some of the tables are extremely large.
So, from what I have learned online (I'm pretty new to replication), I cannot use the Replication Publication GUI to simply add the column article as all articles will be reinitialized.
That being the case and following information I've found I have used the following stored procedures to successfully alter the article: sp_dropsubscription, sp_droparticle, sp_addarticle, sp_articlecolumn (for all previously included article columns plus my new one), sp_addsubscription.
This all works with no problems.
I then start the Snapshot Agent and a snapshot for 1 article is generated. Which is what I want.All good so far.
The problem comes when I start synchronizing and specifically when replication tries to execute the bcp file in the snapshot folder. I get the following error (with changed names and path):
2020-12-15 10:07:17.172 Bulk copying data into table 'Incident'
2020-12-15 10:07:17.172 Agent message code 20037. The process could not bulk copy into table '"dbo"."Incident"'.
2020-12-15 10:07:17.187 Category:NULL
Source:  Microsoft SQL Server Native Client 11.0
Number:
Message: Field size too large
2020-12-15 10:07:17.187 Category:NULL
Source:
Number:  20253
Message: To obtain an error file with details on the errors encountered when initializing the subscribing table, execute the bcp command that appears below.  Consult the BOL for more information on the bcp utility and its supported options.
2020-12-15 10:07:17.187 Category:NULL
Source:
Number:  20253
Message: bcp "DummyDBName"."dbo"."Incident" in "\Incident_37.bcp" -e "errorfile" -t"\n<x$3>\n" -r"\n<,@g>\n" -m10000 -SDummyServerName -T -w
Running the bcp command suggested in the error results in:

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Unexpected EOF encountered in BCP data-file
0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1

Nothing is output to the error file.
Now, I can get the bcp file to execute via distributor job if I change the order of the columns on the subscriber database via the SSMS table designer. If I place my new column in the relevant position with respect to the publisher database everything works fine. The trouble with that is there are a number of constraints associated with the table on the subscriber and SSMS wants to drop and recreate all associated tables when I try and save the table from the designer. Which, again, is a problem as some of them are huge.
So, it seems that the order of the columns in the bcp file is what is causing me my headache. I have found no way to control that order. I tried to generate a format file from the bcp file generated by the Snapshot Agent but it fails with the EOF error.
I know I could drop the problem constraints on the subscriber database, reposition the column in the table designer, save the table and then re-add the constraints. This would probably work and maybe what I have to do?
However, it would be so much easier if I could control the order of the columns that the Snapshot Agent generates in the bcp file.
Is there another better way to add an existing column to an existing article with Transactional Replication that doesn't involve re-initializing all of the articles? Or is there a clever way to control the ordering of the columns in the generated bcp file?
Thanks.


texts: Does a subscriber node in pglogical "pull", or are updates "pushed"?

texts:
I've configured a simple pglogical replication between two nodes.

What I've not found yet is if the subscriber node pulls data, or if the publisher node initiates a push of data.

I'm intending to have geographically distributed nodes behind firewalls, which at this stage (without doing something like a VPN) would mean I have to have the subscribers pull data.


texts: Why is MySQL not using the index with the higher cardinality?

texts: I have an index called source with cardinality of 1122 and a second index called state with cardinality of 22.  MySQL keeps using the state index when when query SELECT C1 FROM tbl WHERE source = 'x' and state = 'y'. Is this normal?  If not, what could be going wrong?


texts: Configuring SQL Server 2016 to allow remote connections

texts:
I have SQL Server 2016 installed on Windows Server 2016 Core.  Locally, it seems to be working.  I can connect using SQLCMD.exe and do some basic selects and what not.

From remote, I cannot connect.  Using SQLCMD.exe on a remote machine:

sqlcmd -S boldiq_db3
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Named Pipes
    Provider: Could not open a connection to SQL Server [53]. .
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login timeout
    expired.
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : 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..

And from SQL Server Management Studio:

In the installation INI I installed from, I have:

  TCPENABLED=1

So, I believe TCP is enabled.  It might be a Firewall issue, but I've run:

  netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN

Which appears to have completed successfully.  Are there any other steps to diagnose where the problem is happening?  Thanks!


texts: I accidentally removed SELECT permission from my user on SQL Server

texts:
I accidentally removed SELECT and other permissions from my user on SQL Server 2014 via Properties.

I checked all the permissions except the Guest in the Owned Schemas list and also in Membership - including db_denydatareader and db_denydatawriter. That's what made me this mistake. There are some permissions (db_denydatareader and db_denydatawriter) that if you check them, you would remove your own permission without being able to regrant them. I checked all of them.

  Cannot alter the role 'sp_denydatareader', because it does not exist or you do not have permission.

Unfortunately I can't use SELECT and other function on my queries or selecting the Properties again in order to uncheck the permissions.

What should I do? I'm not the admin on the box. Is there any way to restore it without asking the sysadmin?


texts:
I have 3 tables.

Grades
Disciplines
Students

I want to select only the disciplines that doesn't have grade 2. What am I doing wrong?
SELECT DISTINCT DISC_NAME
FROM STUDENTS, DISCIPLINES, GRADES
WHERE
GRADES.ID= STUDENTS.ID
AND
GRADES.KOD_DISC = DISCIPLINES.DISC_ID
GROUP BY GRADE
HAVING
GRADE != 2


texts: What determines the maximum number of concurrent connection to SQL Server 2016 Express

texts: After some googling, there seems to not have a straight forward answer to the question. MSDN Forum mentions it depends on CPU, Memory usage, database size etc. Could someone help explain or perhaps suggest an example to show why there are so many factors limiting the number of concurrent connection. Also how could one ever find out the maximum connection of an application?


texts: Deleting rows from big table: what then?

texts:
I will be deleting 40% of rows from a table that currently has 2.5 millions rows, space reserved 17 GB, 150 MB index, unused space 3 MB.

The primary key is the identity, and records were inserted in order. There are 2 non-clustered indexes. The rows I delete are the oldest. There is a FK pointing to the table, but it is not a problem because it only refers to recent records (which will not be deleted). Columns are mostly nvarchar, including a few nvarchar(max).

The actual deletion will take less than 10 minutes, the database server will not be used by anyone else for a couple of hours, so I am not worried by the deletion itself.

To get the maximum advantage from the point of view of disk size and performances, which operations can I do after the deletion? I don't want to create a new table and move the data, but is there a way I can rearrange the data and reclaim the space?


texts: Database Mail works for one Instance but not three others

texts:
I have a test box with 4 Instances of SQL Server on it. One 2012 instance and three 2008 R2 instances.

Database mail works fine on one of the named 2008 R2 Instances but not on any of the others.

I've been going through the troubleshooting steps on Microsoft's website and everything checks out. All of the settings are correct and it's starting just fine, it just won't send an email.

The profiles are public and the logins have the appropriate permissions.

The mail log contains:

Log ID           9137
Process ID       7740
Mail Item ID     6031
Last Modified    3/5/2018 11:34:23 AM
Last Modified By sa

Message
The mail could not be sent to the recipients because of the mail server failure.
(Sending Mail using Account 1 (2018-03-05T11:34:23).
Exception Message: Could not connect to mail server.
(A non-recoverable error occurred during a database lookup).

I have tried both sending the test emails through the UI (via Send Test Email), and through my own procedures. When I look in the database mail log I see the same errors stretching back to when I built the instances. I see messages in the log file about database mail successfully starting and shutting down.

I've rebooted several times and all of the settings are exactly the same between instances. One instance works perfectly and three others don't. If it were a firewall or AV issue it would more than likely be the same for all instances.


texts: How to select specific rows if a column exists or all rows if a column doesn't

texts:
I'm writing a script that gets a count of rows for a few tables, however for some tables I want to only get a count of rows where a flag is set (in this case active=1).  Is there a way I can do this in one query?

Eg:

Table users has a column called active

Table clients does not have a column called active

I want to get a count of users where active=1 and just get a count of clients.

Before you say "just hard code it" this is a query that's going inside a python script that could be run on numerous different databases and I have no way of knowing what tables my script will be selecting and if they have a column called active, and I would prefer to have just one query to do it all instead of two separate ones and relying on mysql to throw an error so I know to use the other one.


texts:
I want a dynamic query which will fetch the employee name who has completed successfully 6 months and are ready for confirmation.

I have written a hardcode query does that. Here is the query

SELECT [emp_name] FROM [emp_mst]
  WHERE Dt_Of_Join >= dateadd(m, -7, datediff(d, 8, getdate()))
  and   Dt_Of_Join <  dateadd(m, -6, datediff(d, 9, getdate()))

For example: if employee has Joined on 1st dec,2014, he will complete 6 months on 31st May 2015.

So in june he can be intimated on any date. The above query gives me the result for Nov16 2014 to Dec 15 2014, based on today's date.  I want it to be handled dynamically

Please suggest.

I am using sql server 2005.


texts:
Conversion failed when converting the varchar value 'Hours' to data type int. This is my query:

Sum(Cast(Cast(DATEDIFF(HH, ld_time_tracker_start_time,
    fld_time_tracker_end_time)%3600/60 As int) +' Hours'+
Cast(DATEDIFF(MINUTE, ld_time_tracker_start_time,
 fld_time_tracker_end_time)As int )+'Minutes' As Int))
As [Time Spent(In hrs)]

I modified the above Code To:

+Cast('Hours' As Int)
+Covert(int,'Hours')

... and got the error message:

  Conversion failed when converting the varchar value ' Hours' to data
  type int


texts: Is database partitioning for performance reasons meaningful in a virtualized environment?

texts:
When running SQL server on physical hardware, placing database data and log files on separate disks or RAID arrays can benefit performance, resilience, and maintenance. Partitioning the database over separate physical disks and RAID arrays is also an option. Simply put, more spindles is better.

When SQL server is running in a virtual environment, the concept of spindles applies differently. You often have little to no direct control over what physical disks your virtual disks are mapped to. Does this mean that it doesn't make sense to partition a database, or split the data and log files over separate virtual disks, the same way we do with physical ones?

I understand that there are other reasons to partition databases that still apply in virtual environments. For instance, large read-only tables can be in a separate partition that only needs to be backed up once, but this is not the subject of this post.


texts: How to easily show very long "canonical" name for each column in all reports?

texts:
Related:

Does the length of a table name affect performance?

For query writing purposes I want to have nice short actual column names.

However, from my actual data source, I have an extremely long "canonical" name for each field.  (This was the column name in the source.)

When showing my query results, I would like to show these long column names.

How can I do this?

I considered a couple ways of approaching this:

I could COMMENT ON each column, which would at least store the long names directly in the table definition, but this wouldn't show the comments with each query result.  (Unless there's some way to do that...?)
I could make a big column alias block of code that I could copy and paste for each query, but that would only work if I'm returning ALL the columns, which isn't likely in most cases.
I could try some fancy magic with the information schema (or system tables) and use PL/pgSQL to run every query so it gives the desired column names.  (I wouldn't know how to approach this, actually, though I have a decent grasp of PL/pgSQL.)
There must be some better way I haven't thought of...can you help me here?


texts: Saving LZW encoded data into a mySQL database

texts:
I send LZW compressed data from the client side with JavaScript to the server.
The problem is that the data becomes corrupted after saving it to database.

So my question is, what collation should I use to accomplish that?
(My current one is latin1-default.)

I already checked if the problem arises during the data transfer from client to server and vice versa by sending encoded data to the HTTP server and sending it back (PHP-echo) immediately without processing it. I could decode LZW properly. So it should definitely be a problem with the database.

More information about the schema:
I only have a single table with three columns:

data BLOB -- I also tried TEXT
user_id INT
type VARCHAR

This is how I save the data:

INSERT INTO `svg`.`saved_data` (`user_id`, `data`, `type`)
VALUES ('{$user_id}', '{$data}', '{$type}');


texts: Using SUBSTR and CONCAT to get the required Result

texts:
I Am trying to get the results for the 'CENTRE' whereby if the 'DEP2' does not exist it has to use 'DEP'

Have done this by using SUBSTR and CONCAT in my query
But only get back 3 from 'DEP' when using it like this

My Query:

SELECT
DEP,
DEP2,
SUBSTR(DEP2 || DEP,0,4)CENTRE

FROM DEPARTMENT

I Get the results back fine but with 'DEP' only showing 3 numbers instead of the 4

----------------------------------
DEP    | DEP2      | CENTRE      |
----------------------------------
0132    | 7000     | 7000        |
0177    | 7000     | 7000        |
7051    | 7000     | 7000        |
7051    |          |  705        |
7051    |          |  705        |
----------------------------------


texts: Missing column from SELECT operation using INNER JOIN


texts:
I'm making form-generating app in php. I have Widget table with type_id (html input) field, and I would like to add options field, like width/height, step, range, etc. Different widgets do have different options though.

I can think of two options:

Use separate meta table for every input type (20+)
Use serialized array as a meta

Option 1 seem more "legal", but option 2 would be easier to implement and maintain(?).

Would performance be considerably better with option 1?

EDIT: This widget table will be accessed only through widget_id and used to render html. Changes are possible, but they shouldn't happen that often.


texts: Mysql : foreign key relative to multiple tables

texts:
I'm trying to improve the database integrity adding some foreign key (it's the first time i use them).

I have some tables relative to different type of devices (different tables because the "devices" are completely different from each other), a table groups and a relation-table group_device (the same device can be in more groups).

devices_typeA

id is primary key

id | column1 | column2 |
------------------------
1  | aaa     | bbbb    |
2  | aaa     | bbbb    |

devices_typeB

id is primary key

id | column1 | column2 | column2 | columnN |
--------------------------------------------
1  | aaa     | bbbb    | cccc    | dddd    |
2  | aaa     | bbbb    | cccc    | dddd    |

devices_typeC

id is primary key

id | column1 |
--------------
1  | aaa     |
2  | aaa     |

groups

id is primary key

id | name    |
--------------
1  | group 1 |
2  | group 2 |
3  | group n |

group_device

id | type_device | id_device | id_group |
-----------------------------------------
1  | typeA       | 1         | 1        |
2  | typeA       | 1         | 2        |
3  | typeB       | 1         | 1        |
4  | typeC       | 2         | 3        |

Now i would like to add some foreign key to the tables group_device (in such a way that when a device is deleted, is deleted also in the table group_device [with the ON DELETE CASCADE action]). My problem is the id_device column, that is relative to multiple tables (devices_typeA.id, devices_typeB.id , devices_typeC.id).


texts: Is ProxySQL Suitable for Connection Pooling Apps?

texts:
I'm looking for some guidance on whether ProxySQL is suitable as a method of failing over Java connection pool based apps in a multi-master replication (with read/write split) set up?

I've found little online to suggest that ProxySQL either does or doesn't play nicely with Java apps which rely on reusable persistent connections via connection pooling (in this case HikariCP), but some anecdotal reports suggested that ProxySQL won't failover existing connections in the case of the 'master' (writer) node being down without the connections being closed and reopened on the app side.

This would make sense if ProxySQL were doing a 1-to-1 passthrough, but surely with its multiplexing features, connections could be intelligently routed to another host?


texts: Can I compress two rows with a difference in two columns into a single row with both columns?

texts:
I have a query that returns customer product information that shows which region a product is sourced from.

Query:

SELECT
    prod.prod_id,
    prod.is_source,
    link.prod_link_disabled,
    loc.location_id,
    loc.customer_id,
    cust.customer_name
FROM
    product prod,
    product_link link,
    region region,
    location loc,
    customer cust
WHERE
    prod.prod_id = link.prod_id
    AND link.region_id = region.region_id
    AND region.loc_id = loc.loc_id
    AND link.prod_link_deleted = FALSE
    AND loc.customer_id = customer_id
    AND prod.prod_id = 5874;

This returns the following result:

| prod_id             | is_source | prod_link_disabled | location_id | customer_id | customer_name   |
| ------------------- | --------- | ------------------ | ----------- | ----------- | --------------- |
| Test Product - 5874 |         t |                  f |           2 |         332 | Test Playground |
| Test Product - 5874 |         f |                  f |           1 |         332 | Test Playground |

This is showing that product 5874 is coming from location 2 (because is_source is true).

I'd like to compress this information into a single row. I'd like a result that looks like this:

| prod_id             | prod_link_disabled | src_location_id | dest_location_id | customer_id | customer_name   |
| ------------------- | ------------------ | --------------- | ---------------- | ----------- | --------------- |
| Test Product - 5874 |                  f |               2 |                1 |         332 | Test Playground |

This compressed the two previous rows into a single row where src_location_id is 2 (again because that row had is_source as true) and dest_location_id is 1

How can I pivot the two row result into a single row?

It is safe to assume that there are always only two results in the first query.


texts: Can't backup a Postgres database: FATAL: Peer authentication failed for user "postgres"

texts:
I can connect to my database no problem remotely, either from PHP scripts on our webserver or using PGAdmin3.

Unfortunately, when I try to run pg_dump backups locally on the server itself, I get:

pg_dump --username=postgres -W omega | gzip > omega.10.10.13.gz

pg_dump: [archiver (db)] connection to database "omega" failed:
FATAL:  Peer authentication failed for user "postgres"

Previously I'd had no password at all for my database, but to try to get around this I actually gave the postgres user a password. Still no dice, peer authentication fails every time.

Here's the settings in my pg_hba.conf file... please tell me what I can do to fix this. Really want to run some backups on my database.

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

host    all             all             70.89.205.250/24        md5
host    all             all             23.21.112.163/24        md5
host    all             all             24.188.1.163/24         md5


texts: Why does selecting top 1 from composite index DESC also used to partition by month not select the top value?

texts:
We have very large database table that I need to select the value of the max id. Given the table size SELECT MAX(id) FROM tableA will not perform well enough.

Table definition (with columns omitted for clarity here)

CREATE TABLE [dbo].[TableA](
    [id] [numeric](19, 0) NOT NULL,
    [timeSampled] [datetime] NOT NULL
 CONSTRAINT [PK_TableA] PRIMARY KEY NONCLUSTERED
(
    [id] DESC,
    [timeSampled] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

The table is partitioned by month on the timesampled, and because we switch partitions out to another table for archiving data the timeSampled column must be included in the Primary Key.

So, the PK is

[id] DESC,
[timeSampled] DESC

The table contains rows for May 2013 and June 2013, and the ids increment in date order (i.e. the max id is in June)

When I select the top 1 id from the table as follows

SELECT top 1 id
FROM dbo.TableA
WITH (INDEX (PK_TableA))

I don't get the max id, instead I get the max id from May (i.e. the very last entry in the May partition).

Any ideas why this is the case? Basically seems to be picking the MAX id from a partition, not max across all partitions


texts: Why in Oracle 11gR2 I can't drop the materialized view with the same user that created it?

texts:
I created a materialized view with the DI_TEST_AL user, let's name it MY_MVIEW. It appears in the USER_OBJECTS table as MATERIALIZED VIEW, I try to drop it, I get a success message, but the object is still there. In fact if I try to re-create it I get an error like "object already exists".

I see that there is a table with the same name belonging to another schema. I assume that should not cause the problem, but I felt like mentioning it.

Here is the SQL*Plus output:

SQL*Plus: Release 11.2.0.1.0 Production on Mon Aug 1 17:01:50 2011

SQL> select object_name, object_type, owner from all_objects where object_name = 'MY_MVIEW';
OBJECT_NAME OBJECT_TYPE       OWNER
----------- ----------------- ----------
MY_MVIEW    MATERIALIZED VIEW DI_TEST_AL
MY_MVIEW    TABLE             DIDEV11

SQL> DROP MATERIALIZED VIEW MY_MVIEW;
Materialized view dropped.

SQL> select object_name, object_type, owner from all_objects where object_name = 'MY_MVIEW';
OBJECT_NAME OBJECT_TYPE       OWNER
----------- ----------------- ----------
MY_MVIEW    MATERIALIZED VIEW DI_TEST_AL
MY_MVIEW    TABLE             DIDEV11

SQL>

I double checked my permission and DROP ALL MATERIALIZED VIEWS is granted to the DI_TEST_AL user.


texts: What is the best identifier for a UserID?  64 bit integers, UUID V5,  or 64 character SHA256 UID?

texts:
I wish to future-proof my application.  What is the best type of identifier to use?

I am considering:

64 bit Integer
UUID
64 character SHA256

I am not developing a distributed system, so is using a UUID necessary?

Also, How good are INTEGER joins vs VARCHAR joins?

Finally, is storing the username (unique) and message data in the same MySQL table better than storing the user id?


texts: SQL Server Database auditing not working

texts:
I'm setting up auditing on a SQL Server Managed Instance in Azure (Version = Microsoft SQL Azure (RTM) - 12.0.2000.8).  Following the instructions in the link, everything worked for server-level auditing.  I created a container called sql-audit-logs and then proceeded to set up everything:

CREATE CREDENTIAL [https://xxx.blob.core.windows.net/sql-audit-logs]
WITH IDENTITY='SHARED ACCESS SIGNATURE',
SECRET = 'sas-token'
GO

USE master
GO

CREATE SERVER AUDIT [myserveraudit]
TO URL ( PATH ='https://xxx.blob.core.windows.net/sql-audit-logs', RETENTION_DAYS = 0)
GO

CREATE SERVER AUDIT SPECIFICATION [myserverauditspec]
FOR SERVER AUDIT [myserveraudit]
    ADD(APPLICATION_ROLE_CHANGE_PASSWORD_GROUP)
    ,ADD(AUDIT_CHANGE_GROUP)
    ,... <excluded for brevity>
WITH ( STATE = ON);
GO

And using Azure Storage Explorer, I started getting log data almost immediately:

I then set up database-level auditing for one of my databases:

USE MyDB
GO

CREATE DATABASE AUDIT SPECIFICATION [mydatabaseauditspec]
FOR SERVER AUDIT [myserveraudit]
    ADD(APPLICATION_ROLE_CHANGE_PASSWORD_GROUP)
    ,ADD(DBCC_GROUP)
    ,ADD(DATABASE_OBJECT_CHANGE_GROUP)
    ,...<excluded for brevity>
WITH ( STATE = ON);
GO

But I'm not seeing any logs being created.  With the DBCC_GROUP setting I should get a log any time someone runs a DBCC on MyDB but despite executing a DBCC, nothing is logged.  I've also tried creating and dropping tables (should be logged because of DATABASE_OBJECT_CHANGE_GROUP) but nothing.

I've checked the tables:

SELECT * FROM sys.database_audit_specifications
SELECT * FROM sys.database_audit_specification_details

And I can see that the specifications I defined in my T-SQL were created and the is_state_enable is 1.  But still no logs.

Am I missing a step somewhere?  I tried turning the server audit off and then on again but that didn't have any effect.


texts: Does SQL Server 2017 CU10 include CVE-2018-8273 hotfix?

texts:
Microsoft released SQL Server 2017 CU10 KB4342123 (14.0.3037.1) yesterday. I tried looking through the Hotfixes included list, but didn't see any reference to the recently released security update for the remote code execution vulnerability hotfix KB4293805 CVE-2018-8273 (14.0.3035.2).

How can we determine if SQL Server 2017 CU10 includes the security hotfix KB4293805 CVE-2018-8273 or not?

Is the higher version number of CU10 enough to determine that?

NOTE: I've already installed the CVE-2018-8273 fix onto CU9.


texts:
I'm confused about the compatibility of Oracle XA with global temporary tables. I've read several places on the internet and in the oracle documentation that say "Distributed transactions are not supported for temporary tables." but it doesn't seem 100% clear whether "not supported" is referring to the creation of a temp table or its usage.

I understand that issuing any DDL will implicitly commit the current transaction with Oracle, and that creating a temporary table is DDL so that can't be done inside an XA transaction. However, does this mean that I can't even insert into an already created temporary table in an XA transaction?

I performed a test inserting and querying a temp table inside an XA (distributed) transaction and it worked fine but I'm afraid to build an application around the results of that one test when the documentation says "not supported"...


texts: How do I identify the source of the 17886 errors ("server will drop the connection") in my SQL errorlog?

texts:
I have a SQL 2012 server that is throwing the following error every 5 minutes or so:

Error: 17886, Severity: 20, State: 1.
The server will drop the connection, because the client driver has sent multiple requests while the session is in single-user mode. This error occurs when a client sends a request to reset the connection while there are batches still running in the session, or when the client sends a request while the session is resetting a connection. Please contact the client driver vendor.

I alert on all Sev 20 errors, so I can't just ignore this.
The good news is that I have some leads on how to fix it (seems like a connection string issue):

At DBA.SE: How to troubleshoot “client driver has sent multiple requests while the session is in single-user mode”
At SO: Error 17886 - The server will drop the connection

In MS forums: Event ID: 17886 Source: MSSQLSERVER (MSSQL Server 2008R2)

The bad news is that I have no idea which application is causing the errors!
The server has over 50 databases, from a wide variety of commercial vendors, contractors, or in-house developers. The error contains no details about which spid or login or hostname or database caused the problem.
I've tried looking at active spids (using sp_WhoIsActive) when the error is occurring, but there is too much activity on the server for anything to stand out (nor do I know what I'm looking for). Same thing with running SQL profiler - without knowing how to filter, I'm just seeing the firehose of data the server handles on an ongoing basis.
I am trying to narrow it down by figuring out which application teams have made recent changes, but that hasn't panned out so far.
Any ideas?


texts: In SQL Server 2008 R2, how would I determine what partition a record currently lives in?


texts: Adding an index without a lag (PostgreSQL)

texts:
PostgreSQL:
Adding an index to a table with big amount of data causes a lag for both SELECT and INSERT operations on that table, doesn't it?
While PostgreSQL does not support live (without lags) adding of indexes, how to solve the problem of adding an index to a table without lagging SELECT and INSERT (there is no UPDATEs on that table)?
My idea is to copy the table by small chunks to another table with the same structure, then in a transaction delete the original table and rename the new table to the original table name. How to ensure that the entire data is copied, while INSERT operations to the old table may happen?


texts: when I try to insert values into my FKs in the DB Browser for SQLite I get the constraints error, but when I run the seeders with knex (npm package) it pass over the FK constraints and it inserts when it should return an error.


texts:
I'm running an index rebuild using Ola Hallengren's script below.

EXECUTE dbo.IndexOptimize
 @Databases = 'insight',
 @FragmentationLow = NULL,
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30,
 @LogToTable = 'Y'

It has been running for almost 2 days. How bad would it be to kill the job? The index it is running against is 78% fragmented. The table at issue has 111,543,114 rows.


texts: Always-On secondary Replica's Read Committed Snapshot Isolation

texts: When you use Readable Secondary Replicas in an SQL Server Availability Group, SQL Server will also use internally Read Committed Snapshot Isolation. When fail-over occurs to a readable secondary replica does the secondary database isolation mode change back to READ COMMITTED as the primary or does it stay Read Committed Snapshot Isolation internally? Finally, if the database isolation mode stayed the same; would it be fair to say if a database supports an Always-On Readable Secondary Replica, then it also has to support RCSI as well?


texts: Using Service Broker internal to a single SQL Express instance?

texts: I understand that SQL Express cannot send Service Broker messages to other instances without a Standard (or better) instance involved in the chain.  But is it possible to use Service Broker to send and receive messages within a single Express instance?  In other words, the queues and services are all owned by (and never leave) the initiating SQL instance.


texts:
I'm confused about the write_location and flush_location of pg_stat_replication view . The document say, write_location stands for last transaction log position written to disk by this standby server , flush_location stands for Last transaction log position flushed to disk by this standby server, is that any difference?

Query pg_stat_replication view on primary database

sent_location :      Last transaction log position sent on this connection
write_location:      Last transaction log position written to disk by this standby server
flush_location:      Last transaction log position flushed to disk by this standby server
replay_location:     Last transaction log position replayed into the database on this standby server

postgres=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid              | 23453
usesysid         | 24619
usename          | repuser
application_name | node1
client_addr      | 192.168.2.37
client_hostname  |
client_port      | 58412
backend_start    | 2015-09-07 20:56:13.141438+08
backend_xmin     |
state            | streaming
sent_location    | 2/3D397B1C
write_location   | 2/3D397B1C
flush_location   | 2/3D397B1C
replay_location  | 2/3D390954
sync_priority    | 1
sync_state       | sync


texts: restarting database while shrink [db] is still running

texts:
I ran a shrink Database command on an SQL Server database to free up about 200GB of space, then mistakenly ran a large query select * from tbl where tbl is large, then tried to cancel that query through SSMS STOP button instead of kill.

SQL Server is now stuck, CPU at max and I can't even log in.

Is it safe to restart an SQL Server that is going through shrink db? Is there a chance I lose data? Or corrupt existing data? I don't think the select is an issue during restarts...


texts:
I would like to do a dump of 10TB db.
I have about 10% of free memmory. Each month I'm generating about 100GB.
Reason for do a dump:
I need to clean data e.g. parse type of some columns and add relations between tables.
My idea was:

Create dump of current data.
Clean it up.
Check if system will be working correctly with new DB.
Replace old DB by new DB

My problem is that I dont have enough place for data duplicates.
There is some other "smart" ways for this process e.g. some dump with compress?
I use POSTGRES 11.8


texts: In a MySQL cursor, will it be possible to skip an iteration or row of the cursor?

texts:
Before asking this question, I was checking if there were any similar questions, but I did not find what I'm looking for. Here we go!.

The problem is that I have a cursor that is crossed, but believe declare a variable that while I run the courses I perform a validation, I would like that when the validation is TRUE, I can jump to the next "row" of the cursor, skipping this "record" so that the following cursor stages do not run.

I know that in the CURSOR I could generate a "sub-query" doing the validation directly, but I would like to be able to learn if it is possible to perform this "jump" of the cursor.

Here is a bit of my code.

DECLARE queryCriterionHasArticle CURSOR FOR
SELECT
  CCLA.CLEG_ID,
  CCLA.ALEG_ID,
  CCLA.CCL_ID,
  CCLA.CCLA_ID,
  CCLAC.CRCA_ID
FROM
  SER_LEGAL.CONFIGURACION_has_CUERPO_LEGAL_has_ARTICULO_CUMPLIMIENTO AS CCLAC
  JOIN SER_LEGAL.CONFIGURACION_has_CUERPO_LEGAL_has_ARTICULO AS CCLA ON CCLA.CCLA_ID = CCLAC.CCLA_ID
  AND CCLA.ENT_ID = companyId
  AND CCLA.CCLA_ESTADO = 1
  AND CCLA.ESA_ID = 1
  AND CCLA.CCLA_TIPO_CLASIFICACION = 1
WHERE
  CCLAC.CRC_ID = criterionId;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET
  noMoreRowsOne = TRUE;
-- ####################################################################################
-- Se comienza a recorrer un cursor que tiene los articulos asociados al criterio
-- ####################################################################################
OPEN queryCriterionHasArticle;
queryCriterionHasArticle_LOOP : LOOP FETCH queryCriterionHasArticle INTO legalBodyId,
legalArticleId,
legalBodyConfigId,
legalArticleConfigId,
criterionConfigId;
IF noMoreRowsOne THEN CLOSE queryCriterionHasArticle;
LEAVE queryCriterionHasArticle_LOOP;
END IF;
-- ####################################################################################
-- 11/06/2019
-- Se procede a agregar una validacion donde no se conside aquellos articulos que tengan
-- una evaluacion en SER_LEGAL.EVALUACION_has_ARTICULO WHERE EHA_TIPO = 1 AND EHA_ORIGEN = 2
-- debido a que estos articulos han sido evaludos con la integracion SISQUIM/ALMACENAMIENTO
-- estos articulos no se pueden modificar desde pre-evaluacion
-- ####################################################################################
SET
  validationIntegrationSisquim = (
    SELECT
      IF(
        COUNT(0) = 1,
        TRUE,
        FALSE
      )
    FROM
      SER_LEGAL.EVALUACION_has_ARTICULO
    WHERE
      CCL_ID = legalBodyConfigId
      AND CCLA_ID = legalArticleConfigId
      AND EHA_TIPO = 1
      AND EHA_ORIGEN = 2
  );
IF (
  validationIntegrationSisquim IS TRUE
) THEN -- ¿¿¿¿¿LEAVE queryCriterionHasArticle_LOOP;?????
END IF;
END LOOP;

I appreciate your help and guides.


texts: Tempdb approaching disk max: Is it OK to wait?

texts:
Today my tempdb pushed up to the edge of the disk allocated. Currently, said disk only has 10 MB left. I'm pretty sure some bad reports run by a colleague earlier in the day pushed up the tempdb to this size.

However, now I've got a tempdb size of 20932 MB and a space available of 20373 MB. So it seems like we've got plenty of space in there to work with. I'd rather wait until the weekend to do a restart and bring it back to a more reasonable size. Am I OK to let this ride, as long as something unusual doesn't happen again?

Working with SQL Server 2012 here. Thanks.


texts: SQL Server using Linked Server to Oracle - table definition not updating with refresh

texts:
Is there some trick for SQL Server Linked Server to update the ODBC connection to a changed table?

SQL Server provides Oracle with a view. Oracle runs a materialized query to basically make a table and refresh the table every 15 minutes. A readonly table.
Yesterday, the SQL Server added a field to the view. The Oracle users can successfully see and use that field.

The Oracle Table shows up in my SQL Server Linked Server. However, after refreshing the Linked Server, refreshing Tables - the new field doesn't show up.
Three other tables that were not changed that use the exact same process (Oracle consumes a SQL Server view and produces a table that is updated every 15 minutes) all work.

In SSMS, right click on the Oracle table and choose Script Table As - Select To - then run the query.

To install ODBC on SQL Server to connect to Oracle, there is a Java service from Oracle running.


texts: Grant access for any logged in user for common database

texts:
In SQL server 2008, I have one database that is a common database that is accessed thru stored procedures in all other databases. Each of the other databases have their own login.

I need a way that all logged in users can access that common database. I know I could create a user for the common database for each login but I would need to create over 100 users in the database then.

Is there a better way?


texts: What's the best way to migrate from mariadb to mysql on ubuntu 18.04

texts:
I've got a server running on ubuntu 18.04.1 with mariadb 10.1. and I have to change to mysql. The reason is that mariadb isn't supported by a product I want to buy. Searching around the internet  I've just  found the opposite way.

Do I have to keep an eye on something special?

Current db-related software:

ispconfig
nextcloud

Any hint will be helpful.


texts: Going against the grain - backing up and truncating the tran log

texts:
As the title states, this is most likely going against best practices but I want to hear some opinions on this.

The database(s) in question are in FULL RECOVERY mode, transaction log backups are being performed every 15 minutes. Maintenance tasks are being performed to Microsoft and Microsoft MVP standards.

We have an ERP system that really uses SQL inefficiently. The database size is relatively manageable, but the log file continuously grows out of control. In the past we've just allocated space to the log file drive in order to control growth. I understand I can limit growth, but our ERP vendor doesn't recommend this approach. Month end processing can make this log almost double in size and make our transaction log backups huge. No problem, right? Well it's a problem as we now have two databases doing this same thing on month end.

To make a long story short, I want to have a discussion on the pros and cons of going against the grain on backing up the transaction log file, then shrinking it to conserve space. I've tackled this processes a couple times out of the year to manage space and get the logs under control but I want to hear some opinions on automating this process to relieve some headaches or to make me think outside the box.

What is recommended in this scenario that I'm not doing already? I'm to the point of offloading transaction log backups every minute to try and further prevent the log from growing but I'm not too hopeful that will solve the problem.


texts: cannot execute as the database principal because the principal does not exist - DB Restoration

texts:
I have received one database backup from one of my client and now I'm trying to restore it into my system.

I tried to search this issue on google and tried most of the suggested solutions but none of them is working. Can anyone please suggest some specific solution for this error after restoration of database?

Screenshot of the Error

Thanks in advance!


texts: Cannot connect to SQL server through SSMS from other machine after IP change

texts:
There was an ip change on the sql server and after that I cannot connect from other machines  ssms but can connect locally .

The firewall is disabled on the server and moreover its listening on port 1433 and all the network protocols are open named pipes, shared memory and tcpip.

TCP/IP properties IPAII - TCP port set to 1433 and TCP Dynamic Ports are blank

Am i missing something?


texts:
Given a query ending in where a and b and c or d, will this behave as where (a and b and c) or d, or where a and b and (c or d)? (Or something else?)

I've read the docs and they seem to suggest it would be the former as and has a higher precedence than or, but my results seem to performing the latter so I'm wondering if I'm either misunderstanding the docs or else I'm not generating the query correctly (via an ORM).


texts: Does import sql dump wipe out current data?

texts:
I have a mysql dump from version 5.5.50. I want to import it into a mysql instance on another machine, running version 5.7.13. I am using the following command to do the import:

mysql -u root -p dbname < /home/dump.sql

I want to be sure that this command will completely overwrite everything on the new mysql instance. That instance already has a database with the same name and some temp data in it.

Or should I manually drop the database on the new instance prior to doing the import to ensure they're completely identical after the import?

Thanks


texts: What is the fastest way to compare polygon geometry data?

texts:
We have two tables with polygons stored in geometry type columns.

I want to fetch the polygons present in one table that are not present in another table.

As of now I am doing a left outer join and using STAsText(), however that is taking a very long time.

FYI, we have approximately 120 million polygons in both tables.

Is there a fast way of comparing geometry type data?  Maybe I need to use the spatial indexing, however I am not aware of this.

SELECT newPolygon.*
FROM table1 newPolygon
    LEFT JOIN table2 oldPolygon
        ON newPolygon.Shape.STAsText() = oldPolygon.Shape.STAsText()
WHERE oldPolygon.Shape IS NULL