texts:
Once and then our SQL Server will "hang" during its weekly full backup. When this happens, external connections start to timeout. The symptoms are:

Connections timeout
The sqlservr.exe process sits at usual memory levels (47gb, 12gb left to OS), but ~0-5% CPU (normal = ~30%) with the occasional spike of ~25% for a few seconds, then going back down to 0-5%.
I can connect to the database using SSMS, but no queries respond, can't get list of databases, can't access DMVs - while not timing out, I get no results at any point. Same with DAC connection.
Nothing else running on the server, it's a dedicated SQL box.

The server is an i2.2xlarge EC2 server with SQL 2012 Standard Edition, running on Windows Server 2012 R2.

Our backup schedule consists of daily differentials and weekly fulls, and this issue has always occurred during the full backup. By simply going through the backup files on disk I can see that it completed a couple of smaller databases (<200MBs) and then never completed one of the bigger DBs (~100GB, typical compressed size = 10GB).

While this is going on, I can remote into the machine just fine, network is good and all disks seem to perform nominally.

The timeline of what happens looks like this:

0300AM: DBCC CHECKDB is run on all databases, all OK.
0345AM: BACKUP is started on the ~100GB database.
0347AM: SQL Server has encountered 1 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [XXX] in database id 7.  The OS file handle is 0x00000000000010D4.  The offset of the latest long I/O is: 0x000002dbbd0000
Repeat ~20 times
0351AM: Time out occurred while waiting for buffer latch -- type 2, bp 0000000BE55A9CC0, page 1:1864895, stat 0x40d, database id: 102, allocation unit id: 7493989815628529664, task 0x0000000B91125088 : 0, waittime 300 seconds, flags 0x1a, owning task 0x0000000B91125088. Continuing to wait.
Repeat ~5 hours & 500k times

The timeouts are not just from the database being backed up, but from all databases. Usually this backup procedures finishes up in 1-2 hours.

After about 5 hours we give up on it, as all external connections are failing. Attempting to stop SQL Server from the service control panel simply hangs, trying to stop. Attempting to kill the process results in an "Access denied" message - we usually have full access to kill the process, and it's been tested afterwards - the Access Denied message only comes during these events. After some time we have to reboot the machine physically as we can't get rid of the stuck sqlservr.exe process. Once the machine comes up, all is running flawlessly and all CHECKDB's come back clean. We the initiate a new full backup and it completes as expected.

Looking at the Windows events logs, around 0347AM we see "xenvbd" logging entries, though with no description. We also see "disk" logging entries - The IO operation at logical block address 0x14a4e070 for Disk 2 (PDO name: \Device\00000033) was retried. These events continue for the duration - ~5 hours.

Disk 2 is our data drive. Disk 3 is our backup drive, but we see no mentions of it in the log. Both drives are SSD EBS drives.

So far my theory is that the EBS drives get clogged up causing IO to bunch up severely. However, even so, I don't understand why that'd cause SQL Server to somehow get stuck on itself, not responding to anything. When I remote into the server, while this is going on, I can access the disks justs fine. Could a huge backpile of IO requests be causing this? Am I missing some crucial EC2 tuning settings? Any tips on what to do, besides moving off of EC2? I'm pretty sure the root cause is EBS, but seeing as this is working flawlessly 99.99% of the time, I'd really like to find a way to avoid this issue.


texts:
I'm attempting to modify an existing query that pulls the most recent order from a set of customers and provided order_type.

The current query works fine (albeit a little slow, all columns below are indexed):

SELECT
    DISTINCT(td1.product_id),
    td1.customer_id,
    td1.date_test_added,
    tdmi.product_type_id
FROM
    order_history AS td1
    LEFT JOIN order_history td2
    ON td1.customer_id = td2.customer_id AND td1.date_test_added < td2.date_test_added
    LEFT JOIN order_types tdmi
    ON tdmi.product_id   = td1.product_id
WHERE
   td2.date_test_added IS NULL
   AND tdmi.product_type_id = 31
   AND td1.customer_id IN (91000, 91001, 91002, 91003)

If I change the less-than operator in the first join to greater than, no results are returned.

What is the best way to change this to pull the earliest order (based on date_test_added)?


texts:
I am trying to create a Trigger that will fire when a new student is being added to the table. It should update the a table called: MAJOR, by incrementing its MCOUNT domain by one.

CREATE TABLE STUDENT(
SID     CHAR(7),
SName       VARCHAR2(20),
SMajor      CHAR(3)
CHECK (SMajor in ('CSC', 'MIS', 'TDC')),
CONSTRAINT PK_STUDENT
PRIMARY KEY (SID)
);

CREATE TABLE MAJOR(
MName       CHAR(3) PRIMARY KEY,
MCount      NUMBER(3) );
INSERT INTO MAJOR VALUES ('CSC', 0);
INSERT INTO MAJOR VALUES ('MIS', 0);
INSERT INTO MAJOR VALUES ('TDC', 0);
SELECT * FROM MAJOR;
COMMIT;

My trigger:

CREATE OR REPLACE TRIGGER addingS
AFTER INSERT ON STUDENT
    FOR EACH ROW
    DECLARE
        counter NUMBER (3);
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Adding student.');
        counter := counter + 1;
        UPDATE MAJOR
        SET MCOUNT = counter
        WHERE MCOUNT = 0;
    END;
    /

It is not updating, now my MCOUNT's value is empty, why is that?


texts: How to copy SQL Server log shipping files to AWS EC2 instance

texts:
I am using SQL Server 2008 R2 and would like to log ship from my local data center SQL Server instance to a log shipping target SQL Server running in AWS EC2.

What is the best way to copy the log ship files from the local SQL Server to the remote EC2 instance?

I have seen plenty of 'how to copy a file to EC2' solutions, but am wondering what is the best method for continuous unattended file copying (such as SQL Server T-Log files every 15 minutes).


texts:
I was implementing a code that involves a database and buckets. Just wanted to ask what is bucket and can someone describe it with an example?

Thanks!


texts: Do MyISAM tables crash from a "clean" mysqld restart?

texts:
I've researched this topic a little bit and available information indicates that MyISAM tables should not crash on their own from a simple server restart. Usually the following causes are put forth:

hardware bug
server terminated abruptly (e.g. SIGKILL)
system-level issues, such as OOM scenarios, out-of-disk space, or system panics

However, on my environment, a "normal" mysqld shutdown and restart operation has been seen to occasionally cause tables to crash. No other anomalous condition or system-level error was present. The tables are almost always the ones that are hit with the most amount of queries. The server is practically executing queries against these tables continuously, and not all of them are DML queries. The tables are frequently altered, optimized, and so on. However, in all cases, the server is shut down by issuing a SIGTERM, and waiting until it exits on its own, with an exit code of 0. It is never abruptly terminated.
The tables are subsequently successfully repaired with a "REPAIR TABLE" query, but this doesn't tell me why the problem happened to begin with.
Changing the architecture to avoid this problem is infeasible, so I need to know why it happens. Is MyISAM inherently subject to corruption upon server shutdown?


texts: When using blitzindex should are the reads the number of times the index was used to read or is that the op stats?

texts: Im using blitz index to identify some indexing issues.  Im concerned that I am reading the output incorrectly.  In my output, im seeing reads, compared to writes in the usage column, and then im seeing a lot more reads in the op stats column.  Which column tells me how many times the index has been used for returning data?


texts: Eliminating PostgreSQL/database network latency: PL/Java stored procedure VS. Java code running on localhost

texts:
A couple years ago, I learned the hard way that having my Java application sitting on one server, with the database sitting on another, and having complex transactions that issue lots of queries each, results in very poor performance due to network latency that keeps adding up.

In my specific case, a single DB transaction can possibly generate hundreds of subqueries/commands or even more, with data going back-and-forth between the application server running Java and the database server running PostgreSQL until the transaction is committed. The network millisecond delays add up big time.

Since then, I have been working with a database that allows me to send my entire transaction's logic, programmed in JavaScript, to the database server so that it is executed remotely there, and only the final result is returned, ensuring that there is only one network back-and-forth between the two servers. I like that approach.

I am considering working with PostgreSQL again as my main database, yet I still want to be able to follow the same approach where the entire transaction's logic is executed on the database server for one network back-and-forth only, no networking delays in-between. And I want to be able to program my transaction logic in either Java or JavaScript -- SQL won't do.

So when it comes to PostgreSQL, it seems like I have two major options.

A) I can either install a stored procedure extension such as PL/Java or PL/v8 and program my transaction logic as functions within the database, or
B) I could implement a Java/JS-based intermediary program that would sit on the database server, receive remote requests through some messaging layer, perform the transactions and their logic against the database on localhost directly, and return the final result.

I would largely prefer to go with B), but I'd like to ask more experienced professionals here if I can expect to eliminate network latency just as much with that option as I would with A).

So, the question is: if my intermediary Java/JS software, sitting directly on the database server, is connecting to PostgreSQL on localhost strictly (not even another server on LAN), are there any networking or others delays imposed that I would otherwise not get if my logic was written within PostgreSQL functions instead?

