texts: SQL Server Management Studio Doesn't do "Full" Backup

texts:
Very new to SQL Server. I'm trying to bring a backup from one our clients Windows 2012 Server Install to our Linux Install. We have the site working perfectly on Linux, but can't seem to do an arbitrary DB dump to get new data. They are running SQL Server 2012 Express (I'm assuming because it didn't have "Maintenance Plans" under the Management tab of the object Explorer)

Or in transact SQL:

BACKUP DATABASE [YourDB] TO  [YourDB_backup] WITH NOFORMAT, NOINIT,  NAME = N'YourDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

When I pulled this down to my Linux server it didn't have updated information (that was created 5-6 hours earlier). Why is this?

To ensure I wasn't making a mistake I deleted the entried DB on my Linux machine with:

USE master;
GO
DROP DATABASE YourDB;
GO

And then uploading it again as dictated here:

RESTORE DATABASE YourDB
FROM DISK = '/var/opt/mssql/backup/YourDB.bak'
WITH MOVE 'YourDB' TO '/var/opt/mssql/data/YourDB.mdf',
MOVE 'YourDB_Log' TO '/var/opt/mssql/data/YourDB_Log.ldf'
GO

Interestingly, their backup task that runs at midnight does have the new information, but I can't see where that task occurs to compare the script.


texts: Why isn't postgres prompting me about a new user?

texts:
I'm following these instructions to get postgres working with rails on Windows 7:

  Run "Start Command Prompt with Ruby" then...

  createuser -U postgres -P NewUserName
  When it prompts for a password for new role, assign it a new password.
  No, the new role should not be a superuser.
  Yes, the new role should be allowed to create databases.
  No, the new role should not be allowed to create more new roles.
  Enter the postgresPWD you made when we installed PostgreSQL.

But this isn't happening. After I run #1, it asks me for a password, then skips straight to 5. This is completely screwing me over! Where did 2 through 4 go?

So then I tried doing it manually with createuser -s -U postgres -d -r (I have to do -U postgres because otherwise it asks for a password for a user that seems to be based on my Windows account, and no password I give it is successful...I tried fixing this by changing pg_hba.conf and then NOTHING worked)....so anyway I tried doing it manually and it was successful, but then I tried createdb newdb_name and it said createdb: could not connect to database template1: FATAL: password authentication failed for user "{my windows account}".

So I guess I could createdb as postgres, but then my rails commands to rake db:create would still fail. I need the active account to have full rights so that I can keep doing rails work.

I'm becoming really miserable about this. Please help...


texts: sql error 1241 operand should contain 1 column

texts:
This is my query

select *
from players
where sport='football'
  and position='DEF'
  and pname!='Binoy Dalal'
  and pname not in (select player1,player2,player3,player4,player5,player6,player7,player8
                    from team
                    where sap='60003100009')
order by price desc;

It works fine without the pname not in ... clause.

I can't figure out whats wrong cause syntactically it's correct as mysql doesn't show any errors.


texts: Pt-query-digest date ranges do not work as expected

texts:
Analyse Queries with Pt-query-digest

I am using pt-query-digest to analyse queries and would like to analyse a specific time range only, in order to measure the effect of adding new indices and tuning the server.

The Query log is of course turned on (MySQL 5.6) and queries are written to the log files as expected. I transfer the log files to my local machine and concatenate all compressed log files into one file like this:

zcat -f $(ls -tr mysql-slow.log*) > MySQL-All-Slowlogs.log

When I then execute pt-query-digest without any parameters, I get the output as expected:

pt-query-digest MySQL-All-Slowlogs.log > output.log

The date range in this output file is the whole range that is covered within the log files and the header states this:

# 490ms user time, 10ms system time, 35.09M rss, 103.70M vsz
# Current date: Fri Apr  7 10:58:38 2017
# Hostname: LocalMachine
# Files: MySQL-All-Slowlogs.log
# Overall: 2.33k total, 125 unique, 0.00 QPS, 0.05x concurrency __________
# Time range: 2017-03-31 03:16:49 to 2017-04-07 06:00:10
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======

Thats fine. Now I would like to investigate on the queries from the last two days, for instance

Problem

Pt-query-digest does not respect date ranges I provide, I still get the full list starting end of march (2017-03-31)

# Gives all queries
pt-query-digest --since 2d MySQL-All-Slowlogs.log > output.log
# Also all queries
pt-query-digest --since '2017-04-05'  MySQL-All-Slowlogs.log > output.log

And specifying the until date gives no results at all

pt-query-digest --since '2017-04-05' --until '2017-04-07'  MySQL-All-Slowlogs.log > output.log

# No events processed.

I also provided a DSN with the same results while connecting via I an SSH tunnel

  pt-query-digest --since '2017-04-05' h=127.0.0.1,P=$PORT,u=$USER,p=$PASSWORD MySQL-All-Slowlogs.log > output.log

Expected Result

The documentation says this:

      --since
type: string

Parse only queries newer than this value (parse queries since this date).

This option allows you to ignore queries older than a certain value and parse only those queries which are more recent than the value. The value can be several types:

* Simple time value N with optional suffix: N[shmd], where
  s=seconds, h=hours, m=minutes, d=days (default s if no suffix
  given); this is like saying "since N[shmd] ago"
* Full date with optional hours:minutes:seconds:
  YYYY-MM-DD [HH:MM:SS]
* Short, MySQL-style date:
  YYMMDD [HH:MM:SS]
* Any time expression evaluated by MySQL:
  CURRENT_DATE - INTERVAL 7 DAY
If you give a MySQL time expression, and you have not also specified a DSN for --explain, --processlist, or --review, then you must specify a DSN on the command line so that pt-query-digest can connect to MySQL to evaluate the expression.

The MySQL time expression is wrapped inside a query like “SELECT UNIX_TIMESTAMP(<expression>)”, so be sure that the expression is valid inside this query. For example, do not use UNIX_TIMESTAMP() because UNIX_TIMESTAMP(UNIX_TIMESTAMP()) returns 0.

Events are assumed to be in chronological: older events at the beginning of the log and newer events at the end of the log. --since is strict: it ignores all queries until one is found that is new enough. Therefore, if the query events are not consistently timestamped, some may be ignored which are actually new enough.

What am I missing here?

Thanks!


texts: Table valued function VS Stored procedure

texts:
There's a function that :

returns only 1 row
has only 1 parameter
has multiple joins on different tables, each only to  return a single scalar value
is called only once at each login of a web application

It currently is written in c# linq-to-sql and takes 150-300ms to execute.

After re-writing it in SQL using a very simple SELECT statement, it takes 30ms to execute.

Now I want to encase this select in a database function, what is the best (fastest execution) way to do so ?

Make a view and at every call do something like  select * from viewXX where id=123
Make a stored procedure
Make a table-valued function

Are there any tricks I could take advantage of in such a case ?

Here is an example of what the query looks like

declare @id int=162;
select top 1 i.id, i.name, itd.description,
(select top 1 case when count(*)>0 then '1' else '0' end from category where category_desc=itd.description) "HasCategory",
(select top 1 number from wheels where wheel_item_id=i.id) "WheelCount"
from items i left items_desc itd on i.id=itd.itemid where i.id=@id


texts:
I'm trying to install oracle grid with asm filter driver.

I've already installed grid software only.

As root, I can't start asm. Actually, I don't have any answer from asmcmd filter driver commands:

root@srvdb18c:/u01/app/18.0.0/grid/bin# env|grep ORACLE
ORACLE_BASE=/u01/app/grid
ORACLE_HOME=/u01/app/18.0.0/grid
root@srvdb18c:/u01/app/18.0.0/grid/bin# ./asmcmd afd_configure
root@srvdb18c:/u01/app/18.0.0/grid/bin#
root@srvdb18c:~# $ORACLE_HOME/bin/asmcmd afd_state
root@srvdb18c:~#

What can be the problem?

Important: it's a Solaris 11 system.


texts: SQL Server - How are data pages stored when using a clustered index

texts:
I recently heard that the data pages in a clustered index aren't stored contiguously. Is this true?

Perhaps data pages are normally stored contiguously with some exceptions to the rule? Or perhaps I heard wrong and data page are always stored contiguously.

Many thanks.


texts: SQL Allow user to run Read Only Stored Procedures

texts:
We have stored procedures in database SQL Server 2016. Say TestDB.

Is there method allowing users to execute only Read-only stored procedures (No write insert/update stored procedures)? The way everything is setup in our legacy system, schemas are not being utilized to separate read access queries. Thank you,


texts: request conversion is not supported from SQL Server to MongoDB

texts:
I'm using a Linked server from SQL Server 2012 to connect to MongoDB via BI Connector ( MongoDB Atlas ).
I can retrieve some info that is int for example, but when running the query below, I receive an error:
select * from openquery([MONGO],'select * from address')

OLE DB provider "MSDASQL" for linked server "MONGO" returned message
"Requested conversion is not supported.".

Msg 7341, Level 16, State 2,
Line 1 Cannot get the current row value of column "[MSDASQL]._id" from
OLE DB provider "MSDASQL" for linked server "MONGO".

I tried everything and I read every post about this issue on the internet with no success. I tried to cast, I tried "allow in process" inside  the linked server but it seems I cant retrieve data from mongoDB.
as I read on google, this is because is the MongoDB ODBC driver is correctly describing some MongoDB data to SQL Server as an NVARCHAR(n), but n is greater than the maximum length that SQL Server supports for this data type, so I should use the Schema Editor to change the length for this column to 4000.
theres something calloed mongosqld for MySQL, and there I can set :
SET GLOBAL mongodb_max_varchar_length = X

but where can I find this on SQL Server?


texts:
I'm trying to install SQL using a config.ini file but it doesn't seem to accept the parameters set in the file.

For example, at the top of my file I have:

;SQL Server 2017 Configuration File
[OPTIONS]

IACCEPTSQLSERVERLICENSETERMS="True"

I run this:

C:\Temp\sql-server-2017.exe /ConfigurationFile="C:\Temp\sql-config.ini"

And yet, it will open the install window and ask me to "Accept Terms"

Another example, I have quiet simple set to true:

; Setup will not display any user interface.

QUIET="False"

; Setup will display progress only, without any user interaction.

QUIETSIMPLE="True"

But when I run: C:\Temp\sql-server-2017.exe /ConfigurationFile=C:\Temp\sql-config.ini /IACCEPTSQLSERVERLICENSETERMS /MediaPath=C:\Temp\SQL

I get this in the summary and error:


texts: SQL Server - dump schema into separate files keeping installation order

texts:
While dumping SQL Server's schema, I have these two options: single file or multiple files. Single file is good because is can be used to install schema on clean server - parents are installed before children so there are no problems during installation. On the other hand separate files are good because they can be versioned and changed objects (tables, view, procedures) can be easily identified - but dependency ordering is lost.

Is there a way to have best of both worlds, that is have all objects in separate files and at the same time preserve dependencies ordering? I am asking about existing schema that needs to be versioned after years of neglecting, not about best practices for starting new project.


texts:
In order to encrypt a SQL db, I run the following commands:

CREATE CERTIFICATE <certname> ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>'
WITH SUBJECT = 'certificate subject', EXPIRY_DATE = '20291031';
go

USE <databasetoencrypt>;
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE <certname>;
GO

