texts: Select rows from table using conditions across multiple rows in left joined table

texts:
I have 3 (or more) tables as follows in MySQL
persons

id
name
gender

1
nim
male

2
nam
female

visits

id
person_id
date

1
1
2020-12-10

2
1
2020-12-16

3
1
2020-12-17

4
2
2020-12-20

5
2
2020-12-21

selected_options

id
visit_id
option_a
option_b
option_c
option_d

1
1
yes
no
yes
yes

2
2
no
no
no
no

3
3
yes
yes
no
no

4
4
yes
yes
yes
yes

5
5
yes
no
yes
yes

Given the above table structure, I am looking to write a query that selects people who have selected "yes" to a set of options at during their visits.
E.g If the options to be queried are option_a = 'yes', option_b = 'no', option_c = 'no'; The query should return person with id 1 since he selected option_a as 'yes' at visit 1, option_b as 'no' in visits 1 and 2 and option_c as 'no' in visits 2 and 3. It should not return person 2 because person 2 selected option_a as 'yes' in visit 4, option_b as 'no' in visit 5 but never selected option c as no in any of their visits.
I have tried the following:
SELECT  *
    FROM  persons
    LEFT JOIN  visits  ON person.id = visit.person_id
    LEFT JOIN  selected_options  ON visit.id = selected_options.visit_id
    WHERE  (option_a, option_b, option_c) IN (('yes', 'no', 'no'))

However it does not match any values.


texts: Service broker - Reusing conversation - Problem of deadlock

texts:
I am re using the conversation as per Remus Rusanu's blog http://rusanu.com/2007/05/03/recycling-conversations/. The event flows from initiator to target. Please note that my service broker implementation processes around 40 to 60 events per second.

I have used 6 hour as the life of a conversation handle.

Majority of the time I have no problem and the event flows nicely. The handle expires after 6 hour and then new handle gets created. All the clean up happens as per the plan.

Sometime though I am facing the errorcode 1205 inside initiator stored procedure (i.e. usp_SenderActivation), then resulting in roll back. This error is happening exactly at the time when old handle expires and new handle gets created.

Upon further investigation I realized that when the handle gets selected from the SessionConversations table, immediately after that the handle gets expired. This results in sending an event using a expired handle.

Has anyone experienced this? Any help?

Thanks,
Suvendu.


texts: How to enable Toolbox window in SQL Server Management Studio?

texts:
I am assuming that the Toolbox windows in SSMS is used to store code samples.  So I am able to highlight code in the editor and drag it to the Toolbox window.
However, if I wanted to use the code sample and drag it back onto the editor (or double click on it), I can't.  All entries in the Toolbox are grayed out.  And when I drag an entry, it turns the mouse cursor into a stop sign.
What do I need to do to enable this functionality?

P.S.  Using SSMS v17.9.1 and the only add-in installed in SQL Prompt 8.
P.P.S. The only workaround that works is clicking on the entry, Ctrl+C, then pasting it into the editor.


texts: Is it possible to upsert into a self-referencing table with a single statement in PostgreSQL?

texts:
If I have a table A, like so:

A {
 id SERIAL
 title TEXT
 ...
 parentId INT references A.id via a foreign key constraint
}

I am pulling data from a source table - A_SOURCE - where there isn't a parentId column. Instead there is a parentTitle column. So the source table looks something like this:

A_SOURCE {
  title TEXT
  parentTitle TEXT
}

I started writing an upsert statement to insert into table A via a selection from table A_SOURCE before I realized that I can't easily resolve the parentTitle column in the source to a parentId in the target.

Since I can't be sure that the parent will have been inserted at the time the child being processed, a join or a subquery could return no results.

My upsert statement is looking something like this:

with source as (
  select
   title
   parentTitle
  from A_SOURCE
)

insert into A
select
title
... I don't think I can resolve to parentId here?
from source
on concflict ...;

I know that I can run two separate statements:

insert with null as parentId
Then update the parentId fields in the second statement

But is it possible to do this in a single query?


texts: What is the ideal database(s) and design for chat applications?

texts:
I want to transfer real-time chat build on firebase RTDB due to scaling issues. The current system has around 5000 active users and 30000 daily messages which we expect to scale around half a million message a day. We have implemented 1-1 chat, Group chat.

What would be the ideal database(s) for us to scale the chat app well and what should be the design?


texts: PostgreSQL database size doesn't match used space on disk

texts:
I have a PostgreSQL 9.6 database with high-volume traffic. I run periodically pg_repack reclaim unused space in tables/indexes. On larger tables repack sometimes fails to complete the process which results in using more disk space that PostgreSQL reports the DB is using.

I use following query to report size of each database:

SELECT schema_name,
       pg_size_pretty(sum(table_size)::bigint),
       (sum(table_size) / pg_database_size(current_database())) * 100 as pct
FROM (
  SELECT pg_catalog.pg_namespace.nspname as schema_name,
         pg_relation_size(pg_catalog.pg_class.oid) as table_size
  FROM   pg_catalog.pg_class
     JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid
) t
GROUP BY schema_name
ORDER BY pct DESC;

    schema_name     | pg_size_pretty |                pct
--------------------+----------------+------------------------------------
 production         | 605 GB         |            62.70818987165323895600
 dev                | 116 GB         |            12.05199834243206743500
 pg_toast           | 12 GB          |             1.26824870382580753200
 staging            | 12 GB          |             1.26031018275065892500
 test               | 1497 MB        |             0.15143744784303601600
 pg_catalog         | 26 MB          |         0.002621403693008641646300
 public             | 624 kB         |         0.000061661486144352849300
 information_schema | 96 kB          |         0.000009486382483746592200
 repack             | 0 bytes        | 0.00000000000000000000000000000000

this gives an idea that the occupied space should be around 750GB. However in reality PostgreSQL is using almost twice as much:

$ du -hs /var/lib/postgresql/9.6/main/base/
1.3T    /var/lib/postgresql/9.6/main/base/

Part of the problem is pgsql_tmp, that is occupying 349GB. Is there a safe way how to remove unused files from pgsql_tmp?

349G    /var/lib/postgresql/9.6/main/base/pgsql_tmp/

I've already tried VACUUM FULL and pg_repack on largest tables without any success. The only way how to get rid of wasted disk space seems to be dumping tables to SQL and reimporting into a clean server.


texts: How to create a form that allows user to enter a variable number of items

texts:
I am a total beginner to Microsoft Access 2010. I need a form that allows a user add items from as list to a list. The items can be added until initial list is exhausted.

Here's the most basic example I can give. Lets suppose that two persons attend a car show. As they are leaving, they are asked their name, and what cars they saw. First person saw only two cars. The second one saw fifty cars.

On the form, they would write their name one time (at the top of the page) and then they have to fill the list of cars they saw from the list of all cars. They would not write their name next to each car.

How do I create an equivalent MS Access 2010 form?


texts: Defining SQL Server domain/AD service accounts during installation vs. after the fact. Anything outside of "personal preference/time saving"?

texts:
Kind of a silly question, but, I'm installing multiple SQL Servers for a client, but they do not have a domain service account for SQL Server created for me yet. They will have one ready for me soon... Does it really make any difference defining the Service Accounts during installation vs. adding them after the fact via configuration manager?

Probably more of a personal preference/time saving question, but I was wondering if there was anything outside of that. I just prefer to have as much done during the installation process rather than going back and adding the domain account after the fact.


texts: Necessity of a DB-replication as extra security layer

texts:
I am confronted with a given infrastructure as following:
A "main" DB has a master-slave replication DB, which only contains certain tables.
A certain DB-user has read-only access to this replication.
A certain application currently uses this user to perform read-operations on the replication.
To my understanding this concept was introduced as security measurement. I am thinking that it might not be neccesary at all and a simple Read-Only user on the "main" DB would be enough.
I am not sure whether I am overlooking a crucial advantage of the current setup. The pros and cons I see:
Pro replica:

Extra layer of security?
If the application (publicly accesable) is attacked / DDoS´d, only the connected replica DB will be affected and the main DB can operate normally

Con replica:

Extra hardware ressources needed. These could be used to increase the main DB peformance
Currentness of data will always be slightly lagged (a few seconds)
Higher maintenance

I know this information might not be enough to properly evaluate the situation, but I feel like I am missing a crucial point. Currently I would like to remove the replication DB, setup Read-Only users on the main DB and increase its performance.
Any thoughts?


texts: Create a view in a user database that can access selected system database tables

texts:
Using the principle of least privilege, what access should I give to a Role "Checklist_role" that has read permission to a view "MaintenanceJobs_view" in a user database "ChecklistDB", that can access certain columns in a system table within the msdb database?
The following is an example similar to what I would like to do:
USE ChecklistDB;
GO
/* Create View */
CREATE VIEW [MaintenanceJobs_view]
AS
SELECT [name],
       [description],
       [enabled],
       [category_id]
FROM [msdb].[dbo].[sysjobs];
GO
/* Create Role based on principle of least privilege */
CREATE ROLE [CheckList_role];
GO
/* What permissions are required? */

Can this be done by granting certain privileges to the the role(Checklist_role) without giving privileges to any objects in the "msdb" database?
At this stage it is mainly to show a proof of concept in a very limited amount of time. In the long term I would definitely like to use a stored procedure.


texts: Can I skip the intermediate table in a join?

texts:
I've written this query :

SELECT
    "BD - Utilizadores".Utilizador,
    "BD - Utilizadores"."Palavra passe",
    "BD - Áreas".área
FROM "BD - Utilizadores"
INNER JOIN "BD - Permissões"
    on "BD - Utilizadores".id = "BD - Permissões"."user_id"
Join "BD - Áreas"
    on "BD - Permissões".area_id = "BD - Áreas".id

I want to join data from table "bd - utilizadores" with "bd - áreas". Since they have no direct link up I had to use a "man in the middle" i.e. "bd - Permissões". Here is the diagram:

My main question is, is there any other way to do this and get the same result?


texts: PL/pgsql: use cursor to update next record based on a condition from current record

texts:
I have this function in Postgresql 9.6 using pl/pgsql.
This function is to update a row from a table based on a condition on previous row.

The table has Item id(id), year of ownership (year) , quarter of ownership(quarter), own by ctc status(ownby_ctc), and
own by ftc status (ownby_ftc).

Each item id will be repeated one time each quarter every year from 2013 to 2016.
ownby_ctc will be marked 1 if item owned by CTC and it is either:
1- Happened before ownby_ftc or
2- Happened in the same quarter as ownby_ftc
and ownby_ctc never happened after ownby_ftc.

In case,  ownby_ctc happened before ownby_ftc

I want to select the row where ownby_ctc = 1 and ownby_ftc  = 0 and update the column ownby_ctc in next rows to be 1 until ownby_ftc is equal to 1.

Notice:
* the table is order by id, year, quarter.
*primary key is id_y_q.
Problem with my code is that the function is updating  one row if the condition is true and this is because  I am updating the actual table but not the cursor.

Please advice how to solve this problem.

CREATE OR REPLACE FUNCTION myfun()
   RETURNS VOID AS $$
DECLARE

cur SCROLL CURSOR
FOR select id_y_q, id, year, qrtr, ownby_ctc, ownby_ftc
FROM mytbl ORDER BY id, year, qrtr;
r1 RECORD;
r2 RECORD;

BEGIN

OPEN cur ;

LOOP

    FETCH cur INTO r1;
    EXIT WHEN NOT FOUND;

    IF (r1.ownby_ctc = 1 and r1.ownby_ftc =0 )THEN

        MOVE FORWARD 0 FROM cur;
        FETCH cur INTO r2;
        EXIT WHEN NOT FOUND;

        IF (r1.id = r2.id) THEN
            IF ( r1.ownby_ctc = 0 AND r1.ownby_ftc =0  )
               OR (r1.ownby_ctc = 0 and r1.ownby_ftc =1  ) THEN

                UPDATE mytbl
                SET r1.ownby_ctc = 0 = 1
                WHERE id_y_q = r2.id_y_q ;

                MOVE BACKWARD 1 FROM cur;

            END IF;
        END IF;

    END IF;

END LOOP;

CLOSE cur;

END; $$

LANGUAGE plpgsql;


texts: How can I assign group IDs depending on content?

texts:
I have the following table with data as shown below; linenrs are monotonically increasing but not necessarily consecutive; when the key field contains an ellipsis ... that indicates an entry that is continued from above:

create table source (
  linenr    integer unique not null,
  key       text not null,
  value     text );

insert into source values
  (  2, 'tags',  'a'          ),
  (  3, '...',   'b'          ),
  (  4, 'title', 'The Title'  ),
  (  5, 'note',  'this is'    ),
  (  6, '...',   'an EXAMPLE' ),
  (  8, 'title', 'over'       ),
  (  9, '...',   'three'      ),
  ( 10, '...',   'lines'      ),
  ( 11, 'about', 'grouping'   );

Now I want to have a view where a group number is assigned based on the content of the key field; group numbers do not have to be consecutive but should be distinct for each group of rows that starts with a key other than ... and continues thru all lines where key is ..., like this:

╔════════╤═══════╤═══════╤════════════╗
║ linenr │ group │  key  │   value    ║
╠════════╪═══════╪═══════╪════════════╣
║      2 │     1 │ tags  │ a          ║
║      3 │     1 │ ...   │ b          ║
║      4 │     2 │ title │ The Title  ║
║      5 │     3 │ note  │ this is    ║
║      6 │     3 │ ...   │ an EXAMPLE ║
║      8 │     4 │ title │ over       ║
║      9 │     4 │ ...   │ three      ║
║     10 │     4 │ ...   │ lines      ║
║     11 │     5 │ about │ grouping   ║
╚════════╧═══════╧═══════╧════════════╝

I tried to do this with windows / partitions and the tabibitosan pattern, but haven't been able to come up with anything working; also, using at the preceding row with lag() doesn't help where there are multiple consecutive lines with .... In a spreadsheet this is an extremely easy thing to do but in SQL I can seemingly not refer to the previous row of the current query, can I?


texts: Application testing: using multiple users in Oracle instead of mem H2. How to make it as fast as possible?

texts:
I decided to stop using H2 for obvious reasons (I have Oracle on production, compatibility mode is a fake). So, I wrote simple test framework which for each test in my application does the following:

Generate random username (in example below it's test_user).
Create new user and tablespace:

create tablespace test_user_ts
datafile 'test_user_tabspace.dat'
size 10M reuse
autoextend on next 500K;

create temporary tablespace test_user_ts_tmp
tempfile 'test_user.tabspace_temp.dat'
size 10M reuse
autoextend on next 500K;

create user test_user
identified by test_password
default tablespace test_user_ts
temporary tablespace test_user_ts_tmp;

grant create session to test_user;

grant all privileges to test_user;

Populate database with test data.
Run test.
Clean up:

drop user test_user cascade;
drop tablespace test_user_ts_tmp;
drop tablespace test_user_ts;

The problem is that stages 1-3 are slow. How can I make them as fast as possible? Is there maybe any way of copying existing db schema to another one?

Db version: Oracle 11g

I have full control over Oracle instance. It runs on a vagrant image on my dev machine.


texts: Big table with simple queries, but with a lot of text, is it bad for performance?

texts:
I am making cache table for website. It will have no problem with indexes (one unique index, simple quires), but it will have a lot of utf text. It will be 10-15gb (2 mln rows).
Caching will help a lot for saving cpu usage, without cache every page has many complex queries.

Is it ok two have such a big table? It will be queried only with "SELECT text WHERE id = $id" several times a second.


texts: SQL Server Timeout Expired in ASP.NET C# WebForms application

texts:
I have the following issue on my database which is used by a c# ASP.NET application:

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

I've tried sp_who and sp_who2 to check the cmd's visible there, which shows there is an average connectioncount of 10 users with AWAITING COMMAND and one CHECKPOINT.

The only way I can fix this database for several days is to take the application offline and take the database offline. Afterwards I can put the database back online and it will function without problems for a few days.

This is not a solid solution because the problem keeps coming back. But I'm out of thoughts what I can try.

What are the next steps for me to find the cause of this issue?


texts: How to delete SQL Server Database Mail history WITHOUT stored procedures

texts:
This is really straight forward. The documented way to delete Database mail history in SQL server is to use the following two stored procedures:sysmail_delete_mailitems_sp and sysmail_delete_log_sp

Great. But what happens when apparently billions of emails are stored in here taking up several hundred gigabytes in msdb, and even the process of trying to clear only 100 days worth of email takes hours or days?

I don't want any history and I don't want to wait until I'm a senior citizen to clear the list of e-mail history. How do I truncate this history in one quick transaction?

So far my efforts at using the built in procedures have been futile - growing tempdb and msdb to terabytes in size and sitting "executing" for hours on end. Any attempt to cancel it results in the entire transaction being rolled back. It so far hasn't even been possible to count the number of emails that exist in the history - it's that bad.


texts:
I have a strange situation where a mysql OPTIMIZE query will never end. After killinh it, it remains in the "killed" state.

Scenario to reproduce:

OS: CentOS 6.5
MySQL: 5.6
Engine: MyISAM (with at least one BLOB/LONGTEXT column)
mysql config 'myisam_repair_threads' = 2

I have a table with 40MB of data and 1500 records. With myisam_repair_threads set to 1, OPTIMIZE completes within a second. Settings myisam_repair_threads to 2 takes forever to complete. After killing the query, it remains in the 'killed' state (at moment of writing already 3 hours). Seems like this is a bug in mysql.

However, the big question: How can I force to kill this query? It currently holds a lock on the meta data of the table, so some processes can't execute because they are waiting for this lock.

Any advice is appreciated!


texts: created and deleted a database on mysql slave host by mistake

texts:
I have 2 hosts: server1 (master) and server2(slave).
By mistake I've created and dropped a database "test" on server2(slave).
If the cluster will switch the master to server2, will there be any issues?


texts: Should a database 'administrator' have the ability to deep dive into runtime query performance issues?

texts:
I acknowledge that everyone's experience and abilities are different. Having said that, I want to avoid setting expectations too high (or too low) for a DBA that through actions; appears to be an 'administrator'.
Given:

I am a developer that is deep diving into SQL Server trouble shooting
and performance issues. At night I'm watching Brent Ozar videos with
a bucket of popcorn.
A company with several divisions, this division having around 100ish team members
Many customers with databases having millions of rows with ETL
A small DBA team that is stretched to handle these same customers. Handle HAG issues, backup, restores, create new deployments for new or upgrading customers.

I am NOT attempting to justify my own opinion. I wish to adjust either my opinion or others in the company.
Question:
Given the above, should expectations be that a DBA is simply an 'administrator'? Is this something you've typically seen?

I went into this soap opera (see my other recent questions) with the expectation that a DBA is 'expected' to deep dive. I have come to believe that I have misunderstood and I am leaning towards a DBA - being an 'administrator'.
I welcome others' experiences and perhaps advice on refining this question.


texts: Howto transfer tracefile into a trace table without loosing the sort order?

texts:
I have traced some data into a trace table into Sql Server using the Profiler GUI. This table has the following structure:

It contains the SQL statements as well as execution plans in the TEXTDATA column. I figured out that usually the execution plan xml that belongs to a select statement has been placed into the row before the sql statement.

So for example the execution plan for the Select statement in row with [ROWNUMBER] = 60 usually can be found in the row with [ROWNUMBER] = 59.

This seems to be quite resilient (enough for my purpose) and I would like to keep this relation for later use. (So far I could not find another way of catching both, the query and the plan - ideally it should come as ONE merged result, for example the plan attached to the query inside of a comment... but I could not manage to create that.)

However, to avoid the massive overhead of using the Profiler GUI I created a trace script, stored the results into a trace file (because unfortunately using a script it's not possible to directly redirect the results into a table) and then imported the trace file into a table using the following statement:

SELECT * INTO mytracetable FROM ::fn_trace_gettable('c:\traces\myTraceFile.trc', default)

Unfortunately this produces a different table structure and worst about that is, that the ROWNUMBER identity column is now missing, so the records are messed up and it's impossible to find the plans that belong to the queries.

Q 1:
Is it possible to create a table from the trace file that has the rows stored in an order similar to the trace table profiler had created?

Q 2:
Does anyone know another better way to get those information without using the profiler GUI?


texts:
In a unittest we run this:

BEGIN;
TRUNCATE table1;
TRUNCATE table2;
...
UPDATE ...;
ROLLBACK;

Unfortunately this results in a dead lock if a cron job starts and this job operates on table1 and table2.

DatabaseError: deadlock detected
DETAIL:  Process 15815 waits for AccessExclusiveLock on relation 291262 of database 290999; blocked by process 16391.
Process 16391 waits for RowShareLock on relation 291431 of database 290999; blocked by process 15815.

Is there a way to isolate the unittest from the cron job (to avoid the dead lock)?

We run PostgreSQL 9.0.10 on x86_64 linux


texts: Postgres INSERT slows down over time until server is restarted

texts:
I have a Rails app backed by an Azure-managed Postgres database and I use NewRelic for monitoring. What I've noticed is that the INSERT performance degrades almost linearly over time - what started off as about 7ms became about 150ms average over a few weeks.

I then restarted the DB server and the INSERT was back to about 7ms.

What are some of the things that could cause this that I can look into? The table is pretty small at only 250k rows but it's at a pretty constant load which I expect to increase in future making these restarts impossible without impacting the customers.


texts: SQL Server Configuration File and InstanceName parameter

texts:
We have a configuration file at E:\SQL_Server_2016_Install\MyConfigFile.ini that looks like below:
[OPTIONS]
ACTION = "Install"
FEATURES = SQLENGINE
INSTANCENAME = MSSQLSERVER
AGTSVCACCOUNT = "MyDomain\MyAccount"
SQLSVCACCOUNT = "MyDomain\MyAccount"
QS
IACCEPTSQLSERVERLICENSETERMS

SQL Server is then installed using below batch file:
E:\SQL_Server_2016_Install\Setup.exe /ConfigurationFile=MyConfigFile.ini /InstanceName="TestSQLServer"
Question:
Will /InstanceName="TestSQLServer" parameter override what we have in the configuration file (/InstanceName = MSSQLSERVER (default instance)), and install SQL Server as a named instance TestSQLServer ?


texts: Installation SQL Server 2012 - “Ready to Install” page missing

texts:
I need to create config file, so I could install sql server from cmd. I need basic server installation so I've downloaded SQLEXPR32_x86_ENU

I need to go through whole installation process til page "Ready to Install", where should be link to config file I need. BUT, there is no such thing in my installation setup window.

On microsoft webpage there is: http://i.msdn.microsoft.com/dynimg/IC505108.png

Although, the screen from MS is sql-server 2008.. How to get config file from installation sql-server-2012?

Here's my screen:


texts: Move very large DB from single to multple drives with not a lot of space

texts:
I have to restore a SQL database that is just over 5 TB; it is going from a single drive that is  7 TB, to another server that has split drives C:\ 4 TB and D:\ 3 TB and also has E:\Bak which is only  0.5 TB

Can this be done Without adding space?

If i split the full bak into  parts can I copy and restore one at a time?

There are multiple data files and only one log


texts:
I have script for SQL plus, which I use to create user and get arguments for username and password.

I want the script to do more things when it runs on oracle 12c, but I'm trying to avoid writing different script for each DB version.

I have the following function to return version major number:

create or replace function get_version return integer AS
oracle_version integer;
begin
  select(substr(version,1,instr(version,'.')-1) into oracle_version
  from PRODUCT_COMPONENT_VERSION
  where product like 'Oracle%';
  return oracle_version;
end;

And I want to do something like this:

if get_version >= 12 then
  alter session set ...;
end if;
create user &1 identified by &2 ... ;
/
grant create session to &1;
grant create table to &1;

Any idea how to do it?

Thanks

EDIT

it works like this:

declare v integer;
begin
  set v:=get_version;
  if v>= 12 then
    execute immediate 'alter session ....'
  end if;
end;
end;
create user &1 identified by &2 ... ;


texts:
Question

What is the cause of Failed to create instance: Standalone DB Instances are not supported for Amazon RDS Aurora? Run the ansible script to create RDS Aurora but getting the error. If "aurora" is changed to "MySQL", no issue.

Ansible Script (Role)

- rds:
    region:          "{{ ec2_region }}"
    zone:             "{{ ec2_zone }}"
    command: create
    instance_name: new-database
    db_engine: "aurora"
    size: 20
    instance_type: db.t2.small
    username: admin
    password: 1nsecure

Error Message

fatal: [localhost]: FAILED! => {
    "changed": false,
    "failed": true,
    "invocation": {
        "module_args": {
            "apply_immediately": false,
            "aws_access_key": null,
            "aws_secret_key": null,
            "backup_retention": null,
            "backup_window": null,
            "character_set_name": null,
            "command": "create",
            "db_engine": "aurora",
            "db_name": null,
            "ec2_url": null,
            "engine_version": null,
            "force_failover": false,
            "instance_name": "new-database",
            "instance_type": "db.t2.small",
            "iops": null,
            "license_model": null,
            "maint_window": null,
            "multi_zone": null,
            "new_instance_name": null,
            "option_group": null,
            "parameter_group": null,
            "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "port": 3306,
            "profile": null,
            "publicly_accessible": null,
            "region": "ap-southeast-2",
            "security_groups": null,
            "security_token": null,
            "size": "20",
            "snapshot": null,
            "source_instance": null,
            "subnet": null,
            "tags": null,
            "upgrade": false,
            "username": "mysql_admin",
            "validate_certs": true,
            "vpc_security_groups": null,
            "wait": false,
            "wait_timeout": 300,
            "zone": "ap-southeast-2a"
        }
    },
    "msg": "Failed to create instance: Standalone DB Instances are not supported for the specified engine"
}

Research

Add RDS support for dbengine Aurora #3180 tells below but not sure how to add the cluster in RDS Ansible.

  fatal: [localhost]: FAILED! => {"changed": false, "failed": true, "msg": "Failed to create instance: Standalone DB Instances are not supported for the specified engine"}

  It's because you need to have an Aurora cluster created before you try and create the Aurora instance and you must pass the cluster name as one of the arguments when calling the create_db_instance API action.


texts: How do I join 2 columns in query result (SQL report builder)

texts:
As the image below shows, on my DB I also have 2 columns that appear in the results pane separately - first name and last name - (lower part of image)

How do I join them together in the report builder?


texts: How do I update azure SQL baseline to remove certain db owners?

texts:
I need to update the baseline to reflect only the approved list of owners and remove others.
Can anyone tell me how do I do that?
Here is what I see:

How do I remove the owners from baseline marked in red?


texts: mysql order by in view is discarded when outer query has where clause

texts:
I have a view v1 with an ORDER BY clause that is ignored when a:

SELECT * FROM v1 WHERE <clause>

is run on that view; but if that statement loses the WHERE clause, then the ordering is preserved.

I searched the documentation but I found no reference except this (from here):

  If ORDER BY occurs within a subquery and also is applied in the outer query, the outermost ORDER BY takes precedence.

I tried specifying all the available ALGORITHM options, but I get the same result.

Is there something I am missing, or is it expected that inner ORDER BY are discarded?

UPDATE

It seems that's not an issue with the view, since I get the same behaviour if, given:

s1 := SELECT ... FROM ... ORDER BY ...
CREATE VIEW v1 AS <s1>

I run the query:

SELECT * FROM ( <s1> ) WHERE ...

The same "reordering" appears. Though I'm not sure, this seems related to this bug.


texts:
I am receiving errors when I run the following query:

SELECT (
        SELECT 1
        FROM dbo.AccountEntry AE
        WHERE AE.Account = P.Account
        )
FROM dbo.Proposal P
WHERE P.id = @Id

When run via ADO.NET it gives:

  Warning: Fatal error 3624 occurred at Jun 20 2016 2:28AM. Note the
  error and time, and contact your system administrator. A severe error
  occurred on the current command. The results, if any, should be
  discarded.

When run in SSMS it gives

  Location:  statutil.cpp:4128
  Expression:    iKey < m_statBlob.GetHeader()->GetKeyCount()
  SPID:      59 Process ID:  1732
  Msg
  3624, Level 20, State 1, Line 1
  A system assertion check has failed.
  Check the SQL Server error log for details. Typically, an assertion
  failure is caused by a software bug or data corruption. To check for
  database corruption, consider running DBCC CHECKDB. If you agreed to
  send dumps to Microsoft during setup, a mini dump will be sent to
  Microsoft. An update might be available from Microsoft in the latest
  Service Pack or in a QFE from Technical Support.
  Msg 0, Level 20,
  State 0, Line 0
  A severe error occurred on the current command.  The
  results, if any, should be discarded.

Interestingly this code works when setting @Account to P.Account:

select 1 from dbo.AccountEntry AE where AE.Account = @Account

I have tried restarting the server.

I have tried dropping all indexes and statistics.

I have run DBCC CHECKDB and it returns no errors.

We don't believe anything has been changed about the server since Friday when it was running happily.

SQL Version: Microsoft SQL Server 2012 - 11.0.5343.0 (X64)   May  4 2015 19:11:32   Copyright (c) Microsoft Corporation  Standard Edition (64-bit) on Windows NT 6.2  (Build 9200: ) (Hypervisor)


texts: SQL Server - Update with Output not working in Parallel environment

texts:
I know that the output parameter does not guarantees parallelism logic.
And the logic I want to implement is failing in a multi thread environment.

To test this, just open 2 windows and execute them side by side as fast as you can. Then compare the Ids, some will be duplicated.

declare @id int
select @id = 1
while @id >=1 and @id <= 100
begin
    UPDATE sys_QueueJob
    SET StartedOn = GETDATE()
    OUTPUT DELETED.Id as Result
    WHERE Id = (select top 1 qj.Id
                    from sys_QueueJob qj
                    where qj.ProcessedOn is null AND qj.StartedOn is null
                    order by CreatedOn asc)
    print 'Count: ' + convert(nvarchar(max), @id)
    select @id = @id + 1
end

I just want to get the oldest entry from the qeueJob table that has not yet being started and at the same time tell that now it has. And the same code needs to provide me 100% certainty that the next execution will give me the next correct Id.

How can I do this?


texts: Update values in one table from data in another

texts:
I have an application using SQLite with two tables a master table and a scheduled table.

I would like to update a value in the master table with the value in the scheduled table if I run a query some time after due has occurred. Once the master table is updated, I would like to delete those values from the scheduled table.

Imagine this is the starting state:

Table: master
   id    value
   ---   -----
   1     "Buy"
   2     "Buy"

Table: scheduled
   key   id   value   due
   ----  ---  ------  -----
   1     1    "Sell"  2018-01-01 12:01:00
   2     2    "Sell"  2018-01-01 12:01:00
   3     1    "Buy"   2019-02-02 13:00:00

I'm trying to craft a query where if I run it after 2018-01-01 12:01:00, I would like to see:

Table: master
   id    value
   ---   -----
   1     "Sell"
   2     "Sell"

Table: scheduled
   key   id   value   due
   ----  ---  ------  -----
   3     1    "Buy"   2019-02-02 13:00:00

I'm new the SQLite, the closest I have gotten is the following:

UPDATE master
  SET
    value = (SELECT scheduled.value FROM scheduled WHERE scheduled.id = master.id)
  WHERE
    EXISTS (SELECT * FROM scheduled WHERE scheduled.due >= ?);

Unfortunately this doesn't seem to work, and I'm not even sure where to start on the DELETE.


texts:
I have 2 nodes in my db2 catalog. How would I include the node on which I want to connect inside the db2 connect paramters?

 Node 1 entry:

 Node name                      = DB2HOST1
 Comment                        =
 Directory entry type           = LOCAL
 Protocol                       = TCPIP
 Hostname                       = host1.sample.com
 Service name                   = 60000

 Node 2 entry:

 Node name                      = DB2HOST2
 Comment                        =
 Directory entry type           = LOCAL
 Protocol                       = TCPIP
 Hostname                       = host2.sample.com
 Service name                   = 60000

This is my query:

    db2 connect to DBASE user user1 using password1 <-- works
    db2 connect to DBASE user user1 using password1 node db2host1 <-- doesn't work


texts: Use session variable as reference to session user on PostgreSQL session

texts:
I would like to build an authorization system to handle role-based permission within the database itself.

Basically, I want my application user to have different access. The problem of basing this on Postgresql users is that if my application has millions of users, then I must have million of Postgresql users with potentially different connection parameters every time, making unable to use any kind of connection pooling (stop me if I am wrong).

So instead I thought I should base this on roles, but since roles don't have much effect on row security (which is my primary purpose here), I can just go on and create a session variable containing the user identification upon querying. Meaning if I want to do a select on some table. My application (actually a REST API) would do the following:

SET SESSION app.user = 'my user'
SELECT * from table

It would add the SET SESSION for every request to the database. If table has the proper row security, this would get the rows from table that belongs to 'my user'

I want to know if this solution makes sense and if I understood the concept of SQL session properly, any big performance impact to expect?

Edit:
The question How to implement business logic permissions in PostgreSQL (or SQL in general)? describes the details implementation on how to go about implementing a full authorization system. I want to know if I can use a session variable, to identify my user and reuse it somewhere later in my query, instead of just using role to identify a specific user. I changed the title to make this clearer.


texts: SSRS Linked Report with allow multiple parameters

texts:
I'm trying to create a linked report in SSRS to a report that has an "allow multiple values" parameter called CompanyCode.

If i call it with a single parameter it works fine, but I can't figure out how to call it with multiple.  Here's what I've tried:

CompanyCode1,CompanyCode2

and

CompanyCode1

CompanyCode2

and

'CompanyCode1','CompanyCode2'


texts: Good tool for simulating many threads doing INSERTS UPDATES SELECTS and DELETES on an ODBC database?

texts:
I've done load testing on a web application already using tools like JMeter, and tested my database for transactional integrity.

But are there any tools for testing an ODBC connected database (like MS-Access) using just the database?

I'd like to be able to simulate a real world situation of multiple connections reading and writing to and from the database.  I want to use this tool to prove something will work before I put it into production.


texts: What happens to a SQL Server 2019 Standard instance with an Annual Subscription Licence when it expires?

texts:
We have 3 production SQL Server 2019 standard instances which we have licenced through a third party supplier on an annual subscription basis. In our use case this works out cheaper if you only require the licences for <3 years.
We've just been informed we need to re-up the licence for the 2nd year within the next 5 days, there's a chance the invoice wont be processed and approved our end until next week, I'm just doing my due diligence on what risk this poses to our production environment.
From what I can tell, previously (at least on 2008R2), the instance validated the licence key locally so if this licence type existed back then, even if it did expire, SQL would continue to function as normal. My concern is that Microsoft have implemented some licence protection features since then to support this licence type, which could stop the instance from starting/working, similar to how an expired evaluation licence instance would stop working when the service is restarted.
There's very little information on this licence type online, its mentioned on Microsoft SQL Server's licencing/pricing page but the detailed documents linked there don't cover it at all.
SQL 2019 Licencing & Pricing
Does anyone have any experience with this licencing method/internals of sql licencing and could shed some light on how the instance will behave once it hits its expiration date?


texts: Stored procedure not displaying data when null is passed into input parameter

texts:
I recently started learning stored procedures in MySQL and created a procedure like this:
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_getAllDetails`(IN _student_no INT,
IN _class_id INT, IN _subject_name VARCHAR(50) )
BEGIN
IF _student_no IS NOT NULL AND _class_id IS NULL  AND _subject_name IS NULL THEN
        SELECT DISTINCT S.Student_No, S.Student_Name, C.Class_id, S.Class,
        C.Class_desc, M.Eng_marks, M.Maths_marks, M.Science_marks FROM Student S
        INNER JOIN Marks M ON S.Student_No = M.Student_No
        INNER JOIN Class C ON M.Class_id = C.Class_id
        WHERE S.Student_No  = _student_no;

        ELSE IF _class_id IS NOT NULL AND _student_no IS NULL AND _subject_name IS NULL THEN
        SELECT DISTINCT S.Student_Name, C.Class_id, S.Class, C.Class_desc,
        M.Eng_marks, M.Maths_marks, M.Science_marks FROM Student S
        INNER JOIN Marks M ON S.Student_No = M.Student_No
        INNER JOIN Class C ON M.Class_id = C.Class_id
        WHERE  C.Class_id = _class_id;

        ELSE IF _subject_name IS NOT NULL AND _class_id IS NULL AND _student_no IS NULL THEN
        SELECT DISTINCT S.Student_Name, S2.Student_No, S2.Class_id, S1.Subject_Name,
        S1.Subject_id, S3.Eng_marks, S3.Maths_marks, S3.Science_marks
        FROM Subject1 S1
        INNER JOIN Subject2 S2 ON S1.Subject_id = S2.Subject_id
        INNER JOIN Subject3 S3  ON S3.Student_No = S2.Student_No
        INNER JOIN Student S ON S.Student_Name = S2.Student_Name
        WHERE S1.Subject_Name  = _subject_name;
    END IF;
    END IF;
    END IF;
END

It is displaying the desired output only when I call the procedure like this in the workbench:
call school.sp_getAllDetails(401, NULL, NULL);

However, if I enter the input parameters in the dialog box from the shortcut in the left-hand menu,
it is taking the same as:
call school.sp_getAllDetails(401, NULL, 'NULL');

This is not displaying any data as it is taking NULL as a string. Can I know where am I going wrong? It will be a great help.
Many Thanks
Edit:
Leaving the question here because anyone who's looking for the same may get some help here. Also, if anyone has a different answer can add to this.


texts:
I have a table which has just started giving me the following error when trying to set a primary key, create an index or take a backup:

ERROR: MultiXactId 1092514672 has not been created yet -- apparent wraparound
SQL state: XX000

After a bit of searching online, I've not found a clear solution to the issue. There is some indication it might be a bug in older versions of Postgres.

I tried taking a backup of the table using pg_dump, as one option might be to just restore a fresh version, but got the same error.

Another possible solution I came across was to run select * from table for update;, but this also does not work with the same error given.

Running Postgres 9.4.3 on Debian 8.1. (Postgres has not been updated recently, thought I'd mention this as for some people this seems to cause the appearance of the error.)

Has anyone come across this before? Any ideas for a solution?

Update:
As suggested in the answer below from Erwin, I tried VACUUM FREEZE tbl;, but this also returned the same error.

Update 2: I have updated to Postgres version 9.4.8. This has had no effect, the error remains.


texts: SQL Server 2008 Replication - Fixed Overhead of Replication?

texts:
We are using SQL 2008 Transactional replication to replicate data from our production database to our reporting database.

We recently created a new 'Global' database that we would like to have replicated to our various production databases around the world. The number of tables in this database is small (less than 30) and the maximum number of rows in any given table might be a couple thousand. We would not be adding tables and data to this Global database very often.

Our DBA said that the "fixed overhead of replication is disproportional to the amount of data that would be actually replicated". I know a little about transactional replication but not enough to refute this argument. Is there really a great deal of 'fixed overhead' with replication, such that if we're not replicating much data, there is still significant resource usage?


texts: Specific disadvantages to storing infrequently accessed large files in a large database

texts:
I have heard many times across the web that it is, for various reasons, a bad idea to store files directly in a database, the preferred method being to store filenames in the database, and files directly on the filesystem. Most explanations, however, seem to assume that the stored files (in my case 2MB longtext object containing XML) are important to retrieve along with the query. I've seen a few comments in passing, however, that suggest that by storing these files directly in your database you are bloating it and thereby "interfere with IO or query performance of the rest of your database."

Under what circumstances does this happen, and why?

It is my understanding that blob and longtext over a certain size were stored externally anyways. Does the database still attempt to load up the entire longtext of an entry when it's respective row is used in operations? My main concern here comes from the fact that most of the queries of the table containing these rows of longtext do not actually need the longtext xml for anything. Does simply having these cumulatively massive chunks of data sitting in the same table really affect the performance of other queries against that table?


texts: How to omit multiple columns where is not null?

texts:
I've constructed the below query which satisfies the requirements, except output includes NULL values between all "vendor_address2" columns, which isn't intended.  I suspect my error is somewhere within the WHERE clause, which is designed to exclude any NULL values in the resulting output.  I've included a copy of the corresponding table below the output for my most recent query:

SELECT vendor_name,
       vendor_address1 || ' ' || vendor_address2 || ' ' || vendor_city || ', ' || vendor_state || ' ' || vendor_zip_code AS "Complete Address"
FROM ap.vendors
WHERE (vendor_address1 || vendor_address2 || vendor_city || vendor_state || vendor_zip_code) IS NOT NULL
ORDER BY vendor_name;

The resulting output:

Here is the corresponding table-view:

Thank you.


texts:
I have created one SSRS text report. I have built and deployed it successfully. I am also able to view it from the report manager succesfully. I am a local admin on windows server but not on SQL Server.

Issue :-

I am trying to schedule a report to run on a daily basis. When I go to Data sources to store my windows id credentials, it's not allowing me to do so throwing below error.

  The permissions granted to user 'app\abcid' are insufficient for performing this operation. (rsAccessDenied)

Things I have tried :-

Added my windows domain id to report manager and given all roles that are there still no success.
Added server to the trusted sites in the IE, still no success.


texts: Permissions on views/stored procedures and the referenced base tables in linked servers

texts:
I need help setting up security division between SELECTing and EXECUTEing from views and stored procedures and access to base tables referenced by those objects on linked servers on SQL Server 2012SP3. We have a number of production application databases in use, and use a centralized reporting server to query against the production databases for our reporting needs. Our reporting account should be able to select from and execute views and stored procedures, but not query the linked server base tables directly.

As I understand it, if you would have the view or SP on the same database as the base table, you would use ownership to give the view or SP the ability to query against a table while giving a reporting account priviliges to select from the view and not from the base table. This does not seem so straightforward with a linked server. I've tried a number of things to emulate this behaviour, such as giving the view owner (sysadmin role) a logging mapping for the linked server, but this results in the following error when selecting from a view:

  Msg 7416, Level 16, State 1, Procedure ProcedureName, Line 7 [Batch
  Start Line 0] Access to the remote server is denied because no
  login-mapping exists.

Other ideas I've had was to add a logging mapping for the reporting login that is mapped to a very limited account on the production databases, but wouldn't this block the functionality of the views and SPs as well? Since if I read it right the warning above indicates it's running the select statement in the security context of the reporting acocunt - which is confusing, since then blocking access to the dbo schema does not prohibit it form select from that linked servers tables. Mark me as confused.

I've also tried EXECUTE AS with the stored procedures, to force the use of the mapped sysadmin account. Using this results in the same warning as the one above. I've read that this might be related to a bug regarding user mapping, as described here: https://stackoverflow.com/questions/32084453/sql-linked-server-yields-error-no-login-mapping-exists-if-non-admin-account-is . However, the schema owner mapped in the linked server has the sysadmin role.

Any ideas on how to tackle this issue?


texts: Reason why SQL Max memory expansion seems slow when migrated to newer version of SQL server

texts:
We are trying to understand below behavior happening in SQL Server and need some guidance:-
After migrating from older version SQL2012 (running on bare metal P Server) to newer SQL2017 running inside VM with matching Hardware specs we are seeing this interesting behavior:
On SQL2012 Phy server:- For server with 700 GB RAM; MAX memory setting of 500GB, after any flush of cache/restart activity on sql instance , approx. in 20-30 mins we see SQL server quickly able to  reach that MAX memory setting and can be seen using 500 GB instantly
On SQL2017 server (VM): -For server with 700 GB RAM; MAX memory setting of 500GB, after any flush of cache/restart activity on SQL instance , it is taking around 4-5 hours yet we still see almost 50% of that max memory usage. This seems that memory allocation extension somehow is very slow on this server
Memory reservation on VM is set to 750 GB
Yes we see little slowness in app response and our top 2 waits are generally PAGEIOLATCH and WRITELOG
Please let me know what additional info i can provide to help me understand why allocation of MAX memory between 2 is vastly diff.
Edit:- Yes we have same load and database running as we did load test on both Physical and VM's. Above is one thing we are noticing. Database migrated to 2017 is still on compat 110 i.e. SQL2012


texts: Docker Mongodb replication on different hosts is not working

texts:
I am trying to setup a mongodb replication in Docker containers running on different hosts. Please find the steps below:

In a VM1 running ubuntu 14.04, installed docker, then created container C1.
Similarly created C2 on VM2.
On C1 & C2, modified the /etc/mongodb.conf to set bind_ip = 0.0.0.0, port=27017, replSet=rs0, fork=true.
Now on both containers, started mongo using the command

mongod --config /etc/mongodb.conf --replSet=rs0 --smallfiles &

(If I use service mongod start it says "unrecognized command".)
In C1, type mongo to start mongo session. rs.initiate() makes C1 as primary.
But when I try to do rs.add("VM instance name of VM2"), the VM2 is added to the replciation set. But in C2, the mongo doesnt change to secondary.

I have mapped the VM1 27017 port to 27017 port of C1. 27017 port of VM2 is mapped to 27017 of C2.

In /etc/hosts of VM1, C1, VM2, C2, the IP address of VM1, VM2 is added. Since the port 27017 of the container is already mapped to the VM, when we do rs.add("VM2"), the container mongod instance should have changed to secondary. But this doesn't happen.

Can you please help.

Regards, Sushma.


texts: How do we express a weak entity to be dependent on another weak entity?

texts:
Perhaps I am looking at my entities as a hierarchy rather than a set of relationships; for sake of an example I have three tables:

Authors
Books
Content

Where Authors writes Books and Books contain content.

The rules for these entities is as follows:

Content cannot stand alone without association to a Book
a Book cannot stand alone without association to an Author

See below the following partial implementation:

CREATE TABLE authors(
   author_id integer,
   author_name varchar,
   author_age int,

   PRIMARY KEY (author_id)
);

CREATE TABLE books(
   book_id integer,
   author_id integer,
   book_title varchar,
   book_published date,

   PRIMARY KEY (book_id, author_id),
   FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

From here I believe I have created a relationship where Books relies on Authors, should it decide to exist.

Arguably some may say that Content can simply just be part of Books, but say that Content has a hefty amount of columns in association to it and that a person would prefer to keep the more general Book data separate from its Content data for simplicity purposes.

In the event of the latter, how can I write a create statement that will make Content dependent on Books?


texts: Most cost efficient way to page through a poorly ordered table?

texts:
I have a table that has three columns:
HashUID1, HashUID2, Address_Name (which is a textual email address, and the previous two hash colunms are of some crazy creation to link event participant tables to email addresses. its ugly, it barely works its out of my control. Focus on the address_name index)

It has 78 million rows. Not properly sorted. Regardless, this index is split onto a lot of fast LUN's and performs REALLY fast index seeks.

I need to create a series of queries to extract only 20,000 "rows per page" at a time, but avoid conflicts or dupes. Since there is no identity column, or easily ordered column, is there an easy way to select all, and page through it?

Am I correct in saying that if I do a select * from hugetablewithemails into a temp table, then select through it by row_number that the table remains in memory for the duration of the transaction, which, to me, is an excessive amount of memory resources?
This seems the preferred method of paging. I'd rather page by statistical percentages. :(

There is one index which maintains the address_name email address in order, and is well maintained. For the past week I have been meaning to help this other developer by spending some time on looking into building a proc that spits out ranges based on windowing functions based on statistics (which I am not great at, but this query really interested me) to provide a range of characters 1 through (variable) LEFT LIKE chars of the index, that meets 20,000 rows--But I have not had time to even start the query...

Couple questions:

Any suggestions? Not looking for actual code, just some hints or suggestions based on experiences, maybe caveats. I want to avoid additional index scans after the initial scan.
Is this the right approach?
I'm thinking of breaking the sum of the index of all email addresses, gathering rowcount(*), /20,000, and usinng that as a windowing function to group min/max substring(1,5) values based on percentages of total rowcount to build grouping ranges. Thoughts?

This is for an ETL process that cannot modify source databases.

I am hoping with one full index scan I can do a:

Query to get a histograph based on index usage (alphabetically sorted) and break it out (windowed) using min/max to create some ranges like this, so to easily seek the needed index:
A-> AAAX, (20k rows for example) AAA-Z, B-> (another 20k), B->BAAR -> BAAR-> CDEFG -> CDEFH > FAAH, etc.

We run read committed in these databases for this ETL process. We are only attempting to batch it out in scores of 20k rows because the DBA's say we are using too much network resources by grabbing tables in full. If the data has changed (which is a concern) we update our DW and staging tables on the fly.

I would love to use temp tables, but if I did, I'd spill into tempdb and get lashings via e-mail from the DBAs regarding it, and that the database is too big.


texts: Update datatype of primary key without deleting the table

texts:
I have created a table State which my other table, Addresses, references.

CREATE TABLE State
(
     StateId varchar(20) PRIMARY KEY NOT NULL,
     Text varchar(40) NOT NULL
)

I want to change its primary key to int and IDENTITY(1,1), however, I cannot delete it because it is a foreign key for the other table. How can I update it without having to recreate everything?


texts: How do you use psql client to connect to a postgresql ipv6 host?

texts:
# postgresql.conf
listen_addresses='::'

and

# pg_hba.conf
hostssl  webdb  webserver   ::0/0    cert

The postgresql server is running on docker with pingable ipv6 address of "GlobalIPv6Address": "fe80::242:ac12:2" - so no firewalls obstructing.

I am using the following command to connect

psql --command="select * from test;" -d webdb -h fe80::242:ac12:2 -p 5432 -U postgres
psql: could not connect to server: Invalid argument
    Is the server running on host "fe80::242:ac12:2" and accepting
    TCP/IP connections on port 5432?

Why is the host not recognized? Is it not possible to use ipv6 with psql? Also, I did not find an ssl parameter option in psql.


texts: Is there a db2 function/procedure/variable that can be used to determine what platform a data source origins from? sysibm.sysdummy1 exists, so I assume that this is Db2, but some of the SQL does not look right according to LUW syntax. Is there an easy way to figure out via SQL what platform a data source origins from?


texts: is there a term for databases which are not system databases?

texts: Is there a term for all databases in SQL Server that are not system databases (such as master, model, tempdb)?


texts: How can i update my database from another database using temporary tables?

texts:
I want to update tables of my database using another big database every day with using temporary tables and without returning result. How can i update it?

This my temporary table :

use data_pro;
DROP TABLE IF EXISTS datasll.table1;
CREATE TEMPORARY TABLE IF NOT EXISTS datasll.table1
SELECT
 cs.id,
 cs.fk_c,
 cs.so,
 cs.pr,
 cs.date,
 cs.o_p,
 cs.sh_c,
 cs.x,
 LEFT(cs.x,2),
 cs.doc,
 cs.fk_op,
 cc.id_c_c,
 cc.name,
 cc.descrip,
 left(cc.n_o,3) as bon,
 cc.fk_c_br,
 cc.de,
 cc.m_mat,
 cc.color,
 cc.fk_c_s
 FROM
   data_pro.catas cs

 LEFT OUTER JOIN
data_pro.c_co cc ON  data_pro.cs.fk_c_c = data_pro.cc._id_cc
WHERE
data_pro.cc.pap = 1 AND   data_pro.cc.stati = 'b';

Many thanks for any help.


texts: How to set malloc-lib in mysql 5.7 in ubuntu 18.04 LTS

texts:
I recently installed jemmaloc 3.6.0-11 using apt-get install libjemalloc1 on my ubuntu server 18.04

The installation were successfull, but when I check using

pt-mysql-summary | grep -A5 -i "memory management"

I get

    Memory management library
    jemalloc is not enabled in mysql config
for process with id 89539
    The End

How I can do this ?

I am using Mysql 5.7.26


texts: How do you create a job in SQL server 2008 R2 that will clear transaction logs?

texts:
I have created a maintenance plan in SQL Server 2008 to delete transaction logs backups older than 3 days. Every day I look in the folder designated and the logs are still there. The job states it has run but nothing is removed. Job schedule is recurring with a daily frequency and the status is enabled.

If I copy the Transact-SQL and run it manually, it appears to clear space on the drive even though I don't see the logs removed up to the last 3 days. The transaction logs are saved to a folder on the target drive not the root itself.

Database recovery model is full and this is required due to the nature of the application that run against the SQL Server box. I have tried removing the maintenance plan and recreating it but I'm getting the same results (nothing). I also checked to see if the SQL Server agent isn't tied up on any job.

What am I doing wrong?


texts:
Here I have two tables in MySQL 5.6...

CREATE TABLE Zones (
  ZoneID int,
  ZoneName varchar(10),
  AccountID int,
  ProxyType int
);

CREATE TABLE ProxyServers (
  ProxyID int,
  ProxyType int,
  ProxyIP varchar(10),
  ProxyPort int,
  Enabled tinyint
);

My structure is a lot more complex than the one outlined about but I am trying to keep this as simple as possible to explain.

Basically I want to return 1 row of data consisting of
Zones.ID, Zones.ZoneName, and a ProxyID selected at random joined by ProxyType

SELECT * FROM Zones z
LEFT JOIN ProxyServers ps ON ps.ProxyID = (
      SELECT ProxyID FROM ProxyServers WHERE ProxyType = z.ProxyType ORDER BY RAND() LIMIT 1
    )
ORDER BY ZoneID DESC
LIMIT 1

This is simple enough to do in T-SQL, but can't figure it out in MySQL. Plus what is rather strange, even thought the data set does not change, the results are different ever time the query is run. Sometimes no rows, sometimes 4, sometimes even duplicate rows. I don't get it :/ I think it's the ORDER BY RAND() causing this because when I remove that it's fine.


texts: What is the algorithmic complexity of a PosgtreSQL greater than or lesser than index query (compared to equality)?

texts:
Assuming there is an index idx_int on an int_col column, what is the algorithmic complexity of a query like this?

SELECT id FROM table
    WHERE table.int_col > 1;

I'm specifically interested in knowing if the query is significantly more inefficient than if doing an equality clause (which, if I understand correctly is O(log N)).  I am pretty sure both of them can use a B-tree, so I would expect them to be about the same in terms of complexity / efficiency.

Thanks very much!


texts: MySQL: Procedure for mysql certification

texts:
I have keen interest in database and since start of my career I worked only in MySQL. But, I have not any professional certification related with MySQL. I have below concerns for certification:

Any minimum eligibility like education or experience
What are the similarities and differences between MySQL certification and MySQL DBA certification?
What is the procedure for registration and appearing in the exam?
What will be the total cost in India?
How much beneficial it would be in my career?
Recommend few good tutorials or documentations or Books for this certification?

Your suggestion would be highly appreciated...


texts: MySQL Installation: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock'

texts:
Tried to install MySQL on Ubuntu, with these 3 commands:
sudo apt update
sudo apt install mysql-server
mysql_secure_installation

When running the last command, it asks for the password for the root account. After I enter it, I got the following error:
Securing the MySQL server deployment.

Enter password for user root:
Error: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

I have checked and apparently there is no mysqld.sock file anywhere.
Is there anything else I should run that I haven't yet?


texts: SSIS best practice to allow easily swappable/ secure/ accessible Database Connections?

texts:
I come from Pentaho PDI.

There, I learned to have scripts load data to a [VariableDB].

That way, I can either initiate that VariableDB at runtime to "ProdDB" or even set the variable to different DBs within the script, temporarily, whatever.

Point is, I don't have to go through tons of files changing database names too often unless some major architecture changes.

Second thing is security. Pentaho's isn't great but you can at least store some of the database credentials is encrypted format, and they are blocked out in GUIs. A 'mastermind' could crack it but they would need a lot of access anyway.

SSIS --- well, it might be the opposite, maybe not. I'd prefer not to store any database passwords in plain text. At the same time, my predecessor left --- and I have no idea where the credentials are. They're "poof" gone -- I don't know. If SSIS scripts and jobs are running on the same SQL DB server, are they stored somewhere? I'm not sure.

Are they encrypted to his username, and now that he's long gone, unrecoverable?

This is not so important, since I can re-enter database credentials, but I'm looking to the future.

I know there are probably a few ways of doing this, but what's a simple way so that if I have 5 packages pointing to "TheFinanceDatabase" I can easily swap out the database host server, name, user, password, etc?

I take it config files and/ or connection managers --- they can be shared between packages within the same "project?". Maybe the "projects" can share connection credentials / pointers within the same "solution"? Just wondering here -- thanks!

I want to avoid re-entering/ losing credentials in the future.


texts:
I have a two node RAC database running in OEL 7 server. If there is a need to change the IP addresses of the already running server to a new one, will that hamper the functioning of the database?

What changes on the database front would I need to incorporate to implement this?

Is it even possible in the first place to run the database after changing the IPs?


texts: Behavior of transactions on multiple connections

texts:
I have a MariaDB database with InnoDB tables.  I have two connections to the same database.  One connection is using a transaction around a large chunk of work.  The other connection is tasked with updating a table in the same database (but outside the scope of the transaction's work) to track the progress of the transaction.

All of this is happening in the same script, but again, with separate connections: one for the actual job being performed (that includes a transaction), and another for keeping meta info on the progress of the job.

I expected that this would work such that the job tracking record would update as the job progressed - 10%, 20%, 50%, etc because the transaction work is being done on a separate connection and impacting other tables.  However, in my testing, the job tracking record doesn't get updated until the transaction for the job is committed.

As far as I can tell, it doesn't deadlock because the "progress monitoring" connection isn't using a transaction at all.  The "worker" connection wraps its queries in a transaction, but the other connection doesn't.

I check progress by just looking at a 'percent complete' field I fill in as the work progresses. Order of operations is:

Connection 1: start transaction
Connection 1: add and update rows
Connection 2: update progress
Connection 1: add and update more rows.

Then, in another HTTP connection I'm polling and outputting the progress value, but it never updates until the transaction on connection 1 is committed.

Is there any way to get the behavior I want, outside of moving the job-tracking table into an entirely different database?


texts: How msdb.dbo.backupset works ( backup_size )

texts:
I Made this simple query to list the Backup_size:

select database_name, backup_finish_date, backup_size
from msdb.dbo.backupset
where day(backup_finish_date) = 26
and year(backup_finish_date) = 2017
and month(backup_finish_date) = 10

OK. I made a database backup, and in my select, I got this result:

  My_database | 2017-10-26 16:59:44.000 | 8509270016

As Seen Here ( Microsoft's website ), this field is a byte field. Ok. Converting 8509270016 bytes to KB I have: 8,309,834 Kilobytes.

but the database size inside the folder, has:


texts: How to link disaggregate data to aggregate data when aggregation adds 0/NULL values?

texts:
I have a table sensor_data in a PostgreSQL 9.6 database:

    Column     |            Type             |
---------------+-----------------------------+
 id            | bigint                      | bigserial
 station_id    | integer                     | not null
 datetime_bin  | timestamp without time zone | not null (one minute resolution)
 category_a    | integer                     | not null
 category_b    | integer                     | not null
 sensor_data   | integer                     | not null

Indexes:

"station_logs_pkey" PRIMARY KEY, btree (id),
UNIQUE CONSTRAINT (station_id, datetime_bin, category_a, category_b),
btree (datetime_bin)
btree (sensor_id)

If the value for a combination of station_id, datetime_bin, category_a, category_b is 0, there is no row. We're aggregating this data to 15 minute bins, and want to add in 0s to make averaging on the aggregated data more user-friendly. We also periodically need to refresh a month of data, so wanted to have deleting the disaggregate data cascade to aggregates based on it.

Our current approach is the following, but I'd be curious to know if there is a better/more proper way.

Current approach

Aggregate table

    Column     |            Type             |
---------------+-----------------------------+
 id            | bigint                      | bigserial
 station_id    | integer                     | not null
 datetime_bin  | timestamp without time zone | not null (fifteen minute resolution)
 category_a    | integer                     | not null
 category_b    | integer                     | not null
 summed_data   | integer                     | not null (with 0s)

Disaggregate table

    Column     |            Type             |
---------------+-----------------------------+
 id            | bigint                      | bigserial
 station_id    | integer                     | not null
 datetime_bin  | timestamp without time zone | not null (one minute resolution)
 category_a    | integer                     | not null
 category_b    | integer                     | not null
 sensor_data   | integer                     | not null (without 0s)
 aggregate_id  | bigint                      | FOREIGN KEY aggregate(id)

Zeroes crossover table

    Column     |            Type             |
---------------+-----------------------------+
 one_minute_id |bigint                       | FOREIGN KEY one_minute(id) ON DELETE CASCADE
 aggregate_id  |bigint                       | FOREIGN KEY aggregate(id)

Triggers

when deleting from disaggregate, delete from aggregate with same aggregate_id
when deleting from disaggregate, cascades to zeroes table with Foreign Key
when deleting from zeroes table, delete from aggregate with same aggregate_id.


texts: How to make SQL Server Agent job history less verbose?

texts:
So, I've created a SQL Server Agent job, according to the answer to my prev question:

running nightly (at 2:11) according to the schedule:

There are 9 .bak files in 3 subdirectory of d:\backup\ source folder, 3 old bakup files are being deleted and 3 new is being created by another preceding SQL Server Agent job.

The described here job copy-purges files but... what the heck the history of this Copy&Purge BAKs SQL ServerAgent job  shows 96 items:

How to make it less verbose?

Eventvwr.msc doesn't contain any eroors for the corresponding period of time (of job run).

UPDATE:

So, I've made these changes

and it seems it simply truncates a per-job log:


texts:
We have a situation here our application is working on SQL server on a cluster environment. Now we wish to migrate the database to a standalone environment, we have approx 2850 databases 1.6 TB in size.

The DBA has said it is not possible to migrate that many database as they need to Export/Import one database at a time. So 2850 databases will take hell lot of time.

Do you think is this the only way to perform migration in SQL server or there is more innovative way of performing this operation.


texts: Query to get yesterdays data only for a specific time interval

texts:
Here is my where condition which I use for daily data but I've got to change the date
everyday.

WHERE X.stage = 'Approval'
AND
X.T_ExitTime between '2015-08-06 06:00:00:000' and  '2015-08-06 17:00:00:000'
ORDER BY x.T_ExitTime asc

How can we make this to use the timeframe 0600-1700 for yesterday or for the same day without having to change it manually everyday and so that we can make this a Job and automate it

I'm using SQL Server 2008


texts: Slow query performance of simple SELECT query (Postgresql 9.6)

texts:
In a app called 'Links', users post links of interesting content they've discovered recently (and other vote on the said posts).

These posted links are saved in a links_link table in my postgresql 9.6.5 DB.

One innocuous looking SELECT query on the links_link table is consistently showing up in slow_log. It's taking longer than 500ms, and is ~10X slower than what I'm experiencing in most other postgresql operations.

Here's an example of the corresponding SQL from my slow log:

LOG:  duration: 8648.676 ms  statement:

SELECT "links_link"."id",
       "links_link"."description",
       "links_link"."submitted_on",
       "links_link"."reply_count",
       "links_link"."net_votes"
FROM   "links_link"
WHERE  "links_link"."submitter_id" = 811645
ORDER  BY "links_link"."id" DESC
LIMIT  1

See the the explain analyze results: https://explain.depesz.com/s/Xp5v

The query ends up filtering 14,331,127 rows according to that!

What I've tried:

My gut feel is that Postgres bases this query plan on misleading statistics. Thus, I've run VACUUM ANALYZE on the said table. However this hasn't changed anything.

Being an accidental DBA of sorts, I'm looking for some quick expert guidance on the subject. Thanks in advance and apologies for the noob question (if it is).

Edit:

Here's the entire output for \d links_link:

                                         Table "public.links_link"
        Column        |           Type           |                        Modifiers
----------------------+--------------------------+---------------------------------------------------------
 id                   | integer                  | not null default nextval('links_link_id_seq'::regclass)
 description          | text                     | not null
 submitter_id         | integer                  | not null
 submitted_on         | timestamp with time zone | not null
 rank_score           | double precision         | not null
 url                  | character varying(250)   | not null
 cagtegory            | character varying(25)    | not null
 image_file           | character varying(100)   |
 reply_count          | integer                  | default 0
 device               | character varying(10)    | default '1'::character varying
 latest_reply_id      | integer                  |
 which_photostream_id | integer                  |
 is_visible           | boolean                  | default true
 net_votes            | integer                  | default 0
Indexes:
    "links_link_pkey" PRIMARY KEY, btree (id)
    "links_link_latest_reply_id_idx" btree (latest_reply_id)
    "links_link_submitter_id" btree (submitter_id)
Foreign-key constraints:
    "link_whichphotostreamid_fkey" FOREIGN KEY (which_photostream_id) REFERENCES links_photostream(id) ON UPDATE CASCADE ON DELETE CASCADE
    "links_link_submitter_id_fkey" FOREIGN KEY (submitter_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
    "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
    TABLE "links_publicreply" CONSTRAINT "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_report" CONSTRAINT "links_report_which_link_id_fkey" FOREIGN KEY (which_link_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_vote" CONSTRAINT "links_vote_link_id_fkey" FOREIGN KEY (link_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_photoobjectsubscription" CONSTRAINT "which_link_id_photoobjectsubscription" FOREIGN KEY (which_link_id) REFERENCES links_link(id) ON DELETE CASCADE


texts: How do I group the data in the following format?

texts:
Using the following table

OrderDate     OrderID
[dates]       [id]

I want to display in the following format :

quater name   nooforders
q1            10
q2            10
q3            20

I have tried the following query :

select count(order_id)no_of_orders,
(case when month(order_date)<=4 then 'q1' when month(order_date)<=8 then 'q2' when month(order_date)<=12 then 'q3' end)quarter
from bab_orders
group by quarter;

However this query does not show the value of a particular quarter when it has no orders associated with it. How do I display that?


texts: How can I select data size group by id for every value in a table

texts:
I hope I can explain exactly what I need to get.
I have a Table that has a specific column of type MEDIUMBLOB.
What I need to do is to determine the size of every value inserted on this column grouped by idMsg and transform this value in MB.
DB Structure is:
CREATE TABLE `sat_msgBlob` (
    `idOwnApp` MEDIUMINT(11) NOT NULL DEFAULT '0',
    `idMsg` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
    `idVeh` MEDIUMINT(11) NULL DEFAULT NULL,
    `idTypeMsg` MEDIUMINT(11) NULL DEFAULT NULL,
    `idProviderComm` MEDIUMINT(11) NULL DEFAULT NULL,
    `idPayCode` MEDIUMINT(11) NULL DEFAULT NULL,
    `msgInOut` CHAR(1) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `idMsgSender` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `txMsg` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    `tmMsg` DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    `idControl` MEDIUMINT(11) NOT NULL DEFAULT '0',
    `txBlob` MEDIUMBLOB NULL DEFAULT NULL,
    `idUpdUser` MEDIUMINT(11) NULL DEFAULT NULL,
    `tmUpdate` DATETIME NULL DEFAULT NULL,
    `idMission` MEDIUMINT(11) NULL DEFAULT NULL,
    `idEvent` MEDIUMINT(11) NULL DEFAULT NULL,
    `tmPrcsLast` DATETIME NULL DEFAULT NULL,
    `idBlob` VARCHAR(250) NULL DEFAULT NULL COLLATE 'utf8_general_ci',
    PRIMARY KEY (`idMsg`, `tmMsg`) USING BTREE,
    INDEX `X2` (`idVeh`, `idOwnApp`, `tmMsg`) USING BTREE,
    INDEX `m1` (`idMission`, `idEvent`) USING BTREE,
    INDEX `X3` (`idControl`) USING BTREE,
    INDEX `X4` (`tmMsg`) USING BTREE,
    INDEX `X6` (`idBlob`) USING BTREE,
    INDEX `X7` (`tmUpdate`) USING BTREE
)
COLLATE='utf8_general_ci'
AUTO_INCREMENT=971935070
AVG_ROW_LENGTH=8450
MAX_ROWS=4294967295;

The data inserted in this txBlob column look like this
0x3C3F786D6C2076657273696F6E3D22312E302220656E636F64696E673D225554462D38223F3E0A3C21444F435459504520566D4D4C2053595354454D2022687474703A2F2F7777772E766961747261636B2E726F2F737065632F566D4D4C5F76302E312E6474

I had tried to use this query but I am not sure that the value is correct:
SELECT sys.format_bytes(LENGTH(txBlob)) AS size
FROM sat_msgBlob
WHERE tmMsg BETWEEN '2021-03-01' AND '2021-03-02'
GROUP BY idMsg


texts:
I have two tables

@T1 TABLE
(
    Id INT,
    Date DATETIME
)

@T2 TABLE
(
    Id INT,
    Date DATETIME
)

These tables have a non-clustered index on (Id, Date)

And I join these tables

SELECT *
FROM T1 AS t1
INNER JOIN T2 AS t2
ON
    t1.Id = t2.Id
WHERE
    t1.Date <= GETDATE()
    AND
    t2.Date <= GETDATE()

This can also be written as

SELECT *
FROM T1 AS t1
INNER JOIN T2 AS t2
ON
    t1.Id = t2.Id
    AND
    t1.Date <= GETDATE()
    AND
    t2.Date <= GETDATE()

My question is, which of these two queries gives the better performance and why? Or are they equal?


texts: I am trying to achieve a Disaster recovery for Mongo DB

texts:
Right now i have a MongoDb HA in site one and i need another site with asynchronous replication enables in another location.This is a base plan of mine to achieve a disaster recovery .
I dont have that much idea on how to establish async replication of mongo .
Also i need this to be done in windows system .


texts: Detect if only one column is changing in a trigger

texts:
I have an after update trigger that is queuing record ID's into another table for further processing at a later time. It uses inserted to get the ids of the records to process.

I'd now like to exclude certain records being queued to that table if the only change has been to one particular column. If any other columns beyond that one column are changed I still want to queue the record.

How can I identify if only one particular column has been changed?

I've had a look at UPDATE(), but it seems like I'd need to list out all the other 47 columns on the table, which I'd like to avoid.

COLUMNS_UPDATED as used in How to use COLUMNS_UPDATED to check if any of certain columns are updated? seemed promising, but I can't get it to work.
The column I want to exclude has the ColumnID 30. By my figuring that gives me an integer bitmask of 536,870,912 (SELECT POWER(2, 30 - 1)).

The calculator in Windows tells me that is correct to have the 30th bit set.

When I did an UPDATE to just the column I want to exclude COLUMNS_UPDATED() returned 0x000000200040. That to be gives a bit pattern that suggests two completely different columns were updated. Column Id's 7 (2^(7-1)=64) and 22 (2^(22-1)=2,097,152).

I'm not sure it it is worth pursing that approach. It seems like it would be an easy comparison to pickup any changes to other fields.

UPDATE:
I've resorted to using a CHECKSUM comparison other fields between inserted and deleted. I'd prefer not to use this approach as it could cause problems if other fields are added to the table in the future. There is also the chance that a hash collision could occur.


texts:
This is an odd question but is it possible to manipulate a Drizzle database through MySQL stored procedures? The reason I need to do this is that I'm migrating my MySQL database (which contains a lot of stored procedures) to Drizzle (which doesn't support stored procedures). This was essentially one of my 'grand' short term ideas for this.

Any ideas for doing this, or some other ones?


texts:
My environment will use 2-3 cluster nodes. The db schema at first was designed without knowing the limitation/rules with NDBCLUSTER on (BIG mistake! I know). Some things I notice and want to understand more:

(1) NDB Cluster normally partitions NDBCLUSTER tables automatically. So when I tried to check my table, it shows there are 2 partitions inside

mysql> explain partitions select * from my_request;
+----+-------------+------------+------------+------+
| id | select_type | table      | partitions | type |
+----+-------------+------------+------------+------+
|  1 | SIMPLE      | my_request | p0,p1      | ALL  |
+----+-------------+------------+------------+------+

but when I tried to check which record going to which partition, it gave me error. why?

mysql> select * from my_request partition (p0);
ERROR 1747 (HY000): PARTITION () clause on non partitioned table

(2) Some of my tables will have billions of records, and I (successfully) partitioned it BY RANGE into daily/weekly/monthly. However NDBCLUSTER only allow partition BY (LINEAR) KEY. Since the PRIMARY KEY is a UUID, I kind of lost how to partition the table.

Note: The partition is used not only for optimized query but also to dump and back-up (like dump the data older than 1y). I created an event and procedure to check from partition to do this. ty.


texts:
In a web system anyone, who gets access to the DB, can make changes in the account of any user (change posts, etc).
Is there any way to prevent this problem?
Hashing of the DB is not the way, as it's changed dynamically.
Increasing the security of the server is obvious advice, however there is no absolutely secure computer.


texts: Displaying parent attributes alongside child attributes


texts: suppress header in output of sybase query in Linux

texts:
Could someone tell me how to suppress header in the output of sybase query in Linux?

My output is like this:

col1 col2
---------
val11 val12
val21 val22

My required format is:

val11 val12
val21 val22

Thanks.


texts: Specify NLS-Settings during Oracle installation under Windows

texts:
Is there an easy way to specify NLS parameters for the database during the installation?

We want to install a new database (environment: Windows). For testing purpose we need NLS settings for nls_language and nls_territory that differ from the default values (German instead of American). To avoid the process of dropping an recreating the database with changed init.ora I would like to specify those setting during a fresh installation on a VM.

I found no settings in the setup dialogue. Is there any way to achieve this?

environment: Oracle Database Enterprise Edition, available versions 11.2 or 12.1.


texts: Is it possible to get server roles and database roles in a same query/script?

texts:
Is it possible to get all database roles and server roles in the same query/script? if so then how can it be achieved?

Initially, I wrote two separate queries, one for server roles and another one for database roles I joined the two queries with 'Union All' but the issue is it doesn't show any database Name which I need.

So I approached the problem from a new angle as the end goal is to get all the databases in the SQL server instance with the roles(e.g db_owner). This will be stored in a temp table from where I can export it to excel. So I wrote a script after doing some reading and online searching. In the following query, I am getting the database roles along with the respective database so that issue is resolved but I want server roles as well and I don't know how to get those using this query. If anyone can help me to modify the script in a way that it provides both the server roles and the database roles along with server and database name.

DECLARE @DatabaseName SYSNAME,
        @sql VARCHAR(1000)
DECLARE @ResultTable TABLE(DatabaseName VARCHAR(100), ServerName VARCHAR(100), Name VARCHAR(100), TypeDesc Varchar(100), DatabaseRole VARCHAR(100) )
DECLARE DatabaseCursor CURSOR
     FOR SELECT Name FROM sys.databases
OPEN databaseCursor
FETCH NEXT FROM databaseCursor INTO @DatabaseName
WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @sql ='SELECT ''' + @DatabaseName + ''' AS [Database],
                      ''' +@@SERVERNAME +  '''AS [Server Name],
                      logins.name Name,
                      logins.type_desc TypeDesc,
                      roles.name RoleName
               FROM '+@databaseName+'.sys.database_role_members rm
               INNER JOIN '+@databaseName +'.sys.database_principals roles ON rm.role_principal_id = roles.principal_id
               INNER JOIN '+@databaseName+'.sys.database_principals logins ON rm.member_principal_id = logins.principal_id'
    INSERT INTO @ResultTable EXEC(@sql)
    FETCH NEXT FROM databaseCursor INTO @databaseName
END
CLOSE databaseCursor
DEALLOCATE databaseCursor
SELECT * FROM @ResultTable;


texts: Maximum number of columns in PRIMARY KEY

texts:
What is the maximum number of columns that can be used in a multi-column PRIMARY KEY in MariaDB?

I know about MySQL, but I can't find the limitation in MariaDB's documentation.


texts:
I have a replicaset with 3 servers and I need to move two if them behind a gateway. The final setup would look something like this,

Server 1 (PRIMARY) ________ |  S    | _____________ | _ _ _ _ _ _ _ | 20000
192.168.1.1:27017           |  w    |               |               |                 |               |
        |                   |  i    |               | Gateway       |  _____________  | Server 3      |
        |                   |  t    |               | gw.some.org   |                 |s3.some.org:27017
Server 2            _______ |  c    | _____________ | _ _ _ _ _ _ _ | 20001
192.168.1.2:27017           |  h    |               |

So the addresses the server 3 (the one outside the gateway) are,

gw.some.org:20000
gw.some.org:20001
s3.some.org:27017

and the ones outside,

192.168.1.1:27017 (or gw.some.org:20000?)
192.168.1.2:27017 (or gw.some.org:20001?)
s3.some.org:27017

Is this possible?


texts: psql multiline commands: How to edit previous lines?

texts:
When writing a query across several lines in psql, sometimes I realize I've made a mistake in one of the lines previously written. In the following example,

select * form (
etc, etc...

there is a mistake in the first line, but I am already writing in the second line. How to return to and edit the first line (and change "form" to "from")?  I have been unable to do this.

Having to rewrite everything because of a single mistake is very inconvenient (specially for long multiline queries).


texts: Feedback on an entity-relationship diagram for a movie theater business domain

texts:
I have an assignment due next week, and for that assignment I am supposed to learn MySQL and create an entity-relationship diagram (ERD).

My current attempt

Now I've made this ERD

The issue

I went to my professor for feedback and he just told me that it is not a good ERD and refused to give me any further feedback.

I'm still a noob, but my question to you is: How do I improve this ERD? I was thinking about removing the Ticket entity type since that might be confusing...


texts:
I work with a few tables with given data. I run a query built in Design View just like
result:![table1].[qty]*![table2].[expenses]
and I get the output in query table in field result. Is it possible to write result back to table2.resultfield? So this way my query does not only produce output table but updates one of the source tables.


texts:
I am trying to do a SQL Server database restore using this T-SQL code:

----Make Database to single user Mode
ALTER DATABASE xyz
SET SINGLE_USER WITH
ROLLBACK IMMEDIATE

----Restore Database
RESTORE DATABASE xyz
FROM DISK = 'D:\\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\xyz_backup_201204100301.bak'
WITH MOVE 'abc' TO 'D:\\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\xyz.MDF',
MOVE 'abc_log' TO 'E:\\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\xyz.LDF'

but while running the above i get the following error:

  Msg 5064, Level 16, State 1, Line 1
  Changes to the state or options of database 'xyz' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
  Msg 5069, Level 16, State 1, Line 1
  ALTER DATABASE statement failed.

How can I run my restore successfully?
Is restarting the database the only way to solve the problem. Any other suggestions?!


texts:
I have a Postgres 9.5 server with 30 databases.
I would like to find out which database uses which extension(s) in one go, preferably in a query that is usable in pgAdmin.
I know that SELECT * FROM pg_extension gives me the extensions for the database I am connected to.
I also know that SELECT * FROM pg_available_extensions lists, well, all available extensions.
I also know that SELECT * FROM pg_database lists all existing databases.
Question
How can I create a list that includes the database name and the extensions used in each database?


texts:
I'm using ubuntu 12.04 LTS
Processor : Pentium(R) Dual-Core CPU E5200 @ 2.50GHz × 2
OS Type : 64-bit
Memory : 3.9 GiB
Disk : 101.1 GB

I installed mysql and wanted to test insert performance. Here is my table structure

 > create table mytable (text varchar(200)) engine=InnoDB;

   +-------+---------------+------+-----+---------+-------+
   | Field | Type          | Null | Key | Default | Extra |
   +-------+---------------+------+-----+---------+-------+
   | text  | varchar(2000) | YES  |     | NULL    |       |
   +-------+---------------+------+-----+---------+-------+

I wrote a simple python script for testing Insert performance.
i.e. How many inserts per second?

  import _mysql
  import time

  con = _mysql.connect('localhost', 'root', 'root', 'mydb')

  stop = time.time()+1

  while time.time() < stop:    # runs loop for 1 sec
      con.query("INSERT into mytable VALUES('Test Data')")

I expected atleast 8-10k inserts. But I'm getting only 25 inserts.

I tried many optimization techniques given in various blogs

1. Tried to run the loop in 1 transaction.
2. Tried LOAD DATA INFILE 'new.txt' INTO TABLE mytable

I also set innodb_flush_log_at_trx_commit=2 in /etc/mysql/my.cnf

But none of them worked, getting only 25-30 inserts per second. What to do?


texts: Slow running Oracle query caused by unnecessary full table scan

texts:
I'm by no means an expert with Oracle, but I know enough in terms of how to analyze explain plans and perform basic optimizations. However, this time, Oracle has stumped me with it's explain plan.

Basically, I've got a single table that is very large (27 million rows) with an index on ScheduleID. What I'm trying to do is get all the records that have a ScheduleID that match one of the ScheduleIDs in a given list (currently stored in a temp table). The temp table will probably have around 100 rows, although it could be up to 500 rows. And for each row in temp row, it will have up to around 10 matching records in the large table. So we are talking about the query returning about 5000 rows max (out of 27 million).

Now, for some reason, Oracle thinks that the best solution is to use a hash join and do a full table scan of the large table. You can see the query and its explain plan that Oracle generates below.

select scheduleintervalid, io_scheduleinterval.version
from io_scheduleinterval
inner join io_querytemp tmp on tmp.data = scheduleid

This doesn't seem to make any sense to me. It is not taking advantage of the index on ScheduleID, which I would think would make the query significantly faster. Now from my understanding, the hash join couldn't take advantage of the index, but wouldn't a nested loop join be much faster. Am I missing something here?

Is there something else I can do to speed up the query (I've tried things like using a dynamic IN clause)?

Thanks for the help!


texts:
I have a database that will be shared across multiple related types of applications for different users. I want to be able to restrict each database user to essentially have full control over their own objects, with only read access to the other objects of the database that they didn't create.

Is this possible and what's the best way to accomplish this?


texts: MDX: How do I combine/merge/union two Measures dimensions with calculated members?

texts:
I am trying to combine two dimensions in an MDX query.

SELECT
NON EMPTY
(
    [XXX].[YYY].children
) ON ROWS,
NON EMPTY
{
    [Measures].[Dimension1A],
    [Measures].[Dimension1B],
    [Measures].[Dimension2A],
    [Measures].[Dimension2B]
} ON COLUMNS
FROM [RePro]
WHERE
(
    [ABC].[ABC].[Val]
) CELL PROPERTIES VALUE

This produces results where all the [Measures] dimensions are in separate columns. How can I make it so that [Dimension1A] and [Dimension1B] are combined/merged into the same column, and likewise for [Dimension2A] and [Dimension2B]?

I've tried the solution here, but I get '#Error' in my results with the message "Aggregate functions cannot be used on calculated members in the Measures dimension". This is what I tried:

WITH SET [Combined] AS
{
    [Measures].[Dimension1A],
    [Measures].[Dimension1B]
}
MEMBER [Measures].[Dimension1] AS Aggregate([Combined])
SELECT
NON EMPTY
(
    [XXX].[YYY].children
) ON ROWS,
UNION
{
    Except([Measures], [Combined]),
    [Measures].[Dimension1]
} ON COLUMNS
FROM [RePro]
WHERE
(
    [ABC].[ABC].[Val]
) CELL PROPERTIES VALUE

I have removed [Dimenion2A] and [Dimension2B] for brevity. Any ideas?

Thanks :)


texts: Where/how does innodb store tables in files?

texts: I read somewhere that InnoDB can store all the tables in a single file or in separate files. Is this a configuration option? What is the default setting? A file for all tables or a file per table? What is the recommended setting?


texts: All in one query for tracking changes in sql server

texts:
Is there any feature in SQL Server 2012 or in any version, wherein I can track all DB activities (DDL and DML) and others in one single query with who has modified and from where and when? I need hostname or IP address as well.

I tried below features which has some limitations

SQL Audit (Unable to track IP)
Event (Unable to track DML operations)
Trace (Unable to track DML operations)

I don't want to use triggers.


texts:
We have MyISAM tables on MySQL 5.5 and there is this select * from theView where serial_number = 'QWE12345' or  select * from theView where user_id = 1234 kind of queries when searching. When I profile these queries I see that most of the time is spend on "sending data" aka on searching the row/rows or filtering the table. And also I have to say that the theview has few joins and the serial number and user id has their own indexes on the table that they are located.

So I know that using view is  bad idea but this is a 8 years old database/application design. And also I have this log for example:

# Query_time: 76.050350  Lock_time: 23.576348 Rows_sent: 2  Rows_examined: 890596
SET timestamp=1418631993;
select * from theView where 1=1  and serial_number = 'TRSNDLD032106700004139'  and LOCATION<>3623 order by  RECNO desc limit 0,25;

It looks like although serial_number has an index in its table, the MySQL does not use this index because now the serial number is searched in the view.

How can I improve performance? Can I force somehow to use index when using views?


texts: Keep getting "Connected to an idle instance" in 18c when trying to connect to database

texts:
I installed  Oracle Database 18c yesterday and then installed Oracle Application Express v20.1. Everything was perfect and I was able to work on Apex too.

Then I shutdown the computer and today I cannot connect to the database. Yesterday when I connected to the database I got Connected, but now I keep getting Connected to an idle instance. When I issued the command startup I got the following errors:

ORA-00838: Specified value of MEMORY_TARGET is too small, needs to be at least 3256M
ORA-01078: failure in processing system parameters

Environment : Windows 10 Pro x64, 8GB RAM, AMD R5 1600 Processor, Installed on Samsung PM961 256GB NVMe