texts:
I have a table order which contains OrderId (WarehouseId and OrderId are composite primary key).

WarehouseId | OrderId | ItemId | OrderDate
-------------------------------------------
          1 |       1 |      1 | 2016-08-01
          1 |       2 |      2 | 2016-08-02
          1 |       3 |      5 | 2016-08-10
          2 |       1 |      1 | 2016-08-05
          3 |       1 |      6 | 2016-08-06

(table is simplified and only shown required fields)

How to efficiently select the last order for particular Warehouse? I currently do:

SELECT TOP 1 * FROM tblOrder WHERE WarehouseId = 1 ORDER BY OrderId DESC

My concern is, when I have a million (or more) orders for particular warehouse, by doing sorting and select the first record, it will be too slow (I think?). From advice I get so far, it is not an expensive operation, therefore it should be fine. Is that right?

Or, is there any more efficient way to select the last order record?


texts: Azure App Service connect to SQL Server using Active Directory principal (Windows / AD authentication)

texts:
Can Azure App Service connect to SQL Server (not Azure SQL DB, not Azure SQL MI) using Active Directory service principal (domain\user.name) or Azure AD Managed Identity ?
Tried creating login for Managed Identity that my App Service has
create login [my-azure-app-identity] from external provider

But this throws an error, syntax works for Azure SQL DB, not for SQL Server
Also can't find a setting which would allow me to assign domain account (domain\user.name) to Azure App Service so it could connect to SQL Server using Integrated Security=True or any other passwordless authentication method ?
The final goal is to make connection string without User and Password
(passwordless, if such word exists)


texts: Normalization using DENSE_RANK and LAST_INSERT_ID

texts:
db-fiddle
I have created a table called orders (orderId, customersId) and a table called orderDetails (orderId, productId). My goal is to denormalize a table called temp which is like
scanId, customersId, productId
1,1,1
1,1,2
1,2,3
2,3,4
2,1,5
2,1,6

I want to insert a new order for every distinct scanId,customersId, and then for every order insert a productId into orderDetails. So given temp you would see 4 orders created, with the 1st and 4th order containing 2 productIds each.
I am used to SQL Server where we can utilize the OUTPUT clause of an INSERT to tie identities back to an initial INSERT. However MySQL doesn't have anything like that. Following advice from https://stackoverflow.com/questions/7333524/how-can-i-insert-many-rows-into-a-mysql-table-and-return-the-new-ids
I came up with the following:
INSERT INTO orders(customersId)
SELECT customersId
FROM temp
GROUP BY scanId, customersId
ORDER BY scanId, customersId;

INSERT INTO orderDetails(orderId, productId)
SELECT
    DENSE_RANK() OVER(
        ORDER BY scanId, customersId
    ) + LAST_INSERT_ID() -1, productId
FROM temp;

The trick being DENSE_RANK() OVER( ORDER BY scanId, customersId ) + LAST_INSERT_ID() -1 constructs the orderId.
Will this always work?
Specifically, will MySQL always assign autoincrement ids to inserts based on the ORDER BY and then utilzing a windowed function on the partition and order always tie back to the inserted id?


texts:
I am trying to migrate from Oracle to MariaDB. At the moment, we are facing an issue with the following Oracle code.

 SELECT id,MK, pid,AFI
   FROM M A
  WHERE LEVEL <= 2 -- limiting iterations here
  START WITH pid = 1
CONNECT BY Pid = PRIOR id;

We have used recursive common expression table but we are unable to restrict the number of iterations to 2 (LEVEL <= 2). We don't want to set the system variables like cte_max_recursion_depth. How else can we limit recursion depth?


texts: SQL Server Memory allocation and max server memory setting

texts:
For one of our SQL Server, we recently add more memory. The initial value was 12GB. The server experienced a lack of physical memory so we added another 12GB, for a total of 24GB. Then I have set the followings values : min. server memory 1024MB - max server memory : 19456. Well everything is running fine, but I have few questions about SQL Server memory usage. Indeed, SQL server suddenly used all the assigned memory :

I have a good book which say :"Once memory usage has increased beyond the min server memory setting, SQL Server won't release any memory below that amount" - Is the same thing happening here with max server memory ?

I'm wondering (again) how to measure how much memory SQL is really using. I'm confused with the parameter "Total server memory (KB)" ("this counter measures SQL Server's total buffer pool usage"). According perfmon it is only 2MB :

Maybe we could spare the ESX some RAM and SQL Server memory (i.e PLE) will still show good values - How to be sure ? A good explanation on how SQL memory works in that case will be really appreciate!


texts: Taking a backup of a single table in the SQL Server

texts:
I know how to take a backup of a database in SQL Server and restore it. But how can I take a backup of a single table, and then restore it? Is it possible?

I have a database with two different names (some different data) and I have to restore one table by taking it from one database to the other. How can I take a single table backup from one database and then restore it to the other one?


texts: replace user defined functions (UDF) and tabled value functions(TVF)

texts:
Are there techniques to replace

UDF
tabled value functions
with something else?

or techniques to make them acceptable? (fast, multithreading and with good estimations)


texts: Postgresql - UPDATE with different WHERE clasules

texts:
I've written a long Query for updating a table, with some large WITH selects. I want to update some columns during some condition and other if other conditions. I tried to write an easy example

WITH sub_select(SELECT column1, column2, column3 FROM my_large_table)
UPDATE my_second_table
SET first_col = column1
WHERE column3 > 1
SET second_col = column2
WHERE column3 = 1
FROM sub_selection;

What I can do is write it as two separate queries, but I prefer to solve it with one query, any suggestions?

with sub_select(SELECT column1, column2, column3 FROM my large_table)
UPDATE my_second_table
SET first_col = column1
WHERE column3 > 1
FROM sub_selection;
with sub_select(SELECT column1, column2, column3 FROM my large_table)
UPDATE my_second_table
SET second_col = column2
WHERE column3 = 1
FROM sub_selection;


texts: SQL Server Real Time Data integration with different Instance

texts:
I have got a SQL application which needs to be merged with a new CRM applicaiton.
so data coming into the source tables, then triggers push certain fields into the Staging table with a flag '0' and then CRM application job picks up from staging table and sets the flag to '1'.

same is true if the data for the same field is initiated from the CRM and needs to go into my application.

All well and good.

Now considering 100s of transactions per minute is the above solution workable.
i can think of other alternatives like

SIS
data replication
service broker (limited knowledge) etc


texts: Lock table size issue when deleting on MySQL

texts:
I'm attempting to delete errant records from a table based on certain dates and running into the following error:

mysql> delete from myTable where dataDate in ('2011-07-12');
ERROR 1206 (HY000): The total number of locks exceeds the lock table size

The table doesn't yet have indexes (this is post-load clean-up), and consists of ~2B records:

mysql> select table_rows from information_schema.tables where table_name = 'myTable';
+------------+
| table_rows |
+------------+
| 2073294315 |
+------------+
1 row in set (0.51 sec)

Suggestions I found via search suggested upping innodb_buffer_pool_size but I've already upped default pool size by 8x in order to get the data to load in reasonable time and current sits at 512MB:

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

I'm planning to reload data for 3 dates (~3M records) after deleting the errant records.  Wondering if I should index the table first or if there are other adjustments that will allow this.


texts: How to reduce "dirtied buffers" occurence

texts:
We have a table:

\d+ alarm_journals
      Table "public.alarm_journals"
     Column      |            Type
-----------------+-----------------------------
 id              | integer
 project_id      | integer
 updated_at      | timestamp without time zone
 active          | boolean
Indexes:
    "alarm_journals_pkey" PRIMARY KEY, btree (id)
    "index_alarm_journals_common_active" btree (project_id, updated_at) WHERE active

There are many inserts into this table (1 million per day) and many updates of the 'active'-field from TRUE to FALSE.

Sometimes we have the lag for the query with active = TRUE (first log):

EXPLAIN (ANALYZE,BUFFERS) SELECT "alarm_journals".* FROM "alarm_journals" WHERE (project_id = 280) AND ((active = TRUE  AND (updated_at <= '2017-07-27 03:47:06 UTC')))  ORDER BY "alarm_journals"."updated_at" DESC LIMIT 25 OFFSET 0;
                                                                               QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.41..7.59 rows=1 width=285) (actual time=0.044..32337.127 rows=20 loops=1)
   Buffers: shared hit=18 read=1659 dirtied=1568 written=31
   ->  Index Scan Backward using index_alarm_journals_common_active on alarm_journals  (cost=0.41..7.59 rows=1 width=285) (actual time=0.044..32337.114 rows=20 loops=1)
         Index Cond: ((project_id = 280) AND (updated_at <= '2017-07-27 03:47:06'::timestamp without time zone))
         Buffers: shared hit=18 read=1659 dirtied=1568 written=31
 Planning time: 506.406 ms
 Execution time: 32337.355 ms
(7 rows)

However it dissappears as soon as the similar query is executed (second log):

EXPLAIN (ANALYZE,BUFFERS) SELECT "alarm_journals".* FROM "alarm_journals" WHERE (project_id = 280) AND ((active = TRUE  AND (updated_at <= '2017-07-27 03:47:06 UTC')))  ORDER BY "alarm_journals"."updated_at" DESC LIMIT 25 OFFSET 0;
                                                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit  (cost=0.41..7.59 rows=1 width=285) (actual time=166.317..401.424 rows=12 loops=1)
  Buffers: shared hit=1 read=16
  ->  Index Scan Backward using index_alarm_journals_common_active on alarm_journals  (cost=0.41..7.59 rows=1 width=285) (actual time=166.315..401.413 rows=12 loops=1)
        Index Cond: ((project_id = 280) AND (updated_at <= '2017-07-27 03:47:06'::timestamp without time zone))
        Buffers: shared hit=1 read=16
Planning time: 0.354 ms
Execution time: 402.204 ms
(7 rows)

We see many "dirtied buffers" in the first log (suppose that it causes the problem), but there is no any in the second log.
VACUUM-ing helps us to avoid this problem, but it takes 6 hours for execution.

How can we avoid queries which take a long time for execution (caused by "dirtied buffers")?

Update:

Vacuum command: VACUUM (VERBOSE, ANALYZE) alarm_journals;


texts: How to restore SQL Server database with mirroring

texts:
I have SQL Server with mirroring.

I am trying to restore the database using the command:

USE [master]
ALTER DATABASE [CentralStorage] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE [CentralStorage] FROM  DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\XXX.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5
ALTER DATABASE [XXX] SET MULTI_USER

I tried the followings:

I set the "remove tail log"
I set the "close connections"
override existing database.

I get the error:

  Msg 1468, Level 16, State 1, Line 2
  The operation cannot be performed on database "XXX" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.
  Msg 5069, Level 16, State 1, Line 2
  ALTER DATABASE statement failed.
  Msg 3104, Level 16, State 1, Line 3
  RESTORE cannot operate on database 'XXX' because it is configured for database mirroring or has joined an availability group. If you intend to restore the database, use ALTER DATABASE to remove mirroring or to remove the database from its availability group.
  Msg 3013, Level 16, State 1, Line 3
  RESTORE DATABASE is terminating abnormally.