When trying to execute the CREATE DATABASE ENCRYPTION KEY command, I receive this message and don't know what to do:

  Cannot use certificate 'name', because its private key is not present or it is not protected by the database master key. SQL Server requires the ability to automatically access the private key of the certificate used for this operation.

When I omit the ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>' during the CREATE CERTIFICATE command, it works. However, I need to create a password protected certificate.

Do you have any idea?! Thank you.


texts: Is there a way to create a MySQL/Percona Slave without using a dump?

texts:
I haven't been able to find anything so I'm sure the answer is no, what are you, a noob? but I feel the need to ask anyway :)

Is there a simple utility that would allow a new slave server to be brought up on an existing master without the need to perform a dump on the master?

I've set up slaves in the traditional manner several times so I'm not without knowledge of the process, but I'm curious if anyone has felt the same as I do, that there must be an easier way.

I would expect such a utility would use something similar to Percona's Xtrabackup, but instead of outputting to a dump file, it would stream the output directly to the slave, and then automatically enable the slave relationship after the feed has ended.

Is this realistic?

I noticed the streaming option in Xtrabackup, but couldn't find examples outside of its ability to just save a tar on another server, which isn't what I was looking for.  I want a solution that could stream directly into the destination DB without the requirement of working with a dump file at all.  This is particularly handy when working with large datasets.


texts: SQL Server 2019 and Transparent Data Encryption - Intermittent (silent) Error Log Messages

texts:
I recently migrated a SQL Server 2017 TDE database to SQL Server 2019 CU4.  I was browsing the Error Log and noticed intermittent messages about
An error occurred while processing log encryption. The process was recovered automatically. No user action is required.

These messages are always preceded by
Error: 1222, Severity: 16, State: 55.

And
Lock request time out period exceeded.

I found FIX: TDE encrypted Databases go in suspect state during the recovery phase when you restart SQL Server, where it discusses a fix that has been back-ported to previous versions of SQL Server.
From that post

Assume that you have a Transparent Data Encryption (TDE) encrypted
database on an instance of Microsoft SQL Server. When you restart the
instance of SQL Server, you may receive a lock time-out error that
resembles the following: Starting up database . Error:
1222, Severity: 16, State: 55. Lock request time out period exceeded.
Error: 9016, Severity: 21, State: 7. An error occurred while
processing the log for database . The log block could
not be decrypted. Then, the database will go in a suspect state.You
can get the database back online by using Emergency Mode. You won't
encounter corruption.

I'm not in the situation where my instance is being restarted, but it seems this fix is similar to what I am experiencing.
While I appreciate the process being automatically recovered, I'm concerned why the error is even showing up in the error log.
I also have an almost identical instance in the same migration scenario from SQL Server 2017 to 2019 that is NOT experiencing these errors.


texts: Upgrading OS and then upgrading to SQL Server 2016

texts:
One of my clients would like to have the OS upgraded to Windows 2012 enterprise and then the plan is to upgrade the SQL Server to 2016. I would like to know what would be the steps in doing so and how to revert the same if something bad happens in between and the upgrade fails. I have not really done this in the past so I do not have knowledge on the same. Below are the details of my client system

SQL Server 2008 R2 standard
OS: Windows 2008 R2 standard
RAM: 48 GB (32 GB usable)
Server: Vmware (Virtual platform)


texts: MySQL upgrade to 5.6.4 from 5.6.33 on Ubuntu

texts:
I just upgraded our MySQL server from 5.5.53 by doing the following:

sudo apt-get purge mysql-server-5.5 mysql-client-5.5
sudo apt-get install mysql-server-5.6

Which got me to version 5.6.33

However, I'm looking to get to at least 5.6.4 where it appears the DATETIME datatype takes up 37.5% less space (per this article).

I can't seem to figure out / find how to upgrade from 5.6.33 to 5.6.4.  Can anyone help?

FYI - I'm running Ubuntu 14.04.  Let me know if you need any other relevant data.

Thanks in advance.


texts: Is there a way to validate XML using variable as schema?

texts:
Is there a way to declare XSD schema as variable inside a construct: DECLARE @xml XML(Books)?

I need to make quick validation of XML using XSD schema which is declared as variable. I suppose there is no way, but I have to ask.

If someone can propose some workaround it will be very helpful also.


texts: Understanding Explain Plan on Composite Index

texts:
On PostgreSQL 12.1, given:

postgres=> create table abc(a int not null, b text not null, c boolean not null);
CREATE TABLE

postgres=> create index on abc (a, b, c);
CREATE INDEX

I then prepared a query that only filters on a, i.e. the first element in the index.

postgres=> prepare only_a(int) as select 42 from abc where a = 1;
PREPARE

postgres=> explain execute only_a(100);
                                 QUERY PLAN
----------------------------------------------------------------------------
 Bitmap Heap Scan on abc  (cost=4.20..13.67 rows=6 width=4)
   Recheck Cond: (a = 1)
   ->  Bitmap Index Scan on abc_a_b_c_idx  (cost=0.00..4.20 rows=6 width=0)
         Index Cond: (a = 1)
(4 rows)

It's able to use the index in order to filter on a, per the above.

Then, I prepared a query that filters on a and c, i.e. does not use b.

postgres=> prepare a_and_c(int) as select 42 from abc where a = 1 and c = true;
PREPARE

Then, I ran explain execute,

postgres=> explain execute a_and_c(100);
                                 QUERY PLAN
----------------------------------------------------------------------------
 Bitmap Heap Scan on abc  (cost=4.21..11.32 rows=3 width=4)
   Recheck Cond: (a = 1)
   Filter: c
   ->  Bitmap Index Scan on abc_a_b_c_idx  (cost=0.00..4.21 rows=3 width=0)
         Index Cond: ((a = 1) AND (c = true))
(5 rows)

My understanding is that Index Cond: ((a = 1) AND (c = true)) means that abc_a_b_c_idx was able to use both a and c values, despite b not being involved in the where.

If so, how can c be used since b is not used?


texts: SQL 2 Columns of Different Types in Same Table - Working With 'Uknown' Amount

texts:
I'm very new to SQL and I've been searching for an answer for a while now, can't seem to find it.

I have 1 table of CustOrders which has 3 columns.

Table CustOrders

Name (varchar)
Number_Orders (int)
order_date (datetime)

Question

I would like to get a list of customers that have had less orders than a specific customer 'x'. How would I go about doing that?

Select Statement

I tried with this logic, which didn't seem to work and is probably all wrong, which was:

SELECT name, number_orders
FROM custOrders
WHERE number_orders < (select number_orders from custOrders where name = 'x');

It returned an empty set, which should not be empty, because I know there are customers there with less orders.

Could you please help me with this?

I am using MariaDB.


texts: Modelling a scenario involving art exhibitions

texts:
Saffron Gallery is about to host an exhibition next month. A number of works of art (paintings, sculptures, etc.) will be exhibited.

Business rules

Every art work will have a barcode, a title and a description.
Each art work is created by a single artist.
Every artist has a name, an address and a contact
number.
One artist may create more than one art work for the exhibition.
People will come and visit the exhibition.
As each person enters the exhibition, their name, address
and phone number will be recorded.
Each person will be given a unique barcode id.
During their visit, people will nominate which art work they consider the best and this will be
recorded by Uptown Gallery.
People will be able to vote for more than one art work.

The task and my current considerations

I have to find elements (entities, attributes and relationships) of relevance in this business environment.

To me, the tables should be structured as follows:

Art work

Barcode (Primary Key)
Title
Description

Artist

Name
Address
Contact no

People

Name
Address
Phone number
Barcode (Primary Key)

Just want to know if I am correct or not?


texts: pg_stat_activity - is it possible to decompose a stored procedure further?


texts: ADO.NET entity data model wizard crashes when creating a Postgresql data model

texts:
I am having trouble creating an ado.net data model from an existing Postgres database. I have used Npgsql and connected successfully but the wizard crashes without any error when I try to add a new entity data model. I have installed the Npgsql PostgreSQL Integration extension(Version 4.1.3.1) and the following packages:
<packages>
  <package id="EntityFramework" version="6.2.0" targetFramework="net472" />
  <package id="EntityFramework6.Npgsql" version="3.2.1.1" targetFramework="net472" />
  <package id="Microsoft.Bcl.AsyncInterfaces" version="1.0.0" targetFramework="net472" />
  <package id="Npgsql" version="4.1.3.1" targetFramework="net472" />
  <package id="System.Buffers" version="4.5.0" targetFramework="net472" />
  <package id="System.Memory" version="4.5.3" targetFramework="net472" />
  <package id="System.Numerics.Vectors" version="4.5.0" targetFramework="net472" />
  <package id="System.Runtime.CompilerServices.Unsafe" version="4.6.0" targetFramework="net472" />
  <package id="System.Text.Encodings.Web" version="4.6.0" targetFramework="net472" />
  <package id="System.Text.Json" version="4.6.0" targetFramework="net472" />
  <package id="System.Threading.Tasks.Extensions" version="4.5.3" targetFramework="net472" />
  <package id="System.ValueTuple" version="4.5.0" targetFramework="net472" />
</packages>

The wizard crashes after clicking next on this window:

Further technical details
Npgsql version: Version 4.1.3.1
PostgreSQL version: 12.5
Operating system: Windows 10
Note:

I am using Npgsql -Version 4.1.3.1 instead of the latest stable version because the latest Npgsql PostgreSQL Integration extension in the VS marketplace is also of this version.
I am using EntityFramework6.Npgsql -Version 3.2.1.1 instead of the latest stable version because my project relies on EntityFramework -Version 6.2.0 and it is the latest version to have EntityFramework -Version 6.2.0 as the minimum dependency.
I have tried using all the latest stable versions of EntityFramework, Npgsql, and EntityFramework6.Npgsql and still had the same results

Thank you.


texts: Offline to Online database synchronization programmatically using PHP and MySQL

texts:
I am working on a project in which the client requires an online as well as an offline application. Offline application data will be manually synced to online database linked to the web application.

Database is MySQL, some of the functions need to be available online as well as offline. Suppose any user who does not have internet access will use the offline application. Once he is done with his work, and has internet access, he will sync all his work/data to the online database with single click.

I know how to push the data to the online database, but the complexity here is how to maintain the indexes/primary keys of the records in sequence, without losing any data or mismatching any data.

How do I maintain the primary keys in sequence if a user syncs his offline data to online?


texts: Optimizing disk space for MyISAM tables: what is the benefit of ALTER and ANALYZE if you already do OPTIMIZE?

texts:
Per this post, in order to optimize the storage of a MyISAM table, you need to:

OPTIMIZE TABLE ;
ALTER TABLE ENGINE=MyISAM ;
ANALYZE TABLE ;

It's obvious what OPTIMIZE does for disk space but what does ALTER and ANALYZE do when used after OPTIMIZE? I am asking specifically about disk storage, so for example, I don't care that ANALYZE improves join performance, etc.


texts: How to add an automatically-populated custom sequence number?

texts:
I have table something like this:

CREATE TABLE [dbo].[ado_test_table](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [tip] [varchar](10) NOT NULL,
    [datum] [datetime] NOT NULL,
    [doc_number] [int] NULL
) ON [PRIMARY]

I want column doc_number have sequential numbers for each tip. To be clear, I want to automatically or default or use a computed column with a function like this:

create FUNCTION [dbo].[fn_test_number]
(
     @tip varchar(10)
    ,@datum datetime
)
RETURNS int
AS
BEGIN
    DECLARE @number int

    select
        @number=    max(doc_number)+1
    from ado_test_table
    where
        year(datum) = year(@datum)
        and tip = @tip

    set @number=isnull(@number,1);

    return @number
END

In SQL Server 2005, can I have a column which will automatically have values? Should this be done by computed column, default (constraint) or trigger?


texts: Why I can't order temporary table in MySQL procedure?

texts:
I have MySQL procedure, which forms temporary table ResultTabl and at the end show it for user:

CREATE PROCEDURE stop_factor_addition (IN fromDate varchar(100), IN toDate varchar(100))
BEGIN

DECLARE week VARCHAR(100);
DECLARE curWeek VARCHAR(100) default '';
DECLARE stopfactor VARCHAR(100);
DECLARE countdeals int;

CREATE TEMPORARY TABLE WeekFactors (name varchar(100));
CREATE TEMPORARY TABLE WeekFactorsDifference (name varchar(100));
CREATE TEMPORARY TABLE ResultTabl (week varchar(100), stopfactor varchar(100), countdeals int);

...
...

select * from ResultTabl;
END

Result is:

But I want to order by week field. I add order by week on select statement :

CREATE PROCEDURE stop_factor_addition (IN fromDate varchar(100), IN toDate varchar(100))
BEGIN

DECLARE week VARCHAR(100);
DECLARE curWeek VARCHAR(100) default '';
DECLARE stopfactor VARCHAR(100);
DECLARE countdeals int;

CREATE TEMPORARY TABLE WeekFactors (name varchar(100));
CREATE TEMPORARY TABLE WeekFactorsDifference (name varchar(100));
CREATE TEMPORARY TABLE ResultTabl (week varchar(100), stopfactor varchar(100), countdeals int);

...
...

select * from ResultTabl order by week;
END

But nothing changes:

Why order by not work on this temporary table?


texts:
I have two tables called record and record_history. For each record, there could be more than one history. They can be joined by id and record_id. I want to get all the record's entries with recent record_history data. I have created the query like,

SELECT rec.id, rec.name, rech1.data AS last_history_data
FROM record rec
LEFT OUTER JOIN record_history rech1 ON (rec.id = rech1.record_id)
LEFT OUTER JOIN record_history rech2 ON (rec.id = rech2.record_id AND rech2.ts > rech1.ts)
WHERE rech2.id IS NULL
ORDER BY rec.id DESC

Here, I am getting the latest one by ts. This works as long as there are no duplicate ts entries. If the recent timestamp is repeated in record_history, this query returns more than one row for a record. How can we apply the limit here on the left join to restrict duplicate rows?


texts: Unable to backup postgresql database. pg_dump memory alloc error

texts:
After 3 days of running an import routine written on RoR to move some 269,000 records from a legacy database via csv files the routine finally finished.
The very first thing I attempt to do is to backup the database using pg_dump before doing anything else.

This resulted in the following

  pg_dump -Ft hsb > hsb_after_full_import.tar

pg_dump: Dumping the contents of table "companies" failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR:  invalid memory alloc request size 18446744073709551613
pg_dump: The command was: COPY public.companies (id, category_id, city_id, name, address, town, neighbourhood, post_code, lon, lat, contact, email, facebook, twitter, listing_url, image_url, phone, website, listing_cid, promotion, description, rating, reviews, wheelchair_accessible, merchant_verified, uploaded, created_at, updated_at, server_id, opening_hours, multinational, places_id, featured_image_file_name, featured_image_content_type, featured_image_file_size, featured_image_updated_at, places_lat, places_lon, source, sanitised) TO stdout;

I am unable to find a tool to verify and fix any potential corruption. Indeed I have encountered many suggestions that Postgresql doesn't get corrupted (I find that impossible to believe) and therefore needs no repair/verification tools.

The import went smoothly and many tests of a few thousand records were undertaken before the full run to determine the suitability of the import script.

There is no point re-creating the database and re-importing if Postgresql is unable to handle this amount of data and just corrupt again.

I would really appreciate some guidance in the best way to proceed? Maybe an alternative backup tool?

The data appears to be OK. The app that is using the data seems to behave itself properly, maybe it's just the pure size of the dump and maybe an issue with the amount of ram (4gb) I have on my machine?

PostgreSQL version

pg_config --version
PostgreSQL 9.3.12

The database was created using Rails db rake task

  rake db:create

From PGAdmin III the create script looks like this

-- Table: companies

-- DROP TABLE companies;

CREATE TABLE companies
(
  id serial NOT NULL,
  category_id integer,
  city_id integer,
  name character varying(255),
  address character varying(255),
  town character varying(255),
  neighbourhood character varying(255),
  post_code character varying(255),
  lon character varying(255),
  lat character varying(255),
  contact character varying(255),
  email character varying(255),
  facebook text,
  twitter text,
  listing_url character varying(255),
  image_url character varying(255),
  phone character varying(255),
  website text,
  listing_cid character varying(255),
  promotion text,
  description text,
  rating character varying(255),
  reviews integer,
  wheelchair_accessible boolean NOT NULL DEFAULT true,
  merchant_verified boolean NOT NULL DEFAULT true,
  uploaded boolean NOT NULL DEFAULT true,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,
  server_id integer,
  opening_hours text,
  multinational boolean DEFAULT false,
  places_id character varying(255),
  featured_image_file_name character varying(255),
  featured_image_content_type character varying(255),
  featured_image_file_size integer,
  featured_image_updated_at timestamp without time zone,
  places_lat double precision,
  places_lon double precision,
  source character varying(255),
  sanitised boolean DEFAULT false,
  CONSTRAINT companies_pkey PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE companies
  OWNER TO wpsmiler;

I'm running Linux mint 17.3

select pg_total_relation_size('companies') gives me 113459200

If I run the query manually I get some records output (I assume 100 because of the message) then the same error

Query returned more than 100 copy rows, discarding the rest...

ERROR:  invalid memory alloc request size 18446744073709551613
********** Error **********

ERROR: invalid memory alloc request size 18446744073709551613
SQL state: XX000


texts: Mongo Scaling - Too Many Connections - Linear Horizontal Scaling with 100s of App Servers

texts:
I have a Mongo Cluster. It's sharded on 6 servers, with 6 replica sets, each replica set having one Primary, and two Secondaries.  Additionally, there are 3 config servers. Though, I think the Mongo Cluster configuration is irrelevant to this question.

I have many application servers (currently 100), with the intention of having many more as my volume grows.  On each application server, I have a mongos process, 8 http servers (node.js) each bound to a port (using HAProxy as a reverse proxy to route traffic from port 80).  Each http server connects to the local mongos process using connection pooling with a poolSize of 3.  Pretty simple so far.

The issue is that the number of connections seems to quite simply be:

(number of servers) * (number of http processes) * (poolSize)

In my current case that would be:

100 * 8 * 3 = 2400 total connections

So it would seem that the number of connections scales linearly with the number of servers I'm running.  If I scale up 5x, then the number of connections goes to 9,600 which is dangerously close to the 10,000 limit (as well as seeming unnecessarily wasteful).  What about a 10x or 100x scale up?

It is stated that "any connection count greater than 1000 – 1500 connections should raise an eyebrow".  How can I possibly maintain that number of connections with 100s of application servers? Is a mongos process per application server at this size still appropriate?


texts: Efficient table structure for application logging

texts:
Am using SQL Server as my RDBMS and i want to maintain some transaction of an auto run tool which will log some info on a daily basis. I need some suggestions how an efficient structure to be.

I mean single table will be an efficient one or two or more tables based on the transaction type (say for example success,failure).

If possible, explain with a simple scenario


texts:
I am trying to construct history entries for my application tables.  (Basically a "who changed what and when" set of tables.)

My problem is that I have a lot of users.  Well over a thousand.  My users use UI applications that then call services.  The users authenticate to those services using OAuth2 and Open Id Connect (OIDC).  So I can't use "Integrated Security" to access SQL Server.  Instead I access SQL Server using a service account.

This means a call to SUSER_NAME() will return the service account instead of the user.

I have thousands of SQL queries.  It will be A LOT of work to go change each one to add a user parameter.

I am really hoping there is some way I can pass the real user to SQL Server.  I know this does not work, but an example would be something similar to adding it to a header in a call.

Is there some way to get the user to SQL Server besides the logged in user?

Or, failing that, is there some way to get SQL Server to work with access tokens (OAuth and OIDC)?


texts:
I already have SQL Server 2014 on my computer. I don't remember installing SSIS.

How can I check if I do have it if not how can I add it ?

Thank you


texts:
I'm developing a program that allows users to view and edit data.
My question:
Is there tools (sql server tools, may be users/roles) to implement the following:
There are two users: A and B. User A opened the edit form for row, at this point the user B can not edit the same row.

I know, that i can use colunm for store UserId(user, which edit this row), but this solution is not suitable.


texts: mysql inner join if value = true ( share system )

texts:
users

|  id  | name  |
|------|-------|
|   1  | John  |
|   2  | Clark |

groups

|  id  | group_name|
|------|-----------|
|   1  | groupName |

posts
authorType*
1- user
2- group

|  id  | post_data   | author | authorType* |
|------|-------------|--------|-------------|
|   1  | Lorem Ipsum |    1   |      1      |
|   2  | Lorem Ipsum |    1   |      1      |
|   3  | Lorem Ipsum |    2   |      1      |
|   4  | Lorem Ipsum |    1   |      2      |
..........

user_posts
share_in*
1- user
2- group

|  id  | user_id | sender_id | post_id | shared_id | share_in* |
|------|---------|-----------|---------|-----------|-----------|
|   1  |    1    |     1     |    1    |    null   |   null    |
|   2  |    1    |     1     |    2    |    null   |   null    |
|   3  |    2    |     2     |    3    |    null   |   null    |
|   4  |    1    |     1     |    3    |      3    |     1     |
|   5  |    1    |     1     |    3    |      1    |     2     |

How to execute the query

select * from user_posts where user_id = 1
if( user_posts.share_in = 1 ) { join users on user_posts.shared_id = users.id }
else join groups on user_posts.shared_id = groups.id


texts: SSRS 2017 reports having issues with IE 11?

texts:
I'm experiencing symptoms very similar to what is described in this 2015 MSSSQLTips article:

SQL Server Reporting Services (SSRS) is not compatible with Internet Explorer 11

  You upgraded to Internet Explorer 11, now when you preview a SQL Server Reporting Services (SSRS) report you find the print command is not visible in the report viewer. Also, sometimes when you change parameter values IE11 stops responding or it takes a long time to load the report. You may also find that the toolbar that displays the report controls and export features, appears on multiple lines when viewed with IE11. When you preview the same SSRS report in Chrome or Mozilla Firefox, it works fine without any issue, which means it is a browser compatibility issue. How can you make SQL Server Reporting Services (SSRS) compatible with IE11?

Virtually all discussion I can find of this problem refers to this article (or to a now-unavailable MS Connect page).

The problem is that the server-side fix suggested for SSRS 2008 doesn't work for SSRS 2017 (there is no <meta http-equiv="X-UA-Compatible" content="IE=5"> tag in the ReportServer\Pages\reportviewer.aspx file).

Is there an equivalent server-side fix for SSRS 2017? Should I be adding this meta tag to this or a different ASPX file?

Other posts suggest upgrading SSRS, like this question about SSRS 2012, but I'm already on the next-to-last version of SSRS 2017 (14.0.600.689), and the change log for the more recent patch doesn't seem to contain any relevant fixes.

(A client-side fix isn't absolutely out of the question, but would involve a more difficult AD group policy push, so I'd prefer a server-side fix if there is one.)


texts: Is there a way to only allow SQL Server 2014 syntax/functions to be used in SQL Server 2019?

texts:
We're developing against SQL Server 2019 currently, but we have a need to deploy the same set of SQL scripts to a SQL Server 2014 database.

For example, in SQL Server 2019 a built-in function called TRIM exists but this is not supported in SQL Server 2014. To make sure we're only using syntaxes supported by both databases, I'm wondering if there's a solution to just allow 2014 syntaxes in 2019.

I thought altering compatibility mode of the database would have this effect, but this does not have the intended effect.


texts: How are auto_increment keys handled in INSERT (SELECT * FROM...)

texts:
I have table1 and table2 in MySQL. Both have a primary auto_increment key id.

If the table schemas match and I do INSERT INTO table1 (SELECT * FROM table2) what happens with regards to the new rows inserted in to table1? Do they keep their old id values and generate conflicts when a row from table1 has the same id? Are new values generated by auto_increment? Does it depend on the storage engine or locking?


texts:
How can I fine tune MariaDB for local work in my computer?
I use a LAMP server in Virtualbox my computer, for web design local development (only me).
The problem is I find the MariaDB slow. For example, sometimes it takes about 10 seconds to edit some Wordpress pages in it's Dashboard. I understand that a database server like MAriaDB is configured by default to handle the workload of many simultaneous connections. So, it must impose limits to them.
But, in this case, as there is only one person, I would like to lift all those limits and unleash the full power of it, so operations that take now 10 seconds can be done in 1 second or less.
So, what changes can I make in order to accomplish this? I have googled and made various changes to mariadb-server.cnf, but only accomplished litle speed. I use some tools to measure performance, and they all give me ultrafast PHP performance, but slow database one.
Also, it's not mandatory for me to use MariaDB; I can stop using it and use other solution more adequate to this situation. Can somebody shed some light in here?


texts: Restarting primary SQL Server instance with log shipping

texts:
I have configured log shipping with 20 minutes for backup, copy and restore. If i need to restart my primary server. do I need to stop all jobs (backup, copy and restore)?

Please advise.

Regards
Arvind Mishra


texts:
I am struggling with array matching and getting multiple records back.

"players" : [
    {
        "pname" : "player_4",
        "loc" : [ -2.2, 1, 0]
    },
    {
        "pname" : "player_2",
        "loc" : [ 9, 1, 5 ]
    }
]

Now assuming above data,

How do I project correctly where I get all the "loc" records that match the criterion?

Let's say loc.0 <15 should give both records, loc.0>0 should only give "player_2" etc.

I tried "players", "players.$". Without the $ I get all the data in "players". With the $, I get the first match. Obviously I am missing something big somewhere.

My trials below:

db.worlds.find({"name":"w1", "players.loc.0":{$lt:5}},{"players":1})

db.worlds.find({"name":"w1", "players.loc.0":{$lt:15}},{"players":1})

db.worlds.find({"name":"w1", "players.loc.0":{$lt:15}},{"players.$":1})

This is on Mongo 3.6.3.


texts: SQL Server Agent job returns a failure status message after the job succeed.

texts:
We have a sql server agent job which runs every day on sql server 2008R2. The job has been running for years and had no issues before; and nothing has been changed recently.

Two weeks ago, the job started sending a failed job email once every 2 or 3 days. It also logs a failed job status both on sql server agent job history  as well as event viewer. However, the job is successfully executed because we are getting a successful report from the application side all the time.  Can someone have any idea how to fix this issue?

Thanks


texts: how do I configure a new Oracle 11g instance to match the configuration of the old Oracle 10g server

texts:
How can I compare two Oracle database servers which are on two different machines?

We need to ensure that the new server is setup correctly.

For example, one machine is running Oracle 10g on Windows, and the second machine is running Oracle 11g on Linux.

How can I ensure that we have correctly set up all the parameters on the new 11g machine?

Some of the items I need configured are:

Temp Tablespace size
SGA size
any other particular fine tuning that has been applied on the Oracle 10g machine.


texts: Does the Int type primary key insert order affect query performance in MySQL?

texts:
Table t has an Int type primary key id without "Auto Increment"

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
);

