texts: Adding a distributor for availability groups

texts:
According to this MSDN article, I need to add a distributor:

Configure Replication for AlwaysOn Availability Groups (SQL Server)

I'm confused by this particular statement:

  The distributor should not be a host for any of the current (or intended) replicas of the availability group that the publishing database is (or will become) a member of.

I have a three node cluster.  The first two nodes are configured as synchronous replicas.  The third node is intended to be read-only, asynchronous.

This is the first time I've come across this requirement and I'm worried my current setup doesn't cover this critical piece.

Can I set up any of these three nodes as the distributor?  Which node would be the better choice or does that matter?

The reason for my question is because we have a vendor application that is not able to access the database when we failover to the second node.  Below is the error we are receiving.

  Description: Error running query in SpDbOdbc State=37000
  Msg=[Microsoft][ODBC SQL Server Driver][SQL Server]The target database, 'Mirage', is participating in an availability group and is currently not accessible for queries.
  Either data movement is suspended or the availability replica is not enabled for read access.
  To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.
  For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online.

  Server: \
  Database: Mirage
  Date: 2014-11-11 15:06:45
  Module: .exe
  Function: hGetErrCode
  Error code: DATABASE_ACCESS

  Error code general description: Could not access the item in the database.

Also getting this message when trying to login through the application.

  Msg=[Microsoft][ODBC SQL Server Driver][SQL Server]Failed to update database "Mirage" because the database is read-only.


texts: mongorestore to --oplogReplay or not to --oplogReplay?

texts:
The MongoDB documentation on --oplogReplay is a bit vague. It just keeps saying a "point in time" snapshot. How is this different from dropping the sink database and restoring from the source?

What are the pros and cons of using --oplogReplay?


texts:
I'm attempting to determine root cause for an issue that caused an outage for my company.

Essentially the site connections to our database server were timing out after not receiving a response for 5 min.  A check of perfmon counters showed that the database server was using about 10% of processor, memory was under utilized, and our hard disks were not being stressed.

A check of long running queries using the following statement showed that there were a lot of queries queued up with a status of suspended the longest of which had been running for 50 minutes.

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

There were also several queries with a status of running but none of these had been running for a significant amount of time.

SQL Server threw no alerts during the time frame and nothing looked out of the ordinary in the logs.  There were also no alerts for Blocking processes at this time.  Finally the decision was made to fail over the SQL Server cluster.

This cleared up the issue.

I've exhausted the places I can think of to look to come up with an explanation for the outage.  Has anyone experienced similar behavior?  Is there something I should be checking that I haven't mentioned?


texts:
I am using different queries to find the blocking query. For example,
select
       ec.session_id,
       er.blocking_session_id,
       datediff(s, es.last_request_start_time, GETDATE()) as elapsed_time_sec,
       er.command,
       es.login_name,
       es.host_name,
       es.host_process_id,
       DB_NAME(er.database_id) as database_name,
       esql.text AS batch_text,
       ISNULL(CASE er.statement_end_offset + er.statement_start_offset
             WHEN 0 THEN esql.text
             ELSE SUBSTRING(esql.text,
                          (er.statement_start_offset/2)+1,
                          ((CASE er.statement_end_offset
                                       WHEN -1 THEN DATALENGTH(esql.text)
                                       ELSE er.statement_end_offset
                            END - er.statement_start_offset)/2) + 1)
       END, '')
       AS statement_text,
       eplan.query_plan,
       es.program_name,
       es.host_process_id,
       es.last_request_start_time,
       es.last_request_end_time,
       ec.num_reads as num_reads_connection,
       ec.num_writes as num_writes_connection,
       es.reads as reads_session,
       es.writes as writes_session,
       es.logical_reads,
       es.transaction_isolation_level,
       es.row_count,
       er.wait_type,
       er.wait_time
from
       sys.dm_exec_connections ec
       left join sys.dm_exec_sessions es on ec.session_id = es.session_id
       left join sys.dm_exec_requests er on ec.session_id = er.session_id
       --left join sys.dm_exec_sessions es_blocking on es.session_id = er.blocking_session_id
       outer apply sys.dm_exec_sql_text(er.sql_handle) esql
       outer apply sys.dm_exec_query_plan(er.plan_handle) eplan
where es.status <> 'sleeping'
order by ec.session_id

Here I am first checking rows with blocking_session_id is not null. Then from this blocking_session_id, finding it is also block by others and so on. So, let's say I have,
| -------------------- |
| Spid | Blokcing_Spid |
| -------------------- |
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 4 | NULL |

Here 4 is the culprit. How can I quickly find the culprit spids?


texts: Troubleshooting an automatic failover in 2 node multi subnet AG

texts:
I am trying to troubleshoot an automatic fail over that happened on our below infra- both of below running on HYPERV  VMs
Windows version 2019

SQL1 - 8 cores 64 GB RAM - No load running (Primary) running on
SQL1\SQLins
SQL2-  8 cores 64 GB RAM - No load running (secondary) running on
SQL2\SQLins

Both are running SQL2017 (RTM-CU16) enterprise in diff data centers
We also have File share witness in 3rd DC to maintain quorum
While browsing in sql error log the very first error message it starts

A connection timeout has occurred on a previously established
connection to availability replica"SQL2\iSQLins with
id{3w17383381-e4y2e483y-u92u329}Either a networking or firewall issue
exists, or the endpoint address provided for the replica.

Looking at MS documentation -> here seems above has been resolved and should not be applicable for SQL2017. Is it true or anyone still has faced this issue?
After 2 minutes in error log i see messages saying " Always on Availability group connection with secondary database established for database bjsas on replica sql2\sqlins"
But soon again in a minute i see
"The local replica of AG-VBDR' is preparing to transition to resolving state and all failover messages
Trying to figure out what happened because from that msdn link it seems we should not being seen connection time out issues or am i looking in wrong direction?


texts: Error installing EM13 templates in Oracle 12 C

texts:
Linux OS version working with:

Oracle Linux Server release 7.3

Oracle Database and Grid Infrastrucutre version:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Oracle Grid Infrastucture 12c Release 2

* All prerequisites met

I am getting the following error message when trying to create the database template in Oracle using dbca:

I am unsure if the error is related to a version issue or something I can't identify. If anyone has come across this issue, please will you provide a solution.


texts: How can I determine size of database prior to importing from SQL dump?

texts:
I had to manually duplicate a server for development, and the only way I could duplicate the database was via SQL dump.  I was importing into MySQL 14.14 on a virtual machine running Ubuntu 14.  (Yeah, really old stuff, but the client isn't ready to allow an upgrade.)
The SQL dump came to about 6GB of non-compressed, plain ANSI text, so assuming that the import queries bloated the data with all the query instructions, character-escape sequences, and textual representations of binary data, the database ought to be smaller, right?  I figured 6GB for the temporary SQL dump, plus 6GB for the database plus another 8GB for the rest of the system (20GB total) ought to be more than enough.  It wasn't.
I rebuilt my virtual machine with a 30GB-disk thinking surely that would be enough, but it still wasn't.  So I rebuilt my virtual machine with 50GB, and ended up with 16GB leftover when all was said and done.  It turns out the database blew up to 20.5GB!
Somewhere along the way I figured out how to check the size of an existing database in MySQL, but what I would like to know, when the original database is not available to query, is there an application or MySQL command that can passively process a SQL dump without building the database, for the purpose of estimating the size of the resulting database?


texts: What makes it so much faster to write to the "WAL" compared to directly to the "real database"?

texts:
PostgreSQL works like this, if I have understood things correctly:

I make an INSERT, DELETE or UPDATE query to a table in my PG database.
PG immediately writes this query and its parameters to the HDD/SSD in the form of a raw (internal) log, probably in binary format.
Assuming that the table is small enough to store in RAM, PG now INSERTs/DELETEs/UPDATEs this "RAM table" to reflect the change, but does not actually change the database files.
At some later point, perhaps in seconds, perhaps in minutes, and perhaps even hours or days later, when it "has time", PG updates the database files with the information from this "WAL" log file, and deletes the relevant line(s)/entries from the WAL log file.

The explanation I've been given for this seemingly strange ritual is that it's much faster to write to the WAL than it is to write to the database files directly for each query.
But why is this? I assume that PG's WAL log file is more sophisticated than just "adding lines to the end of a text file", since that could result in two queries writing to it at once and thus losing or corrupting data. So it has to be more sophisticated than that. Which means more overhead. At which point I wonder what real performance benefit there is to doing this, since both, at the end of the day, involve writing to a slow HDD/SSD disk.
However, I'm not doubting this. I'd just like to know why it's so much faster.
It seems to me that the problems with writing to the database files are the same with writing to the WAL. In both cases, you need to make sure that it's done orderly and properly. My only guess is that maybe various constraint checks and whatnot are expensive to look up.
Apparently, the concept of a "WAL" is unique to PostgreSQL, but it sounds like just common sense to keep at least some sort of "tabs" on what queries have been executed in order to account for power losses and software crashes. Just blindly writing to the database file each time but not even keep any kind of log sounds like a recipe for disaster, and I cannot imagine that any database ever has done this. Imagine if somebody makes a transaction of a million dollars right when the power is cut and then they lost a million dollars from their account but the other person received no money! Not even a database software from the 1940s probably worked like that...


texts: Unique Identifier with Extra Characters Still Matching in Select

texts:
We are using SQL Server 2012 with a unique identifier and we've noticed that when doing selects with additional characters added onto the end (so not 36 chars) it still returns a match to a UUID.

For example:

select * from some_table where uuid = '7DA26ECB-D599-4469-91D4-F9136EC0B4E8'

returns the row with uuid 7DA26ECB-D599-4469-91D4-F9136EC0B4E8.

But if you run:

select * from some_table where uuid = '7DA26ECB-D599-4469-91D4-F9136EC0B4E8EXTRACHARS'

it also returns the row with the uuid 7DA26ECB-D599-4469-91D4-F9136EC0B4E8.

SQL Server seems to ignore all characters beyond the 36 when doing its selects. Is this a bug/feature or something that can configured?

It's not a massive issue as we have validation on the front end for the length but it doesn't seem correct behaviour to me.


texts: Disable / bypass authentication in PostgreSQL

texts:
I need to run benchmarks (pgbench) in different environments on different versions of PostgreSQL. Authentication works inconsistently and is a generally huge headache / adds a lot of fragile workarounds around the code that has no reason to care about access to the database.

Is there any way I could do something similar to MySQL:

update user set authentication_string=password(''),
    plugin='mysql_native_password'
    where user='root';

and be done with it forever?

I would also be interested in being able to run the database and utilities as super-user (root).  Again, I have no interest in all the security dance, there's nothing to protect, but the price for authentication is too high (weeks of work in order to make scripts work reliably, while they aren't even the main focus of my work).