Is there any overhead I am adding if I go with B) instead of A)? (excluding overhead caused by my messaging/RPC layer that I'd build)


texts:
Table albums has (among other field) field id.

Table photos has column id, field album which is a foreign key referring to album id and some other fields (which are irrelevant for the question I ask).

So in an album there is several photos (photos in an album are ordered by id of a photo).

Now I introduce one more "level of indirection": Bunches. There may be several albums in one bunch.

For this I add fields bunch and seq INT UNSIGNED into the table albums. bunch is the ID of the bunch where the album belongs and seq is the number of the album in the bunch.

Now the problem:

Let it is given a bunch ID.

I want to make a SELECT query which selects all photos from albums belonging to the given bunch, ordered first by seqs of the albums and then by IDs of photos in the album.


texts: Removing Database from sharded cluster in Mongo DB

texts:
I Have a mongo shard db system with two shards and and three mongos servers.My application creates DB on daily basis as per our business requirements. We have some sharded collection in each day DB.

I removed some old DB using db.dropDatabse(). Now when i connect to mongos server and run show dbs these databases are not there but when i check config database it still has entries for these database in in different collections. Like database,changelog,chunk

Could you please tell me how i can permanently delete these database


texts: MySQL 1175 Error While Using Primary Keys

texts:
What would cause the error 1175 that starts out with: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column.... The problem I have is that I did include a WHERE clause with key columns: WHERE a.a_id = b.b_id

a_id and b_id are the only key columns in their respective tables.

START TRANSACTION;
    UPDATE table_a a, table_b b
    SET a.update_me = b.update_from_me
WHERE a.a_id = b.b_id;
SELECT * FROM a;

What am I missing?


texts: Ram consumed by single query in Postgres using SqlAlchemy

texts:
I have a list of 36 queries ranging from low complexity(No joins) to high complexity(joins with subquery). I want to find the RAM Consumed by each query.

This is required for performance testing for the application which I am building.

Can anyone please help with the same?

I am creating a SQLAlchemy session object and executing raw queries directly.


texts: Salvaging data from corrupt tables PostgreSQL

texts:
Am in the process of salvaging  very crucial data from a corrupted database.

Am getting the following error from a very crucial table
ERROR:  invalid page header in block 94206 of relation base/11974232/11975439

when I set zero_damaged_pages to on , I realized I end up losing alot of pages which contain data that's significant to a meaningful recovery.

so my question is:-

1.How do I maximize data recovery without loosing the pages?

when I do a query on some tables, the connection gets lost before the query executes. usually the query runs for few seconds before the disconnection. How do I deal with this


texts: SqlServer: can one table's PK be the FK of two table's?

texts:
I have three tables project, users and proj_user. The PK of proj_user table is the FK of project and users table's pk e.g.(project_id,user_id)

My question is, is it possible if the project_id and user_id's data is different

like project_id = '12345' and user_id = 'abc987'

Please reply

kind regards Vivek


texts: What file formats can be restored using pg_restore on PostgreSQL?

texts:
first question here, fairly new to DB's and Data itself.

My question is fairly simple, after playing around with Postgres, PGAdmin3, I realised I would like to access some public data, but I struggled to find any publicly available .tar files to practice with. Leading me to wonder what file formats you can restore from.

What file formats can I restore from using pg_restore? And is there a preference in the community towards certain formats?

Thanks for your time. Look forward to participating on the forum.

PS: If it wasn't clear, I am restoring from command line.


texts: SQL Server table name starting with # in user database, not in tempdb, not a temp table

texts:
Somehow, decades ago, a table got created in our database that starts with a #. It shows up in Object Explorer under the app's database, not in tempdb. For some reason, Azure won't import the database like this.

We can't drop it, rename it, or interact with it. I've tried Delete from Object Explorer, Script Drop, Rename from the GUI and none of them have worked.

We're on SQL 2008 R2.

drop table [*app*].[dbo]."#OBSOLETE";

Database name '*app*' ignored, referencing object in tempdb.
Msg 3701, Level 11, State 5, Line 1
Cannot drop the table '#OBSOLETE', because it does not exist or you do not
have permission.

exec sp_rename "dbo.#OBSOLETE", "dbo.obsolete"

Msg 15225, Level 11, State 1, Procedure sp_rename, Line 338
No item by the name of 'dbo.#OBSOLETE' could be found in the current database '*app*', given that @itemtype was input as '(null)'.

How do we kill this object so we can migrate this to Azure?


texts: "Wrong affix file formate for flag" while loading dictionaries from LibreOffice in PostgreSQL 9.3

texts:
Trying to get good TEXT SEARCH results out of PostgreSQL in English, German and Dutch made that I downloaded proofing tools of LibreOffice (tried of version 4.3 and 3.3) and iSpell. I placed the files in the "tsearch_data" dir and renamed them so PostgreSQL would find them. I now have:

en_us.dict, en_us.affix, nl_nl.dict, nl_nl.affix, de_de_frami.dict, de_de_frami.affix

All of them give the same sort of errors while creating a dictionary in PostgreSQL 9.3 like so:

CREATE TEXT SEARCH DICTIONARY test_ispell (template = ispell, Dictfile = en_us, affFile = en_us, stopwords = english);

ERROR:  wrong affix file format for flag
CONTEXT:  line 2428 of configuration file "/usr/share/postgresql/9.3/tsearch_data/en_us.affix": "COMPOUNDMIN 1"

ERROR:  wrong affix file format for flag
CONTEXT:  line 2533 of configuration file "/usr/share/postgresql/9.3/tsearch_data/en_us.affix": "SFX 123 N 1"

ERROR:  invalid byte sequence for encoding "UTF8": 0xc4 0x62
CONTEXT:  line 18 of configuration file "/usr/share/postgresql/9.3/tsearch_data/de_de_frami.dict"

ERROR:  wrong affix file format for flag
CONTEXT:  line 604 of configuration file "/usr/share/postgresql/9.3/tsearch_data/dutch.affix": "SFX Na N 1"

It got pointed out to me that the first error appears to be a wrongful one as can be found in its man page: http://linux.die.net/man/4/hunspell

COMPOUNDMIN num
   Minimum length of words used for compounding.  Default value is 3 letters.

Update: COMPOUND Hunspell functions can can't work in PostgreSQL see: http://www.postgresql.org/docs/current/static/textsearch-dictionaries.html#TEXTSEARCH-ISPELL-DICTIONARY

The SFX syntax, is beyond me. The invalid byte sequence must be fixable as well I presume if I somehow manage to tell PostgreSQL the file type, yet I have no clue how to do that.

Anyone got any idea how to load these files?

Update:

The Dutch dictionaries provided with Debian still produce errors this is part of the Myspell package, and I can't get it to work. Anyone any success with that?

For now I will try and file a bug report to Debian since it is a function that should be working.


texts: postgres pg_xlog explodes during set tablespace

texts:
I'm running postgreSQL 9.6.0 on CentOS 7.2. Occasionally I need to shift data on to a larger tablespace to prevent /var from filling up. When this is running the pg_xlog directory grows rapidly, and recovers very slowly. Periodic CHECKPOINT's subsequently help to kick the xlog clean up into action. However, this laborious  severely limits the amount of data I can shift in a short space of time. Upgrades and restarts are very tricky since the database records continuous long running collections from various data sources.

My gut feeling is that SET TABLESPACE prevents updates from WAL being committed, but this doesn't make much sense...

Any help or insight greatly appreciated!


texts: Automating the creation of postgres database

texts:
I'm trying to automate the creation of a postgres database running on windows 10.
I'd like to be able to start from a default installation of postgres and automate the following steps (not necessarily in this order)

drop and re-create new database
drop and re-create new tablespaces
drop and re-create new users.
build database objects (tables, views, procedures, triggers etc)
grant users required permissions.
populate tables with initial data.

It's not too difficult to reduce this to a series of database commands (so long as they're executed in the correct order) the problem is that for few commands I keep encountering the error 'cannot be issued inside a transaction block'. This means that some commands like DROP DATABASE IF EXISTS and CREATE TABLESPACE can only be run (as far as I can tell) by manually single-stepping them, whereas I can call my table-creation scripts as a single block and re-create a 100 tables in a fraction of a second.
I'm not too worried about the extra few seconds I'm wasting by single-stepping some of the individual commands, but by doing some of it manually I run the risk of missing steps and making mistakes.
How can I automate (i.e. write scripts for) the whole process?

Update, script looks a bit like this...
DROP DATABASE IF EXISTS ????

DROP TABLESPACE IF EXISTS ????
DROP TABLESPACE IF EXISTS ????

ALTER DEFAULT privileges IN SCHEMA public REVOKE SELECT, INSERT, UPDATE, DELETE ON TABLES FROM ????;
ALTER DEFAULT privileges IN SCHEMA public REVOKE ALL ON FUNCTIONS FROM ????;

DROP USER IF EXISTS ????;
DROP USER IF EXISTS ????;
DROP USER IF EXISTS ????;

CREATE USER ???? PASSWORD '????';
CREATE USER ???? PASSWORD '????';
CREATE USER ???? PASSWORD '????';

CREATE TABLESPACE fastspace OWNER ???? LOCATION 'c:/????/????';
CREATE TABLESPACE slowspace OWNER ???? LOCATION 'd:/????/????';

DROP DATABASE ????;

CREATE DATABASE ????
  WITH OWNER = ????
       ENCODING = 'UTF8'
       TABLESPACE = fastspace
       LC_COLLATE = 'English_United Kingdom.1252'
       LC_CTYPE = 'English_United Kingdom.1252'
       CONNECTION LIMIT = -1;

GRANT CREATE ON DATABASE ???? TO ????;

COMMENT ON DATABASE ????
  IS '????database';

GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA public TO ????;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO ????;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO ????;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO ????;

ALTER DEFAULT privileges IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO ????;

Many (most) of the above commands can be run as part of a transaction block, but a few can't (dropping and creating the database for example).


texts: How to write case when in where statement in Oracle

texts:
I wrote below statement. In MySQL it executes ok, but in Oracle it fails with error:

  meg:ORA-00905: missing keyword

How should I write this SQL?

SELECT count(*)
FROM  userTable
WHERE ( CASE WHEN mac IS NULL THEN mac IS NULL ELSE mac = '000fe95erd32' END )


texts: I've searched here and on google and have been unable to find any PBIRS info for a DD or ERD. Does anyone have or is aware of a PowerBI Report Server Data Dictionary or Entity Relationship Diagram?


texts: adding fields to 2LIS_12_VCITM datasource in ECC

texts: From what I have read the datasource 2LIS_12_VCITM is built off of the ECC tables LIKP, LIPS, VBUK, and VBUP. I would like to add a couple of fields that are in the LIPS table to this datasource and am wondering on the correct way to do it. Do I simply add the fields to the extract structure of the datasource under the include section for the LIPS table?


texts: Query slower with index on 14M rows table

texts:
DB: Amazon RDS MySQL (OS: Linux, 2 vCPU, Memory: 8GB)