Use the following three ways to insert data:

Insert sequence id: 1-10000
Descending insert id: 10000-1
Random insert id: 1-10000

Does the order in which rows are inserted as suggested above affect performance in select, order by id and query operations?


texts:
Can I on purpose lock the admin out of an Azure SQLDB (as admin)

As this may sound as a strange question on first sight. The goal is to only have sql users with data reader & writer rights on the database once the database is setup. Afterwards No accounts should exist anymore that have more rights than Reading & writing to the existing table structure. This behaviour should be irreversible.


texts:
I have two tables, Lpn and Uld that will relate on one column, that is, UldNo.

To give some background on the issue

An Lpn is a label that is assigned to one or more pieces of freight in a warehouse. A Uld is a pallet that the freight is built on so that it can go on an airplane. Every Uld has a unique identifier on it, consisting of a 3 letter type, 5 digit serial number, and 2 letter suffix that indicates the owner of the pallet. A Uld can come through a warehouse multiple times, and each time would have different Lpns (freight) on it.

To ask about the database

An Lpn row will only relate to one Uld row, and a Uld row may relate to multiple Lpn rows. The issue comes along is that UldNo cannot be the sole PRIMARY KEY (PK) for Uld because a Uld may be reused later (with different Lpns), so I created a PK of ID. If I try to create the relationship, I get an error because UldNo is not a PK. From everything I've learned, composite keys should be avoided. Is it correct to use a composite key in this situation? And if so, do I need to create the ID column in the Lpn table to create the relationship?

The following assumptions are correct:

On each flight, multiple pallets are transported.
Each pallet contains multiple LPNs (labels)
Pallets get reused.
LPNs (labels) are NOT reused.

The DBMS I'm using is SQL Server 2016 Express.


texts:
I've found pretty poor informations about mail databases.

My need is to archive mail messages downloaded from a normal pop3 or imap account: I am an ordinary user and I do not own the server, but just the mailbox. So I should exclude solutions like dbmail, suggested in other answers.

Two questions:

1) can MySQL be used for this purpose?

2) If yes, which is the best data type to be used for the column which will contain the body of the mail? It can include attachments (represented as sequences of characters, as you know) and its length is definitely unpredictable: it may vary from a few bytes to several MiB and it should not impose a (small) upper limit to the design. It should have a decent performance, if exists.

In brief: which is the most common approach when handling this kind of data with a MySQL database?


texts: Query performance with primary keys of different data types

texts: Is there a significant difference in performance if i decide to use INT or BIGINT as data type of a primary key in a table? Of course if i use BIGINT the value will have twice it's size. But as the primary key has always an index to speed up queries, is there going to be a significant difference in performance?


texts:
I followed directions from this page: https://serverfault.com/questions/271367/importing-data-from-myd-back-up-file

I dropped the 3 files ( t2010_08.frm, t2010_08.myd, t2010_08.myi ) into my /var/lib/mysql directory.

Then I changed permission with the "chown mysql:mysql t2010_08.* command". Now, when I go to look at my table ( in HeidiSQL ), it tells me that it is type "View" and I get "Can't find file: 't2010_08' (errno:2) message.

What do I still need to do to gain access to this table?


texts:
I have migrated the mysql 5.1 to 5.7 by using the mysqldump file. But i am facing the issue during  master slave replication as below.

Error 'Illegal mix of collations (utf8_general_ci,IMPLICIT) and (latin1_swedish_ci,EXPLICIT) for operation '='' on query. Default database: 'iiflcms'. Query: 'UPDATE site_user_master SET is_active = 'N', modification_date =  NAME_CONST('modificationDate',1615022896), passkey =  NAME_CONST('passKey',_latin1'9419' COLLATE 'latin1_swedish_ci') WHERE email_id =  NAME_CONST('userEmail',_latin1'Raj.fm.kangra@gmail.com' COLLATE 'latin1_swedish_ci')'

I have tried to check the default collection character set in database level as well as table colume level. it is showing the same as utf8. if it is the same then ther should not be any issue during replication.
but not sure why i am getting the above error on slave machine and beacuse of these error my replication is not stop.
i got workaround and set the collection and character set as latin1 and latin1_swedish_ci and my replication is started working. but problem is now on master character set and collection is utf8 and utf8_general_ci and in slave is latin1 and latin1_swedish_ci.
if i set these is there any problm in futues for few tables??


texts: Oracle 12c: installing expdp and impdp on Linux

texts: The default Oracle installation for Linux didn't come with the expdp and impdp command line tools. I have trouble finding them on Oracle's website. How could I install them?


texts: MongoDB - Change primary shard only for a certain database

texts:
I have a sharded MongoDB cluster running 3 different Databases.
Currently all 3 databases has the same shard configured as their primary shard (shard000)
Is it possible to change the primary shard for one of these databases to something other than shard000?

Just to make things clear, I have no intention of retiring shard000 any time soon, I just want to remove some of the non shared collections load off it.

Thanks,
Meny


texts: I have several new servers that need Database Mail configured on them.  I found the TechNet article describing how to add the profile and account via procedure calls, but is there a way to enable database mail and select the profile for the SQL Agent in the same manner?


texts: Getting time delta between two related rows

texts:
I have a table with a lot of entries in pairs, related to one another by a field name, each entry has a timestamp timestamp_received. I'd like to get the delta of the timestamp per name

table:

|id|name|timestamp_received|
|1|A|2016-09-21 11:11:00|
|2|A|2016-09-21 11:11:10|
|3|B|2016-09-21 11:11:20|
|4|B|2016-09-21 12:12:21|

desired output:

|name|delta|
|A|00:00:10|
|B|01:01:01|

What I have tried so far:

SELECT name, timestamp_received - LAG(timestamp_received, 1) OVER (PARTITION BY name ORDER BY id ASC) delta
FROM message;

Which does not produce the expected output

SELECT fpm_left.name, age(fpm_right.timestamp_received, fpm_left.timestamp_received)
FROM
  (SELECT * FROM message WHERE id IN (SELECT min(id) AS id FROM message GROUP BY name)) fpm_left
JOIN (SELECT * FROM message WHERE IN in (SELECT max(id) AS id FROM message GROUP BY name)) fpm_right
    ON fpm_left.name = fpm_right.name AND fpm_left.id != fpm_right.id;

Which produces the expected output, but seems a little innefficient

Can this be done more elegantly?


texts: Is time to deliver data included in time measured for a Slow Query

texts:
I'm looking at some horrible numbers in a SLOW_QUERY_LOG (one example)

Count: 2563 Time=23.21s (59498s) Lock=0.00s (1s) Rows=21.4 (54857)

This is for a simple SELECT statement that runs for less than a second when I try to run it manually.

The database services a java/tomcat application that I know have some problems.

My question:

Is it possible for the calling application to cause these abnormal numbers in the database? For example by asking the database for some data and then taking it's sweet long time to accept it?


texts: Atrocious performance joining INSERTED and DELETED tables in a trigger