texts: How to make a test Database grow in size by running transactions to test autogrowth factor?

texts:
I have created a test Database instance which is currently having zero activity on it. I have set the auto growth parameter for this DB similar to our actual production DB which is 600 MB per day , max size unlimited, initial size 888329 MB. I want to automate or run transactions on this test db instance so that it triggers the auto growth everyday for bench marking purpose before applying the same to our actual PROD DB.
Can anyone suggest how to do it in the best possible way?


texts: How to determine if a hyphen (-) exists inside a column

texts:
In a CASE expression, I'm trying to search inside a text column to identify a hyphen (-):

CASE
     WHEN SUBSTRING(al.ALT_ADDRESS,1,1) IN('1','5','7')
      AND al.NEW_ADDRESS CONTAINS '-'
     THEN CONCAT(al.ALT_ADDRESS,al.NEW_ADDRESS)

The hyphen can be located anywhere in the column, and so I just need to know if it exists, regardless of where it actually is in the column.

I'm currently using the exact same code that @Josh provided (LIKE '%-%'), but it doesn't work, because it doesn't return the correct data for several specific instances where I know that it "should" be. The exact text in ALT_ADDRESS is: "2754 Churchill Circle". The exact text in NEW_ADDRESS is: "O-89421". However, the results that are returned, does not include the NEW_ADDRESS (O-89421).

I have confirmed that dash in NEW_ADDRESS really matches the dash I'm using the search (ASCII 45).


texts: How to connect from local computer (mac) to remote Oracle DB and execute script and save results to CSV?

texts:
I can connect to remote Oracle DB using this signature:
 sqlplus 'user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))'

Now want to execute a script and save results to local CSV file - how to do that?
I already have a script that is doing a work but need a terminal (bash) command to connect (remotely) to a DB and then call a script (all in 1 command).
Guessing something like this (but did not work):
 sqlplus 'user/pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=hostname.network)(Port=1521))(CONNECT_DATA=(SID=remote_SID)))' @sql_script

Thanks.


texts: Retrieve latest 3 values from a large table

texts:
Engine MySQL 5.6.37
I have a continuously growing table (around 10M rows) which keeps the values from devices along with a timestamp. Below is the table structure:

CREATE TABLE `meterreadings` (
 `Id` bigint(20) NOT NULL AUTO_INCREMENT,
 `meterid` varchar(16) DEFAULT NULL,
 `metervalue` int(11) DEFAULT NULL,
 `date_time` timestamp NULL DEFAULT NULL,
 PRIMARY KEY (`Id`),
 KEY `meterid` (`meterid`,`date_time`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

I would like to know if one of the past 3 meter values has crossed zero (for all meter IDs). I tried to use the below query and iterate through all the device IDs using PHP, but this approach is rather slow when I have around 500 meterids to scan:

SELECT COUNT(CASE WHEN metervalue>0 THEN 1 END) AS present
FROM (SELECT *
      FROM meterreadings
      where meterid= ?
      order by Id desc
      LIMIT 3) AS a

Is there a better way I can get this done, preferably with one single query and no iteration?


texts:
I have setup a new slave in a set and I just want to allow it to naturally cach up to the master and start using the binary log once it has caught up.

However, I cannot seem to find anything on this, every method seems to require a pre-requisite dump of data. It would be a lot easier for me to just let the slave catch up naturally over a period of days.

Is there anyway I can do this or do I really have to take a full dump etc cetc?


texts: Query to update column in one table based on conditions met in another table

texts:
I have two tables with the following structure (rest of the columns omitted for brevity) :

tblORDERS

+-----------+-------+
|Order_ID   | Status|
+-----------+-------+
| 100       | 0     |
| 101       | 0     |
| 102       | 0     |
+-----------+-------+

tblUNITS

+------------+-------+----------+
| Unit_ID    | Status| Order_Id |
+------------+-------+----------+
| 1          | 1     |   100    |
| 2          | 1     |   100    |
| 3          | 1     |   100    |
| 4          | 1     |   101    |
| 5          | 1     |   101    |
| 6          | 0     |   101    |
| 7          | 0     |   101    |
+------------+-------+----------+

Order_ID is a Foreign Key on tblUNITS. There can be many 'units' inside of one 'order'. I need to create an UPDATE statement that will check tblUNITS to see if all units with the same order_id are in status 1, indicating "complete" . If they are, then I want status in tblORDERS to be updated to status 1, indicating "complete".

Can anybody help me out in writing such query?


texts: Can't do a full backup because the log is full

texts:
I have a server with 25GB of free space. The software stopped working because the transaction log is full. I noticed that the database has two logs files; one is 45Gb and the other is 65Gb.

I think the guy working here before me (he is not anymore) added a second log file so the software would continue working, however there is almost no space left on disk.

I tried using SQL Server Management Studio to take a full backup but I can't since the transaction log is full.

What are my options? I can shrink the logs but I don't have ANY backup and can't do any of them.

Should I change it to simple mode first? Can I script the whole database and save it as 'backup'?


texts:
Imagine the following hypothetical database structure.  Basically it's a top down structure (Country has Orders, Order has OrderLines, OrderLine has ShipLines, etc...).

I want to partition my SQL Server 2008 database by CountryID.  As you can see, CountryID is only in the first 2 tables (e.g. amCountries and amOrders).  Given that (as far as I understand) that partitioning is done at the table level, do I need to add CountryID to the remaining tables to be able to partition them?  Or is there some type of cascade available in SQL Server that will allow me to skip adding CountryID to everything?


texts:
I have table that is more or less as follows:
table Sample (
    location bigint,
    device bigint,
    timestamp datetime,
    type bigint,
    value bigint,
    )

For performance reasons we also have one mighty index with same columns in identical order, except that value is included rather than used in index.
This works as expected, except that AFAIU data is kept twice in database - once in the table and once in an index.
Question: is there some way to, conceptually, keep data only in the index? I believe that would require keeping table organized in the same way index is organized, effectively removing need for the index. I tried to create composed primary key that would emulate aforementioned index, but it boiled down to automatic creation of identical index.


texts: How can an identity primary key index become fragmented?

texts:
From what I understand about index fragmentation, this should not be possible. The cases I have found in my databases are non-clustered.

Example:

ALTER TABLE [dbo].[ClaimLineInstitutional] ADD  CONSTRAINT [PK_ClaimLineInsitutional]
PRIMARY KEY NONCLUSTERED
(
    [ClaimLineInstitutionalID] ASC
)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON,
FILLFACTOR = 100) ON [PRIMARY]

Update:

I am querying dm_db_index_physical_stats.avg_fragmentation_in_percent, so I believe it is physical fragmentation I am seeing.


texts: I can convert time datatype to 12 hour format using convert etc. But is there any option to save the time in 12 hour format in SQL tables using any settings or property or any other methods?


texts: Renaming a column in a PostgreSQL database table

texts:
This is essentially the same question as How do I rename a column in a database table using SQL?.

So, I want to rename a column in a table. This column is part of a set of tables that lives inside a PostgreSQL schema, which is itself in a PostgreSQL database.

However, I also have constraints like foreign keys involving this column.
It is not clear to me whether this command will also take care of constraints. If not, how should I handle this?

Alternatively, I also have this database available as a dump file. Would it make sense to edit the dump file, and if so, how?


texts: Listing active PostgreSQL foreign data wrappers

texts: I have a few foreign data wrappers set up from my main PostgreSQL database to other databases.  Is there a table I can query to list all of the FDWs? Unfortunately select * from information_schema.schemata doesn't give any hints about which schemas are foreign and which are local.


texts: Dump all Views of multiple databases in SQL Server

texts:
I need to export the views from multiple databases of an older server. (Essentially, to generate a list of views using a particular piece of SQL Server)

The fastest way I've found thus far is to manually use the Generate scripts function to export the views to a file.

I feel like there is probably a better way I don't know about.

Thanks in advance,


texts:
SQL> EXECUTIVE DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(service_name=>'ACC_SRV',waits=>TRUE,binds=>FALSE,instance_name=>'inst1');

Why this function need instance_name parameter?
I think the relations between instance and service are as follows(http://docs.oracle.com/cd/B19306_01/network.102/b14212/concepts.htm):

One Service for one instance
multi-services for one instance

If I call SERV_MOD_ACT_TRACE_ENABLE, a single trace file is created for each session that uses the ACC_SRV service. What effect is this instance parameter which is not null?


texts: Is there a way to setup a user to run SPs, but not access the underlying data tables?

texts:
We have two database instances on two separate server boxes that are linked. The website user needs to be able to run stored procedures on DatabaseA which query DatabaseB tables, however it should not be able to read directly from tables on DatabaseB.

Is such a thing possible in Sql Server 2005?

Simply creating a Stored Procedure and giving the web user permissions to execute it returns an error of Access to the remote server is denied because no login-mapping exists.

I really don't want to give the website user account free access to DatabaseB tables.

Update

Aaron's suggestion of using EXECUTE AS OWNER does not work because Owner doesn't have access to DatabaseB.

Using EXECUTE AS 'UserWithPermissions' returns an error of Access to the remote server is denied because the current security context is not trusted. Looking this up online leads me to believe this is because the database is not marked as TRUSTWORTHY, so the remote database rejects the connection. I suspect it would work fine if the two database instances were on the same server.

I did not want to set the web database as TRUSTWORTHY since some of the security on it is more relaxed, so I went with Aaron's other option: Create a User on DatabaseB that only has access to a few specific views containing the data the website should access, then go into the Security Properties on the Linked Server object and setup the website user to login as the new limited user when accessing the linked server.

This allowed my website user to have limited access to specific data in the private database, without giving it access to any of the underlying tables.


texts: Should I be wary of ON DUPLICATE KEY UPDATE?

texts: I'm new to MySQL, but I have reasonable experience with SQL Server. I know in SQL Server I should be very cautious about using MERGE statements because they can lead to some problems. Are there any special concerns I should have when using ON DUPLICATE KEY UPDATE? Is it considered a best practice in the MySQL community to stick with separate INSERT/UPDATE statements like in SQL Server or is it fine to use ON DUPLICATE KEY UPDATE broadly for simple upserts?


texts: Is there a way to get index fragmentation without using dm_db_index_physical_stats?

texts:
I am using SQL Server 2016 SP1 standard edition.

The view dm_db_index_physical_stats is very slow on big databases. Even if I specify the 'LIMITED' option.

I use the view to get index fragmentation, to determine if I should do a REBUILD or a REORG in my maintenance. But as the databases grows, the call to the DMV dm_db_index_physical_stats takes more and more time. On some database, which can be as big as 1TB, it can take an hour to query the DMV. (I have no down time, there is always clients connected to the database doing work.)

So I was wondering if there's a way to know index fragmentation without querying the DMV.


texts: Import Oracle unicode data with OLE DB Source and destination in SSIS 2015

texts:
My issue is identical to this post on mssqltips.com, however my issue is importing from Oracle, not Excel.

I get all the way to the bottom where I need to map the output columns from my data conversion to the destination. I have mapped these columns yet I still get the error on my source task.

I have also tried just about every answer on this similar thread to no avail.

How can I migrate this table from Oracle to SQL Server using SSIS?


texts:
I have a two node RAC database. I have created a server side service as below:

srvctl config service -d test -s testsrv
Service name: test
Server pool:
Cardinality: 2
Disconnect: false
Service role: PRIMARY
Management policy: AUTOMATIC
DTP transaction: false
AQ HA notifications: true
Global: false
Commit Outcome: false
Failover type: SESSION
Failover method:
TAF failover retries: 180
TAF failover delay: 5
Connection Load Balancing Goal: LONG
Runtime Load Balancing Goal: NONE
TAF policy specification: BASIC
Edition:
Pluggable database name:
Maximum lag time: ANY
SQL Translation Profile:
Retention: 86400 seconds
Replay Initiation Time: 300 seconds
Session State Consistency:
GSM Flags: 0
Service is enabled
Preferred instances: test1,test2
Available instances:

Tnsnames.ora (Is this right on the server side?):

TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = testsrv-scan.idfcbank.com)(PORT = 1653))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = TEST)
    )
  )

testsrv =
  (DESCRIPTION =
    (LOAD_BALANCE = ON)
    (FAILOVER = ON)
    (ADDRESS = (PROTOCOL = TCP)(HOST = testsrv-scan.idfcbank.com)(PORT = 1653))
    (CONNECT_DATA =
      (SERVICE_NAME = testsrv)
      (FAILOVER_MODE =
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

While I connect to the database using the service which I created through sql developer and I run the below query it says it is connected by service which I created but failover_type,failover_method,failed_over is NONE

Why is it that way?

Query:

  1* select inst_id,username,service_name,failover_type,failover_method,failed_over from gv$session where username='SYS'

SQL> /

INST_ID USERNAME                       SERVICE_NAME                                                     FAILOVER_TYPE FAILOVER_M FAI

     1 SYS                            SYS$USERS                                                        NONE          NONE       NO
     1 SYS                            SYS$USERS                                                        NONE          NONE       NO
     1 SYS                            SYS$USERS                                                        NONE          NONE       NO
     1 SYS                            SYS$USERS                                                        NONE          NONE       NO
     1 SYS                            testsrv                                                      NONE          NONE       NO
     2 SYS                            SYS$USERS                                                        NONE          NONE       NO
     2 SYS                            SYS$USERS                                                        NONE          NONE       NO
     2 SYS                            SYS$USERS                                                        NONE          NONE       NO

DB Unique name

NAME                                 TYPE        VALUE
db_unique_name                       string      test

Service name from sho parameter service
SQL> show parameter service

service_names                        string      test

DB_unique_name and service name is one and the same.

As per MOS, I should create the service in this format:

srvctl add service -d <db_unique_name> -s <service_name>

So should I create a service like below or is the service which I created above is fine?

srvctl add service -d test -s test - ?


texts:
I am re-developing an application that has been running on Excel. One of the reports I need to recreate (which will be published in Power BI and needs to be a direct query) is as follows;

Column "Approved Est" is calculated from a specific table, so is "Rec Value Based on Ordered Works".  Column "+/- Approved Est" is a simple calculation based on the first two.   This is where i need advice.  There are many ways I know of to get the result, and here is what I have considered or tried.

1) Create a function that has a temporary table holding the initial columns so I can run one update that calculates the reliant columns without having to do the select for the original columns again, and ultimately returns one table.

2) Create a view with multiple unions that calculates each column.  Down side is that I have to re-run the select for the original columns to give the calculated ones.

3) Create separate functions that return the calculated value for any address

Any advice gratefully received.


texts: Index rebuild blocks itself and runs in serializable isolation level

texts:
I’ve implemented Ola Hallengren maintenance solution in one database, and I’m noticing that it’s getting blocked by itself, I don’t know why honestly.

What I have noticed, is that it seems to be run within a serializable isolation level, which is weird since the default isolation level is read committed, and the maintenance script does not change it explicitly.

So, the questions are:

Could the isolation level be related to this locking issue?
Assuming that indexes are rebuild in sequential way….why would the script block itself within the same SPID?

Blocked process report sample can be found here.

Output of @LogToTable='Y' can be found here.

The process does complete without errors. This is still a problem, due to the fact that all running user requests are put on hold while the reindex process is running. Duration would not be an issue if no locks were generated. Since a lot of locks are generated, then duration (2 hours) is indeed an issue on a heavily used database.


texts: How can I return the number of values in each row which match a criterion?

texts:
I have a table "trial_volts" like this in an oracle database (I think it's 10g):

trial_index NUMBER
voltage_1 NUMBER
voltage_2 NUMBER
voltage_3 NUMBER
voltage_4 NUMBER
voltage_5 NUMBER

And I want to get a list of trial_index where more than N voltages are above some threshold. I had hoped there would be some function that would let me do this:

SELECT trial_index, SOME_FUNCTION(voltage_1>10,voltage_2>10,voltage_3>10,voltage_4>10,voltage_5>10) AS vOver
FROM trial_volts
WHERE vOver>3;

...but there does not seem to be such a function. I know this table design is terrible, but I didn't design it and I can't change it, so I've got to find something that works.


texts:
I am looking for the most elegant solution to prevent the addition of the last/ final separator ';' after the last row.

For example the result should be

  'Peter,Paul,Jill'

instead of

  'Peter,Paul,Jill,'

DECLARE @p varchar(max) = '';
SELECT @P = @P + col2 +',' FROM tab1 WHERE coli = 1
RETURN @P

The solution

RETURN LEFT(@P, LEN(@P) -1);

works only if @P is long enough and so is not a solution...

How can I solve this elegant and reliable?


texts:
In Sql Server 2014 I need to run an Update statement that will update NULL values with values found previously in the table.  To better illustrate, here is a very small subset of my data

Create Table #Booberry
(
  id int
  ,customer varchar(500)
  ,os varchar(500)
  ,ms varchar(500)
  ,st varchar (500)
)

Insert Into #Booberry Values
('1', 'Two', 'Win10', 'M1', 'Stop')
,('2', 'Four', 'Step', 'T23', 'Go')
,('3', 'Two', NULL, NULL, NULL)

(again very small subset for illustration purposes only).  What I am needing to accomplish is for each row where OS is null, scan the table and determine if that customer has a previous value.  I think, how this would take place to update the missing data for customer Two would be something like:

MAX([os]) As MaxOS = Win10
MAX([MS]) As MaxMS = M1
MAX([ST]) As MaxST = Stop

Find the MAX() value for the data points to update then use those in an actual update statement.  Meaning that any other customer in my table with a value of Two should have the fields [os],[ms],[st] equal the values above.

How would I write an update query to accomplish this?

If further explanation is needed, I will be happy to provide.


texts:
We had a bunch of disks fail in the RAID 10 that holds the data, logs, and backups drives on a SQL Server 2012 on Win 2012R2 OS. The OS and DB Server instance are in a separate RAID array.

Working with Dell, they recommend replacing the failed drives and then rebuilding the RAID array because the array has 300+ consecutive bad blocks. At this time, there is no obvious infidelity of data.

What would be the best steps to save my data, rebuild the array and restore the data? Would it be better to restore from backups or attach physical files? What happens to your SQL Server instances if there is no Master DB? It seems to me there are 2 possible scenarios

SCENARIO 1

Take the DBs offline or single user, backup all the DBs off the server, rebuild the RAID, reinstall SQL Server if necessary and restore all the DBs from backups.

SCENARIO 2

Turn off SQL Server services. Change SQL Server services to manual start. Copy all the log (.ldf) and data (.mdf, .ndf) files off the server, rebuild the array, copy the files back, and restart SQL services. Maybe I should take the backups first in this scenario as well.

What do you think?


texts: Always On Setup - OBJECTSTORE_SERVICE_BROKER Memory clerk eating all the memory

texts:
We have an Always On setup where in we have two secondary replicas to our primary replica. One of the secondaries is placed in the same datacenter as the primary and the other one is placed at a distant DR site.
Currently we have both the secondaries configured with Asynchronous availability mode.

We have a peculiar issue, wherein a specific memory clerk on the primary replica gradually eats up all the memory allocated to SQL Server in a few days time and we are forced to restart the SQL Server services on the primary to get the memory allocation back to the buffer pool.

We are running SQL Server 2016 SP1-CU8 Enterprise Edition on all the 3 SQL Server instances involved. We approached MS with the issue and they suggested to upgrade to SP2 CU1 and then come back. We tried to upgrade to SP2 CU1 in our test environment and somehow the installation itself failed.

I am attaching a snapshot of the memory allocation across different memory clerks to indicate the particular one that eats up all of it.
Haven't been able to find much on this issue. Any help/support/direction would be appreciated.

Query to find Top clerks consuming memory.

select sum(pages_kb+awe_allocated_kb)/1024 as Memory_in_MB,
 type from sys.dm_os_memory_clerks
 group by type
 order by sum(pages_kb+awe_allocated_kb)/1024 desc

Output:

153824     MEMORYCLERK_SQLBUFFERPOOL
74591      OBJECTSTORE_SERVICE_BROKER
7971       OBJECTSTORE_SNI_PACKET
526        MEMORYCLERK_SOSMEMMANAGER
168        MEMORYCLERK_SQLSERVICEBROKERTRANSPORT
115        MEMORYCLERK_SOSNODE
91         MEMORYCLERK_SQLSTORENG
79         USERSTORE_SCHEMAMGR
65         OBJECTSTORE_LOCK_MANAGER
42         MEMORYCLERK_SQLTRACE

Additional Info:

All the 3 machines have a physical memory of 262033 MB. They all run 16 logical processors each.

Server A (runs the primary replica). Max Server Memory: 240000 MB.
Server B (runs the secondary replica at the same datacenter). Max Server Memory: 240000 MB
Server C (runs the secondary replica at the distant datacenter). Max Server Memory: 250880 MB.

There are 2 AGs.

The first one has 7 databases.
The second one has 11 databases.


texts: Is there a way to log queries which use sequential scans in PostgreSQL? Or just log everything and then later grep for seqscans?


texts:
I had mysql running on my VPS. Today it crashed for no good reason!

I tried service mysql start, it gave me start: Job failed to start.

First thing I did was to backup /var/lib/mysql which contained folders corresponding to my databases and these other files:

debian-5.5.flag
ibdata1
ib_logfile0
ib_logfile1
mysql_upgrade_info

I removed /etc/mysql and did sudo apt-get purge mysql-server mysql-common and then installed everything again, the result was the same: start: Job failed to start.

I went to /var/lib/mysql/ and deleted ib* files. I could start mysql then, but some of the tables were problematic: I could see their names in PhpMyAdmin, but when tried to run queries, mysql errored out that the table doesn't exist.

I assume ibdata1 is corrupted! I put innodb_force_recovery = 0 and tried it upto innodb_force_recovery = 6, none of them worked and I got the same error when I tried to start mysql.

I still have the whole /var/lib/mysql backup.

Any ideas why this happened and how I can recover the data and start mysql again?


texts: MySQL thread states when changing an in-memory temporary table to disk-based

texts:
In High Performance MySQL (3rd edition) they state that:

  You can't tell whether a table was created in memory and then converted to on-disk or just created on-disk to begin with...

What I was wondering though is -

At the point where (the lower of) tmp_table_size or max_heap_table_size is exceeded will a new query (thread_id) be created that will be the same query but with thread state of Copying to tmp table on disk instead of Copying to tmp table?

During some recent debugging we noticed a second query get created right around the 300 second mark but both were of thread state Copying to tmp table. But occasionally we'd see the second query with thread state Copying to tmp table on disk.

The query is the result of a report that only a particular role can request and we instructed those people not to request the report so we are fairly confident that it is not user-initiated.

We also looked at timeouts in MySQL and our application but nothing seems to align with the 300 seconds (there is a MySQL delayed_insert_timeout that defaults to 300 seconds but we changed that value and the second query/thread still was created at 300 seconds).


texts: Session in "Sql*net message from client", but is active

texts:
I've Googled around, and my impression is that

  Sql*net message from client

suggests the Oracle DBMS is waiting for the client to send new commands to the DBMS, and therefore any time spent in this event should be client-side time and not consume DB server CPUs. In other words, normally, if a session is in this event, it should be "INACTIVE" rather than "ACTIVE".

What's puzzling to us is that starting from this week (after we started using connection pools [we use dbcp]), we occassionally see sessions in the

  Sql*net message from client

event and showing "ACTIVE" at the same time for extended periods of times. And during all this time, CPU usage on the DB is high.

Can anyone shed some light on what this means?  If the DB session is waiting for the client to send a message, what can it be "ACTIVE" and consuming CPU cycles for?


texts: Update the host field in Grant privileges in mysql


texts: Will SYSTEM_USER return the unique user id for users authenticated by Active Directory

texts:
I want to set up Active Directory authentication for my users, who will be assigned to specific database roles. I also need to log activity and ensure I have captured the IDs of the indivudals connected.

I have found simple instructions on how to do it. ( How do I assign an entire Active Directory group security access in SQL Server 2008? )

... But Will SYSTEM_USER return the logon id of the active directory connection or the individual user? (Unfortunately I cannot test this!)


texts: Select repeating number x times and then increment

texts:
I need a select query that can generate z amount of rows starting from 1, repeating x times and then increment to the next number.

For example 10 rows repeating 4 times would result in the following:

| # |
|---|
| 1 |
| 1 |
| 1 |
| 1 |
| 2 |
| 2 |
| 2 |
| 2 |
| 3 |
| 3 |


texts:
I would like to get some clarity on the usage best practices of sp_send_dbmail and I cannot find any answers online.

Our developers love to use sp_send_dbmail to send out application related emails from within the application. Say for instance a user forgets their password, they will send the password reset email that a user requests by executing sp_send_dbmail. This is just one of the many types of emails they send using this stored procedure. Is this a good practice? To me it feels like a dirty solution as there are many API's out there that can handle email sending in your application and as a DBA, I don't like enabling these options on the production servers. I just don't have a choice. Maybe with enough motivation and proof that it's bad practice and/or potentially dangerous for various reasons I can sway them to change it.


texts: MySQL select by primary key against 100M of records within 30ms

texts:
I have no expirience with MySQL and database performance tuning in general.

Wondering whether it is possible to perform a large number of selects against 100 millions of records by primary key within 30ms per request in average.

Some details:

PK is 16 bytes (it's UUID stored as binary(16)).
Each record is approximately 100 bytes and can be shrinked to 24 bytes if normalized (though cardinality is approximately 10 to 1 and we need joins in this case).
Table should be writeable. There may be 1 update per 1 select, though updates can be deferred and their performance is less critical.
I could allocate up to 8GB for mysql needs.
Disk is a regular HDD (this concerns me the most, because seek time is about 9ms)

Please, suggest, what storage engine is preferable, what mysql parameters should be tuned. Also suggest, please, some technics (perhaps selecting groups of records with where in() against record by record, etc.) to improve performance.

Thanks in advance.


texts: Offline Index Rebuild on a Partitioned Table

texts: If I partition a table with ntext, text or image datatypes and rebuild an index on a single partition with online = off, does that lock the whole table or just the partition in question?


texts: Can a un-used NONCLUSTERED INDEX still enhance query speed?

texts:
This is a strange situation, but I'm hoping someone has an answer.
During some performance troubleshooting, we added a NONCLUSTERED INDEX to a table, as was requested by sp_BlitzIndex.  We checked its usage the next day, and it showed 0 reads (0 scans/seeks, 0 singleton lookups), so we disabled it.
The very next minute, we get a complaint of the same app-slowness (performance trouble) that we were trying to check out & resolve in the first place when we added the INDEX.
Now, I know in theory, this sounds purely coincidental.  The INDEX was provably, measurably, NOT USED.  Disabling it SHOULD NOT have caused query performance degradation.  But it's almost TOO coincidental.
Question
So my question, simply enough, is thus:
Is it at all possible, that a NONCLUSTERED INDEX, whose usage-stats (from the DMVs / sp_BlitzIndex) show NO usage, still have been helping query performance somehow on the affected table?


texts: How to search for a string in all columns of a table?

texts:
I use postgresql database and the need is to return all the rows that contain a given string in any column of that table. For example, I have a table like this.

I need a query to return all rows that has Italy in any of their columns i.e the query shoud return 1st and 3rd rows.


texts: Speed efficient query for membership first joined, latest category from membership table (min, max)

texts:
I have the following table representing membership information:

    CREATE TABLE IF NOT EXISTS `membership` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `organisation_id` int(11) NOT NULL,
      `membership_subcategory_id` int(11) NOT NULL,
      `start` datetime DEFAULT NULL,
      `end` datetime DEFAULT NULL,
      `amount` decimal(9,2) DEFAULT NULL,
      `amount_paid` decimal(9,2) DEFAULT NULL,
      `notes` mediumtext,
      `order_id` int(11) DEFAULT NULL,
      `payment_type` varchar(20) NOT NULL,
      `active` tinyint(4) NOT NULL DEFAULT '1',
      `cancelled` tinyint(4) NOT NULL DEFAULT '0',
      `cancelled_date` datetime DEFAULT NULL,
      `cancellation_reason` mediumtext,
      `certificate_sent` date DEFAULT NULL,
      `welcome_email_sent` date DEFAULT NULL,
      PRIMARY KEY (`id`),
      UNIQUE KEY `id` (`id`),
      UNIQUE KEY `order_id_2` (`order_id`,`start`,`end`,`organisation_id`),
      KEY `membership_subcategory_id_idx` (`membership_subcategory_id`),
      KEY `organisation_id_idx` (`organisation_id`),
      KEY `order_id` (`order_id`)
    )

organisation_id is a member
the membership year goes from 1 Jul to 30 Jun, start records when the membership in each year has started - this may be anywhere in the first year, but then its always 1 Jul unless a year is skipped
membership_subcategory_id is an industry category the membership applies to for each year.

I need an efficient query to get the date joined and latest membership category.

I've tried this query, but I get "Invalid use of group function" as an error

SELECT m.organisation_id, m2.membership_subcategory_id, MIN( m.start )
FROM membership m
INNER JOIN membership m2 ON m.organisation_id = m2.organisation_id
WHERE MAX( m.start ) = m2.start
GROUP BY m.organisation_id, m2.membership_subcategory_id


texts: mysqlbackup failed with error when --use-tts option is used

texts:
I just found an interesting behavior when using the mysqlbackup feature to create a backup of a database and i'm wondering if i missed any steps or maybe a config that i missed or created a typo somewhere.
When running the following command:

/path/to/mysql_enterprise_backup/bin/mysqlbackup --host=127.0.0.1
--user=$BACKUP_USER --password=$BACKUP_PASS --include-tables="^*.*$" --with-timestamp --use-tts --backup-dir=$BACKUP_DIR --compress backup-and-apply-log

I get an error at the end of the script:

InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62
63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85
86 87 88 89 90 91 92 93 94 95 96 97 98 99  210120 09:53:45 PCR1
INFO: We were able to parse ibbackup_logfile up to
lsn 31466657750. 210120 09:53:45 MAIN    INFO: Apply-log-compress operation completed successfully. 210120 09:53:45
MAIN    INFO: Full backup prepared for recovery successfully.
MAIN: Can't get stat of
'/path/to/backup/hotbackups/2021-01-20_09-51-53/datadir/ibdata1'
(Errcode: 2 - No such file or directory) 210120 09:53:45 MAIN ERROR:
Unable to get the size of file
/path/to/backup/hotbackups/2021-01-20_09-51-53/datadir/ibdata1. Error
code: 2, No such file or directory
mysqlbackup failed with errors!

As per the error, the script attempts to get the size of the ibdata1 file, but it can't find it and fails.
the only folders available in the datadir directory are my schemas and ibbackup_logfile, but not ibdata
Now this is where it get's interesting and also where i'm getting lost.
I'm using the --use-tts option when creating my backup (in order to be selective and restore in another environment) but looking at the doc for the use-tts option https://dev.mysql.com/doc/mysql-enterprise-backup/3.12/en/backup-partial-options.html#option_meb_use-tts and i quote:

The system tablespace is not backed up, saving disk space and I/O
resources

With this in mind, if i understand correctly, the --use-tts option will not backup and create a ibdata file but the command mysqlbackup will still check for that specific file even though the option to not create it has been specified in the command.
This is causing the backup to get the fail message.
My dilemma at this time is that the mysqlbackup script fails even though my backup seems to have been done correctly. (i'm assuming it was done correctly because of this log entry: 210120 09:53:45 MAIN INFO: Full backup prepared for recovery successfully.)
Has anyone noticed this before?


texts: Problems with a job that rebuilds indexes

texts:
We have a Maintenance Plan (MP) job that rebuilds indexes every week.

The last time, the job failed because of a deadlock. Should I rerun the whole job, or do I just need to rerun the rebuild of the deadlocked index?

The rebuilding of indexes is being performed using standard tools of MP plan.

As I understand it, if a standard MP job for index rebuild stopped because of deadlock, it means that other indexes (after the deadlocked index) also need to rebuild? Or MP job will rebuild all indexes except this one that failed?

Here is example of entry from job history:

  Progress: 2017-09-30 21:00:33.06
  Source: Rebuild Index Task
  Executing query "USE []  ".: 0% complete
  End Progress
  Progress: 2017-09...
  The package execution fa...
  The step failed.

There is no information about which index rebuilds completed, only information that a query started to execute. Looks like one way to check what was rebuilt or not is to check index fragmentation for whole database using T-SQL scripts?

There is only information about deadlock and nothing more in MP log.


texts: How do I get only those values that has related elements from other table?

texts:
I have three tables for a social network: companies, users and countries. Users belong to Companies and Companies have a Country. All tables have their own id column.

Table users has a company_id FK column
Tables companies has a country_id FK column

The thing is that because of some administrative stuff (Duplicated companies, different names for the same company, etc.) the database has companies with no users (we are actually cleaning all this moving the guys to the companies they really belong to).

I need to get only those companies that actually has users related to it.

Something like

select c.id, c.name, count(co.country_id) as amount from countries as c
left join companies as co on co.country_id = c.id
left join users as u on u.company_id = co.id
where "COMPANY HAS USERS RELATED TO IT"
order by amount desc";

For now I had no problems getting the amounts for each country. But now I need those amounts to only count companies with users related to it. Something that can allow me to avoid having to use a second query inside the "while" with an "if > 0".

By the way, the query starts like that because I'm actually making a list that goes like:

Country (amount of companies)


texts:
I need to check whether the surfer has alrady voted this can be done using

Method 1

Select record_num FROM table where etc etc.

i.e.

SELECT record_num
FROM content_votes_tmp
WHERE up = 1
AND ip = INET_ATON('$_SERVER[REMOTE_ADDR]')
AND content = $_POST[id]
AND UNIX_TIMESTAMP(datetime) > '$old_time'

Method 2

Select Sum(votes) FROM table where etc etc.

i.e.

SELECT SUM(up) as up_count
FROM content_votes_tmp
WHERE ip = INET_ATON('$_SERVER[REMOTE_ADDR]')
AND content = $_POST[id]
AND UNIX_TIMESTAMP(datetime) > '$old_time'

Using storage engine as MyISAM ,
Table has around 1 million rows,
ROW Format  is static.

I am looking for query which is faster in terms of performance.

So which query will be faster ? this query will be fired every time someone clicks the vote up or vote down button.,

here is a table structure

CREATE TABLE IF NOT EXISTS `content_votes_tmp` (
  `up` int(11) NOT NULL DEFAULT '0',
  `down` int(11) NOT NULL DEFAULT '0',
  `ip` int(10) unsigned NOT NULL,
  `content` int(11) NOT NULL,
  `datetime` datetime NOT NULL,
  `is_updated` tinyint(2) NOT NULL DEFAULT '0',
  `record_num` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`record_num`)
  KEY `content` (`content`),
  KEY `datetime` (`datetime`),
  KEY `is_updated` (`is_updated`),
  KEY `ip` (`ip`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=25 ;


texts: Performing deltas / diffs over VPN from Oracle to SQL Server database

texts:
I have inherited a reporting structure that is quite the Rube Goldberg device. I have a collection of MS Access 2003 databases that I have begun to migrate to SQL Server. My current hangup is that we have a VPN/ODBC connection to an Oracle database that belongs to an external entity, and the server for this hosted service is over 2000 miles away. While the Access "solution" does "work", it is extremely slow and any kind of development is difficult and time-consuming.

We decided that we would create a mirror database at our local site for all reporting and analytic services, and we were able to transfer a good portion of the data to our local "mirror" database using Microsoft's Oracle migration tool. Unfortunately this tool doesn't seem to provide a functionality to perform deltas.

I have been able to query the remote database with applications such as MS Access and Flyspeed SQL Query, bu they have their limitations. For example, Access 2003 is having a lot of trouble pushing data into the local SQL database. It can do very small data sets, but it chokes on anything more than a few thousand lines. Flyspeed can see all the data sets but I am unable to perform an "INSERT INTO" query for whatever reason, and it times out very easily. It also lacks the ability to schedule a query, from what I can gather, and I don't see that it can be scripted.

Is there a methodology by which I can perform deltas with the VPN / ODBC constraint, as well as with the SQL Server / Oracle differences?

Thanks,

Nate


texts: What is the best way to represent an album and song entity in an e-commerce schema?

texts:
I'm designing a simple mp3(music) ecommerce site, customers can either buy a single Track or whole Album, an Album is made up of Tracks, so there is a one to many relationship between an Album and a Track as shown below. I thought it would be a good idea to have one Orders table and one Product table, the idea I had is to have a generic Product table to represent either a Track or an Album as a Product but I'm kinda struggling to design that on my ERD. I was thinking in a inheritance approach.  all Tracks will have the same price but Albums will have an individual prices.

What is the best way to achieve this requirement? in the orders table I need to see if it was a Track or Album purchased and money spent.

I have searched for examples and samples without any luck.

An example will also help a lot.


texts: How to List All Permissions for SQL Server Fixed Database and Server Roles

texts:
I am trying to list all current permissions for db_owner and sysadmin for SQL Server 2012. I found these SPs:
EXEC sp_srvrolepermission 'securityadmin'
EXEC sp_dbfixedrolepermission 'db_owner'

However, these are deprecated and only accurate as of SQL Server 2000. Is there an equivalent mechanism to accomplish the same thing today?


texts: "Active Directory - Password" login in SSMS for Azure AD - Password expired

texts:
I'm trying to connect to an Azure SQL DB through SSMS (v17.9.1), using an Azure AD user, that is an Active Directory Admin for my SQL Server that hosts my Azure SQL Database.

I'm able to login with my Server Admin "azureuser", using SQL Server Authentication. But now I'm trying to use an Azure AD user (by password) to login through SSMS. I've created this user ("sqladmin") into Azure Active Directory:

And then added this AD user as an AD admin to my SQL Server:

Now, I don't know if this is the correct way to do this, but I'm resetting [sqladmin] password to know it, and then trying to connect with SSMS, but I keep getting the password expired, even when Azure Portal states that the password will be valid until the next login (where it'll be required to be changed):

Am I doing something wrong? I can't create EXTERNAL PROVIDER logins on this server either as it requires to be connected with an AD user (executing with azureuser - server admin):

CREATE USER [sqladmin@myuserhotmail.onmicrosoft.com] FROM EXTERNAL PROVIDER

  Msg 33159, Level 16, State 1, Line 5
  Principal 'sqladmin@myuserhotmail.onmicrosoft.com' could not be created. Only connections established with Active Directory accounts can create other Active Directory users.


texts: what shortcomings does couchbase have, compared to mongodb?

texts:
I see a massive gap of popularity between MongoDB and Couchbase.

What features\advantages does mongo have over couchbase?

I'm deploying DB instances in a cluster, between datacenters, where all servers accept incoming requests.
Is it not simpler to do this with couchbase, that has only one server role, and less configuration?


texts: Create/Edit SSIS Derived Column Task From Text/Code for Large ETL

texts:
I have to insert a large UTF-8 encoded flat file to a 1252 encoded sql server. In order to do so, I am using SSIS to type cast each column. Is there a way, other than copy pasting through all 400 columns, to create/edit a derived column transform from text/pragmatically? I cannot use bulk insert. BCP is an option, but I had issues getting that to work.

Side note:

This is for replicating Salesforce to SQL Server. Other than pre-built solutions DBAmp, Skyia etc..., I'm open to other options. I need to make this happen quickly due to business demands.


texts:
I am not sure if this is an odd question, so please let me know if what I describe below is possible.

We have a server, which is configured by someone else and none of us are "added in it".

As of now I have only sa login in said server.

I am checking this via

SQLCMD -E -S ".\SQLServer"

and then by

SELECT name FROM sys.syslogins

And the output is

sa

I wanted to just run a SELECT statement against a table to see the value.

Question

Is there anything I can do at all?

I have tried DB Changing On and Trustworthy, but since I am no member at all my access is denied.

I just need to run the SELECT statement.


texts:
I have 2 mysql table:

table t1:

CREATE TABLE IF NOT EXISTS `t1` (
  `id` bigint(20) NOT NULL,
  `url` varchar(255) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `type` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `type` (`type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

table t2

CREATE TABLE IF NOT EXISTS `owa_request` (
  `id` bigint(20) NOT NULL,
  `date` int(11) DEFAULT NULL,
  `ip` varchar(15) DEFAULT NULL,
  `d_id` bigint(20) DEFAULT NULL,
  `u_id` bigint(20) DEFAULT NULL,
  `browser` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `d_id` (`d_id`),
  KEY `u_id` (`u_id`),
  KEY `date` (`date`),
  KEY `ip` (`ip_address`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Now I want to make such a query:

select
t1.id,t1.url,t1.type,
t2.d_id,t2.date,t2.ip
from t1,t2
where t1.id=t2.d_id and t1.type='article'
group by t2.ip
HAVING COUNT(t2.ip)>10

get all the data group by t2.ip and each t2.ip should have at least 10 results.

But the query is very slow, 300k rows data (70MB), cost 12.5290 sec, have I make bad indexes? how to optimize let query under half second? Thanks.


texts: Delete an object from within an array using JSONB in PostgreSQL

texts:
I am attempting to remove an element from my database. The element I want to remove is within a JSON object called playerContainer which contains an array named players. This all sits within a table called site_content. I am trying to remove the object based on it's ID.

Here is the sample JSON

"playersContainer": {
        "players": [
            {
                "id": "1",
                "name": "Nick",
                "teamName": "Shire Soldiers",
                "ratings": [
                    1,
                    5,
                    6,
                    9
                ],
                "assists": 17,
                "manOfTheMatches": 20,
                "cleanSheets": 1,
                "data": [
                    3,
                    2,
                    3,
                    5,
                    6
                ],
                "totalGoals": 19

            },
}

Below is the query I am attempting to use to delete the object within the array

UPDATE site_content
SET content = jsonb_set(content, '{playersContainer,players}'::text[], content->'playersContainer'->'id'->'players' #- '{1}'::jsonb

However this seems to delete all the data from my content row in my table and not just the array with an ID of 1 in my players array.


texts:
Background

I have a table with the below format:

id(int) type(varchar)   Name(varchar)   Description(varchar)    Value(decimal)

There are three different types possible, data from each of these types will never be compared against each other and for all intents and purposes are completely unrelated apart from the fact that they can share this table structure.

This is a very small table (10 000 rows), however it will be regularly joined with tables that have around 10 million rows based on the id.

Question

Would splitting the table into three separate tables based on the type be beneficial for performance? If so would it be a negligible difference?

Doing so requires little modification to existing code, and no additional types will ever be added.

From a design and readability point of view is it preferable to have this table together or split, considering that in other instances in the DB separate tables exist for each type as they do not share a common structure.


texts: Listening to mongodb change streams for indexing to elasticsearch

texts: We have a mongodb 4.0 replica set deployment with a collection that stores social media posts. This table sees a lot activity with updates amounting up to 1000 requests per second occasionally. We want to use change streams to listen to all collection level insert, updates and deletes and index to elasticsearch. My question is can change stream cater to that throughput? Also will there be any performance impact because of adding this new change stream?


texts: How do I identify tables that have a foreign key to a specific table in Sybase?

texts:
I am looking for (preferably) an SQL statement that selects the table/and column names for any table with a foreign key to a given table in Sybase. I think it should be somehow possible with the sys... tables but being completely new to sybase, I cannot make head nor toe of it. So any help into the right direction is highly appreciated.

Edit: For completness' sake: SELECT @@VERSION returns

'Adaptive Server Enterprise/15.0.3/EBF 17156 ESD#3/P/Sun_svr4/OS 5.8/ase1503/2726/64-bit/FBO/Fri Feb  5 05:26:23 2010'

Edit 2
Thanks a lot for your suggestions, especially Andrew Bickerton's comment. That allowed me to construct a rudimentary SQL select statement for a starting point to go further.

In case someone else is interested in it, here it is:

select
  fko.name    "Foreign key name",
  par.name    "Referenced table name",
  fk1.name || ' -> ' || pk1.name "Reference 1",
  fk2.name || ' -> ' || pk2.name "Reference 2",
  fk3.name || ' -> ' || pk3.name "Reference 3",
  fk4.name || ' -> ' || pk4.name "Reference 4"
from
  sysobjects      tab                                       join
  sysconstraints  con on tab.id        = con.tableid        join
  sysobjects      fko on con.constrid  = fko.id             join
  sysreferences   ref on con.constrid  = ref.constrid       join
  sysobjects      par on par.id        = ref.reftabid  left join
  ---- 1. Column
  syscolumns      fk1 on ref.fokey1    = fk1.colid and
                         ref.tableid   = fk1.id        left join
  syscolumns      pk1 on ref.refkey1   = pk1.colid and
                         ref.reftabid  = pk1.id        left join
  ---- 2. Column
  syscolumns      fk2 on ref.fokey2    = fk2.colid and
                         ref.tableid   = fk2.id        left join
  syscolumns      pk2 on ref.refkey2   = pk2.colid and
                         ref.reftabid  = pk2.id        left join
  ---- 3. Column
  syscolumns      fk3 on ref.fokey3    = fk3.colid and
                         ref.tableid   = fk3.id        left join
  syscolumns      pk3 on ref.refkey3   = pk3.colid and
                         ref.reftabid  = pk3.id        left join
  ---- 4. Column
  syscolumns      fk4 on ref.fokey4    = fk4.colid and
                         ref.tableid   = fk4.id        left join
  syscolumns      pk4 on ref.refkey4   = pk4.colid and
                         ref.reftabid  = pk4.id        -- Et cetera...
where
  tab.type = 'U'      and
  tab.name = 'tq84_f' and
  fko.type = 'RI'


texts: Remove a SQL Server Patch only visible in View Installed Update List

texts:
Kindly help me regarding our issue with a Clustered Environment's applied patch.

The situation is: We removed a SQL Patch: GDR 6248 for SQL Server 2012 (KB3194721) (64 bit). When we successfully removed the patch on the first node, we rebooted it. Now when we tried to remove it on the second node, we can't proceed to uninstall the patch as there are missing files.

We attempted to remove the patch again using some ready msi files on our repository. We successfully removed the patch. SQL Management Studio detects the correct service pack, which is SP3.

The issue now is on the OS where the patch we're trying to remove is still on the View Installed Updates List. Please advise how to remove it? What steps to take that is least harmful to the clustered setup?

OS Version: Windows Server Enterprise 2012
Database Version: SQL Server 2012 SP3


texts: Any performance gains from having identical clustered and nonclustered indexes on table on different filegroups?

texts:
I am attempting to rearchitect our MSSQL database such that we have two filegroups, one for data, on a 15K HDD, primary, and one for indexes on a separate SSD, indexes, in order to realize some performance gains (which I read could be debatable, but I think in my case it is true).

I would ideally like to create a table on primary with an index on indexes to realize these gains, however the PK constraint happens to contain the same columns we would normally query, therefore the clustered PK index must, by definition also live on primary.

Would I still realize any performance gains if I created a nonclustered index containing the same columns as the PK on the indexes FG?


texts: Stored Procedure Performance using Temporary Tables

texts:
I have the following Stored Procedure I would like to improve the performance of

DELIMITER $$
CREATE PROCEDURE NEXT_UNIT_OF_WORK
(
    IN batchId INT,
    IN size INT
)
BEGIN

   start transaction;

   CREATE TEMPORARY TABLE IF NOT EXISTS BATCH_ITEMS_UOW ENGINE=MEMORY AS (SELECT ID FROM BATCH_ITEMS WHERE STATUS = 'UNPROCESSED' AND BATCH_JOB_ID = batchId LIMIT size);

   UPDATE BATCH_ITEMS SET STATUS = 'PROCESSING' WHERE BATCH_JOB_ID = batchID AND ID IN (SELECT ID FROM BATCH_ITEMS_UOW);

   SELECT * FROM BATCH_ITEMS_UOW;

   DROP TABLE BATCH_ITEMS_UOW;

   commit;
END $$
DELIMITER ;

The intent is to grab a chunk of batch items via a stored procedure identified by a batch job id and restricted to a number of items (size).

The query seems to take 1 second initially and then takes consecutively longer with each call to the stored proc.

Given this stored proc is going to be called hundreds of times per minute by multiple threads, its unacceptable to take so long.

Is there a better approach to this stored proc?


texts: pgpool-II configuring multiple masters and slaves

texts:
Actualy I did create a replication system using Pgpool2, with master/slave servers, now i need to add multiple master servers and I need them to be related and updated across each other.

Is it possible to configure multi-master servers using Pgpool2 ?


texts: Using View filters for tenants in SQL-Server 2008

texts:
I'm currently creating a Multi-tenant data architecture in a SaaS BI-product.

The product are using shared database and shared schema, but with tenant view filters and tenant data encryption
(Inspiration found here: http://msdn.microsoft.com/en-us/library/aa479086.aspx)

When creating the view filter, I can do both these:

CREATE VIEW TenantEmployees AS
SELECT * FROM Employees WHERE TenantID = SUSER_SID()

CREATE VIEW TenantEmployees AS
SELECT * FROM Employees WHERE TenantID = SUSER_ID()

Using SID() I am using the Security Identifier that looks something like this:

  0x3425F1595A67774ABF485CDC30285443

Using ID() I am using the normal Identifier that looks something like this:

  265

Which one is the absolute best to use and why? :)
I expect that both are absolutely unique and will never be reused in newer user-logins


texts: SSDT: Create User in a database not being part of the project

texts:
I want the user of my SSDT Database Project to be created as a user in another database on the same server and give it permissions to connect and add it to the db_datareader role.

I failed to do so. The PostDeployment Script complains about:

USE command is not allowed
not knowing the Create User OtherDB.USERNAME Syntax

How can this be realized? Isn't there a script option where I can place any command I want?


texts:
I'm pasting section of MySQL Tuner result regarding MyISAM Metrics.
[OK] Currently running supported MySQL version 5.5.57-0ubuntu0.14.04.1-log
[OK] Operating on 64-bit architecture

-------- Performance Metrics -----------------------------------------------------
[--] Up for: 25d 1h 40m 20s (6M q [3.149 qps], 170K conn, TX: 15G, RX: 1G)
[--] Reads / Writes: 90% / 10%

-------- MyISAM Metrics ----------------------------------------------------------
[!!] Key buffer used: 20.1% (80M used / 402M cache)
[OK] Key buffer size / total MyISAM indexes: 384.0M/31.6M
[OK] Read Key buffer hit rate: 99.9% (7M cached / 4K reads)
[!!] Write Key buffer hit rate: 1.2% (2M cached / 25K writes)

I'm looking for way to improve or optimize the Write Key buffer hit rate. Or why is it so bad?
I don't know which server variables may play role in this result, so please let me know what variables should I paste here or if you need any specific details.
Thanks for help!
UPDATE 2017-08-11 13:56 CET
I did some more tests based on recommendations from @RolandoMySQLDBA and it really seems I should not worry about this metric. After I lowered the key_buffer_size, the Write buffer key hit rate improved directly. However, I couldn't measure any improvement in speed of reads. I assume it's because there was not enough memory for cache, so almost all writes went through. I still don't really understand what is the good or bad thing about Write buffer key hit rate, but it seems to be related to optimal size of key buffer based on the data and index in database.


texts:
I need the help with following question as part of my assignment / homework

Provided the following structure

CLASS(Reg-NO ,Unit-ID,UnitTitle,Lecturer,Office)

The following functional dependencies are given.

Unit_ID → UnitTitle
Lecturer → Office
UnitTitle → Lecturer

Question 1 : Identify the form in which it exists.

Question 2 : Decompose to 3NF if it is not in that form.

For question 1:
I Identified it to be in the 1NF since it does not have unique PK as per the 2NF requirements.
It also meets the 1NF requirements, as per my observations.

For question 2:
I decomposed it to the following

CLASS(Class-ID,Reg-NO)
UNIT(Unit-ID,UnitTitle)
LECTURER(Lecturer-ID,Lecturer,Office)

I have doubts about my answers and would like some help in reviewing them.

Also what format is structure in? (the : ENTITY(column1,column2,...) format) What's it called?


texts: "Table of Constants" - is this common practice?

texts:
College SQL class, using the book "SQL Fundamentals" by John J. Patrick. In the third chapter, he talks about using a "table of constants" to add columns to a select statement, where all rows have the same value.

For example, if you have table "characters", as so:

first_name  last_name  dept_code
----------- ---------- -------------------
Fred        Flintstone ROCKS
Barney      Rubble     ROCKS
Wilma       Flintstone FACEPALMING_AT_FRED

and you want a SELECT that adds a column "hometown" with value "BEDROCK" to all rows, he recommends making a second table in the database, "temp", with

hometown
--------
BEDROCK

and then doing

SELECT first_name, last_name, dept_code, hometown FROM characters, temp

The idea is that this avoids putting string constants in the SELECT statement, and that if you've got a lot of SELECTs that need the same constants, it's easier to update one table than fifty queries.

The thing is, I've been working with SQL databases for the last fifteen years, and I have never seen this construction. Is it something totally common that I've just missed, or is it something that I can erase from my memory after this assignment is over?


texts: MySQL Memory & max_connections Performance Tuning

texts:
So, our site runs WordPress and in the past few days we have been getting tons of spam comments etc.  I believe that we are becoming the victims of some kind of bot army that has been crashing our server a lot in the past few days and I really need help.

I have called our tech support and they have recommended decreasing some of the buffer sizes in my.cnf and lowering max_connections.   I ran the mysqltuner script and here are the results.

The immediate problem is that we are reaching our max_connections value.  I don't want to increase it beyond 40 because when I do MySQL uses the full capacity of the server's memory and starts to have memory allocation errors when I'm running terminal commands.  The real issue is what do I need to tweak so that I can increase the max_connections without allowing MySQL to use more memory?  I think the buffer pool size is a big factor but I don't know how to tweak it properly.

Also, mysqltuner says I have a bunch of fragmented tables but I just went through by hand yesterday and ran OPTIMIZE TABLE on all of the tables?? Could that be a result of running mysqlcheck --repair --all-databases ??


texts:
Is it possible to generate a UUID version 4 (completely random) in MySQL? I would like to use it as a primary key.

Use Case

Many systems will insert to the table, some may specify a UUID when inserting, but some will not. They will generate their own UUID, then insert it later (offline insert).
I already have systems that use MySQL, and I would prefer to stay with the same DBMS.
I prefer UUID v4 because of its physical machine independence.

Alternatives

I could simply create a trigger that calls the uuid function, as outlined in MySQL - Alter table to automatically put in a UUID, but the uuid function generates UUID v1 ids.

I could always generate the UUID externally, but I would prefer a built-in default.

This is easy to do in Postgres, and some other DBMSes, but is there a way to do it in MySQL?


texts: Aggragate values over time in temporal table


texts: Google Datastore (NoSQL) backend for PHP CMS

texts:
I am working on a personal project where I am creating a small CMS using PHP as the developing language, but I want to be able to deploy it to the Google App Engine and I want to use its Google Datastore (NoSQL).

The question I have is how to do relationship between kinds?

For example:

Kind 1: call category
Kind 2: call tags
Kind 3: call post

I want the posts to be able to have one category, but multiple tags. I am having trouble visualizing it. I am coming from SQL world so having the ID of the category and the IDs of the tags to them JOIN them will take care of the same issue.

Note: if there is some tutorial or reading that you could share I will appreciate it.


texts:
I have been looking into how to build a notification system on SE and elsewhere and found myself drawn to the solution that is the accepted answer here: https://stackoverflow.com/questions/9735578/building-a-notification-system
which uses this structure:
╔═════════════╗      ╔═══════════════════╗      ╔════════════════════╗
║notification ║      ║notification_object║      ║notification_change ║
╟─────────────╢      ╟───────────────────╢      ╟────────────────────╢
║ID           ║—1:n—→║ID                 ║—1:n—→║ID                  ║
║userID       ║      ║notificationID     ║      ║notificationObjectID║
╚═════════════╝      ║object             ║      ║verb                ║
                     ╚═══════════════════╝      ║actor               ║
                                                ╚════════════════════╝

A notification is about something (object = event, friendship..) being changed (verb = added, requested..) by someone (actor) and reported to the user (subject). Here is a normalized data structure (though I've used MongoDB). You need to notify certain users about changes. So it's per-user notifications.. meaning that if there were 100 users involved, you generate 100 notifications.

I thought at first that I understood this approach, but when I started getting ready to implement it I realized I apparently don't understand it particularly well. The last few comments on the answer are questions from other users who also have had trouble understanding the solution.
I'm not sure if this is the model I'll end up following, but given the number of upvotes it has, I'm sure it would benefit me to understand it, and I'd certainly like to learn more. I hope this will also be of use to others who have had trouble grasping this solution (incidentally, I don't have enough internet points to leave a comment on that answer directing to this question, anyone else please do!)
Questions
If I understand it right, notificationObjectID is a foreign key pointing to the notification_object table, and notificationID is a foreign key pointing to the notification table. It seems like object should be a foreign key referring to the ID of the database entry the notification is about (e.g. a specific event or post), but don't we then need another field to indicate which table that ID belongs to?
The author wrote

notification_object.object identifies change type, like a string "friendship" The actual reference to changed object with its extra data that I talk about is in notification_change.notificationObjectID

which doesn't seem to make sense to me. Object is a string (enum?) and notificationObjectID is a foreign key referring to the object the notification is about? Then how are the middle and right tables connected at all?
It seems that the middle table specifies what object (or type of object) the notification is about, e.g. an event or post. We can then have many entries in notification_change that point to the same object type, which allows us to bundle notifications (like "25 users posted on X's wall) - hence the 1:n relationship between the middle and right tables.
But why is there a 1:n relationship between the left and middle tables? Are we going to give "25 users posted on Sam's wall" and "Mary updated her "Friday Picnic" event the same notification ID? If all notifications for the same user have the same notification ID, why do we even need the table on the left?
A performance question - say John posts a comment on Mary's picnic event. It seems like we'd need to do a lookup to see if a notification_object already exists for Mary's Picnic before we created the notification_change entry. Is this going to negatively impact performance, or is it a non-issue? Continuing the questions from the previous paragraph, how would we know which notification entry to point the notification_object to?


texts: Switching out of one scheme into another

texts:
I have a table named dbo.messages.  We are expecting a lot of data in this table.  To keep things to a reasonable level we are looking at a weekly partitioning on that table.  What I would like to do is take the oldest weekly partition, once a week, and switch it out.  I have it set up that a each week will be on its own file/filegroup.  This in itself isn't a big deal.

What I'd then like to do is take that switched out table, and switch it into an Archive copy of the table.  Do I need a separate partition function and scheme for the archive table?  Are there any gotchas with this?


texts: How can I create a join on three tables, each from a different database?

texts:
Here is a simplified example of what I'd like to do:

SELECT a.create_time, b.create_time
FROM DOB.dob_state AS a
JOIN numID.numID_history AS b ON a.create_time = b.create_time;

I'd like to join THREE tables instead of just two (each in its respective database on the same server), and I'm not sure how to do that concisely. The third database.table is entity.entity AS c. Where do I add that into the join?
Moving to slightly more complicated...I actually want to match on three fields, each of which will be included in the tables of the three databases: hash (int), create_time (timestamp), and update_time (datetime). How would I do this in a cost-efficient way?
In designing the three databases, is it essential that I use the same hash ID in all three databases or is there a way to link the three hashes (each referring to the same record) to a master ID in another database? That is for a related issue that I'll mention elsewhere, but it might affect the query process, so I'm including that consideration as well.

I am joining tables that have actual relationships with each other, but separate databases is part of the security strategy, which also includes encryption, etc. I'm looking to eventually figure out a way to do this same thing with databases on different servers, but this is just a start.


texts: When I try OPTIMIZE TABLE  `table` (MyISAM) on a table which is about 300MB, then it is crashed and must be repaired. What could cause this problem? The same problem occurs on other tables over 300MB.


texts:
MySQL is crashing frequently and there is no error in the err log file.

The following error message was found in the event viewer:

  faulting application mysqld-nt.exe, version 0.0.0.0, faulting module mysqld-nt.exe, version 0.0.0.0 fault address 0X001176b8

MySQL version - 5.0.24
OS - Win2003 with 4GB Ram

Is there any problem with combination of MySQL 5.0.24 and Win2003?


texts:
CREATE TRIGGER new_trx_tmp_on_transaction_update AFTER UPDATE ON transaction
FOR EACH ROW
BEGIN
    IF NEW.trx_status = 1 AND ( NEW.cat_id = 'CASHBACK' || NEW.cat_id = 'REFUND') THEN
        INSERT INTO trx_tmp( `id`, `trx_id`, `user_id`, `trx_merchant_trxid`, `trx_amount`, `amount_left`, `cat_id`, `created_at`, `updated_at` ) VALUES ( NULL, NEW.id, NEW.userid, NEW.trx_merchant_trxid, NEW.trx_amount, NEW.trx_amount, NEW.cat_id, NOW(), NOW() );
    END IF;
END

I can't find where I'm doing wrong:

  [ERROR in query 1] You have an error in your SQL syntax; check the
  manual that corresponds to your MySQL server version for the right
  syntax to use near '' at line 5


texts: mysqldump can write to /tmp and /var/tmp but nowhere else

texts:
I have various mysql databases on different servers and at different versions. I'm using the same mysqldump command across Linux versions without issue except for this new Red Hat 7.6 server.

Command:

mysqldump --tab=/var/tmp/mysql/mysql.20190808/wikidb --opt --user=root --password=[password] wikidb

Prior to the mysqldump command:

mkdir -p /var/tmp/mysql/mysql.20190808/wikidb
chown -R mysql:mysql /var/tmp/mysql/mysql.20190808
chmod 777 /var/tmp/mysql/mysql.20190808

This is all scripted so no chance for a typo.

On all the older servers, and especially the older wiki servers this server is replacing, the command works successfully. I get a list of all the tables and .txt files for the database in the directory.

On the new server, I get the very first table, back_archive.sql, and then an error:

mysqldump: Got error: 1: "Can't create/write to file '/var/tmp/mysql/mysql.20190808/wikidb/back_archive.txt' (Errcode: 2)" when executing 'SELECT INTO OUTFILE'

I've tried various permissions including 1777 to give it a sticky bit just in case and it still fails. Ownership is confirmed as mysql:mysql and the database owner is mysql.

I'd done quite a bit of searching and reading of the man pages and everyone pretty much blames it on ownership or directory permissions but as noted above, I believe I've done it correctly, especially since it's a script and is working on every other server.

I will note this is my first master/master replication server. I didn't find anything specific regarding this sort of configuration but I thought I'd mention it.


texts:
I'm using mysql 5.5 and trying to enable Mysql safe update on my.cnf file

[mysq]
--i-am-a-dummy
-safe-update

it got reflected on root mysql login.

  SHOW VARIABLES LIKE 'sql_safe_updates'  is ON

But When I tried to use mysql user, the -safe-update is not working and also

  SHOW VARIABLES LIKE 'sql_safe_updates' show it is OFF.

How to enable mysql safe update for all users permanently?


texts: Are A or D series VMs better for deploying PostgreSQL on Azure?

texts:
In Azure there are VMs that have local storage either as spinning disk (A-series) or SSD (D-series).  Considering this space is wiped on reboot...

Is there a benefit to choosing the SSD option for PostgreSQL?
Can it be configured to use for temp/swap data even though it's wiped on reboot?
How would one need to condition this disk space before PostgreSQL's start?

I think it's worth noting (based on comment) that the install and application databases won't be installed on the local storage (which are wiped)... I'm only curious if there are bits that could take advantage of it (temporary databases/tables, swap space, etc) and what would need to be configured to initialize/re-initialize any directories that would/could be used as such.

The same questions that would apply if one were to have say a RAM-Drive (that is not permanent).


texts: Automatically convert views to underlying query

texts:
I've been moved onto a new project which uses numerous VIEWS in MySQL (5.7) queries. From what I understand, Views in MySQL are not the most performant and shouldn't be used for the sake of saving a bit of typing. I'm trying to profile the performance so I have an idea of whether or not it's a bottleneck but I'm having trouble trying to unravel a view to it's underlying query.

I can see the SQL query for the view but then that SQL contains its own view lookup and there's a chain. Is there a way, ideally using MySQL WorkBench, to have the merged view outputted so I don't need to do it by hand? There can be chains of 4 or more views and my head is just confused trying to put the parentheses in the right places around the right SELECT...WHERE/JOIN queries and I'm sure there's a way to automate this.

Thanks


texts: postgresql bgworker SIGTERM handler called twice

texts:
Im using postgresql 10.1 on Mac.

I have a trouble in terminating a background worker using SIGTERM. In my project, im creating a new background worker using RegisterBackgroundWorker(). i have included a test_function, as a signal handler for SIGTERM. No other signal handlers are defined.

pqsignal(SIGTERM, test_function)

when i issue pkill -15 postgres, i see that the handler is being called twice. Why does it happen? Am i missing anything??


texts: SQL Server Database Experimentation Assistant Analysis Reports

texts:
I am using the Database Experimentation Assistant v2.6 to capture a workload using extended events (.xel) on SQL Server 2017 that is installed on an Azure VM.  I'm then replaying it on a Azure SQL Database Managed Instance.  The capture and the replay work great. I'm getting multiple .xel files from the replay, 15 from the VM and 7 from the Managed Instance.

My issue is when I create the analysis report it only allows me to select 1 .xel file in each of the the "Trace for Target 1 SQL Server" and "Trace for Target 2 SQL Server" fields to compare.  My expectation is that I can run one report for the entire replay by selecting the directories the files are in but this does not appear to be an option?  When I try to select just the directory it will not allow me to exit the directory window.  Below is the screen I'm referring to :

I've looked through multiple articles but the only ones I've found, including this one from Microsoft, refer to selecting only one file and normally a .trc file not a .xel file.

How can I compare multiple .xel files at once?


texts: Best Way to Migrate 100 tables To New Database

texts:
I need to migrate 100 out of 150 tables (data and structure) and everything else (sprocs, views etc.) to a new empty SQL Server database.

I have been using the Import/Export wizard and it has been causing me headaches.  First, I scripted out and created all the tables (because the import wizard will not create constraints) then I imported the data.  Some of the data did not match up so I used Toad data sync to sync it from the source.  I have pretty much got everything fixed but there has to be a better way to do this and here are my thought.  I would love to get some feedback on the pros and cons of each method:

  1.Restore Backup of database to target database and delete the tables I do not need

This would be the fastest method but I wonder will SQL Server utilize the space from the deleted tables? Or is there a way to make it?

  2.Restore Backup to Target database and create a new file group.  Then, move all the table clustered indexes over to the new file group and delete the old one.

Are there any issues with doing this?  If I just moved the sys and users tables to the new file would that be enough?  Anything else need to be copied over?


texts: InnoDB Foreign key updating vs. manual in-application updating

texts:
I've been working on a "legacy" schema in MySQL Workbench that uses a lot of relations. All of the tables are MyISAM, however, and there are no foreign keys.

However, I'm working on a new area of the schema now, and am wondering:

Would it be a good idea to force InnoDB and foreign keys for their ON UPDATE/ON DELETE triggers, or to implement the cascading logic in the application itself?

My super/the CTO seems to be remarkably inflexible when it comes to migrating to InnoDB/FKs (or using a real database construction program instead of phpMyAdmin) and I'm not sure if the gains for having auto-updating would be worth fighting over.

Probably the worst part about this issue is that if I went the application-side route I'd have to add code outside of the specific module, which would probably involve more issues and difficulty -- the system is already pretty complex as it is.


texts: Read mysql config (my.cnf) using mysql console

texts:
I have a mysql server that is not under my control (I don't have ssh on that machine). I only have access to one database using mysql client.

How can I have access to config options that are declared in my.cnf?


texts: mysqldump on slave (without stopping replication)

texts:
Is it okay to run mysqldump --single-transaction on Slave DB without stopping replication (stop slave;)? This is for regular daily backup so we can restore Master DB in case of a storage disaster. Most of the tables are InnoDB, only a couple of them are MyISAM.

Official documentation says that:

  "You should stop replication on the slave before starting the dump
  process to ensure that the dump contains a consistent set of data".

What does it mean consistent data? Does it mean like latest data?

I know --single-transaction means that it takes data from the time it was issued, so new further changes while the dump was happening will not be recorded to the dump. Am I correct?

Do not want to run the backup on master due to lag it creates on our application.


texts: Multiple Instances Of Oracle Or One Instance With Multiple Schemas

texts:
This is on a dev server for multiple different envs of one application.

e.g. a copy of production as of last night, a copy of production as of a week ago,  etc etc

Does it make more sense to have 1 instance of Oracle with 8 schemas (one for each application env) or for 8 instances each with 1 schema?

We are doing backups with datapump instead of RMAN.

The key difference to this question is that in this case it is one application team with multiple instances/versions of their application and that is talk about multiple applications and also RAC.


texts:
As a user with admin privileges in postgres, I can psql database_name and then invoke \dt to see a list of relations.  As the same (unix) user, I execute psql -h localhost -U non-privileged-user table_name, and \dt reports NO relations found.  The only host entries in pg_hba.conf are of the form host    analytics             all             127.0.0.1/32            md5 (where 127.0.0.1 is the first entry).

Where are the relations?  Why are they not visible to the alternate user.  What schema tables should I look in to debug.  Also, note that grant select on table_name to non-privileged-user works just fine.


texts: Cannot revoke permissions or drop user in PgSQL AWS RDS

texts:
I want to drop the user asd from the DB. So, I've got two DBs: foo and bar and the user apparently has dependencies in both. The DBs have been created from the root user postgres, but all tables and sequences inside have been created by users foo_migration and bar_migration respectively.

First I log in as the postgres user to the foo DB. When I try to drop the user, I get:

ERROR:  role "asd" cannot be dropped because some objects depend on it with details about privileges from multiple sequences, tables, the bar DB and a few objects in the postgres DB.

So, I try to clear things one by one (while still logged in as postgres user in the foo DB). I first try to REASSIGN OWNED BY asd TO foo_migration;. This returns ERROR: permission denied, which is weird because I'm logged in as the postgres user. Anyway, then I try

REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM asd;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM asd;

and I get a bunch of WARNING: no privileges could be revoked for all the sequences and tables that the user has rights on. I also try DROP OWNED BY asd; which also ends in ERROR: permission denied.

This was all unsuccessful, so I try logging in the postgres DB as the postgres user and perform the same steps. The REVOKE commands execute successfully without warnings, but no permissions actually get changed/affected. The REASSIGN and DROP OWNED BY still result in ERROR: permission denied.

The error messages are bare. There's no hints or details provided alongside with them.

I do not have the password for the asd user, so I cannot try to log in with it and perform any actions.

Do you have any ideas of how to approach this? Do I need to look into some tables that show ownerships and go from there? Isn't the postgres user the admin user that is "almighty" ?


texts: MySQL uses INDEX for optimizing ORDER BY with GROUP BY except when you add a JOIN

texts:
Question

Why can't Query #2 use the same (car_trims.horsepower_peak) index to optimize the sorting of the rows as Query #1? The only difference between the two queries is the addition of the JOIN in Query #2.

car_trims ~50k rows

PK: (car_trims.id), index on (car_trims.horsepower_peak)

car_makes ~100 rows

PK: (car_makes.id)

Query #1

SELECT car_trims.*
FROM car_trims
GROUP BY car_trims.id
ORDER BY car_trims.horsepower_peak DESC
LIMIT 0, 200

Execution time: .0026 seconds

EXPLAIN:

Query #2

SELECT car_trims.*
FROM car_trims
STRAIGHT_JOIN car_makes ON car_makes.id = car_trims.make_id
GROUP BY car_trims.id
ORDER BY car_trims.horsepower_peak DESC
LIMIT 0, 200

Execution time: .2533 seconds

EXPLAIN:

UPDATE:

I've been continuing to work on this and I believe the index is not being utilized in Query #2 because of the mixing of GROUP BY and ORDER BY. According to the MySQL Docs,

  "In some cases, MySQL cannot use indexes to resolve the ORDER BY ...
  [for example, when] ... the query has different ORDER BY and GROUP BY
  expressions."

Query #1 does mix GROUP BY and ORDER BY and so theoretically the index should not be used according to the docs but I believe that may not apply if the GROUP BY is being ignored entirely due to only 1 table being selected from and grouping on the primary key.

Also, my actual original query is not quite as simple as the example provided here. The crucial difference: usage of GROUP_CONCAT in the SELECT requiring the aforementioned GROUP BY in order to prevent grouping on all rows (i.e. getting a 1-row result). The solution to that issue is using a DEPENDENT SUBQUERY, as discussed here: https://stackoverflow.com/questions/7381828/indexing-with-group-by-order-by-and-group-concat