Additional info:

Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)


texts:
I need to recover my SQL Server 2008 user/pwd.

The situation is: I don't know the SA password and Windows Authenticated user is no longer active user (disable user because the user has left job) so I can not log in using Windows authentication.

Is there anyway to reset the sa password, Windows authentication and to log in to the database?

Last option could be uninstall and install again. In this case data will be lost.

Thanks,


texts: Piecemeal restore, filegroup and file extension

texts:
As per my understanding (so far) any given user database contains three types of files - mdf, ndf and ldf.

MDF stands for meta data file and stores data definition as well as data (in case of small db and no other file exists for data).

NDF is basically for table data and stores data, we can dictate which table can be allocated here based on file groups.

LDF stands for log data file. This stores VLFs (Virtual Log Files)

I was under the impression that there can be only one .mdf file belonging to primary file group and n number of .ndf files belonging to primary or user defined file group.

I was creating a database and created multiple file group and extension left .mdf (by mistake) for all of them and to my surprise, now my database has three .mdf files belonging to different file group as shown below:

CREATE DATABASE [TEST_DBASE]
ON  PRIMARY
( NAME = N'TEST_DBASE', FILENAME = N'E:\DB\TEST_DBASE.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ),
 FILEGROUP [ACCNTS_FILEGROUP]
( NAME = N'ACCNTS_FILE', FILENAME = N'E:\DB\ACCNTS_FILE.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB ),
 FILEGROUP [LOAN_FILEGROUP]
( NAME = N'LOAN_FILE', FILENAME = N'E:\DB\LOAN_FILE.mdf' , SIZE = 8192KB , FILEGROWTH = 65536KB )

In this case, how shall we do piecemeal restore if we need to bring only Accounts table online?

Earlier we were following below steps:

RESTORE DATABASE [TEST_DBASE]
FILEGROUP = 'PRIMARY' FROM DISK = 'E:\BACKUPS\FullBackup.BAK' with NORECOVERY, PARTIAL

RESTORE DATABASE [TEST_DBASE]
FILEGROUP = 'ACCNTS_FILEGROUP' FROM DISK = 'E:\BACKUPS\FullBackup.BAK' with NORECOVERY

RESTORE LOG [TEST_DBASE] FROM DISK = 'E:\BACKUPS\LogBackup.trn' with RECOVERY

Above steps were making Accounts table online and accessible.

To bring Loans tables online at a later point of time (non-critical table), we used to perform below steps:

BACKUP LOG [BANK_DATABASE] TO DISK = 'E:\BACKUPS\Backup_tail.trn' with FORMAT, NORECOVERY

RESTORE DATABASE [TEST_DBASE]
FILEGROUP = 'LOAN_FILEGROUP' FROM DISK = 'E:\BACKUPS\FullBackup.BAK' with NORECOVERY

RESTORE LOG      [TEST_DBASE] FROM DISK = 'E:\BACKUPS\LogBackup.trn' with NORECOVERY
RESTORE LOG      [TEST_DBASE] FROM DISK = 'E:\BACKUPS\Backup_tail.trn' with RECOVERY

My concern in the above scenario is - will there be any change for piecemeal restore considering the fact that meta data file is now spread in all the file groups and does not belongs to primary file group only.

For any partial restoration, restoration of primary file group was mandatory since it contains all metadata however if all files are now .mdf, how will it work?


texts: Does having a .ldf for a index file cause issues?

texts:
Interesting find today, I have a file group that is used for indexing. It has one file that has a .ldf extension, which as you know for SQL Server, is the extension for the transaction log files.

My understanding is the extensions don't really matter. Whatever is first is first and anything else is secondary regardless of the extension. Does that apply to .ldf in this case when clearly it's being used for the clustered indexes?

I ask because I would assume SQL Server treats .ldf differently than say, mdf's.

(And before you ask, yes, there is already a .ldf assigned for the transaction log on another spindle)


texts: How do I return two aliases from the same table query?

texts:
How would I do something like this:

SELECT *
from notifications
WHERE read=false AS unread_notifications
  and WHERE read=true
  AND deleted=false AS read_notifications;

Result Example:

unread_notifications : [
  { id: ...,
    read: false,
    deleted: false,
    subscriber: ...,
    messageDate: ...,
    message: ...
  },
  ...etc
];

read_notifications : [
  { id: ...,
    read: true,
    deleted: false,
    subscriber: ...,
    messageDate: ...,
    message: ...
  },
  ...etc
];

In other words, I want to combine two queries into one:

SELECT * FROM notifications WHERE read=false LIMIT 99;
SELECT * FROM notifications WHERE read=true AND deleted=false LIMIT 99;

Example:

CREATE TABLE notifications (
  id VARCHAR(36) DEFAULT uuid_generate_v1mc(),
  read BOOLEAN DEFAULT false,
  deleted BOOLEAN DEFAULT false,
  subscriber VARCHAR NOT NULL,
  messageDate TEXT DEFAULT TO_CHAR(NOW(), 'Mon DD, YYYY HH24:MI:SS'),
  message TEXT
);

INSERT INTO notifications (subscriber, message, read)
  VALUES
  ('Sherry Waters', 'has been added to the Carlotta Corp gateway.', false),
  ('Carl Sagan', 'has cancelled his membership to the Carlotta Prime plan.', false),
  ('Parker Posey', 'is late to pay for the Carlotta Prime plan.', false),
  ('Bob Aronssen', 'has been succesfully charged for the Carlotta Prime membership!', false),
  ('Axle Root', 'has been suspended due to non-payment', true),
  ('Shaniqua Smith', 'has been succesfully charged for the Carlotta Primer membership!', true),
  ('Adam Vicks', 'has parked his membership and is now an inactive subscriber', true);


texts: Current Host Server for Availability Groups

texts:
We have a 4 Node multi Subnet Availability Group. When i go to the Cluster Manager I could see the Current host server is the Node 4  whereas the AG Primary node is Node 1. Will the AG go down if Node 4 goes down ?

Please advice whether is this a normal behavior and will there be in any problem if i move all the cluster resources to the primary node during production.


texts: MYSQL count the number of members in a group

texts:
Recently started working with MySQL and PHP so my setup is still a bit clunky most likely but here is how my 2 tables look right now:
Table 1 named clanlist

Table 2 named clanusers

What I need to do is check the number of users for each "clan" and show that number in the clanlist in the members column.
So what it should do is count the number of users who have "1" in their name and update the amount in table "clanlist". So if 3 users have "1" in the "clanname" column it should update the clanlist table and show 3 in the members column. I find it a bit hard to explain and I don't know if there are easier ways to do this so feel free to help me out with anything.
I would like to add that I already tried doing something similar in php code but it was unreliable and it required me to include the php code every time a user opened a webpage. Which obviously causes slower loadtimes for the page.
And one more thing, I saw something about the COUNT function which you can use in a query. But the code was really complex and I couldn't get it to work at all.
Created this in paint real quick:

The column members has to amount of rows in the clanusers table which have the clan_id from clanlist in it.


texts:
Can anyone please explain me below situation we have and please suggest me solution for how to deal with Memory issues in SQL Server 2008R2 Version.

Installed RAM 128 GB

FYI... I have assigned close to 122 GB for SQL Server.

Still please clarify what this means :

SQL Server:Buffer Manager Page reads-write/sec has exceeded the threshold

I see PLE more than 300 which is good ?
Please clarify other Performance counters if the value is good or needs to be addressed.


texts:
I am using Amazon RDS db.m1.medium instance. It has at least 40-50 database and atleast 10k table. For last one year it was working fine, but today I was altering one table It started giving error. I am using InnoDB format.

When I create a table or alter a table I get this error:

  #1041 - Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap

How do I resolve this?


texts: The sort column must be the last column used in the index - MongoDB Indexing Advice

texts:
MongoDB docs advices that:

  The sort column must be the last column used in the index

Here is the example:

db.foo.ensureIndex({a: 1, b: 1, c: 1})

Good:

find(a=1).sort(a)
find(a=1).sort(b)
find(a=1, b=2).sort(c)

Bad:

find(a=1).sort(c)
even though c is the last column used in the index, a is that last column used, so you can only sort on a or b.

can someone explain this. Why find(a=1).sort(c) considered as Bad?


texts: Ternary Relations three attributes and an association

texts:
I have 4 tables:

Workers
Work
Workers Relationship/Work
Registration Time

I need to have organized the Workers Relationship with Work so you can make a ListBox organized.

Where each worker may only see is Work.

I just want to create the tables, not do Select.


texts: Review Technical ETL process for knowledge management

texts:
I'm looking for source code and all related component/material to review technical ETL process between source database and Data Warehouse in order to increase my knowledge in technical Business Intelligence. Where can I find these types of source code and database?

I can find the database for adventurework OLTP and its DW but not the source code for the ETL process


texts: What is the recommended method to perform a minor version upgrade of postgres on linux (Centos)? I am looking at upgrading from 9.5.4 to 9.5.5.


texts: MySQL - What is the diffrence between ClearText authentication and PAM authentication plugins?

texts:
PAM Authentication:

PAM enables a system to use a standard interface to access various kinds of authentication methods, such as Unix passwords or an LDAP directory.

Cleartext Client-Side Authentication Plugin

client-side plugin can be used to send the password to the server in clear text. There is no corresponding server-side plugin.

I have read documentation but could not get enough help, PAM authentication is supported only in MySQL Enterprise edition but cleartext authentication is applicable to all MySQL servers as it's a client who invokes this kind of authentication mechanism.

Questions:

Has anybody practically tried using PAM authentication or Cleartext authentication plugin?

If so please explain how to configure and setup with an example?


texts: MySQL - The DBMS must maintain a separate execution domain for each executing process

texts:
I'm trying to get a government system hardened and during that process I have to answer certain criteria. One of them is the below:

  Rule Title: The DBMS must maintain a separate execution domain for
  each executing process.

  Discussion: Database management systems can maintain separate
  execution domains for each executing process by assigning each process
  a separate address space. Each process has a distinct address space so
  that communication between processes is controlled through the
  security functions, and one process cannot modify the executing code
  of another process. Maintaining separate execution domains for
  executing processes can be achieved, for example, by implementing
  separate address spaces.

  Check Text: Review the DBMS architecture to find out if and how it
  protects the private resources of one process (such as working memory,
  temporary tables, uncommitted data and, especially, executable code)
  from unauthorized access or modification by another user or process.

  If it is not capable of maintaining a separate execution domain for
  each executing process, this is a finding.

  If the DBMS is capable of maintaining a separate execution domain for
  each executing process, but is configured not to do so, this is a
  finding.

  Fix Text: Deploy a DBMS capable of maintaining a separate execution
  domain for each executing process.

  If this is a configurable feature, configure the DBMS to implement it.

Anyone have any information or documentation on this?


texts: Database with many publication, How to make snapshots for publications run faster

texts:
I have a database that has about 200 filtered publications. When we run snapshots it drags down the merge processes and it takes a long time to recover because of the locks.

Is there a way to create a snapshot on a busy database without slowing down the running subscriptions.
what would be the impact of snapshot isolation. ( I realize its not talking about the actual snapshot)


texts: How correctly implement and enforce a default option among a list of rows is always present in postgresql

texts:
I have a list of address, and need to set which one is the default.
This look easy:
CREATE TABLE address (
  address_code  TEXT PRIMARY KEY CHECK (not_empty(address_code)),
  customer_code TEXT  NOT NULL CHECK (not_empty(customer_code)),
  place_code    TEXT NOT NULL CHECK (not_empty(place_code)), //ie: House, Main branch...
  is_default    BOOLEAN NOT NULL DEFAULT FALSE,
);

CREATE UNIQUE INDEX idx_address_is_default ON address (customer_code, is_default) WHERE (is_default = true);

However, if the default address is deleted or other address is set to be default, it must select other row, ie: I need to always have a default address (as long exist rows. Is ok to have zero).
For now I have a trigger:
CREATE OR REPLACE FUNCTION address_change_delete()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $function$
BEGIN
    IF OLD.is_default THEN
        --RAISE NOTICE 'DEL:%:%', OLD.is_default, NEW.is_default;
        UPDATE address SET is_default = true WHERE customer_code = OLD.customer_code AND address_code IN
           (SELECT address_code FROM address WHERE customer_code = OLD.customer_code AND place_code <> OLD.place_code LIMIT 1);
    END IF;

    RETURN OLD;
END;
$function$;
CREATE OR REPLACE FUNCTION address_change_update()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $function$
BEGIN
    IF EXISTS (SELECT 1 FROM config
             WHERE name = CAST(pg_backend_pid() AS VARCHAR)) THEN
        --RAISE NOTICE 'REC:%', pg_backend_pid();
        -- it's recursing
        RETURN NEW;
    END IF;

    INSERT INTO config (name, value) VALUES (CAST(pg_backend_pid() AS VARCHAR), true);

    IF TG_OP = 'UPDATE' THEN
        IF OLD.is_default AND NEW.is_default = false THEN
          --RAISE NOTICE 'UP:%:%', OLD.is_default, NEW.is_default;
          UPDATE address SET is_default = true WHERE customer_code = NEW.customer_code AND address_code IN
              (SELECT address_code FROM address WHERE customer_code = NEW.customer_code AND place_code <> NEW.place_code LIMIT 1);
        END IF;
    END IF;

    IF NEW.is_default AND (TG_OP = 'INSERT' OR OLD.is_default = false) THEN
      --RAISE NOTICE 'IN:%:%', OLD.is_default, NEW.is_default;
      UPDATE address SET is_default = false WHERE customer_code = NEW.customer_code AND address_code IN
        (SELECT address_code FROM address WHERE customer_code = NEW.customer_code AND place_code <> NEW.place_code);

    END IF;
    DELETE FROM config WHERE name = CAST(pg_backend_pid() AS VARCHAR);
    RETURN NEW;
END;
$function$;

CREATE TRIGGER address_change_delete
    AFTER DELETE ON
      address FOR EACH ROW
      EXECUTE PROCEDURE address_change_delete();

CREATE TRIGGER address_change
    BEFORE INSERT OR UPDATE ON
      address FOR EACH ROW
      EXECUTE PROCEDURE address_change_update();

Now this is causing me a lot of troubles and wonder if I'm overlooking something.
P.D: This come from data from outside, so I can't rely in a user to pick a address for me, that is why must be automated...


texts:
I'm performing an audit on Oracle database changes and the database is on a Linux OS.

After using the command opatch lsinventory, we learned that this command has not been pulling the complete population of patches on the database. It is only showing the last patch applied. Contrary to many online definition; which is ALL patches.

What could be causing this? What's a foolproof way to pull all changes (patch, scheme, table etc) applied?

Thank you all!


texts: Update multiple rows with different date for each row

texts:
I want to update all records in my database table with a fixed value for pwDays and an increasing value in pWDate.

My current statement:

update [POSUSER] set passDays = '60', passChangeDate = '2017-01-01 00:00:00'

But I do not want all users to have to reset their passwords on the same day. So I would like to increment the passChangeDate by 1 day for every 10 to 20 users, is there a "simplistic" way to do this in a single SQL statement?
(All users must have a new passChangedDate, but no more than 20 users must have the same date.)

The table has a primary key field that is a integer value. (USERID)

Note I want to only increase the date after every +/-10% users.


texts: How to add newly discovered cell node as member of exadata storage server grid in oem12c?

texts:
I have successfully discovered a new storage node in oem using emcli

./emcli add_target -type=oracle_exadata -name="da13cel04.myhost.com" -host="da13db01.myhost.com" -properties="CellName:da13cel04.myhost.com;MgmtIPAddr:10.92.43.23"
Target "da13cel04.myhost.com:oracle_exadata" added successfully

But now i want to add this cell node under existing exadata storage grid target in em and i tried below,but it didn't helped

./emcli modify_target -type=’oracle_exadata_grid’ -name=’Exadata Grid da13.myhost.com’ -properties=’Members:da13cel01.nyhost.com,da13cel02.myhost.com,da13cel03.myhost.com,da13cel04.myhost.com’

Need help

Thanks in advance


texts: Oracle Equivalent of SQL Server's Bitwise Operators

texts:
I am trying to figure out all of the common bit-wise operations in Oracle.

In SQL Server we have some very simple bit-wise operators to use against a bit-wise value:

& - Evaluates if bit exists select 10 & 2   /* result=2 */
| - Add Bit (if doesn't exist) select 10 | 2   /* result=10 */
&~ - Remove Bit (if exists) select 10 &~ 2  /* result=8 */
^ - Toggle Bit (remove if exists, adds if doesn't) select 10 ^ 2  /* result = 8 */

In Oracle, I know we have a built-in bitand() function which is more or less equivalent to SQL Server &, and I have built an Oracle bit-wise OR function to mimic SQL server's bit-wise OR operation (|) as follows:

CREATE OR REPLACE FUNCTION BITOR (x IN NUMBER, y IN NUMBER) RETURN NUMBER AS
    BEGIN
       RETURN x + y - BITAND(x,y);
    END;

My question is: how can I achieve the SQL Server &~ and the ^ operations in Oracle?


texts: Oracle PL/SQL Trigger to update a outstanding field on one table when another table is updated with date

texts:
So I have example 2 tables:

movies:

mid - pk varchar2
MovieName - varchar2
outstanding - num

rental:

sid - num pk
mid - fk varchar2
rent_out - date
rent_in - date

Need to have trigger that would decrement value in movies.outstanding when rental.rent_in is populated.

I tried to write something like this in trigger

outstanding = outstanding - 1
when mid = old.mid

but when trigger is fired it increase number instead to decrease. I used for each row and after update. Need help with this please.

So my first trigger to update rental table is:

create or replace
  trigger rentals_rent_trg
  after insert on rentals
  for each row
begin
  update Movies
  set outstanding = outstanding + 1
  where mid = :new.mid;
end;

Please I need help with 2nd trigger.
This is my 2nd trigger, it is not correct, I have tried few things but is not working:

create or replace
  trigger rentals_return_trg
  after update of return_date on rentals
  for each row
begin
update Movies
  set outstanding = outstanding - 1
  where :old.mid = :old.mid;
end;

I have tried to write 2nd trigger like this and then I am receiving ORA-04091....RENTALS is mutating, trigger/function may not see it

create or replace
trigger rentals_return_trg
after update of return_date on rentals
for each row
declare
    return_date_temp date;
begin
    select return_date
    into return_date_temp
    from rentals
    where return_date = :new.return_date
    and return_date is not null;

if return_date_temp is not null
then
      update Movies
     set outstanding = outstanding - 1
     where mid = :new.mid ;
/*mid = :old.mid;*/
end if;
end;

Since I was getting that error I have created compund trigger, however now I managed to start decreasing number however instead to be based on rows it is based on run I belive since it lowers only for one digit down if :old.outstanding = 5 then :new.outstanding = 4. I really need HELP please hahaha.

create or replace trigger rentals_return_trg
FOR update of return_date on rentals
COMPOUND TRIGGER
      cursor return_date_cur is
      select R.rid, R.mid, R.return_date, M.outstanding
       from rentals R JOIN MOVIES M
       ON R.mid = M.mid;
       type return_typ is table of return_date_cur%ROWTYPE
       index by binary_integer;
      return_tbl return_typ;
      INT number(4) := 0;
BEFORE STATEMENT IS
begin
for rec in return_date_cur loop
      int := int +1;
      return_tbl(int).rid := rec.rid;
      return_tbl(int).mid := rec.mid;
      return_tbl(int).return_date := rec.return_date;
       return_tbl(int).outstanding := rec.outstanding;
     end loop;
END BEFORE STATEMENT;
AFTER EACH ROW IS
    LV_RETURN_DT DATE;
    LV_OUTSTANDING_NUM NUMBER;
BEGIN
   for i IN 1..return_tbl.count loop
        if return_tbl(i).return_date = :NEW.return_date THEN
        LV_OUTSTANDING_NUM :=  return_tbl(i).outstanding - i;
       exit;
       end if;
  end loop;
  if :new.return_date is not null then
  update movies
  set outstanding = LV_OUTSTANDING_NUM;
  end if;
end after each row;
end;


texts: Finding and listing all columns in a SQL Server database with identity

texts: I wanted to RESEED all the identity columns in that database, so I need to create a script so I can find out which column in which table has the identity property.


texts: Is it worth time converting Access query to T-SQL query

texts:
Let me just say, before anything else, that I am a enormous noob when it comes to databases in general, so please forgive me for my rather stupid thoughts and actions.

I had a database built in Access entirely, and had to make it so that multiple users could use it at the same time from different locations. Below are the things I have tried:

I split it and used linked tables to multiple front-ends. This would have worked fine on a local share, however, this had to work for users outside of the network. I tried a VPN which ended up being way too slow using it with a regular Windows share.
After some research I found out that you could link SQL Server tables to Access, and that would have better performance than using a Windows share. Which it was much faster, especially when you access the tables from outside the network via VPN. There were some things that still did not meet performance needs though.
I then found pass-through queries, that are run on the server instead of the client, and don't require the whole table to be requested. So then I thought "Perfect! I'll convert the queries to pass-through queries and unicorns will run free again on the Earth" (not really, but I thought simply changing the query to a pass-through one would work), nope. Turns out you also need to convert the query from the Access SQL language to T-SQL. I have no previous experience with either language.

Is it worth the effort of converting 30 queries from Access to T-SQL? I have looked around online and most things I find I don't quite understand to the point where I know what to change. Any help at all would be extremely appreciated.


texts:
I have got this error message in Vesta Control Panel, in phpMyAdmin:

  SELECT command denied to user ''@'localhost' for table 'pma_table_uiprefs'

SELECT `prefs`
FROM `phpmyadmin`.`pma_table_uiprefs`
WHERE `username` = 'admin_db1'
AND `db_name` = 'admin_db1'
AND `table_name` = 'testtable'

I have already tried to solve with this command:

GRANT SELECT,INSERT,UPDATE,DELETE ON phpmyadmin.*
TO 'admin_db1'@'localhost' IDENTIFIED BY 'mypassword';


texts: How do I list all databases and tables using psql?

texts:
I am trying to learn PostgreSQL administration and have started learning how to use the psql command line tool.

When I log in with psql --username=postgres, how do I list all databases and tables?

I have tried \d, d and dS+ but nothing is listed. I have created two databases and a few tables with pgAdmin III, so I know they should be listed.


texts: MariaDB ability to work in parallel across multiple db users

texts:
How exactly does MariaDB handle the scenario with multiple users and one of them hits some sort of a limit. How would this affect other users?

I would assume one user's 120 second query would not shut down the db for others?

Any links to official documentations much appreciated.


texts: MySql 5.6: Ignores innodb_page_size setting in my.ini

texts:
So I tried to increase the innodb_page_size to 32k, by setting this value in my.ini:

innodb_page_size=32k

I then stopped the MySql service, and restarted it. The service in Win looks like this:

"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld" --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.6\my.ini" MySQL56

And I edited the correct file, and its saved:

but when I look at the variable in HeidiSql, I see the standard value of 16k:

Im not sure what I am doing wrong... Any help is appreciated!


texts: How to convert _id field of all existing documents from type String to Number

texts: At one of my collections I have an _id field that stores numbers. The field was defined originally in the Mongoose model as String. However, all the data concerning this field in the collection contains pure integers stored as strings. I want to preserve all the data in my collection while converting the type of this field (and all associated _id's) from type String to type Number. Any suggestions as how to do that? Thanks.


texts: Why does this partitioning helper view give an 'Input string was not in a correct format' error?

texts:
A database I'm investigating has defined in it a view admin.View_PartitionRanges, which is heavily based on the view PartitionRanges to be found in this MSDN blogpost from 2009. The view definition there is follows:

CREATE VIEW [dbo].[PartitionRanges] AS
select pf.name as [partition_function],
       ps.name as [partition_scheme],
       1 as [partition_number],
       case when prv.value is null then NULL else '<' end as [relation],
       prv.value as [boundary_value],
       type_name(pp.system_type_id) as [type],
       fg.name as [filegroup],
       case when ps.name is null then NULL else N'IN USE' end as [status]
  from sys.partition_functions pf
  join sys.partition_parameters pp on pp.function_id = pf.function_id
  left join sys.partition_schemes ps on ps.function_id = pf.function_id
  left join sys.destination_data_spaces dds
    on dds.partition_scheme_id = ps.data_space_id and dds.destination_id = 1
  left join sys.filegroups fg on fg.data_space_id = dds.data_space_id
  left join sys.partition_range_values prv
    on prv.function_id = pf.function_id and prv.parameter_id = 1 and
       prv.boundary_id = 1
 where pf.boundary_value_on_right = 1
 union all
select pf.name as [partition_function],
       ps.name as [partition_scheme],
       prv.boundary_id + cast(pf.boundary_value_on_right as int) as [partition_number],
       case when pf.boundary_value_on_right = 0 then '<=' else '>=' end as [relation],
       prv.value as [boundary_value],
       type_name(pp.system_type_id) as [type],
       fg.name as [filegroup],
       case when ps.name is null then NULL else N'IN USE' end as [status]
  from sys.partition_functions pf
  join sys.partition_range_values prv on
       prv.function_id = pf.function_id and prv.parameter_id = 1
  join sys.partition_parameters pp on pp.function_id = pf.function_id
  left join sys.partition_schemes ps on ps.function_id = pf.function_id
  left join sys.destination_data_spaces dds
    on dds.partition_scheme_id = ps.data_space_id and
       dds.destination_id = prv.boundary_id + cast(pf.boundary_value_on_right as int)
  left join sys.filegroups fg on fg.data_space_id = dds.data_space_id
 union all
select pf.name as [partition_function],
       ps.name as [partition_scheme],
       pf.fanout as [partition_number],
       case when prv.value is null then NULL else '>' end as [relation],
       prv.value as [boundary_value],
       type_name(pp.system_type_id) as [type],
       fg.name as [filegroup],
       case when ps.name is null then NULL else N'IN USE' end as [status]
  from sys.partition_functions pf
  join sys.partition_parameters pp on pp.function_id = pf.function_id
  left join sys.partition_schemes ps on ps.function_id = pf.function_id
  left join sys.destination_data_spaces dds
    on dds.partition_scheme_id = ps.data_space_id and dds.destination_id = pf.fanout
  left join sys.filegroups fg on fg.data_space_id = dds.data_space_id
  left join sys.partition_range_values prv
    on prv.function_id = pf.function_id and prv.parameter_id = 1 and
       prv.boundary_id = pf.fanout - 1
 where pf.boundary_value_on_right = 0
 union all
select pf.name as [partition_function],
       ps.name as [partition_scheme],
       NULL, NULL, NULL, NULL,
       fg.name as [filegroup],
       case when dds.destination_id = pf.fanout + 1
            then N'NEXT USED' else N'NOT USED'
       end as [status]
  from sys.partition_functions pf
  join sys.partition_schemes ps on ps.function_id = pf.function_id
  join sys.destination_data_spaces dds
    on dds.partition_scheme_id = ps.data_space_id and
       dds.destination_id > pf.fanout
  join sys.filegroups fg on fg.data_space_id = dds.data_space_id

However, when I perform a SELECT * on this view in SSMS, I get the error

An error occurred while executing batch. Error message is:
Input string was not in a correct format.

What's wrong?


texts: Pages to MB, multiple and divide or just divide?

texts:
When you are using things like sys.database_files it gives size of the file, in 8-KB pages, while many other things you are comparing to it to are in MB.

There are a number of ways to convert the 8-KB pages to MB, several ways are offered in the answers to Query to report disk space allocation and used space

The two most common are

Multiple by 8 and divide by 1024 (128000 * 8 / 1024 = 100)
Divide by 128 (128000 / 128 = 100)

The second is simpler, requiring only a single operation. But both seem to give the same answer.

Is there a good reason to choose one method over the other?


texts:
I have used the following procedure to view the last execution plan

create or replace procedure display_plan
as
begin
  for c in (select plan_table_output t from table(dbms_xplan.display_cursor('', '', 'allstats advanced last'))) loop
    dbms_output.put_line(c.t);
  end loop;
end;

With dbms_xplan.display_cursor it is possible to specify the SQL_ID. I understand that you can get a list of SQL_IDs from the table V$SQL but how does one decide what ones belong to the current session?


texts: Backup a database with a huge number of tables

texts:
Is there a way to backup a PostgreSQL database with a huge number of tables?

An attempt with pg_dump for a database of about 28000 tables resulted in the following error message:

pg_dump: WARNING:  out of shared memory
pg_dump: SQL command failed
pg_dump: Error message from server: ERROR:  out of shared memory
HINT:  You might need to increase max_locks_per_transaction.
pg_dump: The command was: LOCK TABLE public.link10292 IN ACCESS SHARE MODE
pg_dump: *** aborted because of error

An increase of max_locks_per_transaction to 256 instead of 64 resulted in a failure to start the server.

Anything else I can try? (PostgreSQL 9.0, Mac OS X.)


texts: Replicating data from SQL Server 2014 to SQL Server 2005 - temporary solution

texts:
I will be installing a new SQL Server 2014 database system, which is based on our current SQL Server 2005 database system.

The new system will be on a completely new infrastructure when we go live, leaving the current infrastructure available for rollback should we encounter any issues.

The business requires that a rollback option be available for 2 weeks minimum. Therefore in the event of a disaster scenario we would be required to switch back to the SQL Server 2005 database system. So the challenge I have is replicating the data upon going live with the SQL Server 2014 database system.

I am looking for a way to replicate the data (not necessarily the schema) from the SQL Server 2014 system to the SQL Server 2005 system.

I am not really a fan of SQL Server Replication and my preference is not to use this. My understanding is that this is not an option for us anyway due to the versions being too far apart, and therefore not supported.

I want to reach out to the community to find out if any tools are available to achieve this type of replication.

It may be possible for us to implement a schema change freeze if this helps in finding the right solution.

Please can you advise what options are available to us, if any? Or should we look to develop a bespoke system internally?

Are you aware of any Microsoft partners that might achieve this for us through a solution already developed?


texts:
I'm running the following simple Powershell command.

$Server = New-Object ('Microsoft.SqlServer.Management.Smo.Server') 'SerName'
$Server | SELECT *

For FCI clusters the ClusterName value is always blank and the ClusterQuorumState and ClusterQuorumType are always NotApplicable.  IsClustered is set to True though.

If I run it against an Availability Group I get back the cluster name and the correct values for the other two properties.

Is there a way in Powershell to get the cluster name for the FCI instances?  Another cmdlet I can use?


texts: How to know which security updates to install?

texts:
I am using KB2977316 and KB3045324 for this example.  These are security updates for SQL Server 2014 RTM.

KB2977316 is version 12.0.2381.0 published 6/2/2015
KB3045324 is version 12.0.2269.0 published 7/13/2015

If I have a SQL Server 2014 RTM system, do I need to install both of these, just the last one released, or the one with the highest version number?  The 2381 update is a 600 MB download, while the 2269 version is 183 MB, so logic would dictate that 2269 does not contain all of the fixes included in 2381 and I need to install both.  I guess what's throwing me off is that it just seems odd to have the version number decrease as newer updates are installed.

Please limit scope of answers and comments to security updates only.  This question has nothing to do with CUs or SPs (other than that different security updates will apply based on the specific CU or SP level).  Thanks!


texts: Does SQL Server use pointers instead of storing duplicate rows?

texts:
I'm using the built in sp_spaceused stored procedure before and after performing a operation in our software to see which tables have row inserts and how the size of each table changes.

What I'm seeing is that out of all the tables that get rows written to them, only a handful show that the table has increased in side. The others that show rows were added show no change in size from this stored procedure.

The only case this is not true is on the first transaction after performing a truncate on all the tables. So to me it appears that instead on storing duplicate data SQL Server is showing rows are inserted but must be just storing pointers to previous identical rows.

Can anyone confirm this please?


texts: SQL Server Import Wizard Excel into SQL Server 2016 Error 0xc020901c

texts:
I am trying to import an Excel file with 3914 rows of data into an existing table on SQL Server 2016 Express through the SQL Server Management Studio. I imported a similar file last month, but this is a monthly update file, so I deleted everything from the table before I imported the new file. However, now, when I go to finish the wizard and execute everything, I get the following error messages:

  Messages

  Error 0xc0202009: Data Flow Task 1: SSIS Error Code DTS_E_OLEDBERROR.
   An OLE DB error has occurred. Error code: 0x80004005.
  An OLE DB record is available.  Source: "Microsoft OLE DB Provider for SQL >Server"
  Hresult: 0x80004005  Description: "Unspecified error".
  (SQL Server Import and Export Wizard)

  Error 0xc020901c: Data Flow Task 1:
  There was an error with Destination - IACList.Inputs[Destination >Input].Columns[Approval Number] on Destination - IACList.Inputs[Destination >Input].
  The column status returned was: "The value violated the integrity constraints >for the column.".
  (SQL Server Import and Export Wizard)

  Error 0xc0209029: Data Flow Task 1: SSIS Error Code >DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "Destination - >IACList.Inputs[Destination Input]"
  failed because error code 0xC020907D occurred, and the error row disposition on >"Destination - IACList.Inputs[Destination Input]" specifies failure on error.
  An error occurred on the specified object of the specified component.  There >may be error messages posted before this with more information about the >failure. (SQL Server Import and Export Wizard)

  Error 0xc0047022: Data Flow Task 1: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  >The ProcessInput method on component "Destination - IACList" (35) failed with
  error code 0xC0209029 while processing input "Destination Input" (48). The >identified component returned an error from the ProcessInput method. The error >is
  specific to the component, but the error is fatal and will cause the Data Flow >task to stop running.  There may be error messages posted before this with more
  information about the failure. (SQL Server Import and Export Wizard)

It looks to me as though the error has something to do with the Approval Number column. When I query the table, it shows 3,824 records on the server after importing, but there should be 3,915 records. I checked the record that I believe would be number 3,824 in the Excel file, and there doesn't seem to be any issue with it. Perhaps I'm misunderstanding the error message, but I don't see a reason the integrity constraint would be violated. It is a primary key, but there is no other field in the column that matches it, so it is unique.


texts:
I'm new to Oracle--can somebody help me with this procedure?

CREATE OR REPLACE PROCEDURE JAKARTA
as
BEGIN
SELECT *
FROM
karyawan
WHERE
alamat = 'Jakarta';
COMMIT;
END jakarta;
/

When I run this I get the following

  Warning : Procedure created with complication errors.

Please help...


texts: PostgreSQL importing thousands of columns as an array


texts: How to return a single composite type from a plpgsql function without repeating the composite type's parts explicitly as OUT parameters?

texts:
I've hunted online and through documentation, but the much more common use case appears to be returning a SETOF some particular row type, not just a single row.

The use case below is trivial for illustration, as I am just trying to determine if this is even possible in plpgsql, and understand the syntax to use for it if it is.  (Just like the Postgresql documentation contains examples of functions to return a static set of fixed values, the point here is not the use case but the syntax.)

With this table:

create table my_table (f1 int primary key, f2 text, f3 real, f4 text, f5 bool);

The following function can be created:

create function my_func1(f1_to_look_up int) returns my_table
language sql
as $$ select * from my_table where f1 = f1_to_look_up $$;

Also, this can be done in plpgsql like so, with explicit out parameters (and with f1 specified explicitly in the where clause to avoid ambiguity):

create function my_func2(f1_to_look_up int, out f1 int, out f2 text, out f3 real, out f4 text, out f5 bool)
language plpgsql
as $$
begin
  select * into strict f1, f2, f3, f4, f5
    from my_table where my_table.f1 = f1_to_look_up;
  return;
end
$$;

Is there a way to accomplish the same thing in plpgsql, without needing to repeat the definition of my_table in order to list all the OUT parameters?


texts: Retrieving page contents from SQL, Postgresql?

texts: Data is stored over pages and not necessarily one row or one column or one attribute maps to one page. Is it possible to use some query to extract data from a specific page in a database environment?


texts: Configure second SQL Server Alias for named instance

texts:
In my devopment environment (Win 2008 server) are running two sql server 2008 instances (default and named instance).

Already one Sql alias is configured and pointing to the default instance. I want a second alias pointing to the named instance but have no idea how to achieve this.


texts: Drop tables but space not claimed in postgres 12

texts:
I have upgraded Postgresql 9.5 to Postgresql 12.4 a few days back using pg_upgrade utility with link  (-k) option.
So basically I am having two data directories i.e. One is old data directory (v9.5) and the current one in running state (v12.4).
Yesterday I have dropped two tables of size 700GB and 300GB.
After connecting to postgres using psql utility I can see database size whose tables was dropped got decreased (with \l+ ) but what is making me worry is that only a few ~50GBs have been freed from storage partition.
I have run vacuumdb only on that database but no luck. I have checked if any deleted open file is there on OS level using lsof but there is none.
Note : Still I have not deleted old data directory (v9.5). I am not sure if deleting will impact my running postgres 12 as I have used 'link' option in pg_upgrade.
Looking for the solution.


texts: What's the point of prefixing each element in the column by its column number in multicolumn GiN indexes?

texts:
According to these slides which, I believe, are incorporated into the current implementation, each column element in multicolumn GiN indexes is prefixed by its column number and is stored separately from one another, so they use storage of Na+Nb instead of Na*Nb for (a int[], b int[]). And the following slides show 2.5X speed improvement for a select query over 2 single column indexes.

But where does this huge performance improvement come from? I don't see why they are so much more efficient than 2 single column indexes. If you prefix each element with its column number, at every level in the tree, they'll be completely split into left and right, and left subset have no relation to the right subset just like 2 single column indexes, so I think they should be very similar in performance.


texts: Can I store half of a record on one page and half of the record on another page?

texts:
I'm watching Paul Randal's MCM video from http://technet.microsoft.com/en-us/sqlserver/gg313756.aspx

From 27:11 of the video, the author said a record must be saved in one page, you can't have half of a record on one page and half of it on another page. This opinion is not what I learned. However I'm not confident about it, since I'm not an English native speaker and may misunderstood Paul's opinion. Anyone can help to explain a little?

Thanks in advance.


texts: Calculate size of the filtered rows of a table in sql server

texts:
My requirement is to know the size occupied(count of records and size in mb) by a table during every 24 hours, I found query to get the whole size of a table but not sure on where to start with or how to achieve this.

I have an audit column(record_inserted_time) by which I can filter the records and get the count but how to know the size occupied during this period by the table(size of the filtered records).

I have to get this stats for around 100 tables all of which has the same audit column


texts: Performance Implications of encrypted data in a Clustered Columnstore Index

texts:
I am using SQL Server 2016 and expecting some details on below points regarding performance point of view:

Is there any performance gain or additional benefit on creating a primary key - unique constraint on same table on which a clustered columnstore index is already created?
If my table contains encrypted columns (out of 10 columns, 9 columns are encrypted), is there any benefit in case of creating clustered columnstore index? I am using SYMMETRIC KEY for encryption.


texts: AWS Oracle Enterprise Edition RDS to Standard Edition RDS Migration

texts:
I have RDS snapshot of Oracle EE edition and want to move it to be converted into Oracle SE edition.

Please help with the best strategy possible.


texts: Error when syncing MySQL database (XAMPP) on Dropbox

texts:
I'm working on a MySQL database for local development using XAMPP, and I would like to work on two different computers. Looking online, I saw this link that gives a tutorial on syncing your database using Dropbox. I've successfully made it work on one computer, but not on the second computer and can't figure out what's wrong. Here are my steps:

Computer 1

Shut down Apache and MySQL on XAMPP control panel
Copy the entire contents of C:/xampp/mysql/data into C:/Users/Steven/Dropbox/.../data
Change the following lines in the C:/xampp/mysql/bin/my.ini file:

datadir="C:/Users/Steven/Dropbox/.../data"

innodb_data_home_dir = "C:/Users/Steven/Dropbox/.../data"

innodb_log_group_home_dir = "C:/Users/Steven/Dropbox/.../data"

Restart Apache and MySQL, and it works successfully

Computer 2

First, made sure the XAMPP server was shut down on the first computer
Made sure XAMPP server was shut down on second computer
Change the local C:/xampp/mysql/bin/my.ini file as in step 3 for the first computer
When I start up the server and MySQL, I get the following error in the XAMPP control panel:

 Error: MySQL shutdown unexpectedly.
 5:05:33 PM  [mysql]    This may be due to a blocked port, missing dependencies,
 5:05:33 PM  [mysql]    improper privileges, a crash, or a shutdown by another method.
 5:05:33 PM  [mysql]    Press the Logs button to view error logs and check
 5:05:33 PM  [mysql]    the Windows Event Viewer for more clues
 5:05:33 PM  [mysql]    If you need more help, copy and post this
 5:05:33 PM  [mysql]    entire log window on the forums

And here is the output from the log file:

     2014-06-06 13:31:19 7276 [Note] Plugin 'FEDERATED' is disabled.
     2014-06-06 13:31:19 17e0 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
     2014-06-06 13:31:19 7276 [Note] InnoDB: Using atomics to ref count buffer pool pages
     2014-06-06 13:31:19 7276 [Note] InnoDB: The InnoDB memory heap is disabled
     2014-06-06 13:31:19 7276 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
     2014-06-06 13:31:19 7276 [Note] InnoDB: Compressed tables use zlib 1.2.3
     2014-06-06 13:31:19 7276 [Note] InnoDB: Not using CPU crc32 instructions
     2014-06-06 13:31:19 7276 [Note] InnoDB: Initializing buffer pool, size = 16.0M
     2014-06-06 13:31:19 7276 [Note] InnoDB: Completed initialization of buffer pool
     2014-06-06 13:31:19 7276 [Note] InnoDB: Highest supported file format is Barracuda.
     2014-06-06 13:31:19 7276 [Note] InnoDB: The log sequence numbers 1625987 and 1625987 in ibdata files do not match the log sequence number 1638951 in the ib_logfiles!
     2014-06-06 13:31:19 7276 [Note] InnoDB: Database was not shutdown normally!
     2014-06-06 13:31:19 7276 [Note] InnoDB: Starting crash recovery.
     2014-06-06 13:31:19 7276 [Note] InnoDB: Reading tablespace information from the .ibd files...
     2014-06-06 13:31:19 7276 [Note] InnoDB: Restoring possible half-written data  pages
     2014-06-06 13:31:19 7276 [Note] InnoDB: from the doublewrite buffer...
     2014-06-06 13:31:19 7276 [Note] InnoDB: 128 rollback segment(s) are active.
     2014-06-06 13:31:19 7276 [Note] InnoDB: Waiting for purge to start
     2014-06-06 13:31:19 7276 [Note] InnoDB: 5.6.16 started; log sequence number 1638951
     2014-06-06 13:31:19 7276 [Note] Server hostname (bind-address): '*'; port: 3306
     2014-06-06 13:31:19 7276 [Note] IPv6 is available.
     2014-06-06 13:31:19 7276 [Note]   - '::' resolves to '::';
     2014-06-06 13:31:19 7276 [Note] Server socket created on IP: '::'.
     2014-06-06 13:31:19 7276 [Note] Event Scheduler: Loaded 0 events
     2014-06-06 13:31:19 7276 [Note] c:\xampp\mysql\bin\mysqld.exe: ready for connections.
     Version: '5.6.16'  socket: ''  port: 3306  MySQL Community Server (GPL)


texts:
I currently have a list of databases where the profiles of users are out of date. A lot are listed under default where we want them to be either USR_PROFILE or DBA_PROFILE.

I have a list of all the usernames of users we want under dba_profile. What would be the best way to run an alter user statement to batch change all usernames to USR_PROFILE other than the list of dba users (who may or may not be registered in the database).

So to do it individually,it would be

alter user U273622 profile USR_PROFILE;

However, that would take forever as this database has over 350 users. Would this be a reasonable guess, to ignore the dba users listed?

alter user where username='U******' except where username='U238234,U3493234,U738467' profile USR_PROFILE;

Kind of scared to test it out as I don't want to break all the usernames. Any help would be great!


texts: Sql select slow on filter for key, but not for primary key, how can I hint the priority

texts:
I have 2 select, the first is superfast, and the second is 10x more slower. It seems to be first join everything from the table, than running the startdate filter, and the first case it is filtering for the id first and only than running the select.
And yes got it, first he is running the subselect, sees that it is only 400 record, and doing the rest. So how can I hint that without this workaround to run that start date filter first?

Tourneys table: 1M records (has index on StartDate)
TourneyPlayers table: 2M records (has index on TourneyId ofc)
Tourneys after the start date filter: 400 records

fast:

select *
from Tourneys t right join TourneyPlayers tp on t.Id = tp.TourneyId
where 1=1
  and t.id in (select id from Tourneys where StartDate > DATEADD(hour,-1,GETDATE()))

https://www.brentozar.com/pastetheplan/?id=S1t8EDQ4V

slow:

select *
from Tourneys t, TourneyPlayers tp
where 1=1
  and t.StartDate > DATEADD(hour,-1,GETDATE())
  and t.Id = tp.TourneyId

https://www.brentozar.com/pastetheplan/?id=rJKzNvmEV


texts: Postgres: trying to fix some unicode encoding issues

texts:
I've got a postgres 8.4 database with a few mangled characters in one of the columns on a table. For example, the word "don't" gets displayed within psql as:

donÃ¢\u0080\u0099t

I want to be able to dig out these odd characters and replace them with simple ' or " alternatives. However, I'm having a bit of a job getting either ~ or LIKE to be able to pull the correct rows in, let alone be able to replace them.

The database is UTF8 and the client_encoding is also set to UTF8. I've tried selecting \u string and \x strings, but can't seem to get anything reliable. For example, all of the following queries return 0 rows or ERROR:  invalid byte sequence for encoding "UTF8": 0x80

select col from tab where col like '%\u0080\u0099%';
select col from tab where col ~'\u0080\u0099';
select col from tab where col like '%\x8099%';
select col from tab where col ~'\x8099';

Any ideas?


texts:
I have a fairly large transactional database (100GB) with a lot of users.  The database access is solely via stored procs that make heavy use of temp tables, table variables, cursors and other fun things.

I'm trying it out on a new box in an effort to boost the performance.  The new box has 5 separate physical hard drives.  I am struggling trying to come up with an optimal setup (mostly though ignorance, I am a c# dev normally).  So far, I got the following:

Drive C: OS, SQL install, TempDB log
Drive D: Database data
Drive E: Database log
Drive F: TempDB data
Drive G: Databases indexes

Am I going about the right way?  Any glaring mistakes?


texts: PostgreSQL keeps asking for user password with "trust" authentication enabled

texts:
I created a user john with createuser and didn't supply any passwords for it, i.e. no -P option specified. I then proceeded to add the following line above all other lines in the pg_hba.conf file:
host    all             john            127.0.0.1/32            trust

I then restarted the server but even after that, when I try to run psql -U john it keeps asking for a password. I even ran the command psql -c "alter role john with password null;" but it changed nothing. PostgreSQL keeps asking for a password for john. What am I missing?
Running PostgreSQL 13.2 on Windows 10.


texts: How to configure an Oracle Listener without SID_LIST_LISTENER in listener.ora?

texts:
I have access to a ORACLE 11g server using the following listener.ora

# listener.ora Network Configuration File: D:\app\Administrator\product\11.1.0\db_1\NETWORK\ADMIN\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.111.111)(PORT = 1521))
    )
  )

and it serves all the instances on that server, as I can verify by

lsnrctl status

My problem is, that when I make a fresh install of Oracle 11g in a virtual machine, I have to use a listener.ora like

# listener.ora Network Configuration File: C:\app\oracle\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = CLRExtProc)
      (ORACLE_HOME = C:\app\oracle\product\11.2.0\dbhome_1)
      (PROGRAM = extproc)
      (ENVS = "EXTPROC_DLLS=ONLY:C:\app\oracle\product\11.2.0\dbhome_1\bin\oraclr11.dll")
    )

   (SID_DESC =
     (GLOBAL_DBNAME = ORCL)
     (ORACLE_HOME = C:\app\oracle\product\11.2.0\dbhome_1)
     (SID_NAME = orcl)
    )

   (SID_DESC =
     (GLOBAL_DBNAME = BMBK)
     (ORACLE_HOME = C:\app\oracle\product\11.2.0\dbhome_1)
     (SID_NAME = BMBK)
    )

 )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 169.254.200.102)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    )
  )

ADR_BASE_LISTENER = C:\app\oracle

Without the SID_LIST_LISTENER enties, i.e. using a listener.ora like that from the other server I get

C:\Windows\system32>lsnrctl status

LSNRCTL for 64-bit Windows: Version 11.2.0.1.0 - Production on 27-NOV-2011 10:14
:24

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Anmeldung bei (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS des LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 11.2.0.1.0 - Produ
ction
Startdatum                27-NOV-2011 10:14:08
Uptime                    0 Tage 0 Std. 0 Min. 18 Sek.
Trace-Ebene               off
Sicherheit                ON: Local OS Authentication
SNMP                      OFF
Parameterdatei des Listener C:\app\oracle\product\11.2.0\dbhome_1\network\admin\
listener.ora
Log-Datei des Listener    c:\app\oracle\diag\tnslsnr\WSV-BK-W7en64-S\listener\al
ert\log.xml
Zusammenfassung Listening-Endpunkte...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=169.254.200.102)(PORT=1521)))
Der Listener unterst³tzt keine Services
Der Befehl wurde erfolgreich ausgef³hrt.

The last message translates to

The listener supports no Services.

I have no idea, why these two servers behave differently. I found no hints in configuration files nor in registry keys.

Edit:

I guess the answer is somewhere buried in this post by Burleson Consulting, but I don't find the spot, where it is actually configured.