texts:
I've got an UPDATE trigger on a table that watches for a specific column changing from one specific value to any other value. When this happens, it updates some related data in another table via a single UPDATE statement.

The first thing the trigger does is check to see if any updated rows had the value of this column changed from the value in question. It simply joins INSERTED to DELETED and compares the value in that column. If nothing qualifies, it bails out early so the UPDATE statement doesn't run.

IF NOT EXISTS (
    SELECT TOP 1 i.CUSTNMBR
    FROM INSERTED i
        INNER JOIN DELETED d
            ON i.CUSTNMBR = d.CUSTNMBR
    WHERE d.CUSTCLAS = 'Misc'
        AND i.CUSTCLAS != 'Misc'
)
    RETURN

In this case, CUSTNMBR is the primary key of the underlying table. If I do a large update on this table (say, 5000+ rows), this statement takes AGES, even if I haven't touched the CUSTCLAS column. I can watch it stall on this statement for several minutes in Profiler.

The execution plan is bizarre. It shows an Inserted Scan with 3,714 executions, and ~18.5 million output rows. That runs through a filter on the CUSTCLAS column. It joins this (via nested loop) to a Deleted Scan (also filtered on CUSTCLAS), which executes only once and has 5000 output rows.

What idiotic thing am I doing here to cause this? Note that the trigger absolutely must properly handle multi-row updates.

EDIT:

I also tried writing it like this (in case EXISTS was doing something unpleasant), but it's still just as terrible.

DECLARE @CUSTNMBR varchar(31)
SELECT TOP 1 @CUSTNMBR = i.CUSTNMBR
FROM INSERTED i
    INNER JOIN DELETED d
        ON i.CUSTNMBR = d.CUSTNMBR
WHERE d.CUSTCLAS = 'Misc'
    AND i.CUSTCLAS != 'Misc'

IF @CUSTNMBR IS NULL
    RETURN


texts: MySQL table locks solution -> InnoDb / Partitions

texts:
I'm experiencing many table locks in a MyISAM MySQL table that is pretty big (Constx10xGB and 15M rows).

It's mainly caused due many heavy inserts. My current thoughts to optimize:

Changing to Innodb - to avoid table level lock. In the past I've had bad experience with Innodb, possibly due to bad configuration. If you would recommend this option, please advise regarding configuration.
Adding partitions - we're running on one server, and we don't have different mounts in the HDs, so we're just avoiding locks in such a solution. Currently the table isn't 'key-value', but it can be done (normalization to different table).

Which would you recommend / other options?

Thanks.


texts:
I am trying to change the pagination from OFFSET, LIMIT to keyset pagination. My problem is, the resultset consists of several groups (plz) ordered by uid. I added a fiddle, see http://sqlfiddle.com/#!9/62a380/10.
Query to get the results
SELECT
  uid, nick, u.plz, geodb_locations.name
FROM
  user as u
LEFT JOIN
  geodb_locations ON u.plz = geodb_locations.id
WHERE
  u.plz IN(29386,30013,29384,29385,29232,29481,29667,29355,29977,30370,30319,30160,29970,29932,29679,29268,30128,29169,29546,29235,29444,29148,29313,30259)

ORDER BY
  FIELD(u.plz,29386,30013,29384,29385,29232,29481,29667,29355,29977,30370,30319,30160,29970,29932,29679,29268,30128,29169,29546,29235,29444,29148,29313,30259),
  uid DESC

Resultset
uid     nick    plz     name
46208   user21  29386   Graz
46050   user22  29386   Graz
44995   user23  29386   Graz
-----------------------------------
12883   user46  29384   Gratkorn     <-- Pagination with only uid stops here
-----------------------------------
32667   user47  29385   Gratwein
-----------------------------------
33950   user50  29481   Hitzendorf
33926   user51  29481   Hitzendorf
-----------------------------------
16027   user52  29667   Lannach
496     user53  29667   Lannach
-----------------------------------
19971   user55  29977   Pöls
-----------------------------------
37080   user58  30370   Weiz
33844   user59  30370   Weiz

To paginate through the results I use
uid < 46050

Problem: Pagination is not possible for the whole resultset because uid can be higher than the last uid because of several groups within the resultset. So pagination has to be done by more than 1 column!
Next problem: There is no second column for pagination.
WHERE .. IN and ORDER BY FIELD sorting is fixed. These are zip-codes and they are mixed for each different query, so they can't be used for keyset pagination like (plz, uid) < (123, 987654)
So how is it possible to paginate through the resultset? Any ideas? Thanks in advance!
Fiddle
http://sqlfiddle.com/#!9/62a380/10


texts: Postgres not returning data on array_agg query as below

texts:
The problem arise when there are no data for books in specific library. Consider a following working scenario.
Table library
--------------------------------
| id |    name     |    owner  |
--------------------------------
|  1 |     ABC     |     A     |
|  2 |     DEF     |     D     |
|  3 |     GHI     |     G     |
--------------------------------

Table books
--------------------------------
| id |    title    |  library  |
--------------------------------
|  a |     xxx     |     1     |
|  b |     yyy     |     1     |
|  c |     zzz     |     2     |
--------------------------------

Now when I do query like below:
SELECT library.name, array_agg(b.title) AS book_list FROM library,
(SELECT title FROM books WHERE books.library = :library_no) as b
WHERE library.id = :library_no GROUP BY library.id

The query generates output for library 1 & 2, but not for library 3. Why and how to solve this issue? (Generate an empty list on no library books)
Required Output:
----------------------
| name |    book_list |
----------------------
|  GHI |      {}      |   # or {null}
-----------------------

I've even tried coalesce as below:
SELECT library.name, coalesce(array_agg(b.title), ARRAY[]::VARCHAR[]) AS book_list FROM library,
(SELECT title FROM books WHERE books.library = :library_no) as b
WHERE library.id = :library_no GROUP BY library.id

Postgres version: 12


texts: When the old execution plans changed in plan cache

texts:
Can any one share demo for when the execution plans changing in plan cache. For example as I know if the table has a modifications of 20% + 500 rows then the table columns and index statistics are updated automatically. So my question is once they updated whether the old execution plan changed to new execution plans with the new update stats on the table or not.

Please provide any demo for checking of same.


texts: How postgres deals with XIDs and wraparounds?

texts:
I was reading the docs about the xid and it's wraparounds. But can't understand how it all works.

  Normal XIDs are compared using modulo-2^32 arithmetic. This means that for every normal XID, there are two billion XIDs that are “older” and two billion that are “newer”.

How are they compared
According to documentation xid is 32-bit number and as far as I understand it is incremented with every transaction


texts: Finding aggregated values based on dates in another table