I have a table with almost 14M rows of data.

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`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

As you can see, I use an index on meterid.

Another table which stores device IDs (around 100 rows of data)

CREATE TABLE `devices` (
`Id` bigint(20) NOT NULL AUTO_INCREMENT,
`meterid` varchar(16) DEFAULT NULL,
`location` varchar(8) DEFAULT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `meterid_UNIQUE` (`meterid`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

To get 15 minute aggregated data, I use the below query

SELECT AVG(metervalue) as value
 , DATE_FORMAT(date_time, "%d %b %Y %H:%i") as label
FROM meterreadings
WHERE meterid IN (SELECT meterid from devices)
  AND date_time BETWEEN '2018-07-23' AND '2018-07-24'
GROUP BY DATE(date_time), HOUR(date_time), MINUTE(date_time) DIV 15
ORDER BY date_time ASC;

Query performance is very bad - It takes approximately around 12 seconds to execute, and causes a temporary spike in DB server usage as well.

EXPLAIN on this query returned this:

1   SIMPLE  devices index   meterid_UNIQUE  meterid_UNIQUE  19      125
   Using where; Using index; Using temporary; Using filesort
1   SIMPLE  meterreadings   ref meterid meterid 19  devices.meterid 322
   Using where

I dropped the index on meterreadings and surprisingly the query performance is better - almost about 6 seconds now. I am still wondering why?

EXPLAIN on the query after dropping the index

1   SIMPLE  meterreadings   ALL                 14580167    Using where;
Using temporary; Using filesort
1   SIMPLE  devices ref meterid_UNIQUE  meterid_UNIQUE  19
   meterreadings.meterid    1   Using index

I am currently doing my query operation on the table without index - Is there a way I can optimize the table / query to do the operation faster (like a composite index on two columns?)

[The table is growing approximately by around 40 rows per second]


texts: Set TRUSTWORTHY On smacks down service broker from working

texts:
I've got a database running on a SQL-Server 2012 DBMS. Consider that the server running SQL Server is not part of the network domain.
When I execute this sql command

ALTER DATABASE myDatabase SET TRUSTWORTHY ON;

a service broker queue (early setted and working) receives messages but the activeted stored procedure associated to the queue doesn't start. Messages continue to flow in to the queue, but nothing else happens.
In the SQL SERVER log I've found this message

  The activated proc 'proofSchema.mySP' running on queue 'proofSCHEMA.myQueue' output the following:  'The database owner SID recorded in the master database differs from the database owner SID recorded in database 'myDatabase'. You should correct this situation by resetting the owner of database 'myDatabase' using the ALTER AUTHORIZATION statement.'

If I run this same configuration on a machine inside a network domain, everything works.

I can't understand what is happening and why trustworthy crush with servi broker queue.


texts: A simple question regarding database design that has always bothered me

texts:
Say I have a table of schools, and each school is either Public, Private, or For-Profit. As well, each school is either '2-4 Year,' '4+ Year', or 'Graduate'. My initial instinct in this situation is to create another table that holds attributes, then create a junction (many-to-many) table to map a school to its attributes. This seems simple enough, but every time I am faced with this situation my stomach begins to turn and the following two questions always arise:

Is it worth it? What reason really is there not to just store the text (Public, Private, For-Profit) in a column in the school table?
The other issue is in naming. Say the first table is named school, the attribute table is named school_attribute (assume there are also attributes that apply to other things, e.g., companies), then I am stuck naming the junction table school_school_attribute, and that just sounds terrible. The naming problem often leads me to not normalize my table, even though I know that's the wrong thing to do. However, I'd almost rather be stuck with a database that is not 3NF than redundant naming in my tables.

So, first I'd like to know if it is even worth it, and for what reason (e.g., it saves space); second, I'd like to know how people name their tables to avoid redundancy.

EDIT

Another option would be to create a table called attribute with a column named type. The type column would tell me what type of attribute it is (e.g., school, company, etc). Wait, no reason to store the text in the column, let me create a table called attribute_type, and now a junction table named attribute_attribute_type... Aggg! This is my biggest block in database design for some reason.


texts: Performance considerations for Caching Aggregate counts

texts:
We have an InventoryActivity table that holds transactional changes to item quantity:

CREATE TABLE dbo.InventoryActivity(
    InventoryActivity_uid int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Organization_uid int NOT NULL,
    MasterInventory_uid int NOT NULL,
    AdjustmentType_cd varchar(20) NULL,
    AdjustmentReason_cd varchar(20) NULL,
    Quantity int NULL
)

We would like an InventorySummary which should aggregate to the current quantity for each. The summary counts should always be derivable from the the sum of the transactional records, but we have several different approaches on how to calculate aggregate counts:

Stored Procedure
Separate Summary Table
Indexed View

What performance considerations should tip the scales in favor of a particular strategy?
What best practices exist?  *(I know best practices borders on discussion, but I want to know what considerations would come into play to help make the decision)

Here's a fiddle with some sample code and data

Stored Procedure

Simplest option is just perform the SUM operation fresh everytime. But involves no caching and would likely cause performance issues over time.

CREATE PROCEDURE dbo.GetInventorySummary
AS
SELECT Organization_uid,
       MasterInventory_uid,
       SUM(Quantity) AS Quantity
FROM dbo.InventoryActivity
GROUP BY Organization_uid, MasterInventory_uid

Separate Table

We could create a table to store the current quantities.  The plus side is fetching this data would be trivial.  The downside is we would have to manually maintain it and keep the records in sync everytime we do a write to the InventoryActivity table.

CREATE TABLE dbo.InventorySummary(
    Organization_uid int NOT NULL,
    MasterInventory_uid int NOT NULL,
    Quantity int NOT NULL,
    PRIMARY KEY (Organization_uid, MasterInventory_uid)
)

Triggers could help alleviate some of that maintenance.

CREATE TRIGGER dbo.InventoryActivity_I
ON dbo.InventoryActivity
AFTER INSERT
AS

    CREATE TABLE #InsertSummaryTemp
    (
      Organization_uid int,
      MasterInventory_uid int,
      Quantity int
    )
    INSERT INTO #InsertSummaryTemp
    SELECT Organization_uid,
           MasterInventory_uid,
           SUM(Quantity) AS Quantity
    FROM INSERTED
    GROUP BY Organization_uid, MasterInventory_uid

    -- UPDATE EXISTING RECORDS
    UPDATE InventorySummary
    SET Quantity = s.Quantity + i.Quantity
    FROM InventorySummary s
    JOIN #InsertSummaryTemp i ON s.Organization_uid = i.Organization_uid AND
                                 s.MasterInventory_uid = i.MasterInventory_uid

    -- INSERT NEW RECORDS
    INSERT INTO InventorySummary
        (Organization_uid, MasterInventory_uid, Quantity)
    SELECT i.Organization_uid, i.MasterInventory_uid, i.Quantity
    FROM #InsertSummaryTemp i
    LEFT JOIN InventorySummary s ON i.Organization_uid = s.Organization_uid AND
                                    i.MasterInventory_uid = s.MasterInventory_uid
    WHERE s.MasterInventory_uid IS NULL

Indexed View

Borrowed from this and this we could create an Indexed View.  Which lowers the maintenance cost associated with option 2.  However there is a concern about performance that we are still aggregating all records through the entire history.  As opposed to a simple read from a table.

CREATE VIEW dbo.InventorySummaryView
WITH SCHEMABINDING
AS
    SELECT
      Organization_uid,
      MasterInventory_uid,
      SUM(Quantity) AS Quantity,
      COUNT_BIG(*) AS Count
    FROM dbo.InventoryActivity
    GROUP BY Organization_uid, MasterInventory_uid
GO

CREATE UNIQUE CLUSTERED INDEX PK_InventorySummaryView ON dbo.InventorySummaryView
(
      Organization_uid,
      MasterInventory_uid
)


texts: Mysql: How does circular replication avoid infinite looping of a transaction

texts:
In case of circular replication A -> B -> C -> A.

How come a transaction started on A will be ignored from being replicated from C to A at the end of one complete cycle ?

I believe parent server-id will be logged along with the transaction and it prevents the transaction being executed on the parent server.

Please correct me if i am wrong.

Thanks in advance.


texts: we have move some SQL Server from Physical to AWS EC2 - my question is regarding Max Memory Setting fro SQL Server instance - I used to change the memory setting in Physical server giving some to OS and some to SQL Server but I am not sure for EC2 - do we have to use the same logic like 10-12% to OS and rest to SQL server in AWS Ec2  or we can keep it default .


texts: How do I copy from one meta_key to another meta_key in same table?

texts:
I am trying to copy the address to the billing address. Both are located in the wp_usermeta table†. I picture something that looks similar to this:

update wp_thafxavhs9_usermeta(my table)
set billing_address_1(meta_key want copied to) = address(meta_key want copied from)

but I know I'm missing something because they are meta_keys. Also, I am trying to do this for all users and I'm using phpMyadmin

Do I need to do anything special to have it copy for all users? But yeah, that's all I'm trying to do. WooCommerce pulls from the billing_address field to populate the forms, so I figured it would be easy since I already have the address.

When I tried:

update 'wp_thafxavhs9_usermeta'
set billing_address_1 = address

I got the following error message:

  1054 - Unknown column 'billing_address_1' in 'field list'

Clearly the error message says the field billing_address_1 isn't there.   (This I just copied from the database, unless I typed something wrong in my sql...)

I have updated my registration form that new members fill out when joining the site, so it captures all that for new members.

Here is the table schema:

SHOW COLUMNS FROM wp_thafxavhs9_usermeta;

Field        Type               Null        Key     Default     Extra
umeta_id    bigint(20) unsigned NO          PRI     NULL        auto_increment
user_id     bigint(20) unsigned NO          MUL     0
meta_key    varchar(255)        YES         MUL     NULL
meta_value  longtext            YES                 NULL

†: My table names somehow magically changed when we switched over from some crappy shared hosting to wordpress hosting. Not sure how that all happened, but it works.


texts:
I'm researchig into databnase encryption and whilst TDE seems to do exactly what it says on the tin, there seems to be no information on how to configure it up in a Microsoft failover SQL Cluster, or if there are any caveats about encryption in a cluster environment (beyond those that are listed on MSDN).

Trying to distill the information I have (mostly from here and here but from other random googles), I think the answer is that I have to do nothing and it just works. The master key and certificate are in the master database and the DMK is in the user database, so in a failover situation, everything exists in the MDF files for the failover node to run with (rather than any keys lurking in the registry which would need to be sync'd twixt nodes).

The problem is there's nothing that explicitly states this (or I've completely failed to read the sentance that says it), so I'm just seeking clarification that I'm not missing something obvious, that nothing special needs to be configured and it does "just work".


texts: requested shared memory size overflows size_t PostgreSQL

texts:
I am running PostgreSQL 9.2.3 on SUN Solaris 9.

This is 64 bit system with 32G swap and 16G RAM. I use same configuration file as on Linux or SUN Solaris 10, where everything is ok. I am unable to set shared buffer 5G, the maximum possible value is 4G.

When I decrease the configuration parameters and start the instance successfully: some queries fails on "out of memory" error. I verified kernel parameters: they looks same as on Solaris 10 and big enough. The only one difference is: Solaris 9 PostgreSQL version, in opposite to Solaris 10 and Linux,  was compiled by me with default options.

My kernel is:

set semsys:seminfo_semmap=64
set semsys:seminfo_semmni=4096
set semsys:seminfo_semmns=4096
set semsys:seminfo_semmnu=4096
set semsys:seminfo_semume=64
set semsys:seminfo_semmsl=500
set shmsys:shminfo_shmmax=0xffffffffffff
set shmsys:shminfo_shmmin=100
set shmsys:shminfo_shmmni=4096
set shmsys:shminfo_shmseg=100

Postgres Config

shared_buffers = 5GB
temp_buffers = 256MB
work_mem = 256MB


texts: How to determine which `Foos` have no associated `Bars`

texts:
I want to find all Foos that do not have any associated Bars.

Here's the query I'm using.  It's returning no records:

select * from foos f where f.id not in(select b.foo_id from bars b);

However, I know that the foo with id = 1583 has no associated Bars, so there should be at least one result in my previous query.

Can somebody point out a mistake I am making?  Thanks.


texts: Cassandra's nodetool snapshot renders node unresponsive

texts:
I have noticed that using nodetool snapshot on a Cassandra node causes heavy load (100% on two cores) for around 10-30 seconds. The snapshots are around 1GB in size. During that time connections to that node time out. How do I prevent this?

EDIT:

Cassandra version 2.1.1

Compression on all tables is LZ4Compressor.

Compaction strategy is STCS.

Incremental backups are off.

Compaction throughput limited to 16MB/s.

Most settings left on default.

Here a snippet of the relevant logs during a backup:

INFO  [CompactionExecutor:49922] 2017-08-21 00:15:16,672 CompactionTask.java:252 - Compacted 4 sstables to [/mnt/cassandra/data/system/compactions_in_progress-55080ab05d9c388690a4acb25fe1f77b/system-compactions_in_progress-ka-56311,].  447 bytes to 173 (~38% of original) in 52ms = 0.003173MB/s.  3 total partitions merged to 2.  Partition merge counts were {1:1, 2:1, }
INFO  [CompactionExecutor:49922] 2017-08-21 00:15:16,674 ColumnFamilyStore.java:856 - Enqueuing flush of compactions_in_progress: 1360 (0%) on-heap, 0 (0%) off-heap
INFO  [MemtableFlushWriter:61136] 2017-08-21 00:15:16,674 Memtable.java:324 - Writing Memtable-compactions_in_progress@399278616(170 serialized bytes, 9 ops, 0%/0% of on/off-heap limit)
INFO  [MemtableFlushWriter:61136] 2017-08-21 00:15:16,696 Memtable.java:363 - Completed flushing /mnt/cassandra/data/system/compactions_in_progress-55080ab05d9c388690a4acb25fe1f77b/system-compactions_in_progress-ka-56312-Data.db (194 bytes) for commitlog position ReplayPosition(segmentId=1482099209638, position=32331738)
INFO  [CompactionExecutor:49922] 2017-08-21 00:15:16,697 CompactionTask.java:136 - Compacting [SSTableReader(path='/mnt/cassandra/data/mycompany_social/comment_notification_preferences-61806e80d6cf11e688f0cf4dda6df27f/mycompany_social-comment_notification_preferences-ka-1799-Data.db'), SSTableReader(path='/mnt/cassandra/data/mycompany_social/comment_notification_preferences-61806e80d6cf11e688f0cf4dda6df27f/mycompany_social-comment_notification_preferences-ka-1800-Data.db'), SSTableReader(path='/mnt/cassandra/data/mycompany_social/comment_notification_preferences-61806e80d6cf11e688f0cf4dda6df27f/mycompany_social-comment_notification_preferences-ka-1801-Data.db'), SSTableReader(path='/mnt/cassandra/data/mycompany_social/comment_notification_preferences-61806e80d6cf11e688f0cf4dda6df27f/mycompany_social-comment_notification_preferences-ka-1798-Data.db')]
INFO  [CompactionExecutor:49922] 2017-08-21 00:15:22,674 ColumnFamilyStore.java:856 - Enqueuing flush of compactions_in_progress: 180 (0%) on-heap, 0 (0%) off-heap
INFO  [MemtableFlushWriter:61135] 2017-08-21 00:15:22,674 Memtable.java:324 - Writing Memtable-compactions_in_progress@542498536(0 serialized bytes, 1 ops, 0%/0% of on/off-heap limit)
INFO  [MemtableFlushWriter:61135] 2017-08-21 00:15:22,704 Memtable.java:363 - Completed flushing /mnt/cassandra/data/system/compactions_in_progress-55080ab05d9c388690a4acb25fe1f77b/system-compactions_in_progress-ka-56313-Data.db (42 bytes) for commitlog position ReplayPosition(segmentId=1482099209638, position=32335294)
INFO  [CompactionExecutor:49922] 2017-08-21 00:15:22,713 CompactionTask.java:252 - Compacted 4 sstables to [/mnt/cassandra/data/mycompany_social/comment_notification_preferences-61806e80d6cf11e688f0cf4dda6df27f/mycompany_social-comment_notification_preferences-ka-1803,].  1,671,656 bytes to 1,671,470 (~99% of original) in 6,015ms = 0.265010MB/s.  40,290 total partitions merged to 40,290.  Partition merge counts were {1:40290, }
INFO  [CompactionExecutor:49922] 2017-08-21 00:15:22,717 ColumnFamilyStore.java:856 - Enqueuing flush of compactions_in_progress: 1514 (0%) on-heap, 0 (0%) off-heap


texts: Ignore white spaces for comparison in unique constraints of Oracle

texts:
How can I tell to Oracle DBMS to ignore white spaces while comparing varchar values for Unique Constraints.

It means that NAME 1 is exists in a table column with Unique Key, inserting NAME1, causes constraint violation exception


texts: mariaDB not caching queries cached by mysql

texts:
I'm migrating my database (MySQL 5.1.73) to a new server, and I am trying to replace it with mariadb (5.5.5-10). Globally mariadb seems much faster, but there is a couple of queries that it refuses to cache where mysql does.
This is a big problem because those queries are used LOTS (there are sort of on the home page so will be executed pretty much every time the user interact).

I thought mariadb was a drop in replacement for mysql, so I'm surprised of this behavior ? As anyone any idea why such a different behavior or how can I force the cache to be used (I tried SELECT SQL_CACHE but that doesn't change anything.

Here is the mysql configuration

    +------------------------------+------------+
    | Variable_name                | Value      |
    +------------------------------+------------+
    | binlog_cache_size            | 32768      |
    | have_query_cache             | YES        |
    | key_cache_age_threshold      | 300        |
    | key_cache_block_size         | 1024       |
    | key_cache_division_limit     | 100        |
    | max_binlog_cache_size        | 4294963200 |
    | query_cache_limit            | 1048576    |
    | query_cache_min_res_unit     | 4096       |
    | query_cache_size             | 16777216   |
    | query_cache_type             | ON         |
    | query_cache_wlock_invalidate | OFF        |
    | table_definition_cache       | 256        |
    | table_open_cache             | 64         |
    | thread_cache_size            | 8          |
    +------------------------------+------------+

And the mariadb one

+--------------------------------+----------------------+
| Variable_name                  | Value                |
+--------------------------------+----------------------+
| aria_pagecache_age_threshold   | 300                  |
| aria_pagecache_buffer_size     | 134217728            |
| aria_pagecache_division_limit  | 100                  |
| aria_pagecache_file_hash_size  | 512                  |
| binlog_cache_size              | 32768                |
| binlog_stmt_cache_size         | 32768                |
| have_query_cache               | YES                  |
| host_cache_size                | 228                  |
| innodb_disable_sort_file_cache | OFF                  |
| innodb_ft_cache_size           | 8000000              |
| innodb_ft_result_cache_limit   | 2000000000           |
| innodb_ft_total_cache_size     | 640000000            |
| join_cache_level               | 2                    |
| key_cache_age_threshold        | 300                  |
| key_cache_block_size           | 1024                 |
| key_cache_division_limit       | 100                  |
| key_cache_file_hash_size       | 512                  |
| key_cache_segments             | 0                    |
| max_binlog_cache_size          | 18446744073709547520 |
| max_binlog_stmt_cache_size     | 18446744073709547520 |
| metadata_locks_cache_size      | 1024                 |
| query_cache_limit              | 1048576              |
| query_cache_min_res_unit       | 4096                 |
| query_cache_size               | 16777216             |
| query_cache_strip_comments     | OFF                  |
| query_cache_type               | ON                   |
| query_cache_wlock_invalidate   | OFF                  |
| stored_program_cache           | 256                  |
| table_definition_cache         | 400                  |
| table_open_cache               | 400                  |
| thread_cache_size              | 100                  |
+--------------------------------+----------------------+


texts: PostgreSQL Function: Returning Boolean Values depending on whether a table exists

texts:
This is my rookie attempt to create a function where it would return True or False depending on whether a table exists in the db. Unfortunately it outputs an empty table, and no raised messages;

CREATE FUNCTION public.check_if_error_table()
  RETURNS void AS
$BODY$
DECLARE
  table_exists text;
BEGIN
table_exists := (SELECT EXISTS(SELECT * FROM information_schema.tables WHERE table_schema = 'public' AND table_name = 'error_table'));
RAISE NOTICE '%',table_exists;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;

SELECT public.check_if_error_table();

The variable structure inside this function is extracted from the second answer of this question :- Store Query Result in Variable

And the SELECT statement giving a True or False value is also from the second answer to this question :- SQL - How to check if a table exists in a given schema

I want to formulate a function like above because;

I was not able to make the original function I made below work. The scenario I wanted to make was - if the first table was created, a message would be returned saying 'table is created please check' in the python script where these functions would eventually be called.

Also if the first table did not exist the second table would be created. RAISE EXCEPTION stopped the whole function/transaction, and RAISE NOTICE message did not appear in the python script where it was called.

-- Function: public.insert_equal_geoms(text, text, text, text)

-- DROP FUNCTION public.insert_equal_geoms(text, text, text, text);

CREATE OR REPLACE FUNCTION public.insert_equal_geoms(
    param_1 text, -- tindex
    param_2 text, --complete topo50
    param_3 text, -- error table
    param_4 text) -- spatially equal table
  RETURNS void AS
$BODY$
DECLARE
   v_row_count integer;
BEGIN

v_row_count := NULL;

   ---make error table if there are any instances of geometries not being equal to topo50 1k layer
EXECUTE format('
   DROP TABLE IF EXISTS public.%s;
   CREATE TABLE public.%s (
   geom geometry(MultiPolygon, 2193),indexname varchar(100),predicate varchar(250));

   INSERT INTO public.%s
   SELECT a.geom, b.indexname
   FROM public.%s as a
   LEFT JOIN public.%s as b ON ST_Equals(a.geom, b.geom)
   WHERE b.indexname IS NULL;',param_3,param_3,param_3,param_1,param_2);

GET DIAGNOSTICS v_row_count = ROW_COUNT;

IF v_row_count = 0 THEN

    EXECUTE format('DROP TABLE IF EXISTS public.%s;
       CREATE TABLE public.%s (
       geom geometry(MultiPolygon, 2193),indexname varchar(100),predicate varchar(250));

       INSERT INTO public.%s
       SELECT b.geom, b.tilename, ''Spatially Equal''
       FROM public.%s as a, public.%s as b
       WHERE ST_Equals(a.geom, b.geom);',param_4, param_4, param_4, param_1, param_2);

    RAISE NOTICE '%: Table updated.', timeofday()::timestamp;
ELSE
    RAISE NOTICE '%: % errors found. Check error table and resolve issues.', timeofday()::timestamp, v_row_count;
END IF;

END;
   $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.insert_equal_geoms(text, text, text, text)
  OWNER TO postgres;

SELECT public.insert_equal_geoms(public.sample1,public.sample2,public.sample3,public.sample4)


texts:
I'm working on a Desktop application that uses a DB to store information i.e (customers, houses, proprietors, payments). So the application must accept payments from customers to company, company to customers(in case of a refund), company to proprietor, proprietor to company. At this moment i'm thinking about this Payments table(there is more columns like dates, quantity).

| id            | int(11)     | NO   | PRI | NULL    | auto_increment |

| type          | varchar(20) | YES  |     | NULL    |                |

| reference_id  | int(11)     | YES  |     | NULL    |                |

With this table i was going to use id as the unique payment id, type to tell if it's a payment from company, proprietor, customer,etx.. and the reference_id is the unique id of the proprietor, customer, etx (the reason i store a reference_id is to find all the payments sent or received to that person)

For some reason it feels like an ugly design not only because i'm not using many-to-many relationship but also it gets kinda tricky to fetch data because you have to specify the type.

What would be a better design?


texts: Permissions not allowing Update or Select on a trigger executed function

texts:
With two databases A and B on the same SQL Server 2014 instance, I've written a series of scripts trying to do the following (written in very high level pseudo code):

User Updates table A.dbo.Main
A.dbo.Main has a trigger on UPDATE, DELETE, INSERT that calls a stored procedure A.CallProcInB
A.CallProcInB then calls a stored Procedure in B called B.RunComponentsUpdate
B.RunComponentsUpdate then merges a view and a table called B.dbo.A_View and B.dbo.B_Table.

The problem is I have gotten these series of scripts to work perfectly on a personal computer running SQL Server 2014 Express, with the exact same schema setup for the databases A and B, but when I set everything up on my company's main server running SQL Server 2014 I get the following error message:

  System.Data.Odbc.OdbcException (0x80131937):
  ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]
  The SELECT permission was denied on the object 'B_Table', database 'B', schema 'dbo'.
  ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]
  The UPDATE permission was denied on the object 'B_Table', database 'B', schema 'dbo'.

I've tried giving the user activating the initial trigger all privileges on both A and B, as this was enough to fix the issue on my personal computer server. However, giving basically "god" privileges hasn't been enough to resolve this error on the server.


texts: Identify Non matching records in Join- Oracle

texts:
I have a Lookup table like below

Table MASTER

CD SEQ DEF
1 1 A
1 1 B
1 1 C
2 1 A
2 1 B
2 1 C
2 1 D

Table INFO

SID CD SEQ DEF
1000 2 1 A
1000 2 1 B
1000 2 1 C

2000 2 1 B
2000 2 1 C
2000 2 1 D
2000 2 1 E

How should i write a query to say for the SID 1000 using the same CD, and SEQ
D is missing compared to Master table.
and for the SID 2000 using the same CD,SEQ All master DEF records exists and also extra DEF record E exists which is not there in Master table.

Your help is highly appreciated. Thanks


texts: Mysql server does not start if binary logging is disabled

texts:
I have been trying to disable binary logging on a slave server, since I do not use it as a master for another one, I have no use for the logs and I am trying to reduce lag so cut out everything that might be unnecessary.

Here's my my.cnf for the slave:

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /srv/mysql
tmpdir      = /tmp
lc-messages-dir = /usr/share/mysql
explicit_defaults_for_timestamp

bind-address    = xxxxxxxxxxx

key_buffer      = 64M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size       = 16
myisam-recover         = BACKUP
table_open_cache                = 512

query_cache_limit   = 1M
query_cache_size        = 256M

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 10
max_binlog_size         = 100M    # this
expire_logs_days    = 10     # this

log-error   = /var/log/mysql/error.log

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

symbolic-links=0

innodb_file_per_table
innodb_additional_mem_pool_size = 20M
innodb_buffer_pool_size     = 24G
innodb_file_format      = "Barracuda"

binlog-format=MIXED     # this
log-slave-updates=true   # this
log-bin        # this
server-id=2
gtid-mode=on
enforce-gtid-consistency=true

[mysqldump]
quick
quote-names
max_allowed_packet  = 32M

[isamchk]
key_buffer      = 16M

!includedir /etc/mysql/conf.d/

So I have tried to remove the lines I marked with "#this", and if I do the server no longer starts up, just hangs on start

Is there anything else I need to remove? or put in in place of this to make it work?

Thanks


texts: What is the best way of checking the current user privileges on a particular table from another schema?

texts:
In my software, at runtime, I need to check if the connected user has the privileges on some tables, across 2 different schemas.
From what I found doing research, I came across these 2 views
ALL_TAB_PRIVS_RECD
ALL_TAB_PRIVS
Should I use one of these views, or is there another better way to do it?
Thanks


texts: Design for user profile information and track changes

texts:
I have a web page with a login functionality. Logging in is based on email addresses. Also, I have to keep basic user profile information like:

street
city
zip code
mobile phone
country
contact name

And the user's email and a hash of their password (of course).

I know storing all of this data in one table is a bad idea, but I cannot come up with a better solution for now. Can you help me with that and suggest a way of dividing this data into different tables?

I would also like to keep track of all changes that user does with his account data (physical address/name/phone number/email/password hash).


texts:
When I execute this script

SET STATISTICS IO ON

SELECT
    *,
    ROW_NUMBER() OVER (ORDER BY [Date] DESC) AS [ROW_NO]
FROM
    vwTblTest
WHERE
    ([Factor] >= 139501 AND [Factor] < 139507) AND
    ...
ORDER BY
    [Date] DESC
OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY;

The vwTblTest is a view that join two table like this :

SELECT
    [tbl1].*,
    [tbl2].*
FROM
    [tbl1]
    INNER JOIN
    [tbl2] ON ([tbl2].[Factor] = [tbl1].[Tbl1Factor]
           AND [tbl2].[ID] = [tbl1].[Tbl2ID])
    LEFT JOIN
    (select Reference,
            CASE WHEN ISNULL(COUNT(Reference), 0) = 0 THEN 'False' ELSE 'True' END
              AS HasReference
     FROM [tbl3]
     GROUP BY Reference
    ) reference ON reference.Reference = [tbl2].ID

tbl1 contains about 9 million records, and it has a partition on Factor field.
If tbl2 contains 25 or more records every thing is fine, But when tbl2 contains less than 25 records statistic report show a lot of logical read to execute script.

Column [Date] is from tbl1. For cross checking with the plans:

tbl1 : doc_LetterInstances
tbl2 : doc_Letters
tbl3 : doc_LetterReferences

Statistics :

  Scan count 1, logical reads 12754432, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Execution Plan :

When I change script like bellow statistic report show normal logical reads. OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY was removed.

SET STATISTICS IO ON

SELECT
    *,
    ROW_NUMBER() OVER (ORDER BY [Date] DESC) AS [ROW_NO]
FROM
    vwTblTest
WHERE
    ([Factor] >= 139501 AND [Factor] < 139507) AND
    ...
ORDER BY
    [Date] DESC

Statistics :

  Scan count 1, logical reads 45, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Execution Plan :

What is wrong in my query?


texts:
I'm currently looking at creating a follower db of a live production database. The follower will be used for analytics purposes only. And will not be inserting any data - if it did then it would be by mistake.

After reading the docs on heroku https://devcenter.heroku.com/articles/heroku-postgres-follower-databases#create-a-follower

I cannot see how you could prevent changes from a follower creeping back into the master db.

Any suggestions or pointers will be very helpful. Thank you.


texts: Upgrading MySQL 5.1 to 5.5 - All tables gone

texts:
I'm trying to upgrade from MySQL 5.1 to MySQL 5.5.

I followed the instructions here:
http://dev.mysql.com/doc/refman/5.5/en/windows-upgrading.html
I also adjusted the Datadir.

But after the upgrade, all tables are gone. I only use Innodb tables.

The Datadir setting seems to be correct, cause the ibdata1 file changes if I change something in the db. But even if I add a new schema with a new table it does not create a directory for it.
Am I missing something completely?


texts: Query in PostgreSQL became slow and then got better on its own - why?

texts:
I want to better understand how to debug and fix performance issues in PostgreSQL.

I have a query that usually costs 1 second to run. However, for some reason, it started taking 30 seconds to run for a period of time! After some minutes and some explain analyze invocations, it went back to normal. I didn't do anything to fix it, nor did I understand why it got better all of a sudden.

How do I debug a problem like this? What kind of logging and instrumentation should I have in place to understand what caused this and how to fix it?


texts:
I have a Database Called Total and I have about 40 tables in it.I am trying to move the tables in the database into a different server So I have prepared all the create table scripts but the problem is all the tables have foreign keys so I want to know the order in which I have to execute the tables.

Is there any way to know it?


texts: How to do a one-time log shrink on a database with transactional replication

texts:
I have inherited a 10 GB database with an 80 GB log [only 3% in use according to DBCC SQLPERF(logspace)]. It seems safe to assume the extreme log growth was due to problems from long before I was hired.

The primary has a log shipping backup job that runs every 15 minutes. The primary has copy and restore jobs that run every 15 minutes.

When I attempt to shrink the log, I get "Cannot shrink log file 2 (DatabaseName_log) because the logical log file located at the end of the file is in use." I have retried this several times 15 minutes apart and even days apart, but always get the same result.

DBCC LOGINFO shows 784 VLFs, with only the first 245 and the last on with a status of 2. p_WhoIsActive shows that the longest open transaction has been running for less than 2 hours (long transactions are not unusual here due to a third-party Microsoft Access app with ODBC driver issues).

How can I successfully shrink this log (without creating an outage for users)?

Thanks,
Mark


texts: Does large amout of idle connections hurt performance of Mysql

texts: We have applications that keep large amounts of idle connections (thoudsands). Does that hurt performance of the database? Is it worth it to lower the number idle connetions for performances sake?


texts:
Can you tell me how to kill connection in pgadmin 4 web postgresql without executing any scripts?

How can I limit the number of connection to database in order to kill the active connection (My case I want to rename database already used !)


texts:
I have a function which needs to create  a dynamic where clause by reading the columns from the temporary table .

CREATE FUNCTION [dbo].[GetWhereClause]
(
 @TempTableName nvarchar(600)

)

RETURNS nvarchar(max)
   BEGIN
   DECLARE @sql as nvarchar(max)

 SET @sql='(SELECT STUFF('+'(SELECT space(2)+'+
            ''''+'and '+'''' +
            '+space(2)+'+''''+'B'+'.'+''''+'+'+'name+'+''''+
            ' = '+''''
            +'+'+''''+'A'+'.'+''''+'+'+'name+'+''''+''+''''+
            'from tempdb.sys.columns where object_id =
            object_id('+''''+'tempdb..'+@TempTableName+''''+')'+
            'FOR XML PATH ('+''''''+')),1, 5,'+ ''''''+'))'

RETURN @sql

END

code to see the ouput

     CREATE TABLE #SingleTempTable(ID  uniqueidentifier  ,Name  nvarchar    (50)COLLATE    SQL_Latin1_General_CP1_CI_AS,Age  nvarchar  (50)COLLATE  SQL_Latin1_General_CP1_CI_AS)
   DECLARE @QueryTable AS TABLE (ID SMALLINT IDENTITY (1, 1) PRIMARY KEY,Clause NVARCHAR (1000));

     DECLARE @sqlsource AS NVARCHAR (MAX) = '';

     DECLARE @whereClause AS NVARCHAR (MAX) = '';

     DECLARE @TempTableName AS NVARCHAR (MAX) = '#SingleTempTable';

     SET @sqlsource= [dbo].[GetWhereClause](@TempTableName,'')

      INSERT INTO @QueryTable
      EXECUTE sp_executesql @sqlsource

      SELECT @whereClause=Clause FROM @QueryTable

      SELECT @whereClause

Is there any better way I can write this line in function

 SET @sql='(SELECT STUFF('+'(SELECT space(2)+'+
            ''''+'and '+'''' +
            '+space(2)+'+''''+'B'+'.'+''''+'+'+'name+'+''''+
            ' = '+''''
            +'+'+''''+'A'+'.'+''''+'+'+'name+'+''''+''+''''+
            'from tempdb.sys.columns where object_id =
            object_id('+''''+'tempdb..'+@TempTableName+''''+')'+
            'FOR XML PATH ('+''''''+')),1, 5,'+ ''''''+'))'

Now its not in a good format and its difficult to understand


texts: What is a scaleable database design for querying items and only returning those a particular user has not already seen?

texts:
I'm looking at database architectures that will allow the storage of a list of items a user has already seen so that I could subsequently query the items table and only show the user items they had not already seen.

It seems like something that's been done before many times but I think I'm using the wrong search terms to find some good information on it. I tried to keep it vague so this might be useful to future searches.

The non-scaleable option would be to put this all in a relational database like MySQL and have a 3rd table alongside users and items which would be seen_items which could have a user_id and an item_id. The select would then just discard anything from the seen_items table for the user_id in question.

I'm trying to look at ways this could scale when we're talking 1 million items and 100,000 seen items for 5 million users, to pull numbers out of the air. The other consideration is that each time something is "seen" it needs to be written to the database making it more write-heavy than read-heavy. My initial thinking is that this lends itself to a distributed NoSQL solution since a seen_items table of 500,000,000,000 rows isn't exactly going to work out, but then could a NoSQL solution store a list of 100,000 IDs per user and ensure those IDs don't turn up in a search for a list of items?

Thanks for any thoughts or reading material you could provide!


texts: Get name column instead of ids from two foreign keys

texts:
I need to get both group names as a result, although I have no idea how to do this query.

Table 1 (groups)

+---------+--------+
| id      | name   |
+---------+--------+
| 1       | newb   |
| 2       | user   |
| 3       | editor |
| 4       | boss   |
+---------+--------+

Table 2 (inheritance)

gid = group id; pid = parent id

+---------+--------+
| gid     | pid    |
+---------+--------+
| 3       | 1      |
| 3       | 2      |
| 4       | 1      |
| 4       | 2      |
| 4       | 3      |
+---------+--------+

Expected Results

Now I want the names of the groups as a result, not the ids, so I need to get this result:

+---------+--------+
| gname   | pname  |
+---------+--------+
| editor  | newb   |
| editor  | user   |
| boss    | newb   |
| boss    | user   |
| boss    | editor |
+---------+--------+

Thanks!


texts: MySQL monitor prompt does not return for slave

texts:
I'm trying to fix a replication error (or an ongoing failure to replicate) between our master and slave MySQL databases.

The procedure I have asks me to use MySQL Monitor to stop the slave.

stop slave;

When I try this, however, the prompt never returns. I've left it for several minutes before Ctrl+C'ing out.

Interestingly, the same thing occurs with:

show slave status;

...but

show master status;

Works fine. Is there some other way I can kill the slave process off, ideally without interrupting the master?


texts: Load file based on line number in sqlldr

texts:
I am trying to load header and detail records from a file. The only way I know the difference between the header and the detail records is the line number. The first line is always the header and the other lines are detail records.

How can I specify the line number of a file when doing a conditional load? I would like to do something like:

LOAD DATA
APPEND INTO TABLE x
WHEN [line_number] = 1
([mapping for header line])
WHEN [line_number] > 1
([mapping for detail lines])

The only reason I ask is b/c I take the primary key from the header record in the database and insert it into each detail record. That is a sequence that doesn't get populated until the insert into the header table. I have to load the header/detail in the same transaction because I can't call currval on the sequence until I call nextval - which is only called in the header insert.

Is there a way for me to do this?


texts: MySQL INFORMATION_SCHEMA.TABLES not adding to file size

texts:
I have a file for a table which has grown very large. I expected to have a large amount of unallocated space needing to be optimized, but when checking INFORMATION_SCHEMA.TABLES, there is little data_free (4 MiB, 0.4 %) and the numbers do not sum to the size of the file:
$ sudo ls -lh /var/lib/mysql/data/modnar.ibd
-rw-r----- 1 mysql mysql 7.0G Aug 23 09:52 /var/lib/mysql/data/modnar.ibd

mysql> select table_name, engine, data_length, max_data_length, index_length, data_free from information_schema.tables where table_name = "modnar";
+------------+--------+-------------+-----------------+--------------+-----------+
| table_name | engine | data_length | max_data_length | index_length | data_free |
+------------+--------+-------------+-----------------+--------------+-----------+
| modnar     | InnoDB |  1172307968 |               0 |            0 |   4194304 |
+------------+--------+-------------+-----------------+--------------+-----------+

The file is 7 GiB but adding the numbers from INFORMATION_SCHEMA.TABLES, (1172307968+4194304)/1024/1024/1024 is only 1.1 GiB.
Why is the file so large, 7 times the amount of data in the table?


texts:
Please help! I am having error switching out from the partition i set up. I have the scripts and error information below :

I created separate file groups for each range

--Create Partition Function

USE [ApplicationLogs]
GO
CREATE PARTITION FUNCTION [FN_SchedulerLog](datetime) AS RANGE LEFT FOR VALUES (N'2016-06-30T23:59:59.998', N'2016-07-31T23:59:59.998', N'2016-08-31T23:59:59.998', N'2016-09-30T23:59:59.998', N'2016-10-31T23:59:59.998', N'2016-11-30T23:59:59.998', N'2016-12-31T23:59:59.998')

--Create Parttion SCHEME
CREATE PARTITION SCHEME [sch_SchedulerLog] AS PARTITION [FN_SchedulerLog] TO ([FG_SchedulerLog_06_16], [FG_SchedulerLog_07_16], [FG_SchedulerLog_08_16], [FG_SchedulerLog_09_16], [FG_SchedulerLog_10_16], [FG_SchedulerLog_11_16], [FG_SchedulerLog_12_16], [PRIMARY])

DROP INDEX [pkSchedulerLogId] ON [dbo].[SchedulerLog] WITH ( ONLINE = OFF )

CREATE UNIQUE NONCLUSTERED INDEX [pkSchedulerLogId] ON [dbo].[SchedulerLog]
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

CREATE CLUSTERED INDEX [ClusteredIndex_on_sch_SchedulerLog_636102140668795637] ON [dbo].[SchedulerLog]
(
    [Date]
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [sch_SchedulerLog]([Date])

DROP INDEX [ClusteredIndex_on_sch_SchedulerLog_636102140668795637] ON [dbo].[SchedulerLog] WITH ( ONLINE = OFF )

--- switching out data from any FGs to a switchout table for archiving purposes
--******************
--Create a switch out table:
USE [ApplicationLogs]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[staging_SchedulerLog_20160923-120700](
    [ID] [int] NOT NULL,
    [Date] [datetime] NOT NULL,
    [Thread] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [HostName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [HostIP] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ModuleName] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Level] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Logger] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Message] [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [FG_SchedulerLog_06_16]

USE [ApplicationLogs]
CREATE UNIQUE NONCLUSTERED INDEX [staging_SchedulerLog_20160923-120700_pkSchedulerLogId] ON [dbo].[staging_SchedulerLog_20160923-120700]
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [FG_SchedulerLog_06_16]
USE [ApplicationLogs]
ALTER TABLE [dbo].[staging_SchedulerLog_20160923-120700]  WITH CHECK ADD  CONSTRAINT [chk_staging_SchedulerLog_20160923-120700_partition_1] CHECK  ([Date]<=N'2016-06-30T23:59:59')
ALTER TABLE [dbo].[staging_SchedulerLog_20160923-120700] CHECK CONSTRAINT [chk_staging_SchedulerLog_20160923-120700_partition_1]

---Switch out partition
ALTER TABLE [dbo].[SchedulerLog] SWITCH PARTITION 1 TO [dbo].[staging_SchedulerLog_20160923-120700];
GO

  Error: ALTER TABLE SWITCH' statement failed. The table 'ApplicationLogs.dbo.SchedulerLog' is partitioned while index 'staging_SchedulerLog_20160923-120700_pkSchedulerLogId' is not partitioned.


texts: Select rows where 'A1' is followed by 'A2'

texts:
I have a set of entries like:

Id ColA

1 A1
2 A2
3 B
4 A2
5 A1
6 C
7 A1
8 A1
9 A2
10 D

In pairs 1, 2 and 8, 9 the first line has A1 in colA and the following line has A2 in colA. How can I select these two pairs?


texts:
In our database (DB for brevity) we store some very sensitive government data, and would like to secure the data as much as possible.

There are two way to think about security here:

defense from external intruders (essentially making sure PCI-DSS compliance is in place, for instance),
security from internal intruders.

My question is focused on the latter.

The sensitivity of information is considered so high that we're facing a very likely possibility of internal staff getting their hands on the data.

In other words, we're afraid that internal staff can mass-steal the data and trade it (the data is only interesting when stolen altogether, as it's a huge DB of people's private info).

The DB is accessed by our application, on a record-by-record manner. This is considered fine, as using record-by-record manner of accessing data  staff is not expected to mass-steal the information.

An obvious solution would be: give "keys to DB" to a single, most trusted man. However our concern is that even in this case someone can put a gun to his head and ask to make the copy of the DB.

Is there any way we could secure the DB, such that even if the DBA is put a gun to his head, he would be physically unable to steal the data?

Note: The database management system we are using is PostgreSQL, but we may easily switch to another one provided it offers the safety guarantees we're seeking.


texts: Inconsistency between object explorer and available database dropdown

texts:
I am working with SQL Server 2008R2.  I am able to see a database in the "available database" drop down, but not in the object explorer. I am able to query it, for example   select * from INFORMATION_SCHEMA.TABLES  is able to run within the database.

Has anyone seen this before?  I have tried right clicking on the SQL server within object explorer, and hitting refresh, but I am not seeing the database within the object explorer.  It happens to be the "distribution database" as mentioned here:

Is that something that ever shows up in the object explorer?

Any information would be greatly appreciated.


texts: How to change SESSION_CACHED_CURSORS for all sessions

texts:
how Can I do to change SESSION_CACHED_CURSORS for all sessions?

I found on google and oracle website:

Alter Session... but I need to changed it for all the session and not only for one of them.

How can I do it?

UPDATE 1:

SQL> alter session set session_cached_cursors=800 scope=both sid='*';
alter session set session_cached_cursors=800 scope=both sid='*'
                                             *
ERROR at line 1:
ORA-02248: invalid option for ALTER SESSION


texts:
How to deploy the SQL scripts to the local database (one-click deployment)?

On a periodic interval, we receive .sql files from Developers. I will have to automate the process of pushing all .sql files to mapped database.

I am trying to get some help on osql / batch script syntax to deploy .sql files.


texts: Amount of Data transferred out in mongorestore

texts:
I'm using MongoDB version 4

Consider I have a database with dataSize 15GB, storageSize 10GB.
After I mongodump --gzip it, the file created is of 5GB.

Now, If I'm restoring it to a remote machine (M2), using mongorestore on my workstation (M1), will the data transferred out of M1 be around 5GB or 10GB or 15GB?


texts: Should master and slave have same mysql version?

texts: I have a master database running mysql 5.5.11, and I am planning to setup a new slave. I am planning to use the 5.5.30 as it is the latest version. For setting up new slave, I am copying files from master 5.5.11 version. Will there be any compatibility issues?


texts: MySQL Schema Design: One Possible Values Between Multiple Columns

texts:
I'm working on a schema for a MySQL database. I have tables which are derivatives of one another. For example, rather than a single "animals" table, I might have separate "mammals" and "birds" tables:

CREATE TABLE `mammals` (
  `id` INT(11) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
  `species_name` VARCHAR(255) NOT NULL,
  `fur_color` VARCHAR(255) NOT NULL
);

CREATE TABLE `birds` (
  `id` INT(11) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
  `species_name` VARCHAR(255) NOT NULL,
  `wingspan` DECIMAL(11,8) NOT NULL DEFAULT 0
);

The tables are separate because a large percentage of the combined columns between the two tables are unique to one specific derivative (e.g. fur_color and wingspan in the example above). The different derivatives also have very different relationships to other tables.

However in some other tables we might want to reference a specific instance of one of those entities:

CREATE TABLE `pets` (
  `id` INT(11) UNSIGNED ZEROFILL NOT NULL AUTO_INCREMENT,
  `owner_name` VARCHAR(255) NOT NULL,
  `mammal_id` INT(11) UNSIGNED ZEROFILL NOT NULL,
  `bird_id` INT(11) UNSIGNED ZEROFILL NOT NULL
);

The above example would allow us to specify what species of animal each pet is, but there's a problem: the pets schema allows for values to be entered in both the mammal_id and bird_id column while in reality I only want to allow input in one or the other.

What is the best solution to this issue? I was considering a check constraint but according to the MySQL Reference Manual:

  The CHECK clause is parsed but ignored by all storage engines.

Should I simply replicate the constraint as a trigger that throws an error, or is there a better solution that I'm not thinking of?


texts: Can not backup multiple (not all) databases Ola Hallengren

texts:
This error is quite easy to replicate. So before I send a bug to Ola, I'd like to check if maybe I'm doing it wrong. Version is 2018-06-27 20:44:12

I created 3 databases. DB1, DB2 and DB3. I'd like to backup these 3 databases only in this script independently of what other databases exists. According to Ola's homepage, this is the way I interpret it to be written for the @Databases parameter.

Here is the syntax

EXECUTE [dbo].[DatabaseBackup]
@Databases = DB1, DB2, DB3,
@Directory = N'C:\Temp',
@BackupType = 'FULL',
@Verify = 'N',
--@CleanupTime = 360,
@CheckSum = 'Y',
@LogToTable = 'Y',
@NumberOfFiles = 4

I get error:

  Msg 119, Level 15, State 1, Line 2 Must pass parameter number 2 and
  subsequent parameters as '@name = value'.      After the form '@name =
  value' has been used, all subsequent parameters must be passed in the
  form '@name = value'.

I've tried

@Databases = DB1, @Databases = DB2, @Databases = DB3, but then I get error - Parameter '@Databases' was supplied multiple times.
I've tried using ; instead of , but still error.
Tried [DB1] etc, also error.
Tried N', also error

I'm trying to accomplish line 7 on the picture below from Ola Hallengren

Any Ideas?


texts: Capture when any database is restored on the server

texts:
Is there any way that I can identify when a database is restored on the server? I created a server trigger but only triggers when I create a new database not after is restored from another one.

I'm using SQL Server 2014, trying to achieve this using MSMS, using msdb.dbo.restorehistory is good but I don't want to create a job for that, trying to find something straightforward

I want to know as s DBA when any database is created or restored, just for control.

I found that there is no DDL trigger for RESTORE, there is however Event Notification for the AUDIT_BACKUP_RESTORE_EVENT but I didn't find any good example of how to use it.

could someone tell me which accounts I need to merge? I'm confuse, I did it with my account on stackoverflow and it says is already linked and I can't continue


texts:
I have an existing database that uses the Arabic_CI_AS collation, but I need to convert it to SQL_Latin1_General_CP1_CI_AS the default collation for the server, but the problem is when I use the following command:

USE master;
GO

ALTER DATABASE MyDB
COLLATE SQL_Latin1_General_CP1_CI_AS ;
GO

It give me the following error message:

  Msg 5075, Level 16, State 1, Line 1
  The object 'GetAllSuppliersInCompliance' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it.  Remove the dependencies on the database collation and then retry the operation.

  Msg 5075, Level 16, State 1, Line 1
  The object 'GetTier1SupplierComplianceStatus' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
  .....

  Msg 5072, Level 16, State 1, Line 1
  ALTER DATABASE failed. The default collation of database 'MyDB' cannot be set to SQL_Latin1_General_CP1_CI_AS.

So I tried the following to disable the constraints temporary using the following command:

EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"

I then executed the first command, however the same error appeared.


texts: How is encoding done in SQL database tables for pattern matching?

texts:
Are strings in table columns represented as bit patterns or Unicode?  Does the database engine bring them into memory in a different representation character set from the disk-persisted representations for optimal performance?

I want to know for the purposes of index creation and optimal query writing.  Select statements pull data that matches a where clause.

Here is an example query:

select name from carTable where name =  'FordEscort97'

Here is the carTable:

name                        columnA columnB     columnC
FordEscort91
FordEscort92
FordEscort93
FordEscort94
FordEscort95
FordEscort96
FordEscort97
FordEscort98

Would the queries perform faster if the column in the table was designed to have the year at the left of the name in the strings that are stored?  (assuming the year component of the string was the most unique)

If there is a character-by-character, left-to-right matching process, the process could be more resource intensive than evaluating bit pattern hashes representing the strings being compared.  If the pattern being matched (the anchor string in the where clause) was a substring with a wildcard, each comparison operation would be more CPU intensive because if there are characters in the column that start out matching the wild card in a leftmost moiety of the string, a rightmost moiety may start out as a second potential match late in the string comparison.  Early non-matching recognition would obviate the need for as much evaluation.  But I don't know for sure that there is a character-by-character pattern match.

There is an implication for creating indexes on unique columns.  While a consideration should be made for a likelihood of uniqueness of strings in a column, designing data in the column can be arbitrary.  Could an index provide a greater benefit if primary column's data had its uniqueness of the string happening mostly within the leftmost characters compared to the rightmost characters?  Does it depend on the type of SQL you are using (e.g., Oracle, MySQL, Postgres, etc.)?  Sometimes underlying data is stored in hexadecimal format or with bit patterns.


texts: How are foreign key indexes helpful?  What is the criteria for keeping a foreign key index?


texts: Are there any issues with sparsely populated partitioned tables

texts:
The partitioning strategy used by a third party product results in a fairly sparse distribution of data.
i.e. of our ~900 partitions only ~300 have data. The way this works it will only become more sparse over time.
Is there any value in merging consecutive empty partitions (I can guarantee there will be no inserts into them) or is this a waste of time?


texts: Postgres, two tables use the same sequence, how to delete one?

texts:
I've just finished partitioning an existing table by 1) renaming the original table 2) creating a new partitioned tables with identical structure as the original, and 3) transferring the data from the old un-partitioned table to the new one.

The partitioning has worked just fine, no problems. The only issue is that I now have two tables with and identical field that both use the same sequence

ident integer NOT NULL DEFAULT nextval('mytable_ident_seq'::regclass)

Question - how do I remove the original table?

I can't delete the original table because in doing so Postgres attempts to drop the sequence, which causes an error because 'other objects depend upon it', and the 'other object' is my newly created, partitioned version.

The first thing I tried was to remove the reference to the sequence

 ALTER TABLE mytable_old ALTER COLUMN ident SET DEFAULT 0;

This seems to work, it returns no errors, but when I look at the properties for the field it still says it has a dependency on the sequence, and when I attempt to drop the table I get the same error. The table currently has zero rows of data, and I've dropped all the fields except for this one, but I still can't actually drop the table itself.

This isn't a critical error, the database works ok, the new version of the table is partitioned and working fine, I'd just like to remove the old table for the sake of tidiness.


texts:
I need to extract AD groups and all of it's subgroup into an excel sheet.Basically the script should loop though each of the AD group and get their subgroup.

AD_Group      Subgroup
----------    ---------

I'm new to PowerShell so any script to acheive the expected result should be helpful.


texts: Postgres: change database name without interruption?

texts:
I have a production database that I'm told is "named incorrectly". Is there a way to change the name without interruption?

The database in question is named something like prod_api_db and should be named api_db.

I was hoping there would be a way to create an "alias" so to speak, such that both names are valid and point to the same database for a time period while all of the app configurations are updated with the new name. Once everything's updated, the old name could be removed.

 -- Stage 1, create alias "api_db":

apps ==> prod_api_db
                    > actual_database
              api_db

-- Stage 2, Update app configs:

    prod_api_db
               > actual_database
apps ==> api_db

-- Stage 3, Remove old name:

apps ==> api_db > actual_database

Does that sort of functionality exist in postgres?

Just for background, this is postgres 11. I do have dev, qa, and production servers to work with, so I can test before apply a change.

Pardon me if I'm being very ignorant about this. I'm not a DBA... just an SRE working for a small company who's been given charge of some database, and I haven't found a good answer yet.


texts:
I've recently seen the following schema and was wondering what the reasons behind it could be. Let's suppose we have three tables A, B and C.

A table has one to many relationship with B, so B has an A_id.
B table has one to many relationship with C, so C has a B_id.

Looks pretty logical so far. However, in the schema I saw C also had A_id as well as B_id. The question is, what could be possible reasons to do so?

The only one that comes to my mind is to speed up the query to get all C entities which belong to A, though I am not sure it's that significant. Has anyone also used the aforementioned schema and why?

The question might seem broad so I'm not sure if it's really Q/A format. I couldn't find any information to read about this design decision since I don't know whether it has a name. If it has, I'd appreciate if someone told me how it's called.


texts: How do I set the isolation level for SELECT statement?

texts:
Using PostgreSQL 9.5 I would like to make sure that when I run a SELECT statement I get consistent results, i.e. if the table is modified while the SELECT is running, I want to get the state that was valid when the SELECT started.

Basically, it's like: Take a snapshot of a table, and don't allow updates to the table while the snapshot is being taken, and then deliver the snapshot, while updates are allowed again.

If I understood isolation levels correctly, REPEATABLE READ is what I'm looking for, isn't it?

Now my question is: How can I run a SELECT with REPEATABLE READ isolation level? Or am I missing something, and my approach is wrong? How would I solve this?


texts:
I'm a newbie. I have stumbled upon this warning (code 1366) when trying to insert a string with an accent like é in my tables.
For instance if I type this in the prompt:

create database db character set = "utf8mb4" collate = "utf8mb4_unicode_ci";
use db;
create table tab(
    string varchar(10) character set "utf8mb4" collate "utf8mb4_unicode_ci" primary key
) character set "utf8mb4" collate "utf8mb4_unicode_ci";

insert into tab value("ö");

I get the mentioned warning, and the character is saved as a question mark. (I'm aware that the code looks repetitive, but it's just for the purpose of diagnosing the problem).

I have read a dozen of questions about this warning on stackoverflow in the past hours and have learned that utf8 is not suited and that I should use utf8mb4 instead.
This documentation page documentation page in the paragraph "Example: Changing the Default Character Set To UTF-8" says to change/add some lines in the my.ini file, and also suggests utf8mb4 (rather than utf8). But despite the changes, the issue is still present.

I'll show that all the possible variables have successfully been set to utf8mb4 (except character_set_system, which is read-only so I assume it's not part of the problem):

MariaDB [db]> SHOW GLOBAL VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

+--------------------------+--------------------+
| Variable_name            | Value              |
+--------------------------+--------------------+
| character_set_client     | utf8mb4            |
| character_set_connection | utf8mb4            |
| character_set_database   | utf8mb4            |
| character_set_filesystem | binary             |
| character_set_results    | utf8mb4            |
| character_set_server     | utf8mb4            |
| character_set_system     | utf8               |
| collation_connection     | utf8mb4_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.021 sec)

I'm running the last version of MariaDB, 10.4
Any help is appreciated

Edit

@RickJames I'm just launching mysql from the command prompt of Xampp. The hex is 94 actually. It always prints a question mark instead of the desired character. If I type

select 'à';

the following is returned:

+---+
| ? |
+---+
| à  |
+---+
1 row in set (0.001 sec)

Also, I have just noticed that if I launch mysql from Windows prompt rather than Xampp's shell, I can insert the accented letters and print them correctly, so I may stick to cmd.
Notice the difference in the result when typing the same command as before:

+---+
| à |
+---+
| à |
+---+
1 row in set (0.001 sec)

Still, after successfully adding the value "ö" in my tab from cmd, if I open mysql in Xampp's shell and do a select of the table from there, it prints gibberish (in particular these two characters together: ├Â).


texts: Index strategy for a Queue Table, getting rid of filesort

texts:
I am trying to get rid of a filesort operation (and preferably also optimize a bottleneck query). We are using an innodb mysql table as a queue to process various incoming business activities. It is not uncommon for the queue to have several hundred thousand pending items to process.

The selection query is as follows:

SELECT
    id,
    user,
    password,
    counter,
    idoc,
    idoc_type,
    company_id,
    host,
    job_id
FROM
    business_queue
WHERE
        finished IS NULL
    AND locked='0'
    AND created <= NOW()
    AND counter <= 5
    AND company_id = 2
ORDER BY
    business_object_priority DESC, created ASC, id ASC
LIMIT 1
FOR UPDATE

The table has the follwing index defined:

ALTER TABLE  `business_queue` ADD INDEX compound_selector(
   `finished` ,
   `locked`,
   `created`,
   `counter`,
   `company_id`,
   `business_object_priority` ) ;

Yet I have a feeling that it could be massively optimized, as currently filesort is used for sorting and it is kinda inefficient when we have a large pending queue. Is there a way to get rid of filesort in the above query completey?


texts: Validation of views even though auto_reval is set to DEFERRED

texts:
According to http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.dbobj.doc/doc/r0054951.html?lang=en it should be possible to create stored procedures and views referencing not existing tables if the setting AUTO_REVAL is set to DEFERRED.

On my system however the following commands (creating a view referencing an invalid table) still result in an error:

db2 get database configuration | grep REVAL

  Auto-Revalidation                          (AUTO_REVAL) = DEFERRED

db2 "create view v1 as select * from not_existing_table"

  DB21034E  The command was processed as an SQL statement because it was not a
  valid Command Line Processor command.  During SQL processing it returned:
  SQL0204N  "DB2ADMIN.NOT_EXISTING_TABLE" is an undefined name.  SQLSTATE=42704

DB2 does not create the view, also not as an invalid object.

Am I missing anything? Are there other options I need to verify? I'm using DB2 LUW 10.5 (fixpack 4).


texts: Convert Varchar To Datetime and Add Seconds

texts:
I have the following two columns in my table

callstarttime       callduration
20180101215910        120
20180101215710        220

The first column's data type is varchar(255) whilst the second column is int.

What I want is to add the callduration to the callstarttime inorder to get the time which the call ended.

So I need to first convert the callstarttime column to datetime and then use the Dateadd function.

My script is as follows:

select convert(datetime, callstarttime) from Mytable

I have also tried using cast as follows

select cast(callstarttime as datetime) from Mytable

However I am getting the following error

Conversion failed when converting date and/or time from character string.

Similar questions have been asked on Convert varchar column to datetime and how to convert this varchar to datetime format?, however I can't tailor my script based on the solutions provided there.


texts: How to select the values in transposed manner(as a new field) with teradata?

texts:
Consider a table named "Table1" which has coulumn1,column2...n
i need a query which fetched the data like below.
TABLE1:
id    Line_nbr   Exam_cd
1      1          ENG
1      2          MAT
1      3          GEO
2      1          ENG
2      2          MAT
3      1          MAT
3      2          GEO
3      3          ENG
4      1          MAT

The expected output:
id    totalofexam_cd
1     1-ENG,MAT,GEO
2     2-ENG,MAT
3     3-MAT,GEO,ENG
4     4-MAT

so ,for id=3 we have 3 different EXAM_CD available. so query should fetch the values whatever the mentioned id have. As id=3 has three different exam_cd it should be fetched as 3-MAT,GEO,ENG (here 3 is the id number).
can anyone please help me on this?
Thanks in advance :)


texts: PAGEIOLATCH_EX Wait on Target table of insert..select query

texts:
We are running SQL Server 2012 on Windows 2008 R2 with 256GB RAM and 90% memory allocated to SQL Server. No other Apps run on the server.

I have a scenario where I’ve to copy a Source (staging) table to the Target (Fact) table. The Staging table is un-compressed, has 215 columns – many are nullable - and has 8 million rows.

The Target table is page-compressed and occupies about 3GB of space after data-copy. I made sure nothing else was running when doing the tests.
It takes 25+ mins. to for the  data-copy to complete. To mitigate the situation, I ran it  on 4 sessions concurrently with ~2 million rows per session. While 2 sessions finished in about 7 mins., another 2 sessions took nearly 14 mins. to complete. I tested it multiple times with similar results.

To understand what is happening,

Using guidance from your ‘where it hurts’ blogs for PAGEIOLATCH and capturing wait stats during a time period blogs, I found out the culprit is  PAGEIOLATCH_EX waits.

We have Local HDDs in RAID 5 as storage sub-system. The Source table is all cached from previous runs and nothing else is running on the server.

Digging a bit deeper, using sys.dm_os_waiting_tasks while the job was running I captured the resource_description of the waits which provided – DBID:FILEID:PAGEID.

Then, using (sys.dm_os_buffer_descriptors, sys.allocation_units, sys.partitions), to my surprise all the waits are from the Target table!

Paul Randall describes PAGEIOLATCH_EX wait @ https://www.sqlskills.com/help/waits/pageiolatch_ex/
as (emphasis mine):

  This wait type is when a thread is waiting for the "read of a data file page" from disk to complete, and the thread is going to modify the page structure once it is in memory (EX = EXclusive mode). The Latches Whitepaper in the sidebar on the right has a description of all latch modes and their compatibility with other latch modes.

I can understand if the IO latch waits are for source table but here they ALL are on Target table’s - DATA_PAGE & IN_ROW_DATA as per (sys.dm_os_buffer_descriptors, sys.allocation_units, sys.partitions)

Why are we seeing PAGEIOLATCH_EX waits on the Target table!? What is SQL Server reading from disk here for the Target table, while it is being loaded for the first time?

Aren't SQL Server Writes data only in Memory - while:
Write-Ahead Logging, Log Buffers & Log Writer takes care of writing to Transaction Log
Checkpoint process taking care of flushing dirty data pages

Can you please help me understand what is happening here? Why am I seeing a "Read from disk" wait for the table that is being written to. There is PLENTY of RAM space and nothing else running... Thanks.

P.S.

Running the same test on a Server with SAN (better IO subsystem) all sessions/application threads finish around same time each taking ~3mins(8 sessions). However I do see same PAGEIOLATCH waits on Target table. Still couldn't understand what is SQL Server reading from disk for the Target table, while it is being loaded for the first time!


texts: Best way to enter data in DB that is spread out date wise

texts:
I have an Excel file that, to keep it simple here, has 3 columns: ActivityID, ActivityName and TotalHours.

TotalHours column is basically all the hours allotted to that specific ActivityID and the file can have about ~1500 of these activityID equating to 1500 rows.

Now the thing is that there are additional columns in the file which break down the TotalHours by week dates. These hours are split into different week periods but adding them all up equals TotalHours.

I don't want to end up dynamically creating table columns in my DB as I could end up with 100+ fields. One solution is that I could read the Excel just like it is and do calculations on the fly but this would then require the file to be strictly templatized meaning the data needs to be consistent and be in specified cells for me to read them. This is something that I am trying to avoid.

Is there any better way that you know I can approach this problem?

Thanks


texts: Best way to remap database login to new database user and keep previous database user

texts:
Here's what my environment looks like:

Active Directory (mydomain.local)
   - Groups
      - group01
         - Members
            - aduser01

SQL Instance
   - Logins
      - mydomain\group01
   - Databases
      - db01
         - Users
            - dbuser01 (login = aduser01, default schema = dbuser01)
         - Schemas
            - dbuser01
         - Roles
            - db_mydbrole

What I'd like to do is create a new database user and schema mapped to login aduser01:

USE [db01]
GO
CREATE USER [dbuser02] FOR LOGIN [mydomain\aduser01] WITH DEFAULT_SCHEMA=[dbuser02]
GO
EXEC sp_addrolemember N'db_mydbrole', N'dbuser02'
GO
CREATE SCHEMA [dbuser02] AUTHORIZATION [dbuser02]
GO

However since aduser01 is mapped to dbuser01 it fails with error:

  The login already has an account under a different user name
  (Microsoft SQL Server, Error: 15063)

What is the best way to go about this? If database user dbuser02 is created and mapped to aduser01, what are the possible states dbuser01 could be left in?


texts: delete all customers with email ending with example.com with specific DATE period

texts: delete all customers with email ending with example.com using mysql with specific DATE period from 9/15/2018 to 9/28/2018


texts: why innodb generate table-lock when two sql using different index?

texts:
I know innodb-engine row lock by add lock to index item. But I don't understand the following scene.

Prepare sql:

DROP TABLE IF EXISTS `tbl_order`;
CREATE TABLE tbl_order (
    `order_id` BIGINT NOT NULL,
    `product_name` VARCHAR(16) NOT NULL,
    KEY `order_id_idx`(`order_id`),
    KEY `product_name_idx`(`product_name`)
) Engine=InnoDB CHARSET=utf8mb4;
INSERT INTO tbl_order(`order_id`, `product_name`) VALUES(1, 'prod1'), (2, 'prod2');

Session-1 execute sql:

SET autocommit=0;
SELECT @@autocommit;
SELECT `order_id`, `product_name` FROM tbl_order WHERE order_id=1 FOR UPDATE;

Session-2 execute sql:

SET autocommit=0;
SELECT @@autocommit;
SELECT `order_id`, `product_name` FROM tbl_order WHERE product_name='prod1' FOR UPDATE;

I know session-1 use order_id_idx index item, session-2 use product_naem_idx index item. I don't understand why session-1 blocks session-2.
The locks information:

The GEN_CLUST_INDEX indicates innodb use table-lock.
Could anyone help me?


texts: Data truncated for column - return fatal error

texts:
I get this error in my error log.
Warning: 1265 Data truncated for column ...

How to configure mysql to return a fatal error on this kind of warnings?
Mysql 5.6.27
cnf
sql_mode
STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION