texts:
I successfully restored a dump from Postgresql 11 into 9.3.9.
postgres=# drop database ambari
DROP DATABASE
[dumps]$ pg_restore -U postgres  -h db ambari.dump
-- Dumped from database version 11.7
-- Dumped by pg_dump version 11.7
-- Name: ambari; Type: SCHEMA; Schema: -; Owner: ambari
CREATE SCHEMA ambari;
ALTER SCHEMA ambari OWNER TO ambari;
SET default_tablespace = '';
-- Name: adminpermission; Type: TABLE; Schema: ambari; Owner: ambari
CREATE TABLE ambari.adminpermission (
  permission_id bigint NOT NULL,
  permission_name character varying(255) NOT NULL,
  resource_type_id integer NOT NULL,
  permission_label character varying(255),
  principal_id bigint NOT NULL,
  sort_order smallint DEFAULT 1 NOT NULL
);
ALTER TABLE ambari.adminpermission OWNER TO ambari;

etc
-- PostgreSQL database dump complete

When I list the databases, there is no ambari even there was in the original database.
[dumps]$ psql -U postgres  -h db
postgres=# \l
                               List of databases
Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
jiradb7     | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
postgres    | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
template0   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
template1   | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +

I cannot find it between the schemas either:
postgres=# \dn
List of schemas
Name  |  Owner
public | postgres

postgres-# select nspname from pg_catalog.pg_namespace;
  nspname
pg_toast
pg_temp_1
pg_toast_temp_1
pg_catalog
public
information_schema

My question where the ambari schema is?
Update:
[dumps]$ psql -U postgres  -h db
psql (11.7, server 9.3.9)
postgres=# create database ambari;
CREATE DATABASE
[dumps]$ pg_restore -U postgres  -h db -d ambari ambari.dump
pg_restore: [archiver (db)] Error while INITIALIZING:
pg_restore: [archiver (db)] could not execute query: ERROR:  unrecognized configuration parameter "idle_in_transaction_session_timeout"
Command was: SET idle_in_transaction_session_timeout = 0;
pg_restore: [archiver (db)] could not execute query: ERROR:  unrecognized configuration parameter "row_security"
Command was: SET row_security = off;
WARNING: errors ignored on restore: 2

postgres=# \l
                               List of databases
Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
ambari      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(7 rows)

postgres=# \c ambari
psql (9.2.24, server 9.3.9)
You are now connected to database "ambari" as user "postgres".
ambari=# \d
No relations found.
ambari=# \dtvs ambari.*
                List of relations
Schema |             Name              | Type  | Owner
ambari | adminpermission               | table | ambari
ambari | adminprincipal                | table | ambari
ambari | adminprincipaltype            | table | ambari


texts:
I have two MySQL(5.6) databases D1 and D2 on two different machines.
There is a table T1 on D1 and there are two tables T2A and T2B on D2.
I want to update T2B whenever T1 is updated.

T2A is a federated table and T1 is base table of it.
There is an "After Update" trigger on D2 which updates T2B whenever T2A is updated.

When there is an update on T2A, T2B table is updated by trigger.
When there is an update on T1, T2A table is updated by federation but the trigger to update T2B as well is not triggered.

So when there is an update on T1, T2B table is not updated automatically, only T2A is.
How to solve this?


texts: Oracle DB 11gR2 netca installation failing

texts:
I am getting below error when I run netca from command line

cmd: /u01/app/oracle/product/11.2.0/db_1/bin/netca /silent /responsefile=/oracle_binaries/database/response/netca.rsp

[main] [ 2013-05-17 10:52:47.975 IST ] [CmdlineArgs.parseLogParam:290]  Parsing /log argument...
[main] [ 2013-05-17 10:52:47.976 IST ] [CmdlineArgs.parseLogParam:345]  Returning Log File = null
java.lang.ArrayIndexOutOfBoundsException: 2
        at oracle.net.ca.CmdlineArgs.parseArgs(CmdlineArgs.java:416)
        at oracle.net.ca.InitialSetup.<init>(NetCA.java:3969)
        at oracle.net.ca.NetCA.main(NetCA.java:405)


texts: What determine how much time the query will cost?

texts:
I'm developer a system, and the database was not made for me. In the beginning all query of the database are super fast, because i don't have any data. Along the project the database grown, but the performance decreased. When the client access a page of the site, the system will require a page on database. As a user of web-browser, everybody wants that page load as soon as possible. But today this query is take like 7,5 seconds:

So my page will load my article, at last with 7,3 seconds minimum (This query was made with no users accessing the database, only me). For a web-user, this is a eternity.

I really wants know, what exactly causes the PostgreSQL take this time to get my query. I make my research, and some people say the size of the database and the number of rows doesnt matter. I know what's is B* and Hash Table, but i really don't know how to understand why it's taking 7 seconds to make a simple select.

This table has: 5,5 millions of rows. And the database is heavy (For me).

And this is the PC. But i think the problem is not here. (Or maybe yes?)

What i want to understand what delay a query, like a simple select? Is the number of rows? The size of the tables?

 Seq Scan on article_article  (cost=0.00..1151560.85 rows=1 width=14)
 (actual time=6727.343..6733.021 rows=1 loops=1)
    Filter: ((title)::text = 'Brazil'::text)
    Rows Removed by Filter: 5655059
  Planning time: 0.134 ms
  Execution time: 6733.050 ms
  (5 rows)


texts: MySQL Error: impossible to write to binary log

texts:
I have a system (inherited from another development company) and I don't know all its features yet.
Construction:
- web client/application (symfony 1.4)
- DB - MySQL.

Project is deployed on 2 servers:
1-st: nginx server + PHP + DB Master;
2-st: DB Slave.

When data are modificated by web-client at DB Master, DB Slave works good and data have normal replication process.
When data are modificated by MySQL Management Client, I have an notification:

  Cannot execute statement: impossible to write to binary log since
  BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine
  limited to row-based logging. InnoDB is limited to row-logging when
  transaction isolation level is READ COMMITTED or READ UNCOMMITTED

And then, DB Slave falls down and replication process breaks (error insert... duplicate entry...).

The similar notification happens (at this situation - error) with the same text, when I try to insert data with java application/module (it runs inside transaction) and after exception, transaction rolls back and no modification happens.

How can I solve this problem and make system work and make it possible modificate data on DB Master and DB Slaves work in normal mode?


texts:
I'm not DBA at all but I need to do something to synchronize four SQL server databases. I just discover there is a Merge Replication system in SQL Server 2008. At this moment I'm reading the specification and the documentation but it's not clear if this tool is able to do real synchronization or is just merging data from my master database to my slave database. By real synchronization I mean the data is synchronized from my master to my slaves but also from my slaves to my master.

https://technet.microsoft.com/en-us/library/ms151329(v=sql.105).aspx


texts:
I have a request from a customer and I am quite sure the answer is no, but wondering if someone has a different answer.

Background

As you know MySQL installation create a database called "mysql" where it stores the databases we create and also the users.

In the user table, there is a field called "authentication_string" where the user password is saved.

Project

On this project each time a customer creates an account a new database user and database is created.

When a customer logs in through a web interface, the system calls an API to authenticate him/her. After that the root db user is used to connect to customer database, not their own database credentials, why? because they do not want to save user and password on database (This is a temporal solution).

They want to change the application so after authentication/authorization process and they would somehow only needed root credentials to somehow get user and password from "mysql db" and then use them to create the connection using customer db credentials.

Is this possible? Or is there some mysql parent - children configuration where this scenario is possible?

Project uses MySQL 5.7


texts: Techniques for moving database from Enterprise to Standard Editiion

texts:
I have a SQL Server 2012 Enterprise edition and a Model database. On a virtual machine I have installed SQL Server 2012 Standard Edition and want to create a copy of the Model database on it.

I have read about the differences between the editions and it seems that no Enterprise-specific features are used in the Model database (anyway, I need to test the migration to be sure).

Which is the better way to move the database from the Enterprise to the Standard edition:

creating full backup and restoring it
scripting the database as .sql file and executing it


texts: Redundant column to maintain data integrity

texts:
Consider the following example:

Syllabus
-id
-course_id
FOREIGN KEY (course_id) REFERENCES Course(id)

SyllabusItem
-id
-syllabus_id
FOREIGN KEY (syllabus_id) REFERENCES Syllabus(id)

Course
-id

Class
-id
-course_id
-syllabus_item_id
-syllabus_id <--- redundant column
FOREIGN KEY (course_id, syllabus_id) REFERENCES Syllabus (course_id, id)
FOREIGN KEY (syllabus_item_id, syllabus_id) REFERENCES SyllabusItem (id, syllabus_id)

The second foreign key constraint is used to ensure the Syllabus Item belongs to the Syllabus, which belongs to the Course the Class belongs to. But in order to maintain this relationship, we have to add a redundant column syllabus_id to Class.

Is there a more efficient way to achieve data integrity in this example?


texts: Why expdp does not include identified by statement?

texts:
I have the following error when I do a impdp (from a pdb in a 12c to 11g. I also used the parameter version=11.2.0)

IMPDP LOG:

ORA-01938: IDENTIFIED BY must be specified for CREATE USER
Failing sql is:
 CREATE USER "BRELELE" DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP"

But when I've created the user I gave a password for

create user BRELELE identified by manager default tab....

Why is this happeninng?


texts: Interpretation of a column in SQL Server profile trace

texts:
In a trace table generated by Profiler I tried to determine the origin of a lock using the column ObjectID2 joined to sys.allocation_units.

The results are not good. It identifies many tables that are NOT on the query that generated the trace.

Anyone knows what is that column means? My Google-fu failed me today.

The base query I used to find the object (the final one does not have the au.*, p.*
part):

select object_name(p.object_id) AS name, tt.RowNumber, au.*, p.*
from
  Trc_Tables_20131210 tt
  INNER JOIN sys.allocation_units au
    ON tt.ObjectID2 = au.allocation_unit_id  AND tt.ObjectID = 0
  INNER JOIN sys.partitions p
    ON au.container_id = p.hobt_id

WHERE au.type IN (1,3) AND tt.EventClass = 24

EDIT: The system health is a good indication of study. But for now I have to restrain myself to an SQL profiler trace table to study the locks of the query as I don't have easy access to customer's production server.
About the trace: the main event in trace is the Lock:Acquired.


texts: How to take backups on a defined directory instead of creating sub-directories

texts:
I am using Ola-Hallengren scripts and now I have a situation in which I require to take backups without creating sub-directories for each DB. I need to define a centralized path like C:\Backups for all FULL, DIFF and LOG backups.

Any help would be appreciated.


texts: Error: 18456, Severity: 14, State: 5; SSMS > Network sever from PC

texts:
I just completed my first install of SQL as a server based, Enterprise Edition.  I wanted to check it out using SSMS on PC using my Admin Access.  But could not connect.

For security reasons my admin account and my Network ID on my PC are different, so I knew I would not be able to use 'windows authentication', but I tried it as part of my trouble shooting

SQL 2014 install completed
Attempted to access with SSMS (from my PC SQL2012) > Fail
Found log MSSQL12.MSSQLSERVER\MSSQL\Log
Logon match fail NT & Admin accounts

2016-01-05 10:21:54.34 Logon       Error: 18456, Severity: 14, State: 5.
2016-01-05 10:21:54.34 Logon       Login failed for user 'NT1'. Reason: Could not find a login matching the name provided. [CLIENT: IP]
2016-01-05 10:22:29.85 Logon       Error: 18456, Severity: 14, State: 5.
2016-01-05 10:22:29.85 Logon       Login failed for user 'Admin1'. Reason: Could not find a login matching the name provided. [CLIENT: IP]

Validated ‘Admin1’ is a member of the 'AD group with access'
Attempted access with SSMS from the server, as the server (windows authentication Server1) > Granted access
Via SSMS on Server, check security of 'AD group with access'; Server1 > Security > Logins it looks like everything should be good, BUT windows authentication is checked, which leads me to suspect that unless ‘Admin1’ is logged on to a computer with SSMS it will not get access.
Tried using ‘Admin2‘, still failed

2016-01-05 11:09:58.89 Logon       Error: 18456, Severity: 14, State: 5.
2016-01-05 11:09:58.89 Logon       Login failed for user 'Admin2'. Reason: Could not find a login matching the name provided. [CLIENT: IP]

Tried to connect with ‘Server1’ from my PC, still failed

2016-01-05 11:15:53.22 Logon       Error: 18456, Severity: 14, State: 5.
2016-01-05 11:15:53.22 Logon       Login failed for user 'Server1'. Reason: Could not find a login matching the name provided. [CLIENT: IP]

Google leads me Error 18456 error severity 14 state 5 SQL Server 2012 SCCM2012 SP1 which is not the issue
Google leads me to https://sqlblog.org/2011/01/14/troubleshooting-error-18456 which tells me that State: 5 = the login does not exist; currently thinking that means we don’t have a connection to AD.

NOTE: IP's and other specifics of account ID redacted from log files, generic place holders used.

The example is with SQL 2014 install but the problem is not version specific.


texts:
Stored procedure and it is getting delayed. Taking nearly more than half an hour if I call the stored procedure in MySQL command line client. This procedure is to update values in two tables

drop procedure IF EXISTS ALTERTABLEVALUES;
delimiter $$

CREATE PROCEDURE ALTERTABLEVALUES()
BEGIN
DECLARE finished INT DEFAULT 1;
DECLARE total_count INT DEFAULT 1;
DECLARE val varchar(15);
DECLARE i INT DEFAULT 1;
DECLARE table_num INT;
DECLARE table_tab varchar(5);
DECLARE number_cursor CURSOR FOR select outpassNo,tab from tempnumbers;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 0;

drop temporary table if exists tempnumbers;
CREATE TEMPORARY TABLE tempnumbers as
    select  num,'temp' as tab
        from  table1
        where  month(inTime)=2
          and  year(inTime)=2018
        union
     select  num,'vary' as tab
        from  table2
        where  month(inTime)=2
          and  year(inTime)=2018
        order by  outpassNo;

SET val = (select num from tempnumbers limit 1);
SET total_count = (select count(*) from tempnumbers);

OPEN number_cursor;
get_values: LOOP
FETCH number_cursor INTO table_num,table_tab;
IF finished = 0 THEN
 LEAVE get_values;
 END IF;
IF table_tab = 'temp' THEN
    update table1 set table1.num= val where table1.num= table_num;
ELSE
    update table2 set table2.num= val where table2.num = table_num;
END IF;
SET val = val +1;
END LOOP get_values;
CLOSE number_cursor;
DROP TEMPORARY TABLE tempnumbers;
END;
$$
delimiter ;

table1 has the newly inserted data and table2 has the processing values came from table1. A business need is there, where few data from table2 are moved to some other table on a monthly basis, based on some criteria. The expectation is to reorder the numbers column of table1 and table2 as a sequence with the missing values of table2.

Can anyone help on handling resultset without a cursor in stored procedure?


texts: Error while executing PowerShell script using SQL Server Agent Job

texts:
I am executing a PowerShell script using SQL Server Agent job, script code is given below(it is added as is in the Job step 1) :

$OriginFile = "\\vmpr-file1\share3\RT-BI_Shared\ABC.xlsx"
$SourceFile = "\\AZSDV-SSRS1\DASHFiles\ABC.xlsx"
$sCSVFile   = "\\AZSDV-SSRS1\DASHFiles\ABC.csv"
$OrigFileExists = Test-Path $OriginFile
$CSVFileExists = Test-Path $sCSVFile

If ($OrigFileExists -eq $True)
{
    # Move latest excel file Base to Source Location
    Move-item –path $OriginFile –destination $SourceFile -Force
    # If CSV file exists at Source Location, delete it and convert Excel to CSV format
    if ($CSVFileExists -eq $True)
    {
        Remove-item -path $sCSVFile
    }
    # Convert excel file to CSV format
    # Get COM Object
    $Excel = New-Object -ComObject "Excel.Application"
    $ExcelDoc = $Excel.Workbooks.Open($SourceFile)
    $Worksheet = $ExcelDoc.Worksheets.item(1)
    $ExcelDoc.SaveAs($sCSVFile ,[Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSVWindows)
    $ExcelDoc.Close($false)
    $Excel.Quit()
}
Else
{
    Exit
}

SQL Server Job is being executed using SQL Admin role, but I am getting below error when executing the job :

The process could not be created for step 1 of job 0xF7D5C86B5CB7AC4885A7639124BA5E7A (reason: The system cannot find the file specified).  The step failed.

The error message is very generic unable to find out the issue, can someone help resolving this error.


texts:
I have two tables in MySQL:

The orders table

CREATE TABLE `orders` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `transaction_id` varchar(20) COLLATE utf8_unicode_ci DEFAULT NULL,
  `task_id` int(11) NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

And the tasks table

CREATE TABLE `tasks` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `content` text COLLATE utf8_unicode_ci NOT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

To create a task, a credit card transaction must be made, hence I recieve a transaction.

When the task is complete, I'd like to settle the transaction amount and create another order.

This will, for every completed task, give me two order lines. I would like to link orders to tasks, as the history for those orders needs to be documented.

My problem is that I can't figure out how to normalize the transaction id. If I should refund the task transaction at some point, my only option is to refer to an order entry WHERE task_id = :id AND transaction_id IS NOT NULL

This just seems wrong to me, as I potentionally will have three or four orders per task with add-on sales, if a task cannot be completed within requirements.

How should I handle this transaction_id so I can settle that specific transaction when a task is completed? I was thinking of adding transaction_id to the tasks table too, but that would be bad normalization.


texts: Searching for and parsing out specific text parts within the values of a column

texts:
I'm working on a CASE expression that I need to have in order to search for, and parse out, specific text values.

Sometimes the relevant column will contain a person's name, and sometimes it will contain a city AND a two digit state abbreviation. I need to search in the column and determine if it contains a city and state, and if it does then I need to parse out the two character state abbreviation. For example, the column will contain '[PHOENIX, AZ.]'.

I currently have:

 CASE
      WHEN RIGHT(RTRIM(cu.address),2) IN('AL','AK','AZ', etc...)
      THEN RIGHT(RTRIM(cu.address),2)

The problem I'm running into, is that sometimes there's a '.' (period) either before or after the two digit state abbreviation.

Rather than write a bunch of different code that would account for every type of anomaly, I need something that will account for everything.


texts:
I found that packages on a 2012 SQL Server could only be accessed using an older version of SSIS, and when digging the subject if found that I should perhaps update those packages, and that there are probably some obsolete packages in there.

My questions is: Can I somehow make sure that those in the SERV-XXX folder will upgraded safely ? (they are are simple exports from a view to a local CSV or XLS or TXT).


texts: How can I check index fragmentation in the quickest way possible?

texts:
Checking index fragmentation in my database seems unruly slow. Regardless if I use the DMV sys.dm_db_index_physical_stats (for a specific database, table, or even index) or if I use the SSMS Index Properties window to look at Fragmentation on a specific index, it takes a really long time.

For example, using the Index Properties window will take upwards of 5 minutes to open up for a single index on my largest (~20 billion rows) table.

I do want to push to implement partitioning but until then I have to support an existing index maintenance job and I'm not sure how we can even check index fragmentation when one index on our heaviest table takes about 5 minutes to analyze. (Each of our tables has at least a few indexes.)

Here's a case where it took so long in the Index Properties window that I think it timed out and returned nothing in window:


texts: Transactional Replication between SQL Server 2017(Publisher+Distributor) and SQL Server 2019(Subscriber)

texts:
So here's the replication scenario that we need to set up.
At organization A, We have SQL Server 2017 which is both the publisher and a distributor.
At organization B, on a remote server, we have a SQL Server 2019 which needs to be a subscriber.
When we are trying to set up the transactional replication between these two servers and while adding the subscriber, it throws this error: The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication.
According to the Replication compatibility matrix, it looks like it is supposed to be working fine, but then we cant seem to understand why this is still failing.

Similar Stack Question that I have referred to
Another link that talks about Microsoft Replication between two different versions of SQL servers


texts: Insert All Columns from one table into another table MYSQL

texts:
I am trying to insert multiple columns with their data from one table into another table, without a common column (i would expect a cross product in the result). I am not sure how that's possible, because each of the two tables have varied number of columns, which rules out UNION on them. I tried a Create..Select to create a 3rd table with all columns from both the tables but unsure about the syntax (keeps throwing syntax error).

Both Tables' syntax:

CREATE TABLE `t_modul` (
  `ID` int(11) NOT NULL,
  `Seriennummer` int(11) DEFAULT NULL,
  `Bezeichnung` varchar(128) DEFAULT NULL,
  `StandortID` int(11) NOT NULL,
  `Firmwareversion` varchar(8) DEFAULT NULL,
  `Optionen` int(11) DEFAULT NULL,
  `Feldstärke` int(11) DEFAULT NULL,
  `Platziert` bit(1) DEFAULT NULL,
  `Sendeintervall` int(11) DEFAULT NULL,
  `Batteriekapazitaet` int(11) DEFAULT NULL,
  `Modulzusatzinfo` varchar(255) DEFAULT NULL,
  `ModulUserinfo` varchar(128) DEFAULT NULL,
  `ReadOutDate` varchar(40) DEFAULT NULL,
  `ReduktionsIntervall` int(11) DEFAULT NULL,
  `Startdatum` varchar(40) DEFAULT NULL,
  `SAMTemperaturEin` double DEFAULT NULL,
  `SAMTemperaturAus` double DEFAULT NULL,
  `SAMCoolingOff` int(11) DEFAULT NULL,
  `SAMCoolingOn` int(11) DEFAULT NULL,
  `SAMCoolingOffMax` double DEFAULT NULL,
  `SAMCoolingOnMin` double DEFAULT NULL,
  `SAMParameterField` int(11) DEFAULT NULL,
  `SAMIsAlarmMelder` bit(1) DEFAULT NULL,
  `SAMNv27` int(11) DEFAULT NULL,
  `Hardwarekonfiguration` int(11) DEFAULT NULL,
  `Anwendungskonfiguration` int(11) DEFAULT NULL,
  `Logkonfiguration` int(11) DEFAULT NULL,
  `Archivsendezeit_0` int(11) DEFAULT NULL,
  `Archivsendezeit_1` int(11) DEFAULT NULL,
  `IsLogger` bit(1) DEFAULT NULL,
  `Datenpunktkennung` varchar(128) DEFAULT NULL,
  `TTRFtpARCHPollDoneDate` varchar(40) DEFAULT NULL,
  `Repeaternummer` int(11) DEFAULT NULL,
  `StammdatenID` int(11) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `ID_Standort_idx` (`StandortID`),
  CONSTRAINT `ID_StandOrt` FOREIGN KEY (`StandortID`) REFERENCES `t_standorte` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE `sensors` (
  `sensorID` bigint(11) NOT NULL,
  `Number` bigint(20) unsigned NOT NULL,
  `ValueIDs` text NOT NULL,
  `MeasureTime` datetime NOT NULL,
  `StartTime` datetime NOT NULL,
  `Configuration` int(10) unsigned NOT NULL,
  `LogCycle` int(11) NOT NULL,
  `Alarms` text NOT NULL,
  `AlarmType` int(11) NOT NULL,
  PRIMARY KEY (`GesySenseID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

UNION query:

CREATE TABLE sensors_1
  SELECT * FROM `sensors`
    UNION
  SELECT * FROM `t_modul`;


texts: SQL - Find Results with MAXimum for given colum

texts:
I have a question on how to select best match in PostgreSQL for a given result/condition:

I have a setup containing 3 tables.
1 is a result overview table with reliability and status|
2 is a detailed segments overview with details per segment
3 is a general table with information on the targets.

Goal is to map segments onto targets based on results from table 1

Setup:

table1: Results_table

DROP TABLE IF EXISTS result_fcd;
CREATE TABLE result_fcd
(
    source_geometry_id integer NOT NULL,
    reliability double precision NOT NULL,
    status text COLLATE pg_catalog."default" NOT NULL,
    geometry geometry(LineString,4326) NOT NULL,
    CONSTRAINT result_fcd_pkey PRIMARY KEY (source_geometry_id)
);

table2: segments (links to originals)

DROP TABLE IF EXISTS  segments;
CREATE TABLE segments
(
    source_geometry_id integer NOT NULL,
    index integer NOT NULL,
    target_link_id integer NOT NULL,
    reversed_target_link boolean NOT NULL,
    start_fraction double precision NOT NULL,
    end_fraction double precision NOT NULL,
    geometry geometry(LineString,4326) NOT NULL,
    CONSTRAINT segments_pkey PRIMARY KEY (source_geometry_id, index)
);

table 3: Originals

DROP TABLE IF EXISTS originals;
CREATE TABLE originals
(
    gid integer NOT NULL DEFAULT nextval('originals_gid_seq'::regclass),
    linknr double precision,
    name character varying(50) COLLATE pg_catalog."default",
    direction double precision,
    length numeric,
    anode double precision,
    bnode double precision,
    namenr double precision,
    geom geometry(LineString,28992),
    CONSTRAINT originals_pkey PRIMARY KEY (gid)
)

Example of output (simple left join)

SELECT target_link_id,reversed_target_link,segs.source_geometry_id,reliability
FROM segments segs
LEFT JOIN result_fcd res ON res.source_geometry_id = segs.source_geometry_id
WHERE reliability > 80
AND status = 'match'
LIMIT 10

results:

target_link_id  reversed_target_link    source_geometry_id  reliability
750 true    6156655 "96.90882243480968"
750 true    6156654 "96.3385845004076"
759 false   299963  "97.88820671302537"
759 false   299962  "90.81686447596023"
759 false   4339973 "83.83639328022115"
981 true    304625  "94.25089913251661"
981 true    304624  "96.521137788089"
981 true    304626  "94.21941014522639"
981 true    304621  "91.93785338486143"
981 true    304622  "95.27831743865612"

now the goal is to merge the results into:

750 true    6156655 "96.90882243480968"
-- 750  true    6156654 "96.3385845004076"
759 false   299963  "97.88820671302537"
-- 759  false   299962  "90.81686447596023"
-- 759  false   4339973 "83.83639328022115"
-- 981  true    304625  "94.25089913251661"
981 true    304624  "96.521137788089"
-- 981  true    304626  "94.21941014522639"
-- 981  true    304621  "91.93785338486143"
-- 981  true    304622  "95.27831743865612"

I tried using a MAX(reliability) and grouping by target_link_id,reversed_target_link,source_geometry_id but that did not really get the results.

Essentially i need the best performing source_geometry_id (max reliability) for each target_link_id

Anyone able to assist with this?


texts:
I'm analyzing a database of login records for a university and I'm trying to write a query that will give me an idea as to how many users are using the labs for short bursts, verse a more extended stay.

I have the query below query, which tells me how many times each user has used a lab for less than or equal to 10 minutes.  What I want is a result that tells me this and their total number of logins.

select username, count(*) as "<= 10 min"
    from logins
    where duration<="600"
    group by username
    order by username asc
    limit 10;

Example desired output:

+----------+----------+----------+
| username | <=10 Min |  total   |
+----------+----------+----------+
| user1    |        4 |        7 |
| user2    |       11 |       22 |
| user3    |        1 |        3 |
| user4    |        4 |        8 |
+----------+----------+----------+


texts: Get distinct count on calculated member?

texts:
I'm using SSAS and created a measure group that is linked to Fact_SalesMetrics on SQL Server. In that, I have seven measures. All measures have aggregation of Sum except Sales Invoice which has aggregation of Distinct Count.

Everything works great. I need to make the aggregation of 'CustomerID' also to Distinct count, but I can't be able to. It says me error states, "The measure group has more than one distinct count measure."

So, I need to have these two measures's aggregation as DistintCount. How can I? Any help will be appreciated.

I thought to create a calculated member to achieve this but got failed as I don't know what expression I have to give!


texts: Read MS Sql query high latency w.r.t. Remote Query

texts:
I'm trying to read a table in a MS SQL Server using python, specifically SQLalchemy, pymssql, and pandas.read_sql. I want to execute the query, put the results into a pandas Dataframe, and continue on with my work. The sql database I'm accessing is a part of the Wonderware product line, is composed of more than 700 tags over a time period of 2 years.

I want to be able to read a simple query such as the following:

sql_query = '''
SELECT DateTime, TagName, Value

    FROM Runtime.dbo.AnalogHistory

    WHERE
        DateTime BETWEEN '2014-05-26 00:00' AND '2014-06-26 00:00'
    AND
        TagName IN (
            'dPC503.ActualValue',
            'dPC504.ActualValue')
    AND
        wwRetrievalMode = 'Cyclic'
    AND
        wwResolution = 5000
'''

The query results in a dataframe object about 100 MB in size and takes a little under 10 min (423 s)

If my math is correct, 100 MB in 423 seconds is about 230 kB/s, which I think over an Ethernet connection is just painfully slow. The first question I have is, where is this speed going?

I posted a related question here on stackexchange.com. Someone commented on my post and told me to look into the Execution plan - I'm just a beginner and have no clue what to do with this information.

Running the query on a desktop with SSMS installed resulted in the same query runtime. After looking into it, I see that the execution plan is broken into 3 parts, heavily favoring the remote query:

SELECT - Cost: 0%
Compute Scalar - Cost: 0%
Remote Query - Cost: 100%

Opening up the Remote Query execution plan into an xml file results in this file snippet.

I'm not sure what this is telling me. The gentlemen who kindly commented on my previous question and asked/gave me some helpful questions/pointers that I hope someone here would be able to expand on. The following are from him, and my follow up questions are in italics:

Which indices are defined for the Runtime.dbo.AnalogHistory table and the execution plan for this query? How do I determine this?
I think when you executed exactly the same query a few times the vast majority of the data pages needed for the result set were already in the MS SQL Server buffer pool and because of that 100% of the cost is in the final 'Remote Query' step, where it 'Sends a sql query to another than the current sql server', otherwise it would have to read data from disk, which is usually the slowest part (unless you have an extremely slow network). So, anyway you would have to check the execution plan. Where in the execution plan is this information hiding?


texts: We are having an ETL process which inserts lots of data into tables. This database is set to Simple Recovery Model and the transaction log is growing a lot. I was thinking that would it help to set this database into Bulk-Logged Recovery Model? We are taking full backups on daily basis. So is there some operations which are not logged in Bulk-Logged Recovery Model compared to Simple Recovery Model?


texts: What's the maximum number of characters you can copy from a cell

texts:
If you ctrl + c a cell how many characters do you actually copy?
I'm getting 43679 I was wondering if this is the limit of ctrl c? is this configurable? the real length of the cell is around 160k

varchar(max) column
MS SQL 2016 ver 13.0.16106.4
Query Results -> SQL Server -> Results to Grid -> default 65k and 2mb xml


texts: is there a way to know all filters applied to one publication with query (SQL Server 2014)?

texts: I'm looking for a way to get all filters applied to all my publication... I know that I can right click properties -> Filter Rows but is there a way to view them with a query?


texts:
I just connect to SQL Server and invoke the following command:

select * from sys.sysrscols

The result table contain a field hbcolid, which seem to be identical to rscolid. I think rscolid is surely for rowset column id. Then what is the meaning and usage of hbcolid?

Is there any document on this?


texts: SQL Server: Do Statistics Updates cause query plans to be flushed? Should they?

texts:
I've found that a big, nasty data-extraction query that runs daily needs updated stats to avoid making horrible query plans based on incorrect rowcount estimates (let's not worry about whether or not my stats should be updating automatically).

My question, as noted in the title, is: Should I be concerned about incorrect query plans sticking around if a query plan happens to be prepared before a given set of statistics is updated, at a time when the desisions made by the optimizer turned out to be wrong?

Or to stats updates automatically cause dependent query plans to be flushed?

If the plans stick around, is there a way to figure out which plans depend on a given index's statistics? (I know, I could go digging in the DMV docs, just hoping someone already has the answer)


texts:
I have a table with 2 columns, name and related_name.

CREATE TABLE _test(
[name] NVARCHAR(50),
related_name NVARCHAR(50)
)
GO

INSERT INTO dbo._test
([name], related_name)
VALUES

('a', 'b'),
('b', 'c'),
('c', 'd'),
('e', 'f'),
('f', 'g')

I want to write a UDF, which will get a name and will return all the related names. If a is related with b and b is related with c this means that a is related with c.

So the expected result for parameter 'a', 'b', 'c', 'd' must be the same.

select * from my_func('a')

The result must be

result
------
a
b
c
d

And the result for

select * from my_func('e')

must be

result
------
e
f
g


texts:
I'm new to AWS Redshift and I wanted to know How i can implement my use-case.

I have a Redshift table which gets lot of Inserts/Selects.
In some cases I need to delete some data from that table. I don't want my Delete to impact other writes and reads.

Can someone suggest what is the best locking mode available in Redshift to implement my use case.

I don't want to end up with dadlocks and some locks hanging in my application.

I tried without locks and I'm observing the following violation:

Serializable isolation violation on table - 156300, transactions forming the cycle are: 6416378, 6416913 (pid:3770);

Also I went through the Redshift cxplicit locks but couldn't find any Official documentation for the same.

I know that Redshift is a wrapper on PostgreSQL, so can I use that documentation for implementing?


texts: Power BI report server: Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' when trying to view a report

texts:
We have SQL Server 2019 and Power BI Report Server installed on the same host. we're using a virtual service account for Power BI Report Server. We get the following error when trying to view a report:

An error has occurred during report processing. (rsProcessingAborted)
Cannot create a connection to data source 'DataSource1'.
(rsErrorOpeningConnection)    Login failed for user 'NT
AUTHORITY\ANONYMOUS LOGON'.

We are trying to migrate and upgrade to SQL Server 2019 and Power BI Report Server on Windows Server 2019 from SQL Server 2014 and SSRS on Windows 2012. SQL Server 2019 has been set up on the new host and database schemas have been migrated over and Power BI Report Server has been installed (same host as the new SQL Server). We can access the database using SSMS without any issues. We are able to log in to the reports URL (https://hostname/reports) and can see that there are some reports that have been created. When we try to view a report, we get the error I mentioned above.
We are using a virtual service account for Power BI Report Server. As mentioned, Power BI Report Server and SQL Server are on the same host.
For Data source properties > Credentials > Log into the data source
If we set it to "As the user viewing the report" and click the "Test connection" button, it says "Connected successfully"
Also tried setting it to "By prompting the user viewing the report for credentials" with "Type of credentials" set to "Windows username and password", but it does not prompt for credentials when trying to view a report.
In both cases we get the "Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' " error.
web.config has
    <authentication mode="Windows" />
    <identity impersonate="true" />

rsreportserver.config has
        <AuthenticationTypes>
            <RSWindowsNTLM/>
        </AuthenticationTypes>

We have also tried RSWindowsBasic and RSWindowsNegotiate, but they did not work.
We have 2 other servers where SQL Server and SSRS (not Power BI Report server) are installed and working. web.config and rsreportserver.config have the same values as above. I can't find any difference in the configs.
Some posts and documentation mention setting up SPNs, but as I understand it, SPNs are only needed if you use a domain account for the service account and not needed if you use the virtual service account. Maybe I misunderstood? Also read the posts about it possibly being a double hop issue, but the solution there seems to be to set up SPNs, but I'm not sure how to do that for the virtual service account.
Two of the docs I referred to:
Configure Windows Authentication on the Report Server
Specify Credential and Connection Information for Report Data Sources


texts:
I want to sort the rows inside the json_agg function.

I found this:
PostgreSQL : select columns inside json_agg

Which gave me a solution to selecting specific columns for json_agg, but now I want to give the aggregated data a specific order. The "distinct" does work the "order by" does not.

json_agg(DISTINCT (colA, colB))

This does not:

json_agg(DISTINCT (colA, colB) ORDER BY colA ASC, colB ASC)

Is there a way to sort the aggregated data?


texts:
I have:

SQL Server 2014 (default instance )is on a Win Server 2012 machine.
SQL Server Management Studio (v17.7) is on a Win 7 machine.
Both machines are on the same domain.
I'm logged into the Win 7 machine as a domain admin.
My domain admin account has SQL Server Security sysadmin
access.

On the Win 7 machine, I bring up SSMS and I connect OK to the DB that is on the Win Server 2012 machine. However, on SSMS, when I do Tools -> SQL Server Profiler, I get a dialog "Profile Application has stopped working". The event log shows the following which seems weird because I would expect Profiler.exe to be run on the remote Win Server 2012 DB machine

Why does SQL Server Profiler crash when its run against a DB machine from a client machine?

Faulting application name: Profiler.exe, version: 2017.140.17254.0, time stamp: 0x5ae98b94
Faulting module name: unknown, version: 0.0.0.0, time stamp: 0x00000000
Exception code: 0xc0000005
Fault offset: 0x75bb48cb
Faulting process id: 0x4e0
Faulting application start time: 0x01d42da026f3a3d9
Faulting application path: C:\Program Files (x86)\Microsoft SQL Server\140\Tools\Binn\Profiler.exe
Faulting module path: unknown
Report Id: 652f0139-9993-11e8-9f5b-327e82b70454


texts:
I'm working in a project, where the client is afraid their own employees (ie.: junior I.T. guys who the client does not trust) querying the database to get sensitive information.

The database is a new SQL Server database, so I have some kind of freedom.

Googling a bit, I landed here at SQL Server Transparent Data Encryption, and wondered if that what I need, and if it is not...

What are the best practices to encrypt all the columns, in all the tables, in a database in order to prevent users for querying the database?


texts:
I'm trying to optimize my Postgres 9.2 database to speed up queries with date restrictions.

I have a timestamp column, but mostly I'm asking for some day, so I have created an index with timestamp to date parsing:

CREATE INDEX foo_my_timestamp_idx
ON foo
USING btree
((my_timestamp::date) DESC);

Now, to increase a performance I CLUSTER foo table using index above:

CLUSTER foo USING foo_my_timestamp_idx;

According to the manual on SQL-CLUSTER, the table

  is physically reordered based on the index information

I wonder whether there is an impact on performance for other queries using a PK of table (let say id_foo). Are there any downsides?


texts: pg_dump inserting C source code in dump file

texts:
Am backing up daily my DB with pg_dump. The strange issue is that when I try to restore it with psql, errors are thrown. After opening the dump file with vim and analyzing the line that is causing the error, it is clear that there is some C source code being written to the file. Not only one, but chunks of them.

How could this happen? Any way to avoid it?

The dump is done through the postgresql webmin module.

I'm using PostgreSQL 9.3.


texts: How to use composite index in range query in mysql

texts:
I have a table：

create table test (
   field1 bigint,
   field2 bigint
);

alter table test
add index field1_field2 (field1, field2)

Here is a sql :

select *
from test
    force index(field1_field2)
where field1 > 100
  and field2 = 2
limit 200 ;

and it doesn't run very well. I Explained it and found that only field1 condition used by index query, field2 condition was ignored.

I want know why, and I need a perfect answer.

There are lots of data with field1 > 100 and lots of data with field2 = 2.


texts: SQL server compress backup for lower version databases

texts:
Recently our development team migrated couple of sql server 2000 Databases onto SQL2K8R2.

Now we creating the backup strategy, and just had a doubt, that:

1) Compatibility level  of those DB's still show 80, we asked the team if that can be changed to current , they had no clue. I searched on web, but could not find any such stuff which clarifies on what point should we or should we not change the compatibility level of the migrated db's.

2) For  the Migrated DB's having compatibly 80, are we good to take compressed backups? I am just curious because if we try to restore on the same or upper version , it might not have any conflict.?

Please advice, thanks!


texts: how to search for a table across catalogs of informix database?

texts:
I would like find schema in which a table exists in Informix database.

I know that we can use below query. But that searches only with a schema.

select tabname from "informix".systables where tabid > 99 ;


texts: How to compress a non-shrinkable table with nvarchar(max) fields?

texts:
We have a serious problem which was on our minds for pretty much two weeks already. We have a big database (~250 GB, MS SQL Server 2014) and we needed to shrink it somehow, since our SSD is almost full. Quick analysis showed that 1) there is almost no free space available in the database for shrinking operation, 2) we have two very big tables (~95 GB, ~5M entries each) in the database. The database is being used by external software (ERP system) and we had everything we needed to clean these tables from software side (these are tables are being used by internal journals and old entries are not necessary), so we did just that, which resulted in ~2,7M entries being deleted (and they actually were deleted, which was confirmed by a query).
But there is the problem - the actual size of the tables did not change. The table with 2,3M entries still use about ~94 GB of disk space, almost exactly as it was when there were 5M entries. The amount of detected free space for shrinking (both DB and files) also didn't change. I restored database from fresh backup on another server, and tried to rebuild, and then delete and restore clustered index, but later learned that it wouldn't work, since the table has two nvarchar(max) fields (these fields store XML data and internal statuses as strings). I also tried to execute the following query as an experiment:
use uvs_experiment;

select * into _InfoRg3380Tmp from _InfoRg3380 where 1=2;

truncate table _InfoRg3380Tmp;

insert into _InfoRg3380Tmp select * from _InfoRg3380 where _Fld3382RRef = 0x80C7A4BF011CE3C311E847800C55C223;

This query was an attempt to create a table which would store entries with the only one, actually the most meaningful status. And the result... was the same. The new target table still use about 90GB of space, with ~1,3M entries. Please note that almost all the entries in the table have some data (sometimes heavy) in nvarchar(max) fields, excluded entries is not an exception, i.e. it wasn't blank entries which was deleted earlier or excluded by this query.

Just what is happening here and how we can free some space? I understand that this is some really strange behaviour of SQL Server regarding how it handles pages with nvarchar(max) fields, but we need a solution (aside from moving database to another server). Unfortunately, GZip compression from SQL Server 2016+ is not an option here.


texts: 000 Unable to connect to server'(local)'; SQLServerAgent cannot start SQL 2008R2 WinServ 2012R2

texts:
The SQL Server Agent will not automatically start whenever the server restarts and has to be manually started I get the error from the SQL Log File Viewer along with these other errors. I know almost nothing about SQL so have been unable to really trouble shoot other than running a SQL repair. Not sure what else to look at, the service is run using the Local System Account.  SQL is running on the DC.
298] SQLServer Error: 848, SQL Server Network Interfaces: The system cannot contact a domain controller to service the authentication request. Please try again later. [SQLSTATE HY000]
[298] SQLServer Error: 848, Cannot generate SSPI context [SQLSTATE HY000]
Any help would be much appreciated.


texts:
I have a third party database in which a large number of the tables have a 'companyID' column, and most of the queries against those tables include companyID = @companyID in the where clause.

[EDIT: In addition to the where clause, the companyID is generally also used in the order by clause of most queries: e.g. order by companyId, otherField1, otherField2 etc..
]

In our organisation the companyID column is always populated with 1 (though there is an outside chance that may change at some point in the future).

Now I am looking at creating some indexes on these tables.  SQL Server recommends some 'missing indexes' and all the recommendations contain the companyID field as the first column in the index.

Going on the principle of specifying most selective column first I would think that I should put the companyID column last, or perhaps not even have it in the index at all.  But then I'm wondering why SQL Server's missing indexes view always suggests putting it first - surely SQL Server is intelligent enough to know that every row contains the same value for that column?


texts: Can a query write to the binary log BEFORE it has finished executing?

texts:
I have a Database which is a Slave and a Master. e.g. it replicates data from our main site, and then onwards to a set of slave databases. (this is mainly a test facility so not monitored out of hours).

On Sunday morning at 7:55 it started executing an INSERT query. This is a standard query that executes thousands of times a day. Its a single query, not part of a transaction. There are no triggers on that table. This table (and all others) are INNODB.

But, on this occasion it got stuck. When I came in the office today it had been running for 24 hours (apparently). And replication was a day behind.

However, when I looked in the Binary logs it showed as COMMITed.

#160814  7:55:25 server id 21  end_log_pos 103319397    Query   thread_id=635785334 exec_time=4294967294    error_code=0
SET TIMESTAMP=1471157725/*!*/;
INSERT INTO tablename . . .
/*!*/;
# at 103319397
#160814  7:55:25 server id 21  end_log_pos 103319424    Xid = 284047583
COMMIT/*!*/;
# at 103319424

I checked the Slave databases, and it was on present on there as you'd expect.

I have a monitoring system which records (amongst other things) the processlist, and it shows that for that INSERT query it was perpetually stuck at STATE = UPDATE.

I also checked the slow query log to see if it was in there, and it wasn't.

My understanding was that a query couldn't be written to the binary log until it was commmited on the master, so can anyone explain what has happenned here?

There was a knock on affect in that once out test system tried to connect at 8:38 this morning, the first SELECT query also got stuck, and then it refused all connetions. So I was forced to restart the database.
At which point it ran crash recovery, and carried on as if nothing had happenned.

It is MySQL 5.5.47 and using Statement Based Replication.


texts: Data Lake : Data Catalog and Data Schema definitions

texts:
I'm trying to understand the definition of data catalog and data schema. After several articles I read, I'm getting even more confused!

I realized when the article is talking about data lake, the term will be used to describe data catalog as containing metadata information within data lake. As obvious as the name, it is a catalog of all information within data lake it self.

Now, as far as I know, data catalog is database. Whereas data schema is described as containing metadata information within a database. Which means data catalog in data lake is equal to data schema in database.
Both seems to have the same purpose such as data discovery.

EXCEPT the terms are used in different level. One for data lake and the other one is database.

My questions are :

Isn't the terms used inconsistent and confusing? What is the right term I should be using for each level?
If these two are different, how are they different? Please enlighten me..


texts:
I have a query white an Outer Apply which is consuming 100% cpu immediately.
Running the 2 separate queries individually gives immediate results.

select s1.correlationid, s1.ucc_id, s1.starttime, s2.concurrentcalls
INTO #Con
from ucc_sessionsummary as s1
      OUTER APPLY (select count(distinct correlationid) concurrentcalls from UCC_SessionSummary as s where s1.starttime between s.starttime and s.endtime) as s2
where s1.starttime between @Starttime and @Endtime

I have upladed the plan, can somebody help?
https://www.brentozar.com/pastetheplan/?id=BJ6Pud4bL

Table definition:


texts: In a banking organization, why would account balance be declared as varchar and not decimal?

texts:
I worked in two organizations where I found money related columns are defined as VARCHAR data type. I didn't get correct explanation from anyone. As the database team is totally isolated with our team.

Is there any specific purpose for defining the amount, account balance, money in varchar datatype?


texts: SQL Server - Log Shipping Into an AlwaysOn Availability Group

texts:
I have a requirement to build an AlwaysOn Availability Group, which I have done.  Now the user wants me to set up log shipping from another server into the AlwaysOn Availability Group.  I’m not sure how to approach this or if it is even possible.

The AlwaysOn Availability Group is on SQL Server 2012 SP2, running on a Windows 2012 R2 cluster.  The source of the log shipping is SQL Server 2008 R2 SP2, running on Windows 2008 R2.

What is happening is that we are converting from the SS 2008 database to SS 2012 on new hardware.  The customer wants the SS 2012 to be AlwaysOn for HA.  They also want to be able to set it all up in advance and migrate the data from the SS 2008 database in as little time as possible, which is why they asked for log shipping.  What I suggested was to build the SS 2012 without AlwaysOn, do log shipping from the SS 2008, then set up AlwaysOn at cutover time, but there is still an outage as it builds the secondary and they don't like that, either.
Does anyone know of a quick way to copy the data from the SS 2008 database to the SS 2012 database, with AlwaysOn already running?  I'm getting a lot of pressure to make this happen with very little downtime.


texts: Long/complex INSERT and UPDATE query parser or visual editor/query builder

texts:
I have long INSERT queries that insert  values to table with over 50 columns. Is there any tool to parse this long query to key-value representation?

like:

INSERT INTO sometable(col1, col2, col3) VALUES (1,2,3)

parse to:

col1: 1
col2: 2
col3: 3

So it would be easier to analyze.


texts: Can I restore the defult SQL Server Public Role?

texts:
I setup a new user login and attached it to a specific database on my server. I then opened up management studio on my local pc, connected to the database using the server IP and selecting the database and everything worked great!

Then I went messing with the default SQL server Public server role as I wanted only this one database to be shown to the user - not the full list of all our databases on the server. I removed the "connect" option from TSQL Default TCP/TSQL Default VIA/TSQL Local Machine/TSQL Named Pipes. This obviously broke what I had so I tried to re-add these settings but now every time I open the properties tab on the Public server role I see a warning message telling me:

Value does not fall within the expected range (SqlMgmt)

If I try to connect to my database on my local pc, it will connect but just see "System Databases - master and tempdb" and "Database Snapshots" which is empty. I don't see the actual database I mapped the login to.

Is there anyway to either restore the Public role to the default settings or a way to get my database to to actually display in management studio when I connect.

Thanks,
Rich


texts:
Considering performance only, can a MySQL Cluster beat a custom data sharding MySQL solution? sharding = horizontal partitioning

When I refer to sharding, I'm considering sharding made in the application layer, for instance, distributing records evenly across independent MySQL instances. For two servers, it could be (key mod 2).


texts:
Currently we have one SQL Server 2008 R2 database server in location X. All application users are in location X. All instances of the application can add data to database table and which may be requested by other users at the same time.

Now few of our users moved to location Y and they are facing lag in accessing data from location X database.

I'd like to create a replica of database X and put in location Y and keep them in sync. Updates in location Y would be available to location X database too and vis-versa. What is the best way to keep both databases in sync and reduce the access lag?

I am new to database management. Please feel free to suggest some articles or reading.


texts: I am fairly new to the whole DBA scene. With that said, i was able to get an ODBC connection established within Microsoft SQL Server 2012 to be able to access a database for a library. Now what I am trying to do is create queries to be able to manipulate that said DB within Microsoft SQL Server but am having trouble finding ways to be able to do so. Can someone please help teach me how to call upon that Linked Server DB to be able to manipulate it as I see fit. Thank you in advance.


texts: How to detach corrupt database in SQL Server

texts:
I have Sql server data files saved on external storage. I want to detach specific database. If external storage is not connected I want to drop database.

Is this safe way to do that:

 DECLARE @DB_NAME SYSNAME = 'Database_name';

 BEGIN TRY

        -- Check if db has correct structure
        DBCC CHECKDB (@DB_NAME)  WITH  ALL_ERRORMSGS ;

        -- Stop using db
        EXEC( 'ALTER DATABASE ' +@DB_NAME + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ');

        -- Detach db
        EXEC sp_detach_db @dbname= @DB_NAME , @skipchecks= 'true';

    END TRY
    BEGIN CATCH

        -- DB files are missing - drop database
        IF ERROR_NUMBER() = 945
        BEGIN
            EXEC ('DROP DATABASE  ' + @DB_NAME);
        END
    END CATCH;


texts: How to cast in oracle -Spatial Database?

texts:
Im trying to get data inside the table which is below.

   CREATE TABLE Eshot (
    eshotNumber NUMBER,
    eshot_geo_location SDO_GEOMETRY);

Using this query returns unsupported data type;

Select e.eshot_geo_location from Eshot e;

Inserted values example

INSERT INTO Eshot VALUES(476,SDO_GEOMETRY(2001, 8307,
SDO_POINT_TYPE(38.3678709,27.1824648,NULL), NULL, NULL));

How to make cast to grab data correctly? Thanks in advance


texts: MySQL Replication: How to restore master FROM slave?

texts:
TL;DR: given a master/slave setup between two MySQL databases, where the connection from the master to the slave is read only, how can you restore the master from the slave in the event of a failure of master?

I'm investigating alternative backup solutions for our main MySQL database.

Up to now, we've been using mysqldump to dump our databases, with the resulting file being zipped & the old backups being rotated.

This has worked well for several years, but we're now getting to the point where the database is so large that the overnight backup operation is extending into business hours and impacting operations (since some or all of the tables are still locked when employees start arriving in the morning).

I have set up a slave database on another server & have it syncing with our main database. It's working nicely, and I have the slave backing up (via mysqldump again). But I have two questions/problems:

How can I be sure that I'll be able to restore master from the mysqldump output from slave?

I'm reasonably confident that the dump files are OK, but there are stored procedures & views in our database and I've had issues with mysqldump before - it can be a little finicky about dumping these things in the correct order/with correct permissions/etc.

I've thought about doing a test restore to a blank system (in a Docker container), but a) it's going to take a LONG time, b) I'll need to write a script to actually compare the test system with the master, and c) it will be hard to get a comparison at a single point in time.

As an alternative, I've thought about simply shutting down the slave database, backing up the data files, then restarting the slave. This will be faster, simpler, and probably more reliable, but how do I then restore in the event of a failure?

I have read the MySQL documentation on the subject, but it only talks about how to do the backup, not how to restore it.

I imagine that it would be possible to shut down all the systems, revise the settings on the slave to remove readonly = 1, then somehow force the slave to push everything to the master.

So, given this setup, my main question is this:

What are the steps involved in restoring all the data from the read-only slave to the master in the event of a failure of the master?


texts: SQL job is completing without sending email to configured recipients

texts:
I have an SQL job completing without sending email to configured recipients. Here is the stored procedure I am using. Names changed accordingly. All other mail is getting sent via db mail through a stored procedure. I believe there is a size limit on the mail send.

USE [master]
GO
/****** Object:  StoredProcedure [dbo].[spReport]    Script Date: 3/3/2016 3:29:27 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      J Romero
-- Create date: 10/21/2015
-- Description: Procedure to email a list of Subscribers for the last 12 months
-- =============================================
ALTER PROCEDURE [dbo].[spReport]

AS
BEGIN
    SET NOCOUNT ON;
    dECLARE @p_recipients as nvarchar(max), @p_profile_name as nvarchar(max)
    declare @TableHTML nvarchar(max)
    SET @p_recipients = N'email1@web.com;email2@web.com;email3@web.com'
    set @TableHTML =
    N'
eMail Subscribers-Last 12 Months
' +
    N'
' +
    N'
eMail Address	Date Subscribed
' +
    Cast ((select td = email,      '',
                    td = Date_Subscribed
    FROM email_subscriptions
    where date_subscribed > dateadd(D,-365,getdate())
    ORDER BY date_subscribed
    for xml path('tr'), type) as nvarchar(max)) + N'
' ;

    EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Admin',
    @recipients = @p_recipients,
    @reply_to = 'email@web.com',
    @blind_copy_recipients = 'email@web.com',
    @subject = 'Subscribers-Last 12 Months',
    @body = @TableHTML,
    @body_format = 'HTML'
END

Every other Procedure is sending mail, and no errors are coming up in logs. Ok, Mail profile has been enabled. I have found that there is a size limit to the emails you can send from db. Is there a setting to change this?


texts:
I have a table where each row contains a raster covering a different geographic area. I need to query the value of the raster at a given point, but only need to query the raster that covers my lon/lat, not each one in the table. I've created a spatial index on st_convexhull(rast), but according to EXPLAIN, queries on the table aren't using the index.

I suspect I'm just not formulating the query properly, but can't quite figure out what I'm doing wrong. Here's what I've got:

CREATE INDEX topo_st_convexhull_idx ON topo USING GIST (ST_ConvexHull(rast));

EXPLAIN SELECT ST_Value(topo.rast, 1, ST_SetSRID(ST_MakePoint(0, 51), 4326))
    FROM topo
    WHERE ST_Contains(ST_ConvexHull(rast),ST_SetSRID(ST_MakePoint(0, 51), 4326));


texts: Partitioning with AUTO_INCREMENT primary key

texts:
I am looking to partition a MySQL table by the month of the entry timestamp column (time that the row was inserted). The table in question uses an AUTO_INCREMENT column as a primary key as this table is just INSERTed into by a triggered process in aws.

When I try to partition by month I get an error saying it is not allowed and when I look up the error it seems that I need to include the primary key as part of the partition.

  All columns used in the partitioning expression for a partitioned table must be part of every unique key that the table may have.

ALTER TABLE TABLE
    PARTITION BY RANGE(MONTH(entry_ts)) (
    PARTITION JANUARY VALUES LESS THAN (2),
    PARTITION FEBRUARY VALUES LESS THAN (3),
    PARTITION MARCH VALUES LESS THAN (4),
    PARTITION APRIL VALUES LESS THAN (5),
    PARTITION MAY VALUES LESS THAN (6),
    PARTITION JUNE VALUES LESS THAN (7),
    PARTITION JULY VALUES LESS THAN (8),
    PARTITION AUGUST VALUES LESS THAN (9),
    PARTITION SEPTEMBER VALUES LESS THAN (10),
    PARTITION OCTOBER VALUES LESS THAN (11),
    PARTITION NOVEMBER VALUES LESS THAN (12),
    PARTITION DECEMBER VALUES LESS THAN (13)
    );

CREATE TABLE `TABLE` (
  `HEADER_ID` int(11) NOT NULL AUTO_INCREMENT,
  `STATUS` int(11) NOT NULL DEFAULT '0',
  `CREATED_AT` varchar(45) NOT NULL,
  `TYPE` varchar(45) NOT NULL,
  `VERSION` varchar(45) NOT NULL,
  `ID` varchar(45) NOT NULL,
  `ORIGIN` varchar(45) NOT NULL,
  `PAYLOAD` json NOT NULL,
  `ENTRY_TS` datetime NOT NULL',
  PRIMARY KEY (`HEADER_ID`)
)


texts: SQL concatenate VARCHAR + VARBINARY with VARBINARY as hex

texts:
As the title suggests, is possible to concatenate the 2, with the result as VARCHAR and the VARBINARY data as hex?

DECLARE @test VARCHAR(50) = 'Hello World!'
DECLARE @testvb VARBINARY(256)

SET @testvb = CAST(@test AS VARBINARY(256));

SELECT @test + @testvb --I don't want to cast this back to var char

The result should be: Hello World!48656C6C6F20576F726C6421


texts: SSRS reports broke after upgrading from 2014 to 2016

texts: One of my team members upgraded the SSRS and SQL servers from 2014 to 2016. Now, rather than doing an upgrade i.e. use existing databases - they uninstalled 2014 and did a fresh install of 2016 for SQL and SSRS. Due to this our reporting server broke. For the DB part - we attached the existing databases so that part was sorted. For SSRS - we detached the new ReportServer and ReportServerTempDB and attached the existing ReportServer and ReportServerTempDB hoping that it would solve the problem partially at least. After doing that we get an error - "The feature: "Scale-out deployment" is not supported in this edition of Reporting Services" whenever we open the Report URL. Can someone please help us? We are blocking some important production reports.


texts:
If I run the query outside the stored procedure, I get results, but I only get the column names as the result after executing the query - even though return value is 0. Any help would be appreciated.

ALTER PROCEDURE [dbo].[Reconciliation2]
(
    @Param1 int,
    @Param2 int,
    @Param3 varchar,
    @Param4 varchar
)

AS

BEGIN
    SET NOCOUNT ON;

    SELECT
        T.FY AS [FY],
        T.FY_PERIOD AS [Period],
        CONVERT(DATE, CREATED_DATE) AS [Created Date],
        CONVERT(DATE, TRANS_DATE) AS [Trans Date],
        T.TRAN_DOC_TYPE AS [Doc Type],
        TASK_ID AS [Task],
        T.DOC_NO AS [Doc Number],
        LINE_NO AS [Line],
        SEQNO AS [Seq],
        LINE_DESCRIPTION AS [Description],
        SUBSTRING(ACCT_NO,1,2) + '-' +
           SUBSTRING(ACCT_NO,3,5) + '-' +
           SUBSTRING(ACCT_NO,8,5) + '-' +
           SUBSTRING(ACCT_NO,13,6) + '-' +
           SUBSTRING(ACCT_NO,19,4) + '-' +
           SUBSTRING(ACCT_NO,23,4) AS [Account Number],
        DEBIT,
        CREDIT
    FROM
        DBVW_FIN_TRANSACTIONS_UDR T
    INNER JOIN
        (SELECT
             INST_ID, FY,
             TRAN_DOC_TYPE, DOC_NO
         FROM
             DBVW_FIN_TRANSACTIONS_UDR
         WHERE
             INST_ID = 'SC00'
             AND FY = @Param1
             AND FY_PERIOD = @Param2
             AND ([ACTION] = '' OR [ACTION] = 'V')
             AND SYS_GEN_DETAIL_LINE = 0
             AND TRAN_DOC_TYPE = @Param3
             AND TASK_ID = @Param4
         GROUP BY
             INST_ID, FY, TRAN_DOC_TYPE, DOC_NO) TRAN_BASE ON TRAN_BASE.INST_ID = T.INST_ID AND
                                                      TRAN_BASE.FY = T.FY AND
                                                      TRAN_BASE.TRAN_DOC_TYPE = T.TRAN_DOC_TYPE AND
                                                      TRAN_BASE.DOC_NO = T.DOC_NO
    WHERE
        T.INST_ID = 'SC00' AND
        T.FY = @Param1 AND
        T.SYS_GEN_DETAIL_LINE = 0 AND
        T.TRAN_DOC_TYPE = @Param3 AND
        TASK_ID = @Param4
    ORDER BY
        T.DOC_NO, T.LINE_NO, T.SEQNO
END


texts: Setting a default principal server in SQL Server 2008

texts:
This should be a pretty simple answer but I can't find anything on it searching the web.

SQL Server 2008 R2, mirrored with a witness:

Node1 (Principal)
Node2 (Mirror)
Node3 (Witness)

If Node 1 fails, Node 2 takes over as it should, however when Node 1 recovers, Node 2 remains the principal.

Is there a way to make Node 1 the primary/default server so that when it's available, it's always the principal server?


texts: Selecting rows ordered by foreign key count and filtering by date

texts:
I've got myself in a little pickle with an SQL query that is a bit out of my experience.
Here's the two tables I'm using:
                                          Table "public.Vote"
  Column   |              Type              | Collation | Nullable |              Default
-----------+--------------------------------+-----------+----------+------------------------------------
 id        | integer                        |           | not null | nextval('"Vote_id_seq"'::regclass)
 createdAt | timestamp(3) without time zone |           | not null | CURRENT_TIMESTAMP
 serverId  | integer                        |           | not null |
Indexes:
    "Vote_pkey" PRIMARY KEY, btree ("createdAt")
    "Vote.id_unique" UNIQUE, btree (id)
Foreign-key constraints:
    "Vote_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"(id) ON UPDATE CASCADE ON DELETE CASCADE
    "Vote_serverId_fkey" FOREIGN KEY ("serverId") REFERENCES "Server"(id) ON UPDATE CASCADE ON DELETE CASCADE

                                          Table "public.Server"
  Column   |              Type              | Collation | Nullable |               Default
-----------+--------------------------------+-----------+----------+--------------------------------------
 id        | integer                        |           | not null | nextval('"Server_id_seq"'::regclass)
 title     | text                           |           | not null |
 content   | text                           |           |          |
 createdAt | timestamp(3) without time zone |           | not null | CURRENT_TIMESTAMP
Indexes:
    "Server_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "Server_authorId_fkey" FOREIGN KEY ("authorId") REFERENCES "User"(id) ON UPDATE CASCADE ON DELETE CASCADE
    "Server_versionId_fkey" FOREIGN KEY ("versionId") REFERENCES "Version"(id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
    TABLE ""Vote"" CONSTRAINT "Vote_serverId_fkey" FOREIGN KEY ("serverId") REFERENCES "Server"(id) ON UPDATE CASCADE ON DELETE CASCADE
    TABLE ""_ServerToTag"" CONSTRAINT "_ServerToTag_A_fkey" FOREIGN KEY ("A") REFERENCES "Server"(id) ON UPDATE CASCADE ON DELETE CASCADE

Here's how my current query looks:
SELECT s.id, s.title, s.content, count("serverId" WHERE "createdAt" >= '2020-01-01 00:00:00') AS "voteCount" FROM "Server" AS s LEFT JOIN "Vote" AS v ON (s.id = "serverId") GROUP BY s.id ORDER BY "voteCount" DESC;

At the moment the above query does this:

Selects all entries in "Server"
Counts how many entries in "Vote" each "Server" has
Orders the output data by "voteCount"

It's working pretty well, but now I would like to add an extra layer of complexity to it and only count votes made after a certain date.
So I came up with this query:
SELECT s.id, s.title, s.content, count("serverId") AS "voteCount" FROM "Server" AS s LEFT JOIN "Vote" AS v ON (s.id = "serverId") WHERE v."createdAt" >= '2020-01-01 00:00:00' GROUP BY s.id ORDER BY "voteCount" DESC;

While the above technically does work, it's also not outputting rows in "Server" that have no rows in "Vote". Is there any way to get those rows as well?


texts: Access to the path 'c:\some\path' is denied for MSSQL CLR

texts:
I think this is a permissions problem, but I'm having trouble locating it.

I have a group of CLRs on one server (SQL Server 2016) and they work as they should.  All are marked UNSAFE and they do various types of file I/O (read, write, copy, move, rename, etc.).  I can run them via SSMS or from a job with equal ease.

I need to install them on another server (also SQL Server 2016).  Using the original Visual Studio Project I have deployed them to the new sever.  They show up in SSMS.  That part looks fine.

When I, from SSMS, try to run one I get the following error: "Access to the path 'whatever path I passed in' is denied."

I'm logged into SSMS under my windows login.  I have permissions to the database, I'm dbo.  I'm an admin on the server.  I have permissions in the file system.

What else could I be missing?


texts:
Consider the following relation instance myTable

num1    num2
100     100
100     NULL
NULL    100
NULL    NULL

Query 1:

SELECT *
FROM myTable
WHERE num1 != NULL;

Query 2:

SELECT *
FROM myTable
WHERE num1 = NULL;

I know that NULL values are looked up using IS NULL and IS NOT NULL.
But I want to know how are the above two queries evaluated. What is the number of tuples outputted by Q1 and Q2 by the ANSI standards?


texts: Choosing primary keys: Scientific names of species or system-assigned numeric identifiers?

texts:
Scientific names are well accepted unique identifiers of species. However, scientific names have special characters such as spaces and dots. Can it be a good unique identifier for databasing or should one assign/implement unique numerical identifiers?

If it is with the latter, is there a globally known numeric identifier? Or should one assign own identifiers for the rows?


texts: Disable foreign key checks during import

texts:
I have started a humongous (~800GB sql dump) import to a mysql 5.5.

At some point I realized that while the db was being imported, the progress was starting slowing down as the db size increased;

That was a clear hint that the increasing size of foreign key checks was probably slowing the process;

I realized I had forgotten to disable them, which I just did (in the middle of the import);

So my question is if whether the specific import will benefit from this or it will keep performing FK checks (which were on in the import initialization)

mysql> show variables like 'fo%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | ON    |
+--------------------+-------+
1 row in set (0.00 sec)

mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'fo%';
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| foreign_key_checks | OFF   |
+--------------------+-------+
1 row in set (0.00 sec)


texts: SQL schema for movie rating and seenlist

texts:
I'm making funny app.

User can mark movie as seen.
User can rate movie (1-10).
When user rate movie it is automatically marks as seen.
User can't remove movie from seenlist if movie is rated by him.

User have 2 tabs: seen movies which have movies in seenlist and rated movies. And tab with rated movies for movies that has rating.

I was going to make 2 tables:
ratings (user_id, movie_id, rating, created_at)
seen(user_id, movie_id, created_at).

But I don't like to duplicate data, so I kind of think maybe single ratings table will be fine, if user wants only mark as seen, rating value will be 0 or -1.
One think will be is to make where clause when calculating movie avg rating (where ratiny >0). But everything else is done dryer.

What do you think is better single table or separate tables?


texts: Is there any performance difference among MariaDB (xtradb plugin) and XtraDB Server for InnoDB?

texts: Title says it all. However i have to enter at least 30 characters.


texts: Connect to SQL Server Management Studio over VPN (Hamachi)

texts:
I just got my Hamachi VPN set up. For anyone familiar with Hamachi, I have it set up as a gateway so I'm part of the network when I'm away.  Almost everything seems to be working perfectly.  I can even backup using Windows Home Server if I want.

I cannot connect to my SQL Server from SQL Server Management Studio.  Of course, when I'm at home, everything works perfectly.  I can communicate with the database server just fine remotely (i.e., ping).  I just can't connect with SSMS.  The network configuration is at the default (TCP Enabled).

Does anyone know

Why I can't connect?
How I can determine why I can't?

Extra info:

Using a Workgroup, not a domain.
Using port 1433 to connect
Connecting to the default instance


texts: SQL get difference of row data in another row

texts:
I want to calculate how many days a product has been out of stock

I have an audit.inventory table where data gets inserted after change in the inventory table.

Inventory Table is as below:

  inventoryid    productid     stock
  1              1             8
  2              2             5

And audit.inventory is same as inventory with an extra stamp field for when the change occured

audit.inventory is as below:

  inventoryid    productid     stock    stamp
  1              1             8        2018-06-26 14:59:33
  1              1             6        2018-06-26 14:59:33
  1              1             5        2018-06-26 14:47:09
  1              1             0        2018-06-21 18:30:50
  1              1             0        2018-06-04 16:57:02
  1              1             3        2018-06-04 14:58:57
  1              1             2        2018-06-02 12:54:18
  1              1             0        2018-05-27 11:44:22

So if I run this query for productid = 1

select extract(day from stamp) as day_of_month, max(stock) from
audit.inventory where productid = 1
and extract(month from stamp) = 6 group by extract(day from stamp);

Gives me:

day_of_month    stock
2               2
4               3
21              0
26              5

How can I calculate the stock on days in between, for example 1(day_of_month) will have stock 0, 3(day_of_month) will have 2 as the stock and 4-20(day_of_month) will have stock 3 and then 22-25 will have stock 0.
So the total number of days the product stock has been 0 will be 6 ie. 1,21,22,23,24,25


texts: Trun a query result to argument of EXEC sp_executesql

texts:
I blatantly stole from the answer here to make this : (this is not the only case I am looking a solution for)

SELECT   'SELECT Top 10 [table] = '''
         + REPLACE(name, '''','')
         + ''', * FROM '
         + QUOTENAME(SCHEMA_NAME([schema_id]))
         + '.'
         + QUOTENAME(name)
         + ';' as SQLText
FROM     sys.tables AS t
ORDER BY name;

The reason was that in the answer the DECLARE @sql NVARCHAR(MAX) was not enough to hold all the tables names, I think we have 120 tables, (or whatever), and the SQL text was being clipped at NVARCHAR(MAX) limit.

Maybe in this case can EXEC sp_executesql SQLText and unionizing them,
but is there a way to turn all the rows into one text and execute that instead?

Is there a limit on the size of how big text of a stored procedure can be?

Please note, I am not after the answer for this specific case, but this is an example of type of problem I am trying to solve.


texts:
I have a materialized view of around 200 million records and would like to add an index to the 'full_names' field to speed up the search. The name data is stored like this;

| full_names                                                    |
-----------------------------------------------------------------
| Smith, John | Doe, Jane,    | Doe, John                       |
| Smith, James| Smith, Clare, | Doe, John                       |
| Adams, John | Doe, Jane,    | Doe, Steven                     |

Since my search term uses 'ILIKE' and '%' a traditional btree index will not work. I had intended to use a trigram index using the following:

CREATE INDEX idx_full_names_trgm_gist ON sample_table USING gisy(full_names gist_trgm_ops);

but received this error:

ERROR: index row requires 10216 bytes, maximum size is 8191

My next thought was to us a ts_vector but my understanding was that ts_vector breaks down words to dictionary defined lexemes. I assume this won't work with names since they are not dictionary words?

Any suggestions on the best way to index this data for fast searching?

This data is largely static and is not updated regularly. External search tools like Solr are not currently an option dues to resrtictions on the server so I was hoping to use postgres' native functions.

Postgres 9.5/RHEL 6.8


texts: Availability Group cluster memory issue after network issues. How to dump HADR Log Block Msg Pool?

texts:
We have a four node Availability Group, two nodes in one site, two nodes off site in another data center. I have noticed after every WAN issue where the WAN connection is flapping, and the off site nodes constantly disconnect and reconnect (using AOAG health from the AOAG dashboard), the memory of the primary server gets consumed by the "HADR Log Block Msg Pool"

SELECT  *
FROM    sys.dm_os_memory_clerks
ORDER   BY pages_kb DESC

type: OBJECTSTORE_SERVICE_BROKER
name: HADR Log Block Msg Pool

In the worst case, when the network was flapping for hours, this memory clerk will end up consuming over 90% of the memory of the SQL Server causing the SQL Server to stop functioning (SQL has 10GB of memory "HADR Log Block Msg Pool" was using 9.8GB).

Is there any way to dump this HADR Log Block Msg Pool?  Or stop it from growing so large in the first place?  Our only solution so far has been to failover and restart the box.

There are no errors, just the logs for the node disconnects and reconnects and logs for the DBs re-hardening after the reconnects.

As more and more memory gets eaten by the "HADR Log Block Msg Pool" the memory available for everything else drops, affecting performance. Normally this 10GB of RAM is fine for this AOAG group and usage. It's only when the WAN flaps for a while that we have this issue.

We could throw more memory at the server, but I don't think that will solve the underlying problem, it would just buy us more time before it severely hurts performance.

I agree the network is the root cause, but it seems strange that after the issue is resolved and the AOAG is back in sync that SQL would not recover/reallocate RAM back to other SQL memory clerks like most SQL memory clerks do.

Log shipping won't work; it is a transactional environment, we need near real-time, preferably real-time, offsite DR. The AOAG group works 99% of the time and is almost always real time in sync. We are trying to work with the network team to improve connectivity, and/or maybe make it so it would just disconnect instead of flapping.

System Info
SQL version: SQL 2016 SP1 CU6 13.0.4457.0
OS version: Windows 2012 R2 6.3.9600
Server MEM: 12GB
SQL Max MEM: 10GB

Availability Group config info
Four databases are in the AOAG
The AOAG databases all together are 364GB
The two local nodes are in sync mode with one vote each
The two remote nodes are in async mode with zero votes
There is also a local file witness with one vote.


texts: PostgreSQL COPY - Generated columns cannot be used in COPY

texts:
Im trying to import a SQL dump file for a table (A COPY command dump file). The table has a generated column. While restoring the dump Im getting this error.
ERROR:  column "call_status" is a generated column
DETAIL:  Generated columns cannot be used in COPY.

How do I bypass this error?
PostgreSQL version: 12
Sample copy command
COPY test_revision (acc_id,revision,status) FROM STDIN;
acc-001  5  pass
acc-002  4  pending

the status column is a generated column based on revision id.
If revision 5 then status column value would be pass


texts: Create query to capture the count of user logins per a given date range

texts:
I am working with SQL Server 2016. I am not sure if there is a built in report or method to do this or if I need to write a sql query.
I want to be able to generate a report (simple table is fine) to capture all the users who logged in (accessed) a certain database and the count of how many times they accessed it. I have very little SQL writing skills... table I am looking for to be generated would look something like below... the date range would be a variable I determine.. YYYY/MM/DD - YYYY/MM/DD or something similar

UserName | Count of Logins
---------------------------
user1    | 3
user2    | 5
user3    | 1

EDIT**

I am looking for the number of times a user authenticated to access database "x" (I will replace "x" will my dbid) for example if a user authed to the database at 8am and did nothing up until 2pm, and then preformed a query that counts as 1 "login" however if they login at 8am, then logout. and at 2pm again they log back in that counts as 2 "logins" and should reflect "2" in the counts field.


texts:
seems like my problem pretty much describes GC (Garbage Collector) but I don't want to make a procedure to run every X time. so this is my question:
I have three tables or more, refer to those as A,B,C.
C holds some columns and A & B refer to those columns.
A & B data can be deleted -> and here comes my question:
I'd like the referential to go as when there are no valid references to C, the referenced row will get deleted, since it's not in use.
I thought of adding an auto_increment to C, and make A & B refer to the index, and just keep multiple of the same data, and just delete the attached. This hardly seems like the most reasonable solution.
If anyone could point some light on my problem, that would be great.
Thank you.

EDIT: I'm using SQLite.


texts: Is Windows 10 compatible with SQL Server 2014 Developer MDS?

texts:
I have recently installed SQL Server 2014 Developer edition onto my PN, which has Windows 10 Professional installed. I upgraded from Windows 7 Pro to Windows 10 Pro last year as it was a free update from Microsoft.

I am trying to install Master Data Services and I receive the following warning:

  Before creating an MDS web application, you must correct the following errors:
  Internet Information Services (IIS) is not configured on this server.
  These required Web Server role services are not installed:
  Windows Authentication
  For web application requirements, see http://go.microsoft.com/fwlink/?LinkId=309491.

I am now going to install Windows authentication to correct the problem.  However, I have looked at the link and Windows 10 Pro is not listed as being compatible with SQL Server 2014 Developer edition. Is that correct?


texts: Really Small details about storage ( data size ) on columns

texts:
I'm doing tests to better understand those minimal technical details about tables, pages, and etc.

I'm using sp_spaceused to better understand this with INT values ( I will do a lot of more tests with other datatypes, But I think the logic behind each datatype is the same).

OK. First I created a simple table with id int.

create table Aliens( id int)

Inserting only 1 value (Insert into table value (1)), I have this:

| name | rows | reserved | data | index_size | Unused |
|------|------|----------|------|------------|--------|
| id   | 1    | 16 KB    | 8 KB | 8 KB       | 0 KB   |

OK. I can understand that we have here USED 8KB because this is the size of a page. correct? But why does SQL server reserves 16KB for this?

Then, I delete this row, and the result is the same:

| name | rows | reserved | data | index_size |
|------|------|----------|------|------------|
| id   | 1    | 16 KB    | 8 KB | 8 KB       |

This is because I didn't rebuild the table. After rebuilding the table:

| name | rows | reserved | data | index_size | unused |
|------|------|----------|------|------------|--------|
| id   | 0    | 72 KB    | 8 KB | 8 KB       | 56 KB  |

Why now I have more reserved ( 72KB )? I deleted data and rebuilded. Now, is my table bigger? Is this because of log? and why is unused 56KB? 72-56 is 16. Is this because DATA and INDEX are separated? my table has no PK, nor indexes.

Now, when I insert 286 values ( all 1's, with go 286):

| name | rows | reserved | data  | index_size | unused |
|------|------|----------|-------|------------|--------|
| id   | 286  | 24 KB    | 16 KB | 8 KB       | 0 KB   |

Now I can see data = 16kb because There's so much information, that the SQL is now using 2 pages. But why index_size is still 8kb?

and How about NULL VALUES? according to This question here, null values on int field takes 4 bytes. a Char(10) takes 10 bytes. So, null values store the same amount as the datatype itself? Unless we are using sparse columns.

Thanks. any additional information would be appreciated.I think it's time to improve my knowledge with this details.


texts: Need a way to redesign tables such that there is minimal redundancy

texts:
I am new to this so please guide me if I may have missed standard practice in asking question.

I have two tables. These are used together and practically there is no use of them individually. I have attached sample structure in the images here.

First image contains the queries that will provide value of the field when executed.
Second image contains the table that provides positional and format info which will be used when the label is printed.

In short, first table gets the value and second gives the info about where to put the value on page when printed.

PROBLEM:
The first table has around 350 records. Meaning 350 different fields whose value will needed to be fetched every time someone hits "print".

Second table has over 100K records (350 fields x 300 different label templates). So there is a redundancy in second table. I am storing same type of info for same fields again and again for 300 times.

Is there any way I can avoid this? I am hesitant to create too many tables and I also want to avoid this type of redundancy.


texts: Syntax error when trying to import database from two PostgreSQL databases

texts:
Following on from this prior question, I'm trying to export a database from one server and put it on another using phpPgAdmin. My process is like this:

select the database in server 1 and hit the export button
select "data and structure and select the option in the dropdown "copy"
download
create the a database of the same name in server 2 with the same name as the database from server 1
select that database, open up the SQL pane, and paste the SQL code I downloaded from server 1
Execute

That's when I get the error:

ERROR:  syntax error at or near "OWNED"
LINE 73: ALTER SEQUENCE address_customer_id_seq OWNED BY address.cust...

Line 73 in its entirety is:

 ALTER SEQUENCE address_customer_id_seq OWNED BY address.customer_id;

I've read here that it may be because I've have slightly different versions of PostgreSQL on my two servers. So I checked with the server tech, who said that, yes, there was that issue. So he upgraded server 2. I'm getting the same error however.

Any idea what could be going on?


texts: How to Return XML Node Ordinal, or delete node based on element value?

texts:
I have an XML document similar to this:

<Root>
  <Sub>
    <Record>
      <Guid>aslkjflaksjflkasjfkljsd</Guid>
    </Record>
    <Record>
       <Guid>opqiwuerl;kasdlfkjawop</Guid>
    </Record>
  </Sub>
</Root>

I am replacing an entire <Record> node based on some criteria.  The <Record> nodes contain a <Guid> that I can use to identify them (pretend those are valid GUIDs please!).

Knowing the GUID, I am returning that node's XML into a variable for further processing.  Downstream, I need to be able to delete that particular node so I that I can insert the modified version of the node back into the original document.

Is there a way to determine the ordinal, or another way to use a delete/replace method  to remove a node based on an element's value?


texts: How to restore a database from a newer version of SQL Server

texts: I have SQL Server 2008 and I want to restore a database from SQL Server 2008 R2. When I try to restore, SQL Server produces an error message that the database came from a newer version of SQL Server than the one I have.


texts: Oracle 11gR2 IMP Won't Import Table in Dump File From 10g

texts:
I've got a screwy problem using imp on an Oracle 11gR2 instance to try and import export files created with exp on an Oracle 10g instance (different hardware, if that matters).

I've read through the Oracle documentation and I know that I can use imp from any higher version to import *.dmp files created from any lower version of Oracle - or at least it shouldn't be a problem going from 10g to 11gR2.

Note: I can't use DataPump (tried) because the 10g is 10.2.0.1 which has a known PL/SQL bug when trying to use DataPump from a remote DB; the solution is to patch to at least 10.2.0.2, but our environment is too unstable to do that at this time.

We have a nightly job that exports schema-level *.dmp files from the Oracle 10g instance for all of the active users and places them on a common network share.

We've previously exported the user schemas from the Oracle 10g instance and used those exports to create those same schemas on the Oracle 11gR2 instance, and while that was several months ago, most of the schemas haven't changed much - there are a couple of new tables, but the imp should be able to handle that just fine.

So, before I wrote a giant parameter file and ran a mass import, I decided to do a small-scale test with one table from one schema - the schema isn't mine, but ultimately I can fix that.  I just want to make this work first - so I do:

C:\Users\digitalnoise>imp digitalnoise/passwordispassword file=Y:\user1.dmp tables=(zomg_must_have_dataz)

But then I get this:

Import: Release 11.2.0.1.0 - Production on Tue Dec 10 21:32:17 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via direct path

Warning: the objects were exported by USERGOD, not by you

import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
. importing USERGOD's objects into DIGITALNOISE
IMP-00033: Warning: Table "ZOMG_MUST_HAVE_DATAZ" not found in export file
Import terminated successfully with warnings.

Which leaves me really confused, because:

C:\Users\digitalnoise>imp digitalnoise/passwordispassword file=Y:\USER1.dmp show=Y full=Y LOG=USER1.LOG

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V10.02.01 via direct path

Warning: the objects were exported by USERGOD, not by you

 "ALTER SESSION SET CURRENT_SCHEMA= "USER1""
 "CREATE TABLE "ZOMG_MUST_HAVE_DATAZ" (<<SNIP>>)  PCTFREE 10 PCTUSED 40 INITRANS "
 "1 MAXTRANS 255 STORAGE(INITIAL 540016640 FREELISTS 1 FREELIST GROUPS 1 BUFF"
 "ER_POOL DEFAULT) TABLESPACE "ZOMG" NOLOGGING NOCOMPRESS"
. . skipping table "ZOMG_MUST_HAVE_DATAZ"

Clearly indicates that the table ZOMG_MUST_HAVE_DATAZ exists in the USER1.DMP file - but when I got to actually import it, imp says that it doesn't exist...

My user ID has the IMPORT FULL DATABASE system privilege on the target via Role assignment, so I don't think that's the issue.

Am I missing something rather obvious here?


texts: Slave I/O for channel error connecting to master Error_code: 2003

texts:
i am trying a master master replication mysql with phpmyadmin using wamp server.
everything seems to be fine, the configuration was well done, so the server is slave and master at the same time ,

but when i did the command show slave status\G i have this result :

    mysql> Show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 10.24.60.213
                  Master_User: user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File:
          Read_Master_Log_Pos: 4
               Relay_Log_File: MUL-N003-relay-bin.000017
                Relay_Log_Pos: 4
        Relay_Master_Log_File:
             Slave_IO_Running: Connecting
            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: 0
              Relay_Log_Space: 154
              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: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'user@10.24.60.213:3306' - retry-time: 60  retries: 4
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
                  Master_UUID:
             Master_Info_File: c:\wamp64\bin\mysql\mysql5.7.26\data\master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp: 190626 13:44:11
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

i've tried to add bind-address='....' and it doesn't work, i did also bind-address = 0.0.0.0, doesn't work too ..

when i did a test of the 3306 port, it seems like it's not associated with TCP protocol..

any ideas to solve this problem please !