texts:
I am using MS SQL SERVER. I have a table EMPLOYEE_ABSENCES that looks like this (it's not actually a temporary table, I made it into a temp table for this example):
CREATE TABLE #EMPLOYEE_ABSENCES
(
    EMPLOYEE_ID  VARCHAR(3),
    ABSENCE_DATE DATE
);

INSERT INTO #EMPLOYEE_ABSENCES
(
     EMPLOYEE_ID,
     ABSENCE_DATE
)
VALUES
    ('111', '2020-03-24'),
    ('222', '2020-04-16'),
    ('111', '2020-07-13'),
    ('333', '2020-07-13'),
    ('111', '2020-10-19');
GO

I have another table that looks like this:
CREATE TABLE #EMPLOYEE_POSITIONS
(
    EMPLOYEE_ID         VARCHAR(3),
    POSITION_START_DATE DATE,
    POSITION_END_DATE   DATE,
    FTE                 NUMERIC(6, 5)
);

INSERT INTO #EMPLOYEE_POSITIONS
(
     EMPLOYEE_ID,
     POSITION_START_DATE,
     POSITION_END_DATE,
     FTE
)
VALUES
    ('111', '2019-09-01', '2020-04-08', 0.75),
    ('333', '2020-01-01', '2020-09-18', 0.25),
    ('333', '2020-01-01', '2021-01-19', 0.5),
    ('222', '2020-01-19', '2021-01-19', 0.2),
    ('222', '2020-02-02', '2020-04-20', 0.3),
    ('111', '2020-05-20', '2020-07-14', 0.4),
    ('333', '2020-06-06', '2020-09-18', 0.25),
    ('111', '2020-07-15', '2021-01-20', 0.5),
    ('111', '2020-08-08', '2020-12-24', 0.3);
GO

As employees change position, their FTE changes.
An employee can hold more than one position, in which case, their FTE on any given date is the total of all FTE values they hold on that date.
For example, employee 111 holds a single position with a value of 0.5 from 2020-07-15 until 2020-08-07.
On 2020-08-08, she gains a second position with a value of 0.3 giving her a total FTE of 0.8 until on 2020-12-25 she loses her second position and returns to a single position with a value of 0.5
I need a query that will tell me the dates employees were absent in the year 2020, the employee number and their sum(FTE) on the date they were absent.
I am expecting a result like this for the above tables:
-- 2020-03-24, 111, 0.75
-- 2020-04-16, 222, 0.5
-- 2020-07-13, 111, 0.4
-- 2020-07-13, 333, 1
-- 2020-10-19, 111, 0.8

Note that two employees were absent on 13 July: employee 111 with an FTE of 0.4 on that date and employee 333 with a total FTE of 1 (0.25+0.5+0.25) on that date.
I think that the solution will involve creating a tally table of all dates in 2020, joining it to an aggregate of the EMPLOYEE_POSITIONS table to get the employees' total FTE for each day of the year, then joining that with the EMPLOYEE_ABSENCES table, but I can't seem to get it to work. Does anyone have any suggestions? Is there an easier way?
Thank you,
Brian


texts: Selecting entries with the same count as in another table

texts:
I'm using a SELECT statement to select multiple IDs and than use

GROUP BY ID
HAVING COUNT(*) = someNumber

to only have the IDs with a certain count.

What I however actually want to do is to select all those IDs that have the same count that they have in another table.
I tried some variations of the following but couldn't get my desired result.

GROUP BY ID
HAVING COUNT(*) = (SELECT COUNT(entryName_ID) FROM entryNames)

Is it even possible to do what I want? And if so how?


texts: Why does my WHERE clause benefit from an "included" column?

texts:
According to this answer, unless an index is built over the columns which are used to restrict, the query will not benefit from an index.

I have this definition:

CREATE TABLE [dbo].[JobItems] (
    [ItemId]             UNIQUEIDENTIFIER NOT NULL,
    [ItemState]          INT              NOT NULL,
    [ItemPriority]       INT NOT NULL,
    [CreationTime]       DATETIME         NULL DEFAULT GETUTCDATE(),
    [LastAccessTime]     DATETIME         NULL DEFAULT GETUTCDATE(),
     -- other columns
 );

 CREATE UNIQUE CLUSTERED INDEX [JobItemsIndex]
    ON [dbo].[JobItems]([ItemId] ASC);
 GO

CREATE INDEX [GetItemToProcessIndex]
    ON [dbo].[JobItems]([ItemState], [ItemPriority], [CreationTime])
    INCLUDE (LastAccessTime);
GO

and this query:

UPDATE TOP (150) JobItems
SET ItemState = 17
WHERE
    ItemState IN (3, 9, 10)
    AND LastAccessTime < DATEADD (day, -2, GETUTCDATE())
    AND CreationTime < DATEADD (day, -2, GETUTCDATE());

I reviewed the actual plan, and there's just one index seek with the predicate exactly as in the WHERE - no additional "bookmark lookups" to retrieve LastAccessTime even though the latter is only "included" into the index, not part of the index.

It looks to me like this behavior contradicts the rule that the column must be part of the index, and not just "included".

Is the behavior I observe the right one? How can I know in advance if my WHERE benefits from an included column or needs the column to be part of the index?


texts:
I have trawled through masses of documentation both on Microsoft website and Stack Exchange, yet I have failed to find answers to some basic questions:

What is a CAL? A license code that I need to enter? Or a legal document allowing me to use the software in a certain way?
Let's say someone simply installs SQLServer 2016, restores a DB and points 200 clients to it via TCP/IP protocol on port 1433. Will the clients be able to connect and use the DB server? If not, what software exactly and on what side (DB server/client) will stop it from working?

If you feel that I haven't searched well enough, would you be so kind and provide a link per each point above, leading to an exact answer (as opposed to several paragraphs of vague text that is usually found on MS website)


texts:
I am a little stumped with this CTE update stmt:

DECLARE @a TABLE (ID int, Value int);
DECLARE @b TABLE (ID int, Value int);
INSERT @a VALUES (1, 10), (2, 20);
INSERT @b VALUES (1, 100),(2, 200);

WITH cte AS
(
    SELECT * FROM @a
)
UPDATE cte
SET    Value = b.Value
FROM   cte AS a
INNER JOIN @b AS b
ON     b.ID = a.ID

SELECT * FROM @a
GO

Why does this result in table @a having 100 for both rows? I thought it should be 100 for ID 1 and 200 for ID 2.

I get expected results if I use a table instead of a common table expression to do the updates:

DECLARE @a TABLE (ID int, Value int);
DECLARE @b TABLE (ID int, Value int);
INSERT @a VALUES (1, 10), (2, 20);
INSERT @b VALUES (1, 100),(2, 200);

SELECT  *
FROM    @a

UPDATE @a
SET Value = b.Value
FROM @a AS XX
INNER JOIN @b AS b ON b.ID = xx.ID

SELECT  *
FROM    @a

This results in table @a with 100 and 200. But aren't we supposed to get both the same values? based on the previous explanation of referencing issue? -- Update being done to @a table and not the referenced XX.


texts:
I have SSRS2008 set up and running locally on the server (SRV0100).  It is set up to run under the localsystem account for the service account (default).

I changed the rsreportserver.config to use RSWindowsNTLM.

I have set domain users up to have browser access to the reports.

However - on other PC's, when I try to get to the report, it says:

Error Message 401.3: You do not have permission to view this directory or page using the credentials you supplied (access denied due to Access Control Lists). Ask the web server's administrator to give you access.

I added 'Everyone' to the report as well as domain\Domain Users.  Still the same issue even when restarting the SSRS service.

Any help?  I feel like I have read and tried everything :(

thanks in advance!

Andy


texts: dbcc checkdb runs for interminably long time, 50% of the time

texts:
I've got a nightly maintenance job (largely taken from the ola hallengren maintenance package) that runs database backups and runs an an integrity check on every database.

The integrity check part is running online, against production dbs, and usually completes in about ~2.5 hours - but sometimes it runs for much, much longer (12+ hours in some instances).

There aren't any other scheduled jobs or other activity that is running on the days where it hangs vs. the days where it doesn't, and the db it's usually hanging on is only about 170gb.

I took a look at my wait stats:

And my write stalls:

But I'm not a DBA by training and am not sure what to actually do about this information, or if it's even where I should be looking for this particular issue.


texts:
Guy I am trying to ranking some data from my database, and I notice that it's going very wrong when I put the group by clause;

SET @rank=0;

SELECT @rank:=@rank+1 AS RankSemGenero
    ,a.nome AS Artista
    ,f.nome AS Musica
    ,SUM(rnk.total) AS Tocadas
    ,rnk.mes AS Mes
    ,rnk.dia AS Dia
    ,current_timestamp() AS Criado_Em_Sem_Genero
    ,23 AS RankComGenero
    ,current_timestamp() AS Criado_Em_Com_Genero
    /*,CASE rnk.categoria
        WHEN 1 then 'AM'
        WHEN 2 then 'FM'
        WHEN 3 then 'Web'
        WHEN 4 then 'Comunitaria'
    END AS Categoria_Radio*/
    ,'Todas' AS TipoEmissora
    ,5 AS Relevancia_Emissora
    ,'Nacional' AS Local
    ,5 AS Relevancia_Local
    ,1 AS fl_ativo
FROM rnk201901 rnk
LEFT JOIN artistas a ON rnk.artista = a.id
LEFT JOIN fonogramas f ON rnk.fonograma = f.id
WHERE rnk.dia = 10
-- AND rnk.fonograma = 35876
-- GROUP BY rnk.fonograma
ORDER BY rnk.total DESC;

This code above bringing the information on the right way 1 until ....

But if I change the GROUP BY line, I am receiving something like: 1700 instead of 1.

GROUP BY rnk.fonograma

Any idea how to handle this group by counting 1 by 1?

Thanks!!


texts: Merge difference MySQL DBs into a single new server

texts:
I have 4 servers which has it's own MySQL server along with databases and users.
Now we have a new server dedicated only for MySQL and we would like to merge those MySQL servers into this new one.

What is the most efficient way to move those MySQL servers data (databases, users) into our new server?

btw, we use centos for all of our servers, all databases are mysql 5.1, and users and db names are uniques


texts: Get a list of orders by excluding the recents one in the same category?

texts:
I have a table of the following structure (simplified):

Table orders

id
created
name
executed
category_id

I need a list of n orders from distinct categories (if n is 5, I should have at most 5 different categories, and never two same categories). I successfully done this one using the following request :

SELECT   name
FROM     orders
WHERE    executed IS NULL
GROUP BY category_id LIMIT 5;

But I'd need to exclude the categories that has already been processed less than an hour ago.

For that, I was able to do the following query :

SELECT   name, category_id
FROM     orders
WHERE    executed IS NULL
AND      category_id NOT IN (SELECT   category_id
                             FROM     orders
                             WHERE    executed > (UTC_TIMESTAMP() - INTERVAL 60 MINUTE)
                             GROUP BY category_id)
GROUP BY category_id
LIMIT 5;

But I was wondering if there was a faster way to get the results.

I've made an SQLFiddle to show my example, but I find it quite slow and I was not able to get a successful result from them.

Here's an alternative version with a LEFT JOIN I tried but it doesn't work:

SELECT  u1.some, u1.columns
    FROM  orders AS u1
    LEFT JOIN  orders AS u2  ON u1.category_id = u2.category_id
      AND  u2.executed > (UTC_TIMESTAMP() - INTERVAL 5 SECOND)
    WHERE  u1.created < (UTC_TIMESTAMP() - INTERVAL 60 SECOND)
      AND  (u1.executed IS NULL
              OR  u1.executed < (UTC_DATE() - INTERVAL 1 MONTH)
           )
      AND  u2.category_id IS NOT NULL
      AND  u1.category_id NOT IN u2.category_id
    LIMIT  10;


texts: The right way of starting up a Galera Cluster

texts:
I am confused about the bootstrap process of MariaDB Galera Cluster.

Let's say we have 3 nodes of a MariaDB Galera Cluster, working and functioning normally on CentOS6/7. However, at some point we have (doesn't matter by what reason) to stop all 3 nodes and then start up the cluster. We are stopping the nodes 1 by 1 normally and cleanly. In order to start up the cluster, I go and check out grastate.dat and see which node is with most current data. I bootstrap this node first.

What is unclear to me is how the bootstrap is done exactly for CentOS 6 and 7. The 2nd and 3rd node can be started with systemctl start mysql. How do we start the first node? Do we use service mysql start --bootstrap on the first node with most current data? I read that for CentOS 7 it's systemctl start mysql@bootstrap.service. What is the procedure of boostrapping the first node of the cluster after we stopped all 3 nodes normally?

PS: Related to this, with some more details, is the post by dr01:
grastate.dat with seqno -1 on a healthy cluster. Why?


texts: sql architect regarding one to many relationship between table


texts: Connecting to a local MS SQL Server from a GUI client

texts:
I am very new to SQL and database servers. I have recently been using SQL Server Express and accessing my database using SQL Server Management Studio on Windows. I am now trying to achieve a similar setup on a Linux machine.

I would like to create a local SQL Server instance on the machine, and then use SQLectron as a GUI client for querying my database. I am using Manjaro Linux  and so install my packages from the AUR.

So far I have installed mssql-server, mssql-tools and sqlectron-gui. I have run the sudo /opt/mssql/bin/mssql-conf setup command in order to (I believe) set up a local server. After doing so, running the command systemctl status mssql-server returns this:

[kev@XPS-Manjaro ~]$ systemctl status mssql-server
● mssql-server.service - Microsoft SQL Server Database Engine
Loaded: loaded (/usr/lib/systemd/system/mssql-server.service; enabled; vendor preset: disabled)
Active: active (running) since Wed 2017-08-23 13:43:49 IST; 2h 42min ago
    Docs: https://docs.microsoft.com/en-us/sql/linux
Main PID: 9130 (sqlservr)
    Tasks: 165
CGroup: /system.slice/mssql-server.service
        ├─9130 /opt/mssql/bin/sqlservr
        └─9144 /opt/mssql/bin/sqlservr

Aug 23 13:43:52 XPS-Manjaro sqlservr[9130]: [145B blob data]
Aug 23 13:43:52 XPS-Manjaro sqlservr[9130]: [66B blob data]
Aug 23 13:43:52 XPS-Manjaro sqlservr[9130]: [96B blob data]
Aug 23 13:43:52 XPS-Manjaro sqlservr[9130]: [100B blob data]
Aug 23 13:43:52 XPS-Manjaro sqlservr[9130]: [71B blob data]
Aug 23 13:43:52 XPS-Manjaro sqlservr[9130]: [124B blob data]
Aug 23 13:49:03 XPS-Manjaro sqlservr[9130]: [156B blob data]
Aug 23 13:49:03 XPS-Manjaro sqlservr[9130]: [194B blob data]
Aug 23 13:52:31 XPS-Manjaro sqlservr[9130]: [74B blob data]
Aug 23 13:52:31 XPS-Manjaro sqlservr[9130]: [199B blob data]

I am now attempting to create a local connection to my server through SQLectron on the following screen:

As I have very little experience with SQL Servers, I am completely lost with where to get all of these credentials to connect to my local server. The only one I know of is the password which I set in the sudo /opt/mssql/bin/mssql-conf setup command.

How do I find the server name, host address (127.0.0.1?), port, domain, Unix socket path and username?


texts: Using Inteval Datatype to store result of calculation

texts: I want to create a table which has hiredate and Interval of hiredate and sysdate. How to create a column that automaticlaly stores the calculation.


texts:
Consider the following queries

CREATE TABLE MyTable (f2 INT);

ALTER TABLE MyTable ADD COLUMN f1 INT, DROP COLUMN f1;

In the above query drop column seems to happen before add column and query fails. Similarly rename column happens before add column.

Alter does not seem to happen in the given order and there are number of alter operations such as modify, add/drop index, add/drop unique key. I am not able find any MySQL documentation for their order of execution.

What is the order of execution of alter operations in an alter query in MySQL?


texts:
Looking for assistance improving a query. This query is used to review the last N transactions in the LDF file and then add some useful information like the user, start time, query text, etc..

Here is the query:

SET NOCOUNT ON
DECLARE @LSN NVARCHAR(46)
DECLARE @LSN_HEX NVARCHAR(25)
DECLARE @tbl TABLE (id INT identity(1,1), i VARCHAR(10))
DECLARE @stmt VARCHAR(256)
DECLARE @NUMOFTRANSACTIONS AS INT = 3;

-- common table expression to get the last N LSNs from the LDF
with currentlsns as (
SELECT TOP(@NUMOFTRANSACTIONS) [Current LSN] FROM fn_dblog(NULL, NULL) WHERE [Transaction SID] IS NOT NULL order by [Transaction ID] DESC
)
-- set the @LSN to the lowest because we are reading forward
SELECT @LSN = MIN([Current LSN]) from currentlsns

-- Convert @LSN to HEX for use in fn_dblog
SET @stmt = 'SELECT CAST(0x' + SUBSTRING(@LSN, 1, 8) + ' AS INT)'
INSERT @tbl EXEC(@stmt)
SET @stmt = 'SELECT CAST(0x' + SUBSTRING(@LSN, 10, 8) + ' AS INT)'
INSERT @tbl EXEC(@stmt)
SET @stmt = 'SELECT CAST(0x' + SUBSTRING(@LSN, 19, 4) + ' AS INT)'
INSERT @tbl EXEC(@stmt)

SET @LSN_HEX =
 (SELECT i FROM @tbl WHERE id = 1) + ':' + (SELECT i FROM @tbl WHERE id = 2) + ':' + (SELECT i FROM @tbl WHERE id = 3)

-- insert everything into a temp table so we only refer to fn_dblog once
IF OBJECT_ID('tempdb..#transactions') IS NOT NULL
DROP TABLE #transactions

SELECT * INTO #transactions
FROM fn_dblog (@LSN_HEX, NULL)

-- now show last N transactions and some connection, query info for those transactions
SELECT fd.SPID, es.login_name, text, Description, Operation, Context, [Log Reserve],AllocUnitName,[Begin Time],[Transaction Name],
[End Time], [Transaction Begin],[Number of Locks]
FROM
    #transactions,
    (   SELECT
            [Transaction ID] AS [tid],
            [Transaction SID],
            SPID
        FROM #transactions
        WHERE
        [Transaction SID] IN (select top(@NUMOFTRANSACTIONS) [Transaction SID] FROM #transactions WHERE [Transaction SID] IS NOT NULL order by [Transaction ID] desc)
   ) [fd]
INNER JOIN sys.dm_exec_connections c on c.session_id = fd.SPID
LEFT OUTER JOIN sys.dm_exec_sessions es ON es.session_id = fd.SPID
CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) as st
WHERE
    [Transaction ID] = [fd].[tid]
    AND [Transaction ID] IN (select top(@NUMOFTRANSACTIONS) [Transaction ID] FROM #transactions WHERE [Transaction SID] IS NOT NULL order by [Transaction ID] desc
 )

This part of the query takes too long

-- common table expression to get the last N LSNs from the LDF
with currentlsns as (
SELECT TOP(@NUMOFTRANSACTIONS) [Current LSN] FROM fn_dblog(NULL, NULL) WHERE [Transaction SID] IS NOT NULL order by [Transaction ID] DESC
)
-- set the @LSN to the lowest because we are reading forward
SELECT @LSN = MIN([Current LSN]) from currentlsns

I just want to get the Nth from the last LSN where the Transaction SID is NOT NULL. The only way I could think of to accomplish this was by creating a CTE and inserting the last N LSN's that match.

Question:
What would be a better way to find the last N'th Current LSN where [Transaction SID] IS NOT NULL?


texts: Estimation of creating index in SQL Server

texts:
We create new index on our production database, with a few fields, our database size is around 300GB and it works for one hour and do not finished yet.

How can you estimate this operation?
If we will stop index creation; How time it will make rollback by your approximate estimation?
How can we check creation status in management studio?


texts: Why adding FOREIGN KEY constraint does not lock the table

texts:
A "large" table :

mysql> select count(*) from MyTable;
+----------+
| count(*) |
+----------+
|  6594951 |
+----------+

Adding a FOREIGN KEY disallow to insert into the table

mysql> ALTER TABLE MyTable ADD CONSTRAINT FK_DC7F6DF281F7EA87 FOREIGN KEY (key_id) REFERENCES Key (id);

Take almost 20 minutes during it is impossible to add a new row.

My question: but why mysql does not consider the table as a locked table?

mysql> SHOW OPEN TABLES WHERE `Table` LIKE '%MyTable%' AND `Database` LIKE 'db';
+----------+-----------------------+--------+-------------+
| Database | Table                 | In_use | Name_locked |
+----------+-----------------------+--------+-------------+
| db       | MyTable               |      0 |           0 |
+----------+-----------------------+--------+-------------+
1 row in set (0.02 sec)

Thank you


texts: What is the HY number (HY000) in MySQL Error messages?

texts:
When I get an error some of them have an HY code,

  ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2 "No such file or directory")