texts: Can i control access end point When some table data changed In Mysql Semisynchronous Replication

texts:
I'm trying to set up One master Multi slave in MySQL 5.7

My architecture is like this.

Master - First slave(sync)
Second slave, Third slave, Fourth slave, Fifth slave(async)

My question is, If aTable data is changed Then application must access First slave. Until all slave is synchronized.

Because another slave data is not realtime sync...

Do you know this function supports library or tools?
If you have an other idea, please help me.


texts: Error in skipping command in transactional replication in SQL Server 2012

texts:
My transactional replication is failing continuously. I am trying to skip the command but not able to do that. Below are the steps which I performed.

1) use distribution

select * from MSrepl_commands where xact_seqno=0x003A16D800003FF0000600000000 and command_id=6

2) Now, on subscriber I ran this command

USE subscriber_db
GO

sp_setsubscriptionxactseqno  'publisher', 'publisher_db', 'publication_name', 0x003A16D800003FF0000600000000

I am getting this error

  Msg 14135, Level 11, State 1, Procedure sp_setsubscriptionxactseqno, Line 142
  There is no subscription on Publisher 'UBNRDB15', publisher database 'Unibet_affiliate', publication 'Test Player Replication'

Anybody please let me know how to solve this? Or any other way I can skip that command?


texts: Optimize MAX or ORDER BY column DESC with LIMIT 1

texts:
I have a scenario where I need to Insert a new record and increase a sub-id.
The Query is very slow.
Exist any better Solutions for this?

Table

CREATE TABLE `link_` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `duid` bigint(20) DEFAULT NULL,
  `domain_id` int(11) DEFAULT NULL
  PRIMARY KEY (`id`),
  KEY `domain_id` (`domain_id`),
  KEY `duid` (`duid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

Solution 1

INSERT INTO link_ (duid, domain_id)
SELECT
    IFNULL(MAX(duid), 0) + 1 AS duid, 5
FROM
link_
WHERE domain_id = 5;

Solution 2

INSERT INTO link_ (duid, domain_id)
SELECT
    IFNULL(duid, 0) + 1 AS duid, 5
FROM
    link_
WHERE domain_id = 5
ORDER BY duid DESC
LIMIT 1;


texts: I have a table in mysql, in which searching, insertion, deletion and updation is done quite frequently and I have read that indexes affection insertion and updation performance. so should I use Indexes for faster searches?


texts: AWS Aurora for PostgreSQL strange vacuum cost parameters

texts:
Today, I found out that in AWS Aurora for PostgreSQL 11 the default cluster parameter vacuum_cost_page_miss = 0 whereas vacuum_cost_page_hit = 1.
I'd love to understand the logic behind pricing a page miss as cheaper than a page hit.


texts: Can PostgreSQL's COPY create CSV with Locale-specific number formatting?

texts:
I want to create a CSV with PostgreSQL 9.1 COPY that directly opens correctly with a double-click in crazy German Excel 2010. Using ";"-Delimiter is easy.
The encoding is solved, but Excel only recognizes numbers correctly if they use the  , as decimal seperator.

Thats what the CSV should look like;

10,7;8,1;90,0;"some, text."

Thats what the CSV looks like now:

10.7;8.1;90.0;"some, text."

Is there a way to do that with COPY?

What I tried:

SHOW lc_numeric;
"de_DE.UTF-8"

is set but has no effect.

I found to_char(double precision, text) function. But my table has ~200 columns. Will I have to put to_char( column , 'FM9999999999999999D999999999999999999') for every numeric column? Is there any setting to make COPY change it automagically?

Thanks in advance, Steve


texts: Is it possible to make an index for an OR query?

texts:
I have a query like this:

select * from things where person_1_id=123 OR person_2_id=123

Is it possible to make some sort of index that covers this? Assume that the id (123) matched against will always be the same for both columns.


texts: Can an active session in oracle be dead?

texts:
I have a time consuming query which populates a materialized view.  It has been running for 18 hours - which was not expected.

SELECT STATUS
FROM V$SESSION
WHERE SID=194;

The above query shows the session as 'ACTIVE'.  I was wondering if that is sufficient proof that the query is still running and whether there is any other way to double check that the session is not dead.

UPDATE

The session was not dead; but there was a constant wait:

 SQL*Net more data from dblink

I used sql trace as suggested by Phil to reach this conclusion.


texts: PostgreSQL partitioning query performance inexplicably bad

texts:
I find this a bit mysterious. The cost of getting the maximum value out of just the partition parent table (which is empty) is very cheap:

evlampts=# explain select max(timestamp) from only mcdata;
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Result  (cost=0.15..0.16 rows=1 width=0)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..0.15 rows=1 width=8)
           ->  Index Scan Backward using mcdatatime_idx on mcdata  (cost=0.00..19.20 rows=130 width=8)
                 Index Cond: ("timestamp" IS NOT NULL)
(5 rows)

Likewise, the cost of getting the maximum value out of just the newest partition is very cheap.

evlampts=# explain select max(timestamp) from mcdata_2011_09;
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=3.17..3.18 rows=1 width=0)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..3.17 rows=1 width=8)
           ->  Index Scan Backward using mcdata_2011_09_timestamp_idx on mcdata_2011_09  (cost=0.00..560255403.07 rows=176936192 width=8)
                 Index Cond: ("timestamp" IS NOT NULL)
(5 rows)

Here's the weird thing. Getting the maximum from the whole partitioned table is very expensive. PostgreSQL doesn't seem to produce efficient subplans, even though there are only two tables here (the master and one partition) and both can be queried separately efficiently.

evlampts=# explain select max(timestamp) from mcdata;
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=4663394.03..4663394.04 rows=1 width=8)
   ->  Append  (cost=0.00..4221053.22 rows=176936322 width=8)
         ->  Seq Scan on mcdata  (cost=0.00..11.30 rows=130 width=8)
         ->  Seq Scan on mcdata_2011_09 mcdata  (cost=0.00..4221041.92 rows=176936192 width=8)
(4 rows)

Is there a configuration tunable I've missed or is this a limitation I don't understand?

Thanks!


texts:
I was using the official mariadb 10.3 image as a database with innodb and file per table turned on. A few months ago I pulled the image for a minor release and let it run. This as I now found out did not automatically upgrade the database its self as the logs have been saying to run the mysql_upgrade. The database has not been restarted since that pull.

New data has been added and used in that time however the container restarted and it seems now all data since the pull a few months ago has been lost on certain tables and the .idb files even on backups are saying  there was no modification on the file.

Any suggestions on where this data might have been written too or how to recover it?

error logs pre restart

InnoDB: Table mysql/innodb_table_stats has length mismatch in the column name table_name.  Please run mysql_upgrade
InnoDB: Table mysql/innodb_index_stats has length mismatch in the column name table_name.  Please run mysql_upgrade

error logs during crash

    Using Linux native AIO
    [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
 [Note] InnoDB: Uses event mutexes
[Note] InnoDB: Compressed tables use zlib 1.2.11
 [Note] InnoDB: Number of pools: 1
 [Note] InnoDB: Using SSE2 crc32 instructions
[Note] InnoDB: Initializing buffer pool, total size = 512M, instances = 1, chunk size = 128M
[Note] InnoDB: Completed initialization of buffer pool
[Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
[Note] InnoDB: Starting crash recovery from checkpoint LSN=471249973567
[Note] InnoDB: 16 transaction(s) which must be rolled back or cleaned up in total 16 row operations to undo
[Note] InnoDB: Trx id counter is 602890104
 InnoDB: Starting final batch to recover 170 pages from redo log.

All recovery modes were tried but only 6 worked.

InnoDB: Failed to find tablespace for table `database name`.`table name` in the cache. Attempting to load the tablespace with space id 1216


texts: Which SQL Server Express edition is right for me?

texts:
I have developed a Java program which works with SQL Server as it's database. I have installed SQL Server 2012 Enterprise edition on my Windows and designed the database via SQL Server Management Studio; for example: I have designed its tables, have set a backup schedule and it takes a backup from itself every 24hs once, and other features.

Which version of SQL Server Express should be installed on a user's Windows computer so that not only can my program connect to a local db and work with that, but also the other proscribed database features (like backup schedule) would work?

The database and Java program are going to be on the same computer.

The criteria I have for chosing a database edition are

My database has some tables
The Java program is going to insert, update, delete, select, and add columns to tables
iReport will be used to report on the data, in addition to the Java application
The database file is set so that it can not be deleted
It has a scheduled backup and nothing else


texts:
There was an update statement which was ran in our production environment which should not have been run.

I wanted to know if there was a way I can find when and who ran that specific update statement. I no longer have the corresponding .trn files but I do have the .mdf and .ldf file from that specific day.

Is it possible to find this without the .trn files?


texts: How to run mysql_upgrade on mysql workbench

texts:
Is there anyway to run mysql_upgrade on mysql workbench?
Or it can be run only on command line?


texts: Multiple Writes to Same table in Redshift

texts:
I have a table in Redshift.

My Application has Multiple Writes/Reads on this Table concurrently.
How I do Make sure Reads won't wait for Writes to Commit/RollBack.

I wanted to know which Lock Mode I need to use in this case.
Please provide if any such behaviour is possible in PostgreSQL so that I will check the relevant Redshift implementation.


texts:
I have the following error

  The database 'Test' cannot be opened because it is version 661. This
  server supports versions 655 and earlier. A downgrade path is not
  supported.

Usually this kind of error occurs when the database was made with SQL Server 2008 R2 and is opened with for example SQL Server 2008 SP2, but the problem is that I'm trying to open it exactly in SQL Server 2008 R2 which supports version 661. So what's the problem?


texts: MySQL datatype for username of maximum 15 characters

texts:
I currently have a database with the "user" table having username as one of the columns.

I allow a maximum of 15 characters for the username in my system. Is it fine to have the username column as a varchar(15) datatype?


texts: What are the Oracle Dataguard Implications of Migrating to a PDB?

texts:
If I have an OLTP database with two physical standby instances (one remote) what are the implications of migrating the primary DB to being an Oracle 12c PDB? i.e. Does the process of migrating to being a PDB send a sensible EOR record via Dataguard that will leave the data files on the physical standbys in a state that can be picked up from or would it require a complete backup of the whole CDB with the new PDB plugged in on the primary to be restored to each standby before Dataguard could be resumed?

The Primary DB would already be a 12c non PDB.


texts: function checking type of input and reacting

texts:
I have a function that can check if each element in an array is present in a given column of a table, and it works fine. I would now like to extend the function so that it can handle json/jsonb arrays as well as SQL arrays.
In this MWE (PSQL-11) the commented lines are psudo code for what I would like to do. Keeping them commented out lets you run the code but it then only works for SQL arrays.

CREATE TABLE IF NOT EXISTS animals
(
    animal text COLLATE pg_catalog."default" NOT NULL,
    animal_doc jsonb,
    CONSTRAINT animals_pkey PRIMARY KEY (animal)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE animals
    OWNER to postgres;

CREATE OR REPLACE FUNCTION check_is_animal(anyelement) RETURNS boolean AS $$
DECLARE
   i text;
   arr text[];
BEGIN
--    IF ((SELECT pg_typeof($1)) = 'jsonb') THEN
--        arr = jsonb_array_elements($1);
--    ELIF ((SELECT pg_typeof($1)) = 'json') THEN
--        arr = json_array_elements($1);
--    ELSE
        arr = $1;
--    END IF;
    FOREACH i IN ARRAY arr LOOP
        IF i NOT IN (SELECT animal FROM animals) THEN
            RAISE NOTICE '% is not a known animal', i;
            RETURN FALSE;
        END IF;
    END LOOP;
    RETURN TRUE;
END;
$$ LANGUAGE plpgsql;

INSERT INTO animals (animal) VALUES ('Cat') ON CONFLICT ON CONSTRAINT animals_pkey DO NOTHING;
INSERT INTO animals (animal) VALUES ('Dog') ON CONFLICT ON CONSTRAINT animals_pkey DO NOTHING;

DO $$
BEGIN

IF check_is_animal(array['Cat','Dog', 'Horse']) THEN
  RAISE NOTICE 'All good';
ELSE
  RAISE NOTICE 'Failed';
END IF;

/*
IF check_is_animal(jsonb('["Cat","Dog", "Horse"]')) THEN
  RAISE NOTICE 'All good';
ELSE
  RAISE NOTICE 'Failed';
END IF;
*/
END $$;

The SELECT pg_typeof($1) was found here.
Any suggestions for how the get this to work?


texts: How to resolve error 15517 when DBO is set to SA?

texts:
Newbie DBA here, just getting settled into my first DBA job and trying to figure out how things work outside of a classroom environment. Your patience is appreciated.

I'm trying to create diagrams for all the databases so I can try and get a grasp on how things are laid out here and a problem I'm running into is that when I attempt to create a diagram for one database in particular I'm getting the
 following error:

  Cannot execute as the database principal because the principal "dbo"
  does not exist, this type of principal cannot be impersonated, or you
  do not have permission. (Microsoft SQL Server, Error: 15517)

I've been searching for more info on this issue and it looks like it usually is caused by the owner of the database getting deleted but this database shows SA as the owner so that shouldn't be the issue. I'm not even able to create a diagram if I sign in as sa...

Not sure if this may have something to do with it but this particular database was migrated to a new server a few months ago so something may have broken when that was done (although the database is live and in use so it isn't entirely broken).

Maybe its looking for the sa account from the old server and it just needs to be updated to use the sa account on the current server?

Edit: This DB may not actually have an owner after all. Performing a Right click > Properties > Files on the database shows no owner for the database even though the owner field under general does.

I tried to change it through the GUI and was informed that set owner failed with the following error:

  Lock request time out period exceeded. (Microsoft SQL Server, Error:
  1222)

I also tried the alter authorization ... statement and it just never completes (or I cancelled it after a few minutes to be more accurate). Am I being to impatient with that statement or is something else going on here?


texts: Retrieving a fixed range of rows from Postgresql

texts:
I have a query:

select qid,ansid,ans from table1 where askerid='something'

This query can retrieve many rows. But I want to display only 50 rows at a time and then if the user clicks on more, the next 50 rows should be retrieved. I have thought of a query like this :

select qid,ansid,ans
from table1
where askerid='something'
limit 100
minus
select qid,ansid,ans
from table1
where askerid='something'
limit 50

I want to know if this is a good query performance-wise and alternate queries that perform better than the above one.


texts: Why are foreign keys ignored on subscribers?

texts:
We are using merge replication on SQL Server 2008 R2. There are many tables with foreign keys providing a logical dependence on each other. For this question, let's say there are User and File tables, where File has a foreign key for the user who created the file.

We often see conflicts of the "constraint violation" type when a User and File have been created on a subscriber, on exactly this FK, implying that a row insert was attempted on the File table with a user_id that doesn't exist in User.

However, once synchronisation is finished, the new user does exist on the publisher. The new file doesn't, and has the conflict mentioned above. This seems to imply that while both tables were synchronised by replication, they were done in the wrong order.

Using SSMS, I can see that the FK in question (and all others) has the "Enforce for replication" option set to "Yes" on the publisher, but set to "No" on the subscriber.

I was not the one who created the tables (and that person doesn't work here any longer) so I can't know for sure if the FKs were created with NOT FOR REPLICATION. However if I try to Script Table as > CREATE to > New Query Editor Window, no such flag is present on either Publisher or Subscriber:

ALTER TABLE [dbo].[File]
    WITH CHECK
    ADD CONSTRAINT [FK_File_User]
    FOREIGN KEY([user_id])
    REFERENCES [dbo].[User] ([user_id])
GO
ALTER TABLE [dbo].[File] CHECK CONSTRAINT [FK_File_User]
GO

Am I correct in assuming that the conflicts are caused by the "Enforce for replication" setting being "No" on all subscribers? And if that is the case, how do we ensure they are set to yes when creating new subscribers?

The subscriber database is created manually as an empty database before the first synchronisation and populated (both schema and data) by replication itself.

In case it matters, these are the (censored) scripts used when a new subscriber is added.

On the publisher:

declare @db_name nvarchar(50) = N'SubscriberDB';
declare @client nvarchar(50) = N'Subscriber\SQLEXPRESS';

use [PublisherDB]
exec sp_addmergesubscription
    @publication = N'sync',
    @subscriber = @client,
    @subscriber_db = @db_name,
    @subscription_type = N'pull',
    @subscriber_type = N'global',
    @subscription_priority = 75,
    @sync_type = N'Automatic'

On the subscriber:

declare @db_name nvarchar(50) = N'PublisherDB';
declare @net_timeout int = 900;

use [SubscriberDB]
exec sp_addmergepullsubscription
    @publisher = N'Server',
    @publication = N'sync',
    @publisher_db = @db_name,
    @subscriber_type = N'Global',
    @subscription_priority = 75,
    @description = N'',
    @sync_type = N'Automatic'

exec sp_addmergepullsubscription_agent
    @publisher = N'Server',
    @publisher_db = @db_name,
    @publication = N'sync',
    @distributor = N'Server',
    @distributor_security_mode = 0,
    @distributor_login = N'dlogin',
    @distributor_password = N'dpassword',
    @enabled_for_syncmgr = N'True',
    @frequency_type = 4,
    @frequency_interval = 1,
    @frequency_relative_interval = 1,
    @frequency_recurrence_factor = 0,
    @frequency_subday = 8,
    @frequency_subday_interval = 1,
    @active_start_time_of_day = 0,
    @active_end_time_of_day = 235959,
    @active_start_date = 0,
    @active_end_date = 0,
    @alt_snapshot_folder = N'',
    @working_directory = N'',
    @use_ftp = N'False',
    @job_login = null,
    @job_password = null,
    @publisher_security_mode = 0,
    @publisher_login = N'plogin',
    @publisher_password = N'ppassword',
    @use_interactive_resolver = N'False',
    @dynamic_snapshot_location = N'',
    @use_web_sync = 1,
    @internet_url = N'https://ourserver/replisapi.dll',
    @internet_login = N'ilogin',
    @internet_password = 'ipassword',
    @internet_security_mode = 0,
    @internet_timeout = @net_timeout
GO


texts: I found a situation where it would be useful to know who started SQL Server Service. First we did not know why it was stopped, it was not that urgent since this was a TEST instance but suddenly one day later we found that SQL Server Service was started. I look on Windows Event Log (System) and found the entry that mention that sql server was started. It says User: system. Any ideas? Thanks!


texts: Moved database to new server, not sure about NT AUTHORITY\* users

texts:
I just moved database to new instance of SQL Server (SQL Server 2012 Web from Amazon EC2) and am now cleaning up database users.

I am not sure if/why these 2 users are needed (they exist in original database):

NT AUTHORITY\IUSR
NT AUTHORITY\NETWORK SERVICE

Note that there are also 2 database schemas with exactly the same names.

I am more clear on NT AUTHORITY\IUSR - this seems to be a user for IIS. IIS is not even installed on the new instance, so this one probably can be deleted.

I am not sure about NT AUTHORITY\NETWORK SERVICE (or how it got created to begin with - new databases created on new server don't have it, or NT AUTHORITY\IUSR)

Neither of these 2 users exist at server level on a new instance or on an old instance.


texts: Are there any downfalls to not using foreign key relationships in a multi-tenary scheme?

texts:
We have a proposed restructure of our CRM containing almost 500 databases containing ~25 tables each into a single multidimensional database with one giant fact table linking everything together.

I have looked at the star schemes but there will be so many different star points that a multidimensional data type seems more fitting.

Here is a rough generalized mock-up structure:

pivot_table

id | parent_table | parent_id | child_table | child_id | relate_type

person

id | first_name | last_name | birth_date

organization

id | name | lat | long

tag

id | slug | tag_name

addresses

id | line_1 | city | state | country_id | zip

phone_number

id | country_id | number | phone_type_id

country

id | name

phone_types

id | phone_type

There are virtually an unlimited amount of joins between just these tables as people and organizations can both have addresses and phone numbers and people can have other people and organizations can have people and people can have organizations.

There is a small amount of data that can have foreign keys such as the phone types and countries.

Are there other downfalls to this data type other then putting the crux of security at the data API level instead of in the database itself.

Are there any caveats in not being able to have foreign keys between the fact table and the dimensions?


texts: Mysqldump with ansible for database backup

texts:
I am trying to backup remote database using ansible and mysqldump. I have installed ansible in my local server.Generated the ssh keys. I also sent the public key to the remote server.
Everything is fine in ansible part but can't backup using ansible and mysqldump.

I tried this:

ansible -m shell -a 'mysqldump -h 192.168.10.155 -u root -proot database_name  > database_name.sql' remote_server

But the backup is being created in home directory of remote_server. My requirement is that I would like to take the backup of remote_server into my local_server.

edit:

Now i using playbook:

hosts: remote_server
become: yes
tasks:

name: do a mysqldump
ignore_errors: yes
shell: mysqldump -h 192.168.10.155 -u root -proot database_name > dbname.sql

hosts: remote_server
tasks:

name: Transfer file from remote to local

synchronize:

src: /home/user/test.txt
dest: /home/user/backup
delegate_to: local_server

Backup is happening but how can i get the backup in the server i am running ansible from?

I studied that this can be done using playbook as well.

How can I achieve it with playbook?


texts:
I'm trying to create the table CanTeach but I am getting an error ERROR 1005 (HY000): Can't create table 'mydb'.'CanTeach' (errno: 150 "Foreign key constraint is incorrectly formed"). Ultimately, I'm trying to create a table of "classes that teacher's can teach" verifying they work for the department that offers the class.
CREATE TABLE Department (
    Name varchar(255) NOT NULL PRIMARY KEY
);
CREATE TABLE Person (
    SSN CHAR(9) NOT NULL PRIMARY KEY,
    LastName varchar(255),
    FirstName varchar(255)
);
CREATE TABLE Professor (
    SSN CHAR(9),
    PRIMARY KEY(SSN),
    FOREIGN KEY (SSN) REFERENCES Person(SSN)
);
CREATE TABLE WorksFOR (
    SSN CHAR(9),
    Salary float NOT NULL,
    DepName varchar(255),
    CONSTRAINT WorksFor_Salary CHECK (Salary>=0),
    CONSTRAINT WorksFor_pk PRIMARY KEY(SSN,DepName),
    CONSTRAINT WorksFor_ssn_fk FOREIGN KEY(SSN) REFERENCES Professor(SSN),
    CONSTRAINT WorksFor_dep_fk FOREIGN KEY(DepName) REFERENCES Department(Name)
);
CREATE TABLE Course (
    CourseDepartment varchar(255) NOT NULL,
    CourseNumber varchar(8) NOT NULL,
    CONSTRAINT Course_pk PRIMARY KEY(CourseDepartment,CourseNumber),
    FOREIGN KEY(CourseDepartment) REFERENCES Department(Name)
);
CREATE TABLE CanTeach (
    SSN CHAR(9) NOT NULL,
    Department varchar(255) NOT NULL,
    CourseNum varchar(8) NOT NULL,
    CONSTRAINT CanTeach_pk PRIMARY KEY(SSN,Department,CourseNum),
    FOREIGN KEY(SSN,Department) REFERENCES WorksFor(SSN,DepName),
    FOREIGN KEY(Department,CourseNum) REFERENCES Course(CourseDepartment,CourseNumber)
);


texts: Show All online SID Database Oracle 11 in Linux SuSe and Find Database file

texts:
I'm new with Oracle DBA in my new office, and they gave me a development server that already have a lot SID database online in SuSe (I checked with lsnrctl status)

My questions are :

How to check what SID instance that really live/online, because turns out when I checked again with ps ax|grep ora, only 3 of databases live/online?

If only 3 SID databases that really online like in 1st question, how do I know where all the 3 SID the database file position that already live/online? what do I have to type to know it?

I try to login sqlplus sys as sysdba when I checked with select name from v$database; I was logging in 1 SID database. Continuing from 2nd question, How do I logout from current SID database and then switch  to other 2 SID database?
(example in MongoDB, I type use tutorial to enter tutorial database and type use dbdevelopment to switch to database dbdevelopment)


texts: How to create SQL Stored Procedure with parameters of type List

texts:
I need to create a Stored Procedure that will take parameters of type List. For example :

Select *
 From Table
Where State   in @StateList
   or Lob     in @LobList
   or Company in @CompanyList

The parameters @StateList, @LobList and @CompanyList will contain string data like :

StateList:[ "GA", "AL", "TN" ]
LobList:[ "D20", "PXL", "APM" ]
CompanyList:[ "QQ", "WW", "RE" ]

Does SQL allow this ?


texts: SQL Server: Should we use TCP or Named Pipes or use the default?

texts:
When connecting to a SQL Server 2008 R2 from a .NET 4 client application on a different server in the same LAN, one can set three different network protocols:

TCP
Named Pipes
Don't set anything in the connection string and use the default

What is best practice? What to choose?

Additional information: Both TCP and Named Pipes are enabled both on the server and on the client. The application is using database mirroring. Client and server communicate over a fast LAN.

We are investigating this because we have rare and spurious connectivity and timeout problems. (But regardless of that I'd like to know the best practice).

There is an article on this subject on MSDN but it is very generic and vague. It does not advise or recommend anything useful.


texts: How to find "outer join in the where" syntax in SQL 2005 using cmptlevel 80?

texts:
Well last Christmas I was very happy, because we ceased support for SQL Server 2000.

I could stop twisting my brain and use friendly analytical functions. (Believe me, when it comes to migrate stored procedures from SQL-Server to Oracle Analytical functions are one of your best friends).

Later we discovered that we overlooked a tiny annoyance.

When customers replaced there old machines by new ones, by default their compatibility level is set according to the old machine and problems are postponed until later.

Which problem? Analytical functions work in SQL Server 2005 despite compatibility level 80.

I'm afraid to tell that other new features like PIVOT is inhibited.

Now it is good time to try to get rid of cmptlevel 80, but how?

For me it is mostly scanning the procedures, views, functions and triggers for old style outer joins like '*=' and '=*'.

First let us find the defining text from the system tables. Initially it was saved in small chunks in syscomments. Hhm how to deal with the case when '*' and '=' are split onto to succeeding chunks.

Next don't waste your time with Select ROUTINES from INFORMATION_SCHEMA.ROUTINES it truncates the definition to 4000 characters.

Finally this is the code to query for the problematic objects:

select o.name , o.type, datalength(definition) len, len(definition) len, definition from sys.sql_modules m
join sysobjects o on o.id = m.object_id
where (definition like '%*=%' or definition like '%=*%')

But there is a further hurdle. Because default is derived from default the default width for text results is too small ( I think it is 256, but as it is the first thing I change to 8192 in SSMS, I wont bet).

Any case this helps to identify the rows which need deeper examination.

Perhaps sp_help displays the full definition for all possible cases.

Perhaps it does, I don't know for sure, because I got tired of that problems created by punchcard heads. Normally I use ADO and PowerShell, but they may not be installed on the customers machine.

Are there any 100% waterproof T-SQL scripts to do the job of displaying or exporting the full definition of procedures found by some condition?