What is HY000 in the above context. See this question for another example of HY000 in an error message


texts:
Right now I have the following tables items

name (varchar)
category (varchar)
id

...etc

I want to create a categories table and items_categories mapping table so i can start tracking categories in its own table.

The problem is with a large items table it takes awhile

Right now I do select distinct category on items table. That is going to be slow and there really isn't a way around it.

What is the fastest way to migrate the category field in items to have a category table and a categories items table? This has to be done in all Sql (no external scripts)


texts: How to load .dat files to oracle database

texts:
I have .DAT files extracted from telecom switch.

I want to upload this file to an Oracle server using IBM data-stage ETL tool.

Unfortunately these files are not readable format.

This is a picture of the .DAT file when opened in notepad:


texts: Setting up a many-to-many relationship based off of two other many-to-many relationships

texts:
I have 3 main top level entities:

Project (PK: Id)
Owner (PK: Id)
Product (PK: Id)

Here are the rules:

A Project can have many Owners. An Owner can have many Projects.
A Project can have many Products. A Product can have many Projects.

This is the part that is confusing me:

A ProjectOwner can have many ProjectProducts and a ProjectProduct can have many ProjectOwners.

Here is how I currently have it setup (w/ two additional tables besides the ones above):

ProjectOwner (PK: ProjectId, PK: OwnerId)
ProjectProductOwner (PK: ProjectId, PK: ProductId, PK: OwnerId)

I am trying to figure out if I am following best practice with this because something seems strange. For some reason I feel like I should have this broken out into 3 tables (ProjectOwner, ProjectProduct, ProjectOwnerProduct).

Just to give you an idea of how things will be saved in the application it will be as follows:

User creates  a Project. User creates Owners and during this process they add them to the project. User adds multiple Products to the Project and associates them with Project Owners from the previous step.


texts: How to change database owner for login and database created inside stored procedure?

texts:
I have a signed stored procedure that creates a new database, a new login and assigns the new user to be the owner of the new database. Well my stored procedure creates the new login and new database but will not assign the new user as the owner of the new db. The stored procedure is in an application database and not master.

The error I get is:

  Cannot find the principal 'BlahUser', because it does not exist or you do not have permission.

The stored procedure is executed under a different login (simpleUser) with limited rights (execute on stored procedure).

The certificate user has the following permissions:

create any database
alter any login

The code in question is :

SET @sqlStmt = 'ALTER AUTHORIZATION ON DATABASE::'+@sessionid+'Data TO ['+@orgName+'User]'

SET @metasql = '
USE [master]
EXEC (''' + REPLACE(@sqlStmt, '''', '''''') + ''')
'
EXEC sp_executesql @metasql

I tried granting impersonate from the executing user (simpleUser) to the new user inside the stored procedure like this, but it also results in an error.  I'm not sure if users who create other users can automatically impersonate them.

SET @sqlStmt = 'GRANT IMPERSONATE ON LOGIN::'+@orgName+'User TO simpleUser';

SET @metasql = '
USE [master]
EXEC (''' + REPLACE(@sqlStmt, '''', '''''') + ''')
'
EXEC sp_executesql @metasql


texts: Oracle 12 set user password Expiration date or EXPIRED(Grace)

texts:
Is it possible to set a future expiration date on a user account in Oracle?  Or to set a account status to EXPIRE(GRACE)

We want to implement a more strict password policy, and I've created the new Profile, but would like to use Oracle's password grace period function to warn users (instead of just running a script to immediately expire the user accounts).

(In short, yes I can and will send out an email telling everyone their password will expire in 10 days, but I'd rather have Oracle tell them when they log in so they can't deny seeing the warning)

I don't see an option for ALTER USER... for expiration dates.

Also when I added some users to the new profile (which has a 90 day Password Life Time and a 30 day Password Grace Time for some reason it automatically set their expiration date to 51 days and 6 hours from now, which I thought was rather odd.


texts:
I am new to linux, and i just installed MySQL-server-5.1.59-1.glibc23.i386.rpm in my linux machine.

I wanted to know where my.cnf file gets stored..?
Will my.cnf gets created with the installation or not..?

I am using Fedora core 13

Edited: Still i am not able to find this file.
When i asked my admin, he is telling we need to copy my-large.cnf and place in /etc folder.. is it so...?


texts:
Our server has a large MyISAM MySQL table that is no longer needed and is currently crashed. If we repair it then it greatly slows down our server consuming sources and it's almost 50GB so the process will take a very long time.

Can we safely delete the .myd and .myi files for the table manually? What would be the proper procedure for this to not affect the other tables. Would MySQL simply need to be restarted after we delete the files or are there more steps to this?

Thank you!


texts: how do I configure postgresql to find the pg_controldata command

texts:
I am trying to execute the pg_controldata command, I got the error command not found. Then I checked:

 ls -l /usr/lib/postgresql/9.4/bin/ | grep "control"
-rwxr-xr-x 1 root root   25960 feb  5 18:24 pg_controldata

Well, there is a pg_controldata, then:

Why I got the error command not found?
What kind of configuration do I have to do to my postgresql or my sistem?

Please try an answer with a code or file configuration example.


texts: MYSQL Master-Slave replication not working with PHP

texts:
My data gets replicated from the master database when I do it manually but when it gets inserted from PHP into the master database it doesn't get replicated to the slave database.

My Master database - my.cnf

server-id       = 1
log_bin         = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size         = 100M
binlog_do_db        = database

My slave database - my.cnf

server-id       = 2
log_bin         = /var/log/mysql/mysql-bin.log
relay-log               = /var/log/mysql/mysql-relay-bin.log
expire_logs_days    = 10
max_binlog_size         = 100M
binlog_do_db        = database
replicate_do_db     = database
slave_net_timeout = 30
sync_master_info = 1
sync_relay_log = 1
sync_relay_log_info = 1

My database table setup

  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tmdbid` varchar(9) NOT NULL,
  `poster` varchar(256) NOT NULL,
  `title` varchar(128) NOT NULL,
  `tagline` varchar(128) NOT NULL,
  `plot` text NOT NULL,
  `year` year(4) NOT NULL,
  `release` varchar(18) NOT NULL,
  `runtime` char(6) NOT NULL,
  `genres` varchar(256) NOT NULL,
  `actors` text NOT NULL,
  `votes` varchar(5) NOT NULL,
  `mpaa` varchar(6) NOT NULL,
  `file2` text NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `link` text NOT NULL,
  `file` text NOT NULL,
  `link1` text,
  `link2` text,
  `uploaded` text,
  `been_sent_to_folder` text,
  `poster_backup` varchar(256) NOT NULL,
  `saved_poster_name` text NOT NULL,
  `poster140x100` varchar(256) NOT NULL,
  `poster65x95` varchar(256) NOT NULL,
  `poster_orig` varchar(256) NOT NULL,
  PRIMARY KEY (`tmdbid`),
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;

PHP to insert to DB

mysql_query("INSERT INTO `database`.`tmdb` (`id`,`tmdbid`,`poster`,`title`,`tagline`,`plot`,`year`,`release`,`runtime`,`genres`,`actors`,`votes`,`mpaa`,`file2`,`link`,`file`,`poster_backup`,`saved_poster_name`,`poster140x100`,`poster65x95`,`poster_orig`) VALUES (NULL,'".$tmdbid."','".$poster."','".$title."','".$tagline."','".$plot."','".$year."','".$imrelease."','".$runtime."','".$genres."','".$actors."','".$votes."','".$mpaa."','".$file2."','".$link."','".$file."','".$poster_backup."','".$saved_poster_name."','".$finalthumb."','".$imgfinal."','".$imgfinal225."');")...


texts: ms-access concat a string with control source

texts: Sorry for this likely noob question but I cannot seem to find the solution. I'm trying to modify an existing field on a report, standard in this field the data is being pulled from the control source Insert under the properties pane and looks fine. I'm trying to change this into a 1D barcode which requires * before and after. I check the syntax for concating strings with inputs and it should be = "*" & [Insert] & '*" but this just throws a #Type! error on the report when it's viewed. What am I doing wrong?


texts: Query take a long time! Is that server issue?

texts:
When Query runs on Server (shared-server) it takes (2.5332 sec)
But when I run the same Query on another Server (dedicated-server), it takes only (0.0205 sec).

I don't know where is the issue! Is that server issue or query issue ?

Query :

SELECT n.title,
       res.field_res_type_value,
       res.field_spanish_value,
       n.created,
       n.nid,
       COUNT( n.nid )
FROM `node` n
  INNER JOIN accesslog log ON n.title = log.title
  INNER JOIN content_type_resource res ON n.nid = res.nid
WHERE n.type = "resource"
  AND log.timestamp >=1366365304
  AND log.timestamp <=1371635704
  AND n.status = 1
GROUP BY n.nid
ORDER BY COUNT( n.nid ) DESC LIMIT 0 , 6

Note : I used Drupal 6
shared-server has PHP Version 5.1.6
dedicated-server has PHP Version 5.3.17


texts:
According to the docs:

  PL/Python is only available as an "untrusted" language, meaning it does not offer any way of restricting what users can do in it and is therefore named plpythonu. A trusted variant plpython might become available in the future if a secure execution mechanism is developed in Python.

Why exactly is it difficult to develop a secure execution mechanism for Python but not for other languages such as Perl?


texts: WHERE vs. HAVING on non-aggregate columns. Advantages / Disadvantages / Irrelevant?

texts:
I'm in the process of rewriting queries that no longer pull in all the required data. My question is in regard to a practice I've never seen and haven't found any question on StackExchange that specifically addresses the issue.

I know the point of the HAVING statement is to introduce conditions on aggregations, just like WHERE introduces conditions on individual rows. However, what I'm seeing in this code is HAVING being used in lieu of WHERE on queries with aggregations. The conditions in HAVING are not applied against the aggregations, but on the non-aggregated columns.

For example:

SELECT id, filedate, SUM(amount)
FROM Sales
GROUP BY id, filedate
HAVING id = 123 AND filedate = '1/1/2018'

As opposed to:

SELECT id, filedate, SUM(amount)
FROM Sales
WHERE id = 123 AND filedate = '1/1/2018'
GROUP BY id, filedate

Are there performance implications or other advantages/disadvantages to this strategy?

I haven't tried running diagnostics myself, not a priority and I'd have to do it on my own time. However, I think I may if there isn't a clear answer on this.

My concern is how the optimizer views this query. Does it aggregate all data and then restrict the result set based on the HAVING clause, or does it realize it can apply the having conditions on the individual rows since they are specifically referencing non-aggregated columns?

EDIT: For my example queries and the actual SQL I'm rewriting, the plans are identical, but the queries are of similar complexity and I'm not yet knowledgeable enough to draw conclusions from the identical plans.


texts: How to determine character encoding in Hyper SQL database

texts: I have Hyper SQL database and I don't know what is current character set. Is it set for whole DB or it can be different for every table? How to evaluate encoding in my base?


texts: Instant synchronization of multiple SQL servers

texts:
First of all, I am sorry if this is a duplicate. I was unable to find anyone who adressed this particular question, or I might have failed to realize it as I read it. Most likely I used the wrong search-phrases, as I am not all that familiar with the terminology.

We have 3 servers:

A. SQL-server

B. Alarm system

C. Website + API + Other

Server B and C has to connect to server A all the time, and there have been several instances of downtime which has resulted in a lot of customer dissatisfaction.

We want to have the following: (different letters to make commenting easier, though it may not :p )

W. Alarm system (with SQL-server)

X. Website + API (with SQL-server)

Y. Website + API (with SQL-server)

Z. Other (with SQL-server)

EDIT: X and Y are technically identical, but X would normally host the website, and our apps and 3rd party solutions would normally connect to the API on server Y, to ensure that a hickup is only felt through one channel. If X were to crash, then Y would serve both the website and the API until X was back online and vise versa.

It is vital that the SQL-servers are synchronized. If a client makes a change in the database through the website (server X) and then activates the Alarm system W (to which the change matters) then it must have allready been synchronized.

Each system is using only some of the databases and tables, to save on resources we could sync only the vital data instantly, and the rest every few hours or so. Edit: not an option

We will be using SQL Server 2014 on all servers (unless someone convinces me otherwise)

Do we need any 3rd party software to accomplish this?
We are obviously keen on using the most cost-efficient solution, both money-wise and resource-wise, but customer dissatisfaction must end at all cost, we have never lost a client and we don't intend to lose one for having been cheap.
Would we have to use triggers, or should we, for the vital tables?
Could we use views?

That is to say; create the same views on all SQL-servers which loads from whatever server has the latest timestamp for that perticular table, in such-and-such a way that it will have instant sync. A real sync would still be necessary, but less frequent.
Not sure how this would work, but I wouldn't be surprised if someone have done this at some point, and found a solution to all the obvious and less obvious problems.

We have several functions, procedures, and jobs that are changed every now and then, can these be synced, or do I have to update them manually (which is not a big deal)?

UPDATE:

We've been in contact with a company that has helped us in the past with setting up servers, and getting the correct versions and licenses. Their suggestion was the following:

Give each SQL server a copy of the tables, to which they will read and write data.
Have replicated all tables from the other servers on each server; from which it can only read.

This way, each server can work independently, and have access to any changes made on the other servers.

I am not quite sure how to detect deletion of rows, other than to run through and see what's missing, unless TRIGGERS work post-synchroniously in replicated tables?

EDIT: Another concern of mine is what would happen something is inserted in the same table, on different servers, while the replication is down due to network problems or whatever. Then we'd have 2 rows with the same ID, and we'd have no reason to assume the latter is not an edit of the former (other than the fact that the ID is in only 2 of 4 severs, but it could well be in all 4 if the network is down for a long time)

These problems could all be solved ny adding more colums for creation date, last edit date, insert-id, and global-id.
But then we'd have to change so much on our website and in other systems as well, that we'd prefer to avoid it.

Any other obstacles we might face with this solution?
Any better ideas?


texts: SELECT FOR UPDATE locking other tables in PostgreSQL

texts:
The situation, with PostgreSQL 9.6:

table A with integer primary key
table B with foreign key constraint
on its primary key referencing table A's primary key

SELECT id FROM A FOR UPDATE; blocks UPDATE B SET x=y; until the lock on A is released.

I assume this is because the referenced value might change. What can I to avoid the first statement blocking execution of the second one without dropping the foreign key constraint?

If I drop the foreign key constraint, what bad things should I expect? In the actual database where this problem occurs, it wouldn't be a significant issue if table B had rows left over after deleting from A. We also don't ever update primary keys.

Edit with code:

-- Setup
create table a (id int primary key);
create table b (id int primary key references a (id) on update cascade on delete cascade, x varchar);
insert into a values (1), (2);
insert into b values (1, null), (2, null);

-- Left
begin;
select 1 from a for update;
/* Keep the transaction open */

-- Right
begin;
update b set x = 'abc' where id = 1;
update b set x = 'xyz'; /* It blocks here /*


texts:
I am running a query with an UNLOAD statement to save the results into a CSV file,

UNLOAD
SELECT
...
FROM
...
WHERE
...
TO 'C:\\TEMP\\MyFile.csv'
    QUOTE '"';

I am getting status updates like

55% (1182880573 of estimated 2147483647 rows) complete after 01:10:00; estimated 00:57:04 remaining

and so on for 58%, 62%, etc. Does the update refer to the amount of time to write MyFile.csv, the amount of time to run the query, or both? That will help me figure out if the bottleneck is creating the file vs. running the query. The file size grew on disk over time to 539 GB, and the estimate undershot since it started giving values like,

769% (3635495259 of estimated 2147483647 rows) complete after 16:30:00

When I killed the query, it reported an execution time of 59706 seconds, so I think this retrieved a row, saved it to the file, retrieved the next row, and so on, but it would be nice to confirm.

I am using Sybase SQL Anywhere 12.


texts: Trigger or rule to convert all strings to lower case

texts:
I have about 26 applications interacting with our database, some of these will store email addresses in mixed case. Since I made the mistake of using varchar as the column type and not a case insensitive one, I would like to make some kind of trigger or rule, which will convert all attempts to save email addresses as mixed case, into lower case. I could achieve the same using the following query:

ALTER TABLE your_table
ADD CONSTRAINT your_table_the_column_lowercase_ck
CHECK (the_column = lower(the_column));

But that would throw error messages in all the applications, and potentially stopping new customers from registering. Therefore, I am looking for this auto-converting rule/trigger which will handle any updates or inserts into this table. Is it possible to do this at all, so it won't come back and bite me later?


texts: Pausing AlwaysOn Availability Group replication for failover

texts:
We are currently implementing a new software package which has one part of its system using database replication (but doesn't support AlwaysOn Availability Groups) replicate from another database that does support AOAG's.

Currently, they only support replication setup via their installer, which creates the entire config on both the publisher and subscriber servers in one go.

It is using transactional replication, and there are both pull and push subscriptions.

The installer creates the distributor on the publisher, which I assume will break replication after a failover and require generation of new snapshots and reinitializion of subscriptions.

Assuming we are doing a planned failover (say for O/S patching), looking to pause replication until we can failover back to the node with the distributor.

Am I on the right track in saying the most reliable method of "pausing" replication would be to use sp_MSstopdistribution_agent on the publisher (for PUSH) and on subscriber (for PULL) before failover to the (non-distributor) node, then do a sp_MSstartdistribution_agent after we fail back again?

We are using SQL 2014 on both publisher and subscriber.