texts: Rules of Simple predicates in distributed database

texts:
There are two rules that should be achieved for a set of simple predicates , which are :

The Set should be complete
The Set should be minimial

I am so confused about them through reading their definitions.

An important aspect of simple predicates is their completeness;
another is their minimality. A set of simple predicates Pr is said to
be complete if and only if there is an equal probability of access by
every application to any tuple belonging to any minterm fragment that
is defined according to Pr.
The second desirable property of the set of predicates, according to
which minterm predicates and, in turn, fragments are to be defined, is
minimality, which is very intuitive. It simply states that if a
predicate influences how fragmentation is performed (i.e., causes a
fragment f to be further fragmented into, say, fi and f j), there
should be at least one application that accesses fi and f j
differently. In other words, the simple predicate should be relevant
in determining a fragmentation. If all the predicates of a set Pr are
relevant, Pr is minimal.

Reference : Principles of Distributed Database Systems
Authors: Özsu, M. Tamer, Valduriez, Patrick
So please someone explain them using this example :

Consider relation ASG.  Suppose there are two applications that access
ASG. The first is issued at five sites and attempts to find the
duration of assignment of employees according to their responsibility.
Assume that managers, consultants, engineers, and programmers are
located at four different sites. The second application is issued at
two sites where the employees with assignment duration of less than 20
months are managed at one site, whereas those with longer duration are
managed at a second site. Derive the primary horizontal fragmentation
of ASG using the foregoing information

Now I have calculated the simple predicates  according to the given information :
From application (1) :
P1 : RESP=”Manager”
P2: RESP=”Consultant”
P3: RESP=”Engineer”
P4: RESP=”Programmer”
From application (2) :
P5: Duration <20 P6: Duration>=20
So Pr is the set that includes {P1 , P2 , P3 , P4 , P5 , P6}
Now how I can test that this set Pr is complete and minimal in simple words, please ??


texts:
The schema

I'm using MySQL
Consider a table with columns

id uuid
type char
is_active Boolean

Here are some facts about the data in this table

type is either 'A' or 'B'
There will be a maximum of 2 records for any id, one for each type
A record with type 'A' will exist for all IDs. A record with type 'B' is optional. That is to say, there will be a minimum of one record for any id, with type 'A'

The scenario

A user can select one or both the types to apply a filter. For example, a user might select type 'A' and filter based on is_active true. Or they might select type 'A' with is_active true AND type 'B' with is_active false.

Our task is to count the number of records which satisfies the is_active condition for selected types.
Here are some scenarios better explanation

If user selects type 'A' and is_active true, count should include all IDs where (type is 'A' and is_active is true) or (type is 'B', regardless of is_active)
If user selects type 'A' with is_active true and type 'B' with is_active false, count should include all IDs where (type is 'A' and is_active is true) or (type is 'B' and is_active is false)

If user restricts is_active for type 'B', IDs without type 'B' should not be included.

If user restricts is_active for type 'A', IDs without type 'B' should be included.

The question

Can this be achieved without using stored procedures? Any help will be greatly appreciated.


texts:
I read in the Oracle Documentation about key-preserved table in Updating Join Views Section.

However, I didn't find any simple to way understand it.

I hope to receive some simple conceptual details other than the official Oracle Documentation.


texts: what is the best choice from those two tables?

texts:
what is the best choice from those tables??

table A : keep DB simple with lower number of tables ... but i will find difficulties if i wanted to extract certain data from column of doses coz it's crowded with age ranges and doses and many routes of admin ... so if i wanted to make analysis for example: all doses given i.v. through all my DB.. i will face difficulties
table B : it make things arranges in separated tables, so it will be easier to retrive data easily..but for one point i had many tables and i have to link them to each others
now i ask: what is the best way to manage my DB and for querying data from it effectively and easily .. following example A or example B ???


texts: Does the fact a table is big can impact the overall performance of a PostgreSQL server?

texts:
If I have a table that is getting bigger and bigger (i.e. it is taking more and more storage space - currently 65GB), can this affect the overall performance of the PostgreSQL server, e.g. impact the speed of queries on other tables?
This is for a PostgreSQL 9.6 database (we plan to upgrade to 10 -> 11 -> 12 later this year), hosted on Google Cloud (Cloud SQL for PostgreSQL).


texts: The service name is invalid while starting EM on oracle 11g on windows

texts:
I am trying to start EM on oracle 11g on Windows. When I performed

emctl start dbconsole

It shows

The service name is invalid more help is available on net helpmsg 2185

and when I wrote

net helpmsg 2185

it shows same thing "the service name is invalid"

What will be the right solution for this?


texts: SQL Server - Nonclustered Index with Include on User Defined Table Type?

texts:
I was working with in-memory User Defined Table Types and got stuck on creating indexes on them.

Here's what I am trying to do:

CREATE TYPE dbo.CustomersTbl
AS TABLE
(
        customerID int NOT NULL,
        customerName nvarchar(100),
        customerAddress nvarchar(100),
        customerCity nvarchar(100),
        customerEmail nvarchar(100)

        INDEX IDX1 NONCLUSTERED (customerID) INCLUDE(customerName,customerAddress,customerCity)
)

    WITH
    (MEMORY_OPTIMIZED = ON);

This works fine if I do not include the INCLUDE part.

If this were a standard table I would create the index with the INCLUDE.

So my question, is there a way to create that index on the User Defined Table Type with the INCLUDE, or would there be a reason why I would not even want to? I am using these table types to create in-memory table variables.

I am currently using SQL Server 2017.


texts: What MariaDB storage engine should i use?

texts:
I have a database with size about 170GB with rows between 20.000 - 700.000.000 rows (depending on the table). Currently i'm using MyISAM storage engine.

In my case, querying (READ) speed is very important, but transactional is optional.

My CRUD Operation estimates is :

98% READ
2% INSERT
< 1% for both UPDATE & DELETE


texts:
I have a problem with restoring the database using RMAN. I have a script that does a full backup of the database every one week. Logs show that the backup was created correctly.

set ORACLE_SID=MY_DB
set LOGFILE=path/full_%DATE%.log
set CMDFILE=path/restore.config

rman target / nocatalog cmdfile=%CMDFILE% msglog %LOGFILE%;

restore.config
run {
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
backup as compressed backupset incremental level 0 database format '\\path\data_%Y_%M_%D.%U';
backup as compressed backupset archivelog all delete all input format '\\path\archive_logs_%Y_%M_%D.%U';
backup as compressed backupset current controlfile format '\\path\control_files_%Y_%M_%D.%U';
backup as compressed backupset spfile format '\\path\spfile_%Y_%M_%D.%U';
CROSSCHECK BACKUP;
delete noprompt obsolete recovery window of 0 days;
}

Backup files are located on another server.
I prepared the file, which was to restore the database from the backup.

run {
shutdown immediate;
startup mount;
catalog start with 'path to dump files';
restore database;
recover database;
alter database open;
}

After this command I have this error:

unable to find archived log
archived log thread=1 sequence=13407
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 07/26/2016 12:16:26
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 13407 and starting SCN of 111916667960

Recovery Manager complete.

Then I reworked the script but it did not help:

run {
shutdown immediate;
startup nomount;
restore controlfile from 'path to control file';
shutdown immediate;
startup mount;
catalog start with 'path to dumps\';
restore database;
recover database;
alter database open resetlogs;
}

and now i have this error:
Oracle Error:

ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01152: file 1 was not restored from a sufficiently old backup
ORA-01110: data file 1: 'Path to db\SYSTEM01.DBF'

I would add that if I create a base and tries to create a backup and then restore it then these commands works.

Could someone explain to me why it does not work and how can I restore the database from this backup?


texts: MySQL 8 - Optimize query using ST_Contains or ST_Within

texts:
I have a table called locations with ~70000 records which includes a geometry column of points and a spatial index as follows:

UPDATE locations SET geom = ST_GeomFromText(CONCAT( 'POINT(', latitude, ' ', longitude, ')' ), 4326);
ALTER TABLE locations MODIFY geom GEOMETRY NOT NULL;
ALTER TABLE locations ADD SPATIAL INDEX(geom);

I have another table called location_boundaries with ~350 records and a geometry column for country/region boundaries converted from a geojson string of polygons/multipolygons as follows:

UPDATE location_boundaries SET geom = ST_GeomFromGeoJSON(geo_json, 1, 4326);
ALTER TABLE location_boundaries MODIFY geom GEOMETRY NOT NULL;
ALTER TABLE location_boundaries ADD SPATIAL INDEX(geom);

I have a table called jobs and I have the following query to return all jobs within a particular region - in this case North West England (note the geojson boundry for this region was taken from the following site: https://nominatim.openstreetmap.org/search?q=North%20West%20England&countrycodes=gb&polygon_geojson=1&format=json

The goal of this query is to generate a paginated result set. However the query takes forever to execute (well over 40 mins before I had to cancel it). To filter through ~6000 jobs is far too slow.

SET @g = (SELECT geom
   FROM location_boundaries
WHERE NAME = 'North West England');

SELECT DISTINCT jobs.*
      FROM  jobs
INNER JOIN locations
        ON locations.id = jobs.location_id
     WHERE St_contains(@g, locations.geom)
       AND jobs.start_at <= now()
       AND jobs.end_at >= now()
       AND jobs.status_id = 4
 ORDER  BY Coalesce(IF(jobs.product_id = 4 AND jobs.refreshed_at <= UTC_TIMESTAMP(), jobs.refreshed_at, jobs.start_at)) DESC
        , jobs.id DESC
     LIMIT 25 offset 0

This is the explain:

+----+-------------+-----------+------------+--------+-------------------------------------------------------------------------------+---------    +---------+-------------------------+------+----------+----------------------------------------------+
| id | select_type | table     | partitions | type   | possible_keys                                                                 | key     | key_len | ref                     | rows | filtered | Extra                                        |
+----+-------------+-----------+------------+--------+-------------------------------------------------------------------------------+---------+---------+-------------------------+------+----------+----------------------------------------------+
|  1 | SIMPLE      | jobs      | NULL       | ALL    | jobs_location_id_foreign,jobs_status_id_start_at_index,jobs_status_id_foreign | NULL    | NULL    | NULL                    | 5731 |    33.33 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | locations | NULL       | eq_ref | PRIMARY,geom                                                                  | PRIMARY | 4       | testdb.jobs.location_id |    1 |     5.00 | Using where; Distinct                        |
+----+-------------+-----------+------------+--------+-------------------------------------------------------------------------------+---------+---------+-------------------------+------+----------+----------------------------------------------+

Why arnt the spatial indexes being used? How can I optimize this query. Any help appreciated.

Strangely when switch the boundary to a different multipolygon say to country level as follows I can get the query to execute in 32 seconds which is still too slow.

SET @g = (SELECT geom
   FROM location_boundaries
WHERE NAME = 'United Kingdom');

When you validate the geojson for Northwest england in qgis it is valid


texts: Should I snowflake or duplicate it across my facts?

texts:
I'm building up a data warehouse to be used by SSAS to create cubes on, and I'm debating between two possible schemas.
In my warehouse, I've got two different fact tables that tracking daily changes in dollar values. Each entity in these fact tables have an underlying Sales Order and Line to which they relate. These SOs and Lines then have other related dimensions, such as customer, product, etc. About 12 sub-dimensions total so far.

My question is if I should be rolling all these sub dimensions up directly into the fact tables, or if I should use a little snowflaking in my warehouse, and have them branching off the Sales Order and Lines dimension instead.

The first option obviously follows a star-schema model better. However, if changes are made such as adding additional dimensions, it becomes more maintenance, basically having to do the ETL twice for each fact table, rather than just the once on the SO dimension. As well, if a new fact is added that relates to Sales Orders, I'd have to go through the whole process again.

As this is my first DW/OLAP project, I'm not familiar on where the line should be drawn on snowflaking, and other people's thoughts would be highly appreciated.


texts: Declarative and Procedural Query Languages

texts:
In the book Database System Concepts 6th Edition, Chapter 2 (Relational Algebra), it states that there are three formal query languages, the relational algebra, the
tuple relational calculus and the domain relational calculus, which are declarative query languages based on mathematical logic.
But then, on another page it states that relational algebra is procedural, whereas the tuple relational calculus and domain relational calculus are non-procedural.
Which of these is the right statement? Could you provide simple examples illustrating the difference?


texts: Is there a product to auto-build indexes based on slow queries?

texts:
I'm looking to either find, purchase, or write a script to identify slow queries, identify potential missing indexes, and when needed create the appropriate index within a reasonable amount of time of identifying the potential index. Anyone know of a product or capability that does it?

Thanks


texts: Column vs Field: have I been using these terms incorrectly?

texts:
I feel kind of embarrassed here, I've always used the terms "column" and "field" completely interchangeably, which recently caused some confusion in a technical discussion.

I was told, though, that this wasn't correct, that it should be (translating each term into spreadsheet terminology, ignoring data types and all the other stuff that make databases useful):

Database Column: like a spreadsheet column
Database Record: like a spreadsheet row
Database Field: like a spreadsheet "cell" (a specific column of a specific row)

Is this right? I could have sworn that column and field are used more interchangeably than that. I certainly have been.

So we don't add fields to a table, we add columns to a table, and fields are only relevant when talking about data within a record?

Other thoughts on column vs field?

Edit: to clarify, the current context is MS SQL Server. My background before SQL server was MS Access, which might influence my use of these terms.


texts:
I am using an instance of Postgres on RDS and I am trying to run:

=> create language plperlu;

but I get:

ERROR:  must be superuser to create procedural language "plperlu"

If I run:

=> grant rds_superuser to treex;
NOTICE:  role "treex" is already a member of role "rds_superuser"
GRANT ROLE

Is it actually possible to use perl extensions for Postgres on RDS or the only solution is creating a Docker instance with Postgres and Perl modules preinstalled?


texts: MySQL: Does the "length" setting affect table size in bytes?

texts:
Regarding setting the length of allowable characters for a field (11) in the example:

Does this number affect the size of the table?

For example, I want to create a char type column and I only want to use three letter codes. Does setting the length to 3 make for a smaller byte size overall?

Is it a waste of space to set it to 16 when I know I'm only going to be using 3? Or is it purely for organizational purposes, i.e. nothing beyond three characters is allowed to be written?


texts: ORA-01031: insufficient privileges while connecting with sys as sysdba

texts:
I installed Oracle database 10g as an administrator on a windows 10 machine, it worked fine, but when i try to connect sys as sysdba it gives me the error:

ORA-01031: insufficient privileges

I checked sqlnet.ora file and if SQLNET.AUTHENTICATION_SERVICES is NTS and it is:

SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

I added the user to the ORA_DBA group and i checked with:

c:\Oracle>echo %username%

and

c:\Oracle>NET LOCALGROUP ORA_DBA

and the user is in the group.

I don't know what else i should do, anything will be helpful.


texts:
I've written ETL in MongoDB which processes data from multiple collections and insert the processed data in new collection. While inserting processed data to new collection, I get Duplicate key Error E11000 due to reprocessing of some already existing records and _id conflicts with existing. How can I upsert these duplicate documents??
I'm using this:
var data = db.getCollection("col1").find().toArray()
db.ProcessedData.insert(data,{upsert : true})

But no use.
I don't want to loop through all the records in col1 to insert one by one as it has large number of docs every run.


texts: how to script out all the permissions in a folder using powershell?

texts:
Using the script below I find out where are the data and log folders:

    declare @rc int,
    @dir nvarchar(4000)

    declare @data    nvarchar(4000)
    declare @log     nvarchar(4000)
    declare @SQLPath nvarchar(4000)
    declare @Backup  nvarchar(4000)

    exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLPath', @dir output, 'no_output'
    select @SQLPath = @dir

    ----------------------------------------------------------------
    --For the default data location:
    ----------------------------------------------------------------

    exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultData', @dir output, 'no_output'

    if (@dir is null)
    begin
    exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\Setup',N'SQLDataRoot', @dir output, 'no_output'
    select @dir = @dir + N'\Data'
    end

    select @data = @dir

    ----------------------------------------------------------------
    --For the default log location:
    ----------------------------------------------------------------
    exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'DefaultLog', @dir output, 'no_output'

    select @log = @dir

    ----------------------------------------------------------------
    --For the default Backup location:
    ----------------------------------------------------------------
    exec @rc = master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', @dir output, 'no_output'

    select @Backup = @dir

    SELECT
    [data path] =     @data,
    [log path] =      @log,
    [sql path] =      @SQLPath,
    [Backup Folder] = @Backup

Then I manually go to that folder and have a look at the permissions sql server (the sql server services account) has on that folder.

I would like to script those permissions so that I can apply them to a new folder.

Using powershell, how can I script all those permissions from this folder let's say folder a:\data and apply those permissions to folder b:\data on the same machine?


texts: Database design for product purchase history

texts:
Say I run a store that sells products of some sort. A product has typical attributes, most importantly price. When a customer makes a purchase, an invoice which references one or more products is generated.
Question: If the staff of the store updates the product, we may not be able to figure out how much a customer paid for a particular item because that product's price has been updated. How do I handle this use case? Three options I can think of:

Add a version column to the products table. When making a change, insert a new row and increment the version number, instead of updating. When I reference a product from an purchase, I reference a specific version of the product so I always know how much a product cost at the time of purchase.

The problem I see with this is that, ignoring performance and ever-increasing need for storage, whenever I need to update a price (or other product attribute), I need to update all foreign key references to that item. In my database I may have a dozen such tables, most of which do not need 'historical' information like past prices.

Create a separate product_history table, with the columns of the product table plus additional version and change_time columns with all previous versions. When updating the product table, I will save the previous version to the history table. Purchases, and any other table that needs historical information will reference rows in product_history, not product.

Add a version column to the products which is incremented every update. When there is a purchase, copy the product to a purchase_product table. The purchases table will reference purchase_product, not product. I would only keep one row per version of a product, so there is no worry of the table exploding in size when a lot of people buy the same item.

3 seems slightly better 2 because I only keep versions of products that I actually need (not for every single change of the product price). However, that means I won't be able to run analytics on when a product was NOT selling because (say) the price was too high...
Or, is there a more obvious solution?


texts: Can I throttle individual databases instances on one SQL Server 2005 box

texts:
I am running a very old server that has a database that is being permanently retired within 14 months.  My company will not invest anymore in hardware or software for this system as we are moving to a whole new system that is hosted within 6 months.  I have only found reference to this Exchange Entry, but that is about servers and potentially using Virtual Servers.

My server services about 500 users, of which roughly 100-125 are concurrent during any given time of the work day.   There are four 1.5 hr periods of very heavy activity during the day, and as the DBA and developer assigned to work with this server, I have about 1 hour in the morning and overnight jobs to do anything that could seriously impact performance for the machine.  There are 2 active production databases on it that will be used until the change over occurs in 3-6 months, and a development instance that I use for testing/fixing issues, since this is the only SQL server left in the organization.  (For those DBA's cringing at this point; All your DBA concerns about safe practice, let alone best practice, backups and DR, are valid and I have raised them, but they REALLY will not spend anymore on this system or the infrastructure.  So a new box is Moot.)

So, I am left with the desire to throttle (if possible) the two production databases to use no less than 80% of the resources, and give me a 0-20% usage so that if I run something like a DBCC command on the DEV area, it does not chew up the resources and give me the rash of 50 phone calls from the production users 5 minutes later?  I am looking for the ability to throttle so that I can do the data preparation and extraction testing as we migrate to the new system with out messing too much with the day to day business activity.


texts: Unix timestamp vs timestamptz index performance

texts:
I'm new to the world of DB admin and have a large table (2 billion rows) of cryptocurrency tick data for multiple currencies in a Postgres database. Most of my queries will involve selecting a moving time window for each currency individually - for this, I currently have an index on my "timestamp" and symbol columns.
Querying appears to be pretty slow, around 100 seconds to query the last 2 days of data for 1 symbol (~130,000 rows) (I checked with explain analyze and the composite index is being hit)
Here's the kicker - I have the timestamps stored as Unix timestamps as int8 e.g. 1611751286078083
Will converting these to timestamptz, storing these in a new column and then using that as the index be worthwhile/ speed up query? I read that it's bad practice to create a new column with data that is available in another column already.
If so, is it likely that adding this new column will break my existing (constantly running) INSERT script that only inserts on the columns currently defined?
tldr;
Is an index on timestamptz noticeably more performant than on Unix timestamps stored as int8.
Index Scan using huobi_timestamp_symbol_idx on huobi  (cost=0.58..438627.67 rows=177735 width=48) (actual time=0.041..101131.528 rows=488785 loops=1)
  Index Cond: (("timestamp" > '1611751286078083'::bigint) AND (symbol = 'YFII'::text))
  Buffers: shared hit=68973 read=219478
  I/O Timings: read=99426.796
Planning Time: 13.925 ms
Execution Time: 101179.934 ms

I have 1 index on (timestamp desc, symbol) and the primary key is on (timestamp, market) and am using Postgres 12.3 hosted on AWS.


texts: InnoDB & Isolation Levels - Aren't repeatable reads a bad thing?

texts:
I was reading about InnoDB's isolation levels and it makes sense for the most part, but what I don't get is why are unrepeatable reads a bad thing? Shouldn't it be the other way around?

For examples:

So say we had a stock column for selling products and each time someone bought an item we would take 1 item from the column, with Repeatable read or serializable isolation levels wouldn't this corrupt the data integrity?

For example:

TX A: start transaction;
TX B: set session transaction isolation level repeatable read;
TX B: start transaction;
TX A: select stock from products;               -- val = 8
TX B: select stock from products;               -- val = 8
TX A: update products set stock = stock - 1;    -- val = 7
TX B: select stock from products;               -- val = 8
TX A: commit
TX B: select stock from products;               -- val = 8, incorrect!
TX B: commit;
TX B: select stock from products;               -- val = 7

Unless it uses the CORRECT value when running an update?


texts: Database Normalization - Can one field point to another?

texts:
This may sound like a dumb question, but I am a software developer new to database design... So the concept makes sense to me, but maybe this doesn't carry--

Is it possible for a field in a record in Table B to simply point to a field in a record in Table A?

Example: If I have two tables... one containing a list of all employees and another containing a history of those employees coming and going from the office.

Employees
    ID
    First Name
    Last Name

History
    Event ID
    Event Time
    Event Type (Entry/Exit)
    EmployeeID

I can establish a relationship between History.EmployeeID and Employees.ID with a foreign key, but that duplicates data. So then History.EmployeeID and Employees.ID would contain the same ID number which would be stored in memory twice. So if I went into the database and changed John Smith's employee ID, I would need to write a script or something to scour the database and do a find/replace for that ID.

What I want is for Employees.ID to contain the real ID and History.EmployeeID to simply contain a pointer to Employees.ID. That way, if I updated John Smith's ID, the change would be centralized to one field in one table.

Is this possible?

Thanks


texts: Does anyone have experience with Scalearc sharding and it's ability to scale ? Does Scalearc needs to have the whole data in memory or does it behave well with disk storage ? I'm thinking to big database application like 10TB and above.


texts: Locking issues under transaction on Postgres

texts:
I recently got a "Out of shared memory - You might need to increase max_locks_per_transaction" error.

This is a long running process where I import a bunch of CSV files after proper processing.
I loop through them and open a transaction for each file, process it and then when I'm done with the file, close the transaction and move to the next file.

These files are not big, the biggest clocking in at around 12K lines.

The processing I do, as far as PG is concerned, amounts to doing a few SELECTs (a couple per line, no joins or anything) and then an UPDATE of one existing row (at most one per line).

The problem is, pg_locks fills up with transactions that have no relation, are of type transactionid and have a mode of ExclusiveLock.
I got as many as 20K rows like that in pg_locks and, besides the ones that, as expected, refer ti the two tables I'm using, an overwhelming majority looks like this (sorry, the indentation will be screwed up, I'm posting a link to pastebin too):

"locktype" "database" "relation" "page" "tuple" "virtualxid" "transactionid" "classid" "objid" "objsubid" "virtualtransaction" "pid" "mode" "granted" "fastpath"

"virtualxid" "" "" "" "" "11/18291" "" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "t"
"transactionid" "" "" "" "" "" "61840165" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "f"
"transactionid" "" "" "" "" "" "61843843" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "f"
"transactionid" "" "" "" "" "" "61833173" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "f"
"transactionid" "" "" "" "" "" "61835511" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "f"
"transactionid" "" "" "" "" "" "61846000" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "f"
"transactionid" "" "" "" "" "" "61838308" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "f"
"transactionid" "" "" "" "" "" "61832936" "" "" "" "11/18291" "6308" "ExclusiveLock" "t" "f"

http://pastebin.com/V4K3AvHD

Basically, none of those records have either a database or a relation, only a transaction id.

The only different row is the first one, which I take as coming from the transaction acquiring a lock on itself.

All the locks are being acquired by the same connection, and apparently only when executing the UPDATE, which really is no harder than

UPDATE t SET foo = COALESCE(foo, 0) + 23.4 WHERE bar = 'hey' and baz = 'ho'

and can affect at most one row.

When I'm done processing the file and commit the transaction, all those thousands of records just disappear from pg_locks, going back to a more normal number of between 15 and 30.
It's as if each UPDATE somehow acquired a lock, making the record count of pg_locks increase to the tune of one every one or two seconds.

I assume I must be doing something wrong, but I can't figure out what.

As a temporary workaround I indeed increased max_locks_per_transaction, but I would really like to actually solve the problem.
I don't think an increase of max_locks_per_transaction is warranted for my use case, which is quite simple.

Also, note that nobody else is accessing the two tables I'm using. Nobody is even accessing any table in the same schema.

I'm on Windows Web Server 2008 R2.

PostgreSQL 9.3.5, compiled by Visual C++ build 1600, 64-bit

Connecting with Npgsql 2.2.5

Can anyone shed some light?

I thought about possible indexing issues, but the table in question is not huge by any means (fewer than 50K rows), and doing a SELECT on it with the same criteria used by the UPDATE is very fast.

Thanks in advance.


texts: Postgres: SELECTing bytea data partially with offset and length

texts: I have a table containing a bytea column, with an average entry size of 5MB. Is it possible to query from random points in the bytea data (given an offset and length of data required) instead of SELECTing the whole byte string into the memory?


texts: Sum of table sizes don't match with mdf size

texts:
I just ran a disk usage report on my database and found it's grown a lot over the past month and is currently using 3.98 GB on data (it was around 300 MB on last month)

However, when I run the disk usage by tables report I see that the space usage from the tables are currently very far from 3.98 GB

How can I find what is using the remaining space so that it adds up to 3.98 GB?

EDIT

This is the screenshot for the Shrink file dialog:


texts: Query function won't take rows with given default value

texts:
I have only recently delved into the depths of PostgreSQL and have finally hit a snag. There are 3 tables, Notes, Tags, and Notes_Tags (Many to Many). Some Notes do not contain tags, but still need to be displayed. Maybe there is something that I have a poor understanding of that is not quite apparent to me, but I can't seem to figure out how to get it to display Notes that do not have Tags.

Here is the query I've built:

    select n.id, n.created_at, n.title, n.body,
    case count(t.name) when 0 then '{}' else array_agg(t.name) end as noteTags
    from notes_tags nt
    inner join tags t on t.id = nt.tag_id
    left outer join notes n on n.id = nt.note_id
    where n.user_id = userId and
      case
        when tagFilter = 'all' or tagFilter = '' then t.name != ''
            else t.name = tagFilter
        end
    group by n.created_at, n.title, n.body, n.id
    order by n.created_at DESC
    limit note_limit;


texts: Azure SQL Database in different locations

texts:
I'm deploying a Sitecore website using Azure and Azure SQL, and one of the requirements is to have some of the databases in different Azure Regions but sharing the same data among the other databases.

For example:
1. Web database will be in East US and West US
2. Sitecore website also in East US and West US
3. Website from East US will point to East database, and Website from West US will point to West database
4. Databases must be in sync (some lag is acceptable)

I had a look into Azure SQL Data Sync but looks like it is in preview mode, and I was wondering if Azure SQL has another feature to address. Despite the fact Azure SQL will be in place, I'd also wondering if on-premises SQL have anything similar?


texts: Transaction Log for database 'myDatabase' is full due to ACTIVE_TRANSACTION when creating a Non Clustered Index

texts:
I am trying to build a non clustered index as follows:

CREATE NONCLUSTERED INDEX IX_MyIndexName ON MyTable(MyColumn) INCLUDE (Column1,Column2,Column3)

However, when i do so, I get a 9002 error:

  The transaction log for database 'myDatabase' is full due to
  'ACTIVE_TRANSACTION'.

When I run DBCC OPENTRAN()I get

  No active open transactions

I am thinking that it is the create index statement itself that is the active transaction filling the log as by the time I run DBCC OPENTRAN() the transaction will have rolled back?

The database is in simple recovery mode (It is a reporting database created from a backup of the production database)

The log is set to an initial size of 2MB, to autogrow by 5120MB and is limited to 100000MB.

If I run DBCC SQLPERF(LOGSPACE) I can see the log size is 1.125MB and Log space used is 31%

The disk that holds the log file has over 1TB free space

I tried to change the initial size of the log using

  ALTER DATABASE ... MODIFY FILE....SIZE=...

and am getting

  MODIFY FILE encountered operating system error 59(An unexpected
  network error occurred.) while attempting to expand the physical file
  \\path\to\my\file.ldf

So I wonder if when SQL server is trying to expand the transaction log to process the transaction, there is this unexpected network error occurring and therefore the transaction fails.

I get the network error every time I run the MODIFY FILE command for the database in question and the same happens for another database (both of these databases have active connections) but I can resize other databases we have that do not have any active transactions (the files for all of the databases are in the same location) so I am thinking this network error may be related to number of connections?

SQL server is running as a domain account and that account has full control over the folder that the database files are stored in.

I suspect moving the database to a local drive rather than a network drive could solve this so that is my next step but that will have to be out of hours.

In the mean time, does anyone have any suggestions of what may solve this? Am I heading down the right track?

EDIT

I've added an additional log file to the database, this time it's initial size is 10,000MB, autogrowth by 64MB, unlimited max space. I set the location as a local drive and ran the create index statement and it worked.

I then dropped the new log file and recreated on the same network location, dropped the index I created before, re-ran the create index and all worked OK.

Next step is to go back to the single log file and put it on a local drive (I'll leave the size the same) and see if the log auto grows


texts: Why does shrinking the transaction log generate lots of log (LOP_SHRINK_NOOP) and not shrink the log?

texts:
I just tried shrinking an overly big log file (FULL recovery model). This operation is not expected to always succeed and indeed the log was not shrunk.

But I observed that a lot of new log was generated. At a log file size of about 20GB each shrink attempt generated a few hundred MB of new log. I repeated the shrink operation a few times and received the same result each time.

Reading out the log I found that all of that space was for LOP_SHRINK_NOOP log records. The web does not have information about this log record type.

DBCC LOGINFO shows that there is a mix of active and unused VLFs. There is one used VLF at the end of the file ( Status = 2).

Why does attempting to shink the log sometimes...

...cause lots of new log data?
...not shrink the log file although apparently work was performed on it?
...generate LOP_SHRINK_NOOP records? What does that log record type mean and when is it expected to appear?

The statement used was:

DBCC SHRINKFILE('logfilename', 8192) --shrink to 8GB

Log file size on disk: 20GB

Log allocated size as reported by DMV: ~2GB

The log was not backed up prior to performing the SHRINKFILE. After performing a log backup the shrink succeeded.


texts: PostgreSQL: permission denied for relation

texts:
I'm a bit confused about setting permissions in PostgreSQL.

I have these roles:

                             List of roles
 Role name |                   Attributes                   | Member of
-----------+------------------------------------------------+-----------
 admin     | Superuser, Create role, Create DB, Replication | {}
 meltemi   | Create role, Create DB                         | {rails}
 rails     | Create DB, Cannot login                        | {}
 myapp     |                                                | {rails}

and databases:

                                    List of databases
        Name         | Owner  | Encoding |   Collate   |    Ctype    | Access privileges
---------------------+--------+----------+-------------+-------------+-------------------
 myapp_production    | rails  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 ...

user myapp has no problem querying the myapp_production database adding & deleting records. I'd like for meltemi to also be able to query the same database. So, I created a role rails which owns the database and made both meltemi and myapp members of rails. But I still get permission denied for relation errors. Meltemi can view the schema but can't query the DB.

I just noticed (with \dt command) that myapp is the owner of the tables:

             List of relations
 Schema |       Name        | Type  | Owner
--------+-------------------+-------+-------
 public | events            | table | myapp
 public | schema_migrations | table | myapp
 ...
 public | users             | table | myapp
 ...

The tables were created via an ORM (Rails' ActiveRecord migrations).

I know authorization is very different in PostgreSQL (as opposed to MySQL & others I've used). How should I be setting up my database so that different users can access it. Some should be able to CRUD but others may only be able to Read, etc...

Thanks for any help. Sorry, I know this is a very basic question but I haven't been able to find the answer myself.


texts: If a user is created in a primary postgres database, will the user be copied to a follower?

texts:
Say a user is created in postgres 9.6 with some specific privilege set:

CREATE USER foo_user WITH ENCRYPTED PASSWORD 'foo_password';
GRANT CONNECT ON DATABASE bar_db TO foo_user;
GRANT USAGE ON SCHEMA public TO foo_user;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO foo_user;
GRANT SELECT ON ALL TABLES IN SCHEMA public to foo_user;

(Copied and modified from here.)

If that user is created on a primary database, and there is a follower synced with WAL streaming replication, will that user be created with an identical set of privileges on the follower automatically?

It looks like the answer is yes, because users and permissions are represented with tables, and those special tables are probably treated the same as any other table for write ahead logging.  But I can't confirm this in any of these docs pages.  Can confirm or debunk my thinking?  And ideally point me at some related documentation that I can't seem to find?


texts: database to use for Automated Teller Machine

texts:
I have been working on an Automated Teller Machine project using Visual Studio and C# for my Bachelors Degree project.

Which database platform should I use for this?  I'm considering MySQL, but am open to other suggestions.


texts:
We are generating logs like following example(this is a table no actual pipes):

2014-06-10 09:00:03.457 | Channel1 | Operation3 | Function15 | 15ms
2014-06-10 09:00:08.245 | Channel2 | Operation5 | Function10 | 22ms
2014-06-10 09:00:22.005 | Channel1 | Operation3 | Function15 | 48ms

Think about this with at least 25-30 columns of time-series application log data. Every row is a transaction. So I aggregate the same ones and get the sum(also average of the durations). After the aggregation, my unique clustured key is all of the columns except the metrics like transaction duration or sum.

In example between 2014-06-10 09:00:00 - 2014-06-10 09:01:00 our example will be:

2014-06-10 09:00:00 | Channel1 | Operation3 | Function15 | 2 | 31,5ms
2014-06-10 09:00:00 | Channel2 | Operation5 | Function10 | 1 | 22ms

Is there a better way to do this? Processing this data also costs me a lot while showing it to users for monitoring and analyzing purposes.

UPDATE-1

I think this question need more clarification. The raw log data is like in the first example. I'm running an ETL agent that gets it as per minute interval and aggregates to an another table like in the second example.

The second table has a primary key of all columns except metric columns(Count,ResponseTime). Because in the end 2014-06-10 09:00:00 | Channel1 | Operation3 | Function15 is the only thing that gives me uniqueness.

When the user wants to analyze it. He/She chooses values from every column which I named "Dimensions". He/She wants to see "Function15 transactions which are on Channel1" or "Operation5 response times on Channel1" and so on. I'm storing the data like this to achieve these requests from the user.

Regards


texts: sp_blitzcache not returning an execution plan for a long running query

texts: No execution plan is returned only a null value in the DMV sys.dm_exec_query_plan's query_plan column.  Any clue as to why?  I have used sp_blitzcache 'reads' and 'duration' to show the long running query but no execution plan returns.


texts: Can't delete a login due to db ownership — but no dbs are owned by this login

texts:
I have a SQL Server 2014 instance that's installed on an OS that I've migrated from one domain to another. The new domain and user account are from separate forests, but they bear the same descriptive names (e.g. DOMAIN\User).

Naturally, I can't log into the database with the old domain account, so I need to delete that. The trouble is that I can't.

I keep getting the error message that the login owns one or more databases. But it's very clear that it doesn't—I've cleared mappings and reassigned dbo in all databases.

Yet the error persists. This is very odd.

Is there a way to determine what's preventing me from deleting this login so I can proceed to add the same-named account from the new same-named domain?

--EDIT--

Correction: I'd reassigned dbo in all databases that displayed a login in the Login name field. That field was empty for the two RS databases, incorrectly leading me to believe that they weren't owned by the login that I was trying to delete. The script provided by @hot2use did the trick.


texts: In the my.cnf for our PXC cluster nodes, we have been using root user for wsrep_sst_auth with all the admin privileges that comes with the root account. We know using the root user:password inside the config file might not be the most responsible and safe thing to do, so we are thinking to change this. So my question is, what are all the necessary permissions required for the wsrep_sst_auth user that would make the PXC work effectively without having any issue whatsoever?


texts: Connecting SQL Server using Server IP Address

texts:
This works when I try it internally (Within the LAN network)

sConnString = "PROVIDER=SQLOLEDB;" & _
            "Data Source=192.168.1.200,1433;" & _
              "Initial Catalog=MyDB;" & _
              "User Id=MyUserName;" & _
              "Password=MyPassword;"

But this fails when I try to connect it over internet using server IP address.

sConnString = "PROVIDER=SQLOLEDB;" & _
            "Data Source=XXX.XXX.XXX.XX,1433;" & _
              "Initial Catalog=MyDB;" & _
              "User Id=MyUserName;" & _
              "Password=MyPassword;"

Any suggestions? The firewall is completely turned off.


texts:
I need some advice on how to store information about who created/updated an "entity". I keep going back and forth between Option1, 2 and 3 (see below):
Background

This is a legacy system and there are currently two types of users: e.g. employees and customers.
The users have different userId (saved in different tables...)
The employee key is a char of 50 characters and the customerId is a char of 30 characters.
Also, the employee is working at an office (might be several), so we want to track from which office the update/insert came from
The office key is a char of 50 characters.
The database is quite large: 200+ tables

Option 1
Store the "meta data" together with the entity.
Create table order(
    orderId int not null primary key auto_increment
    ...
    insertedByEmployee CHAR(50),
    insertedFromOffice CHAR(50),
    insertedByCustumer CHAR(30),
    insertedDate dateTime,
    updatedByEmployee CHAR(50),
    updatedFromOffice CHAR(50),
    updatedByCustomer CHAR(30),
    updatedDate timestamp
);

The primary advantage of this method is that it is straight forward fo find the updated field. The downside is that there are redundant columns.
Option 2
In this scenario I've moved the "meta data" to a separate table. Adds the possibility to have history. Don't know how relevant history is in this case though.
Create table order(
    orderId int not null primary key auto_increment
    ...
);

CREATE TABLE order_meta (
    order_metaId INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    orderId INT,
    customerId CHAR(30),
    employeeId CHAR(50),
    officeId CHAR(50),
    action ENUM('create','update'),
    dateTime` TIMESTAMP
);

What I like about this approach is that some redundancy is removed. Also, if we want to track "reads" we could simply updated the "action" column. This design makes it harder to find the last updated record though. Perhaps there should be updatedDate in the entity table. Also, I'm going to need one meta-table per entity, which i really don't like.
Option 3
Instead of using multiple columns for user id:s, create a "virtual user" and assign an id which will be used regardless of type.
A generic table (meta) is used to track changes to all entities. The downside is that this table might get used A LOT and makes querying for changes more cumbersome. I'm worried it might become a bottleneck.
This design doesn't require creating a separate table for each entity. Finding the actual user performing the update/insert is even more complicated than the other options. On the other hand I think this one might scale better.
Pseudo code (vuserId currently doesn't exist):
Create table order(
    orderId int not null primary key auto_increment,
    updated TIMESTAMP NOT NULL
    ...
);

CREATE TABLE virtual_user(
    vuserId INT NOT NULL PRIMARY KEY AUTO_INCREMENT),
    ...
);

CREATE TABLE customer(
    vuserId INT NOT NULL PRIMARY KEY,
    customerId CHAR(30),
    ...
);

CREATE table employee(
    vuserId INT NOT NULL PRIMARY KEY,
    employeeId char(50)
    ...
);

CREATE TABLE meta (
  metaId INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  entityName VARCHAR(30),
  entityId VARCHAR(50),
  vuserId INT,
  officeId CHAR(50),
  action ENUM('create','update'),
  dateTime TIMESTAMP NOT NULL
);


texts: Recursive CTE to find Total for all children

texts:
Here is an assembly tree that I want to search using a recursive T-SQL Query (presumably CTE) with the expected results below. I want to know the total amount per assembly given any part.

Meaning if I search for 'Rivet', I want to know the total count at each level within the assembly, not just the direct children count.

Assembly (id:1)
    |
    |-Rivet
    |-Rivet
    |-SubAssembly (id:2)
    |   |
    |   |-Rivet
    |   |-Bolt
    |   |-Bolt
    |   |-SubSubAssembly (id:3)
    |      |
    |      |-Rivet
    |      |-Rivet
    |
    |-SubAssembly (id:4)
       |-Rivet
       |-Bolt

    DESIRED Results
    -------
    ID, Count
    1 , 6
    2 , 3
    3 , 2
    4 , 1

Currently, I can get the direct parents, but want to know how to extend my CTE to allow me to roll this information upward.

With DirectParents AS(
--initialization
Select InstanceID, ParentID
From Instances i
Where i.Part = 'Rivet'

UNION ALL
--recursive execution
Select i.InstanceID, i.ParentID
From PartInstances i  INNER JOIN DirectParents p
on i.ParentID = p.InstanceID

)

select ParentID, Count(instanceid) as Totals
from DirectParents
group by InstanceID, ParentID

Results
-------
ID, Count
1 , 2
2 , 2
3 , 2
4 , 1

Creation script

CREATE TABLE [dbo].[Instances] (
  [InstanceID] NVARCHAR (50) NOT NULL,
  [Part] NVARCHAR (50) NOT NULL,
  [ParentID] NVARCHAR (50) NOT NULL, );

INSERT INTO Instances
Values
  (1, 'Assembly', 0),
  (50, 'Rivet', 1),
  (50, 'Rivet', 1),
  (2, 'SubAssembly', 1),
  (50, 'Rivet', 2),
  (51, 'Bolt', 2),
  (51, 'Bolt', 2),
  (3, 'SubSubAssembly', 2),
  (50, 'Rivet', 3),
  (50, 'Rivet', 3),
  (4, 'SubAssembly2', 1),
  (50, 'Rivet', 4),
  (51, 'Bolt', 4)


texts: How to create a 'keywords' table from a column's values

texts:
I have a table with 8 million+ rows in it. It stores jobs and their characteristics. There is a column in it that holds job titles. I am planning to create a keyword table based on the job titles from that column, so that later the keyword table can be used to implement an auto-complete field on the client side.

For example, if the job title is The Java programmer, then I want to store Java and programmer. I want to eliminate all special characters, get rid of some words like a and the and break the string on space. Can you please advise me on what my approach should be? From where should I start? Is this even the right way of creating a table to store keywords?


texts:
I have 3 tables: users, posts, videos. The last 2 tables have user_id that associate with users.id (first table).

The problem is that when I try to run inner join query and return all posts and videos that have user_id and exists in both tables, I get multiple same records.

select * from `users`
inner join `videos` on `users`.`id` = `videos`.`user_id`
inner join `posts` on `users`.`id` = `posts`.`user_id`
where `users`.`id` = ?
limit 50 offset 0

If I run the queries separately, I get 6 rows and 8 rows but with the join I get 48 rows.


texts: Why the decimal marker I get is not the one defined in NLS_NUMERIC_CHARACTERS?

texts:
According to the Oracle documentation, NLS_NUMERIC_CHARACTERS stores the decimal marker used by the current session. To get the current NLS_NUMERIC_CHARACTERS, I use this request:

select value
from nls_session_parameters
where parameter = 'NLS_NUMERIC_CHARACTERS';

And I get this result:

VALUE
,

So my decimal marker is 'comma'.

But when I make a SELECT request to get a decimal value, the decimal marker return by Oracle is different. If I run this dummy request:

select 3/2 decimal_value from dual;

I get this result:

DECIMAL_VALUE
1.5

So, my questions are: why is it not 1,5 as expected? What need I change to get the decimal marker as defined is NLS_NUMERIC_CHARACTERS?

Important note: I can't change my SELECT request.

After reading this article (suggested by @EdStevens in the comments), here are some precisions.

According to the article, the decimal marker is defined by :

The NLS values defined in the database initialization parameters
The NLS settings of the OS of the client
The NLS settings of the Oracle client
The parameters of the TO_CHAR function if it is used in the SELECT statement

In our case, because we don't use TO_CHAR function in the SELECT statement, the decimal marker should be defined by the NLS settings of the Oracle client (NLS_NUMERIC_CHARACTERS) but we saw above that is not the case.

Interresting fact, if I use TO_CHAR function in my SELECT statement:

select TO_CHAR(3/2) decimal_value from dual;

I get this result:

DECIMAL_VALUE
1,5

If nlsparam is omitted, the documentation says:

  If you omit nlsparam or any one of the parameters, then this
  function uses the default parameter values for your session.

So, when we use TO_CHAR function, Toad use the NLS value of the session but not in the others cases.

Is it possible that Toad performs an implicit conversion (NUMBER -> CHAR) before printing the value in the data grid without using NLS parameters? If it is the case, why this implicit conversion don't use NLS_NUMERIC_CHARACTERS? What can I do to get my number in the correct format?

Here is a little recap of the informations present in the comments:

The problem I expose above is made with Toad for Oracle (version
9.1.0.62) but I also reproduce this issue on the application I develop who uses OCI drivers.
The session value of NLS_TERRITORY is France, NLS_LANGUAGE is French and NLS_LANG is FRENCH_FRANCE.WE8MSWIN1252. Those values are defined in the Windows registry.
If I use SQL*Plus, I get a correct result. Indeed the decimal marker corresponds to the one defined in NLS_NUMERIC_CHARACTERS and if I change this value, it directly changed the decimal marker I get (that is not the case with Toad).


texts: We have hit  deadlocks in 10.1.25-MariaDB-1~trusty. Looks like it has something to do with select for update queries. But the deadlock increases when number of tps increases. Is there any alternative to fix this from DB end or from application end to change the logic?


texts: Problem connecting to SSRS reporting services

texts:
When I try to connect to my reporting services server (SQL Server 2005) from my local machine, SQL Server 2014 I get the error message below

  Invalid Namespace (System.Management)

But when I RDP into my reporting services server and connect to reporting services there, everything is fine.

There is nothing wrong with the reporting services.

Is there a way to connect to SQL Server 2005 Reporting Services from a SSMS 2014?


texts: How to mirror the privileges of an user to another in Oracle Database?

texts:
I need to grant all the privileges of a user (say 'AAA') to another user (say 'BBB').

Is there such an Oracle command (11.2.0.3.0)?


texts: postgresql optimize query with 'where not in' clause

texts:
I have query to select all rows from table1 that they are not in tabl2 then insert them in table2.
This query is taking log time to execute since I have millions of rows in table2.

Is there better way to optimize or rewrite this query to make it execute faster.

WITH t1 AS (
select *
from table1
where id  not in (select id table2))
insert into table2
select * from t1 ;


texts: How do I determine which IP I should use for my MariaDB Galera Cluster?

texts:
I have set up MariaDB Galera Cluster (let's say 3-4 nodes).

Which IP should I specify in the configuration of my application?

Should I specify all of them or I should have some proxy in front of the Cluster so the app can see the cluster as one?

Can I have the cluster without a proxy and still see it in my app as one node not as a cluster?


texts: Select sum of a field that is in already in the select

texts:
select Article.CodArticle as [Code], isnull(Stock.Stock,0) as [Stock],
    isnull((select Stock - isnull(sum(quantity),0) from StockReserve where IDStock=Stock.IDStock),0) as [Avalaible Units]
    from Stock
        inner join Article on Article.IDArticle=Stock.IDArticle
        inner join Warehouse on Stock.IDWarehouse=Warehouse.IDWarehouse
        where Warehouse.CodWarehouse in ('15','400') and Stock.CodCompany = 100
        order by Article.CodArticle

I have this query which is giving me duplicates for stock as it founds values in both warehouse 15 and 400. So my idea to solve this problem was to just sum that stock with a proper group by and problem solved.
BUT, there's also the 'Avalaible Units' column which uses the stock field.
If that wasn't in the select, i could just write:
select Article.CodArticle as [Code], isnull(sum(Stock.Stock),0) as [Stock]
    from STKStock
        inner join Article on Article.IDArticle=Stock.IDArticle
        inner join Warehouse on Stock.IDWarehouse=Warehouse.IDWarehouse
        where Warehouse.CodWarehouse in ('15','400') and Stock.CodCompany = 100
        group by Article.CodArticle
        order by Article.CodArticle

And that gets rid of my duplicates, easy enough. But of course i cannot just erase that column.
And with that in the mix, I get group by errors of Stock and IDStock not being in the group by or using aggregate functions. Of course if u just simply add those fields to the group by, the query doesn't give errors, neither a proper result. How on earth are you gonna sum the same field (Stock) that you are grouping by?
How do I solve this? Do I make a subquery for sum(Stock)? I mean, the original query had a sum function inside a subquery and it didn't produce any group by errors despite not even having the clause.
That makes me think, if I somehow turn my sum(stock) into a subquery I won't have to fight with group by?
I'm sorry, I'm but a noob in SQL. I kind of understand what I'm doing but not really...
How the subquery of (select Stock - isnull(sum(quantity),0) from StockReserve where IDStock=Stock.IDStock)works? and how is it not having any trouble using an aggregate without a group by?
Any tips on which direction to look for to solve this? Do I have to remake the 3rd field somehow? Is sum(stock) ok and I have to write a proper group by?


texts: Where is it best to store a reference of the user-selected default?

texts:
Consider a user can have multiple addresses. There is a users table, an addresses table, and a user_addresses table that links addresses and users with a many to many relationship.
Users can define what their default address is.
Is it best to have the users table have a default_shipping_id column linking to the appropriate entry on user_addresses, or should the user_addresses table have a is_default column?
Users table

ID
name
default_user_address_id

--------
--------------
--------------

v.s.
User_addresses table

ID
user_id
address_id
is_default

--------
--------------
--------------
--------------

What's the better approach here?


texts: Can Records be Replicated in an Availability Group Out of Sequence?

texts: A data warehouse ETL process is querying a read-only secondary in an availability group. The ETL process queries a single table incrementally using datetime range criteria of a minute and read committed isolation level. At the time of execution, 5 records that meet the criteria are committed on the primary, but another 3, with slightly earlier timestamps than the first 5 (but within the criteria range) are still in open transactions. Does the nature of availability groups require all transactions to be applied in LSN order (delaying the visibility of all 8 records until all are committed) or do the delayed 3 records get later LSNs and are applied as soon as they are committed, potentially after the ETL process has adjusted its date criteria?


texts: Migration from Teradata to MYSQL over partition by

texts:
Working SQLs in Teradata
select  row_number() over (partition by job_name, parent_job_name order by elapsed_sec) as rowno from  schema.xyz

select min(elapsed_sec) over (partition by job_name, parent_job_name order by elapsed_sec rows between 1 following and 1 following) - elapsed_sec as diff from schema.xyz

Not Working SQLs in MYSQL
select  row_number() over (partition by job_name, parent_job_name order by elapsed_sec) as rowno from  schema.xyz

select min(elapsed_sec) over (partition by job_name, parent_job_name order by elapsed_sec rows between 1 following and 1 following) - elapsed_sec as diff from schema.xyz

Getting error as row_number doesn't exist. Kindly help in conversion.


texts: Can you set up mirroring for sql server reporting services?

texts:
I have mirroring set up for our SQL Server 2008 R2 Standard database server.

Questions

How do I ensure high availability for reporting services if the server goes down?
Is there a kind of mirroring available for that as well?


texts: SQL Job to be run earlier on last work day of month

texts:
Right now, I have a SQL job (within SQL Server 2008 R2) that dumps a complex select statement into a table every weeknight (excluding weekends) and it works great.

Due to our company's Month End procedures, they require the data in this table for that day when running Month End. Right now I fire the job off manually, disable it so the same data isn't dump twice for the same day then enable the job again the following business day.

Is there a way to schedule this? Or script a job and have it depict whether or not it's the last work day of the month and depict what time to fire based on this parameter?

If this is not possible, then so be it I suppose... I haven't been able to find anything that might help point me to such a solution thus far. Any help is appreciated though!

EDIT: Here's a sample result set of a view that I'll using for the last work day of the month comparison (as you can see, I only have Work Days selected):

    CalendarDate:           WorkDay:
    2015-03-02 00:00:00.000        1
    2015-03-03 00:00:00.000        1
    2015-03-04 00:00:00.000        1
    2015-03-05 00:00:00.000        1
    2015-03-06 00:00:00.000        1
    2015-03-09 00:00:00.000        1
    2015-03-10 00:00:00.000        1
    2015-03-11 00:00:00.000        1
    2015-03-12 00:00:00.000        1
    2015-03-13 00:00:00.000        1
    2015-03-16 00:00:00.000        1
    2015-03-17 00:00:00.000        1
    2015-03-18 00:00:00.000        1


texts: Query is slow in SQL Server 2014, fast in SQL Server 2012

texts:
During the migration of one of our databases from SQL Server 2012 (SP1, CU2) to SQL Server 2014 (SP1), we have experienced some strange issues.

One of the queries that completes within seconds on SQL Server 2012 seem to be hanging on SQL Server 2014.

SELECT  DISTINCT
    src.[Id]
FROM
    [stg].[BaseVolumes] src
JOIN
    [tmp].[Dates] d ON src.[CalWeek_Nmbr] = d.[CalYrWkDense_Nmbr]
WHERE
    EXISTS (SELECT  *
            FROM
                (SELECT     ctry.[ISOCode]                  AS  [Mkt_Code]
                            , so.[Code]                     AS  [SlsOrg_Code_AK]
                            , so.[DistributionChannelCode]  AS  [DistChnl_Code_AK]
                            , prd.[SupplierCode]                AS  [SKU_Code_AK]
                            , cl6.[Code]                        AS  [CstHierLvl06_Code_AK]
                            , lp.[BaseDateID]                   AS  [Dte_EK]
                 FROM   [PM_APP].[edw].[BaseVolumeDayCurrent]   lp
                 JOIN   [PM_APP].[dbo].[Country]                ctry    ON  lp.[CountryID] = ctry.[ID]
                 JOIN   [PM_APP].[dbo].[SalesOrganisation]      so      ON  lp.[SalesOrganisationID] = so.[ID]
                 JOIN   [PM_APP].[dbo].[Product]                prd     ON  lp.[ProductID] = prd.[ID]
                 JOIN   [PM_APP].[dbo].[CustomerLevel6]         cl6     ON  lp.[CustomerID] = cl6.[ID]
                 WHERE
                     lp.[ModifiedByApp] = 1) lkp
            WHERE
                src.[Mkt_Code]                  =   lkp.[Mkt_Code]
                AND src.[SlsOrg_Code_AK]        =   lkp.[SlsOrg_Code_AK]
                AND src.[DistChnl_Code_AK]      =   lkp.[DistChnl_Code_AK]
                AND src.[SKU_Code_AK]           =   lkp.[SKU_Code_AK]
                AND src.[CstHierLvl06_Code_AK]  =   lkp.[CstHierLvl06_Code_AK]
                AND d.[Dte_EK]                  =   lkp.[Dte_EK]
        )

Row count:

BaseVolumes:            23108
Dates:                  18628
BaseVolumeDayCurrent:   108115503
Country:                249
SalesOrganisation:      29
Product:                18446
CustomerLevel6:         295

Wait stats showing high CXPacket, but there is one hanging task with SOS_SCHEDULER_YIELD, (print from sys.sysprocesses)

 spid | blocked | waittime | lastwaittype          | cpu     | physical_io
 76   | 0       | 9886044  | CXPACKET              | 13902   | 31192
 76   | 0       | 0        | SOS_SCHEDULER_YIELD   | 9829719 | 83077
 76   | 0       | 11248    | CXPACKET              | 11110   | 0
.
.
.

Attached are the execution plans from SQL Server 2012 and SQL Server 2014.

Execution plan SQL Server 2014
Execution plan SQL Server 2012

Any help much appreciated.


texts: How to recover database with MDF file only

texts:
I am trying to recover a couple dev-dbs where the data directory was on a spanned partition that was inadvertently deleted.  We were able to recover the folders/files using a recovery software, however the actual MDF file seems to have some issues.

Any attempt to attach or repair will fail with the following:

  SQL Server detected a logical consistency-based I/O error: torn page (expected signature: 0x00000000; actual signature: 0x7bd02384). It occurred during a read of page (1:0) in database ID 9 at offset 0000000000000000 in file 'G:\data\database.mdf'.

Steps I've tried:

create database for attach
dbcc checkdb(database, repair_allow_data_loss) - after creating empty db and copying MDF file over the new one)

Note - the recovery process identified several different versions of the same file with different extension, I already tried with each files, same error.  There are no backups.

Anything else that could be tried?


texts: Adding CASE statement to flag percentage available less than 5% as red ELSE green

texts:
USE [TEST]
GO

CREATE PROCEDURE [dbo].[SQLDBA_DatabaseSpaceReport]
AS
BEGIN
DECLARE @dMaxLogDate Datetime = (SELECT TOP 1 LogDate FROM
SQLDBA_PS_Get_DbaDatabaseSpace ORDER BY LogDate DESC)

DECLARE
@html NVARCHAR(MAX),
@table VARCHAR(MAX)

SET @html = --HTML layout--
            '' +
            '
Prod Databases with less than 10%
            space available
' +
            '' +
            '' +
            '
' +
            '
'+
            'SqlInstance	' +
            'Database	' +
            'File Name	' +
            'FileType	' +
            'Used Space MB	' +
            'Free Space MB	' +
            'File Size MB	' +
            'Percent Used	' +
            'Auto Growth	' +
            'Auto Grow Type
';

   SELECT @table = CONVERT(nvarchar(max) ,
(SELECT
td = [SqlInstance],
'',
td = [Database],
'',
td = [FileName],
'',
td = [FileType],
'',
td = CAST([UsedSpaceMB]as varchar(30)),
'',
td = CAST([FreeSpaceMB]as varchar(30)),
'',
td = CAST([FileSizeMB]as varchar(30)),
'',
td = CAST([PercentUsed]as varchar(30)),
'',
td = CAST([AutoGrowth]as varchar(30)),
'',
td = [AutoGrowType]

FROM [TEST].[dbo].SQLDBA_PS_Get_DbaDatabaseSpace D
INNER JOIN [TEST].[dbo].[Environment] E on D.ComputerName =
E.VirtualName AND E.ServerUse IN ('PROD','DR')
WHERE ( FileName NOT LIKE '%temp%' and FileName NOT LIKE 'SP%' and FileName
NOT LIKE 'Share%' )
AND LogDate = @dMaxLogDate and AutoGrowth > 0
ORDER BY [PercentUsed] desc

FOR XML PATH(N'tr'), TYPE));

SET @html = @html + @table + CHAR(10) +
      N'
';

IF LEN(@HTML) > 10

BEGIN

--print @HTML

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'Tester@TEST.com',
@body_format =  'HTML',
@body= @HTML,
@subject = 'Alert!! Prod Databases With Less Than 10% Space Available!!',
@profile_name = 'SQL_DBA'

END

END

Something like this: (Not 100% sure about the code below or where to add it)

CASE WHEN PercentUsed > 95 THEN
'' +
CAST([PercentUsed]as varchar(30)  +'	'
ELSE
'' +
CAST([PercentUsed]as varchar(30)  +'	'
END +
'
'
FROM [TEST].[dbo].SQLDBA_PS_Get_DbaDatabaseSpace


texts: How to convert a variable to be used in where clause

texts:
I have stored Job_id value of msdb..sysjob in a variable @job_id_char, which i want to use against msdb..sysjobactivity to get the 'start_execution_date' value.
I have used following query but getting error as conversion failed

select start_execution_date from msdb.dbo.sysjobactivity where job_id = @job_id_char

I know that i have to convert the variable, but i don't know how


texts: Why does Recursive CTE estimate just 1 row?

texts:
Given two cascading, self-contained (no real tables) recursive CTE's:

create view NumberSequence_0_100_View
as
with NumberSequence as
(
    select 0 as Number
    union all
    select Number + 1
      from NumberSequence
     where Number < 100
)
select Number
  from NumberSequence;
go

create view NumberSequence_0_10000_View
as
select top 10001
       v100.Number * 100 + v1.Number as Number
  from Common.NumberSequence_0_100_View v100
 cross join Common.NumberSequence_0_100_View v1
 where v1.Number < 100
   and v100.Number * 100 + v1.Number <= 10000
    -- please resist complaining about "order by in view" for this question
 order by v100.Number * 100 + v1.Number
go

Then generate estimate/actual plans for:

select * from NumberSequence_0_10000_View

Estimate

Actual

Runtime 23ms but estimating just one row for final output (2 rows for just the first view).

The problem is that when this is used as a subquery to join with real data (by "DaysAgo" for instance), the plan is usually a very slow nested loop and I often need to add a join hint/reverse order etc.

Is there anyway to improve the estimate while keeping the CTE approach?  Has there ever been a request for a "with (AssumeMinRows=N)" hint?  That seems like a great general purpose helper for many cases (not just CTEs).


texts: Join with comma separated values in SQL Server

texts:
I have a table [CourseMaster] LIKE

CourseId    CourseName
-----------------------
  01          ABC
  02          DEF
  03          GHI
  04          JKL
  05          MNO
  06          PQR
  07          STU

And I have another table [StudentMaster] for Student Details LIKE

ROLLNO  NAME    ADDRESS          Course
------------------------------------------------
12345   RAM     RAM ADDRESS      01,02,06
25695   HARI    HARI ADDRESS     02,06
89685   JEFF    JEFF ADDRESS     03,05,06,07
47896   DAISY   DAISY ADDRESS    03

Here I want to fetch the Student details with CourseName(Not CourseId).

If the values in Course is not comma separated than it would be very simple query to fetch the details with join.

As of my knowledge I can run two queries for the same result what I want, One query for fetching the details of student from [StudentMaster] to the front end. And other one for only fetching the CourseName from [CourseMaster] by corresponding CourseId through a loop.

But the fact I want the result by only one query rather than write two queries for this small task.

I guess it is 100% possible. And my expected result will look like:

ROLLNO  NAME    ADDRESS         Course
-------------------------------------------
12345   RAM     RAM ADDRESS     ABC,DEF,PQR
25695   HARI    HARI ADDRESS    DEF,PQR
89685   JEFF    JEFF ADDRESS    GHI,MNO,PQR,STU
47896   DAISY   DAISY ADDRESS   GHI

Thank you and any valuable suggestion will be highly appreciate.


texts: Application considerations when using a multi-primary innodb cluster

texts:
I'm developing a scalable cloud application and I wish to use a multi-primary cluster to be better able to scale horizontally when needed. I have experience with a single primary cluster configuration.

When in multi primary mode, the group replication is slightly delayed from node to node. So, for example, two sequential requests, say PUT /users/1 and GET /users/1, can successfully insert but then fail to select until some time later.

Not necessarily specific to that issue above, are there established ways of designing an application to be built on top of a multi-primary cluster?

I considered making each user session stick to one of the databases for the lifetime of the session. That way anything that's created will be available on subsequent requests because it'll request the same cluster node.

This could be implemented by connecting to the node with id sessionId % n where n is the node count. However, actually doing that seems rather brittle and non-standard. So I was wondering if there are better, established methods of achieving this kind of consistency.

Thank you!


texts: Determine Sequence last used value for sql server before 2017


texts: Find rows where any column matches a search pattern

texts:
I have a table "Test" with 15 different columns of different data types.

I'm looking for rows having characters like "ABC". But "ABC" can be present in any column and I want to find whole records having "ABC".

I cannot restrict the row using a WHERE clause, because I really do not have any idea in which column the ABC resides? Is there a way to do this?


texts:
I understand that in PostgreSQL if there is a multi-column FK from table B to table A and one or more of the FK columns in table B are nullable then the FK is still considered as "honored" for the rows in B where the value of such columns is indeed NULL with no check for the presence of a corresponding row in table A (as primary key columns cannot be nullable):

SQL Fiddle

However, is this PostgreSQL-specific or is it in the SQL standard? (experimenting with SQL Fiddle it seems that both MS SQL Server and MySQL behave in the same way).


texts: Will I benefit from SQL Server 2014 In-Memory tables if my tables are already fully cached in-memory?

texts:
Our most frequently used database is relatively small (5GB), and as a consequence, our 16GB memory dedicated SQL Server 2012 database server chooses to cache all used tables in memory (Buffer Cache Hit Ratio is 100% constantly). Some people in our team want to move to SQL Server 2014 to increase performance with In-Memory Tables, however I'm not convinced that doing such will increase performance since our tables are already in memory.

Is there more to the new In-Memory Tables in SQL Server 2014, or in my scenario there is indeed little to gain?


texts:
How will I get the all the date even though there is no order between two dates?

Sample Table

OrderID  | Date      | CusID
33942    | 6-21-2014 | 6005
34059    | 6-20-2014 | 4003
53333    | 6-23-2014 | 6005
59234    | 6-23-2014 | 4003

How I could have this result?

CusID   | Date      | OrderID |
4003    | 6-20-2004 | 34059   |
4003    | 6-21-2004 | null    |
4003    | 6-22-2004 | null    |
4003    | 6-23-2004 | 59234   |
6005    | 6-20-2004 | null    |
6005    | 6-21-2004 | 33942   |
6005    | 6-22-2004 | null    |
6005    | 6-23-2004 | 53333   |

This what I did so far.

I create a calendar table

CREATE TABLE #Calendar
(
    [CalendarDate] DATETIME
)

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @EndDate = GETDATE()
SET @StartDate = DATEADD(Year, -1, @EndDate)

WHILE @StartDate <= @EndDate
      BEGIN
             INSERT INTO #Calendar
             (
                   CalendarDate
             )
             SELECT
                   @StartDate

             SET @StartDate = DATEADD(dd, 1, @StartDate)
      END

Then here is my query but does not give me the needed result

Select t.CusID, c.CalendarDate, t.OrderID
From #Calendar c
left outer join
#temp t
ON
CONVERT(VARCHAR(10), c.CalendarDate, 112) = CONVERT(VARCHAR(10), t.Date, 112)
Where c.CalendarDate Between '6-20-2014' and '6-23-2014'
Order By t.Name


texts:
I have a text file, which I want to insert into a table in my database.

My query looks like this:

BULK INSERT myDepartment
FROM 'C:\myDepartment-c-t.txt'
WITH (
  DATAFILETYPE = 'char',
  FIELDTERMINATOR = '\t', -- default terminator
  ROWTERMINATOR = '\n'
);

On executing it, SQL Server Management Studio 2008 gives the following error:

'BULK' rowset provider not supported in this version of SQL Server.

I googled it and found that SQL Server 2008 does support BULK INSERT. Can anyone tell me what am I missing?


texts:
I have a stored procedure as described below.

DROP PROCEDURE `GetVoteID`//
CREATE DEFINER=`u1037413_manager`@`%` PROCEDURE `GetVoteID`(IN minimum_votes INT(11), IN current_round INT(11), INOUT votes_id TEXT)
BEGIN
    SET SESSION group_concat_max_len = 100000;
    SET @eliminated_candidates := (
        SELECT GROUP_CONCAT(CONVERT(vote_candidate, CHAR(11)))
        FROM vote_final
        WHERE votes = minimum_votes
        AND round_id = current_round
    );
    SELECT GROUP_CONCAT(CONVERT(vote_id, CHAR(11))) INTO votes_id
    FROM votes_copy
    WHERE FIND_IN_SET(vote_candidate_1, @eliminated_candidates) != 0;
END

Since I'm using a group_concat that produces quite long strings in both the set-statement and the select-statement I need to change the sessions group_concat_max_len to 100000 chars. But this is where I run into problems. Using this method I've figured out that SET SESSION group_concat_max_len = 100000; really never changes the limit. When the length of @votes_id reaches 1,024 it's content is truncated - even though I've changed the limit to 100,000.

CALL GetVoteID(501,3,@votes_id);
SELECT sum(char_length(@votes_id));

This leaves me with two questions.

When does this session actually end? At the end of the stored procedure or at the end of each statement within the procedure?
What am I doing wrong with the group_concat_max_len?


texts:
I am trying to replace the character ' with the character ", using:

UPDATE posts SET text = replace(text, ''', '"');

but:

  1 errors were found during analysis.
  Ending quote ' was expected. (near "" at position 59)


texts: Error log - What does this mean and what can I do about it?

texts:
I have the following repeated errors in my error.log and I don't know what it means or what I should/can do about it:

  Access denied for user 'root'@'[some IP]' (using password: YES)

There are many different IPs showing in the [some IP] field.

I've been reading some security posts that recommend changing the root username to something else, is this something I should do here?

I am using MySQL 5.5.22

I believe these are attempts to gain access to my database from hackers. I want to know what I can do to prevent the attempts. Most of the IPs are coming from China.


texts: How do I remove duplicate entries in a given timeframe?

texts:
My table looks like this:

+-----------+
|     id    | <- primary key
|    time   | <- DateTime
|    row2   | <- varchar
|    row3   |
|    ...    |
+-----------+

I want to remove all entries, except one, which are within a timeframe of one full hour and have the same value of row2 (distinct row2). Is it possible to do this without a stored procedure and/or non-sql code?


texts: Remove duplicates and connect relations to the first one


texts: Any ways to find past week blocking session

texts: Any query to find past week blocking session in SQL server or blocking session which happen 2 weeks ago? Thanks


texts:
I would like to optimize querying in SQL Server of a table to know whether it has a count of records less than a certain amount. I don't need to know the complete count, just whether it is less than, say, 2.

Would it be efficient to do something like this?...

if (
    select count(*)
      from (select top (2) *
              from sys.databases) t
   ) > 1
begin
  select 1
end
else
begin
  select 0
end


texts:
My company uses a multi-tenant database (one database, all tables have a customerID field).

We are in the process of setting up a data warehouse and giving clients direct access.  Our initial thoughts on architecture are to create a schema for each customer, with views in each schema that providing the filtering by customer ID.

i.e.

Main Database

dbo.Table1

Schema1

schema1.Table1 (View) Select * from dbo.Table1 where custId = '1'

This works, except the user with rights to schema1 can ALSO do a select all from dbo.Table1.  I would like to prevent this.

Is that possible?


texts: Returning only the row with maximum value from related rows

texts:
In the example below how do I get the rows where the swap id is the maximum value in relation to the reservationId?   I am currently joining multiple tables to get customer, reservation, room etc information.  But I don't want rows with a swapnumber of 1 if a value is higher then that.

    DECLARE @RoomExchange TABLE (ID INT IDENTITY(1,1), reservationID int,
                     swapNumber int, oldRoom int, newRoom int)

INSERT INTO @RoomExchange (reservationID, swapNumber, oldRoom, newRoom)
SELECT 12, 1, 1,2   UNION ALL
SELECT 13, 1, 3,4   UNION ALL
SELECT 14, 1, 5,6   UNION ALL
SELECT 14, 2, 6,5   UNION ALL
SELECT 15, 1, 7,8   UNION ALL
SELECT 15, 2, 8,9   UNION ALL
SELECT 15, 3, 9,7   UNION ALL
SELECT 16, 1, 10,11

So above i only want rows where the ID would be

1, 2, 4, 7 and 8.


texts: Using TempDB in SP causes to compile plan PER CONNECTION

texts:
uses sp_blitzcache by Brent Ozar

SQL SERVER 2012 (11.0.6020.0)

Steps to reproduce:

create table b (b int)
create table #b (b int)

create procedure Y as
begin
insert into b select 1
select * from b
insert into #b select 1
select * from #b
end

open two query windows in MS SSMS.
run

checkpoint
go
dbcc freeproccache
go

in both of them (just to be sure)
run sp_blitzcache
this will return empty (ok)

run procedure Y in both few times.
run sp_blitzcache
As can be seen, the procedure has a plan compiled for each connection separately

My question is:

Is there any way around? For the same stored procedure to use the same plan, even trogh the (right now this causes over 80% recompile)


texts: Find values which occur in every row for every distinct value in other column of the same table

texts:
I need a select statement which finds values from Column B which occurs in all distinct values for A. Meaning in my example it would return:  x since x occurs in every value of A. (1,2,3 and 4)

I don't know which values are in Column B so I can't specify this in my query. How can I achieve this in SQL Server 2014?

    +---+----+
    | A | B  |
    +---+----+
    | 1 | x  |
    | 2 | x  |
    | 3 | y  |
    | 4 | y  |
    | 3 | x  |
    | 4 | x  |
    +---+----+


texts: SQL Server : how to drop all constraints on a table in T-SQL

texts:
I´d like to drop all constraints on all tables in my database, because I need to change the relationships of many of the tables.

Is there any way to do that with T-SQL?  I cannot find any solution and code like:

ALTER TABLE testDB.dbo.testTable1
DROP ALL CONSTRAINT
GO

doesn´t work.


texts:
I tried google at first but no luck.
Is it possible to update tables from csv file?
I am using SQL developer and I am using a script to export edited rows to a csv file. I would like to update the edited rows through that csv file from a client. I don't want to import the whole file as the client already has a mirror table, I just would like to update the data from what it is in the csv file.
Is this possible?

If not what would be the best approach?


texts:
We have an Availability Group configured. Total number of nodes in the cluster is 3 (odd). All are in same subnet. Also we do have file share witness. Everything is working fine for now.

My question is: do we really need file share witness? And what should the ideal quorum configuration be?

As per my knowledge, it is not needed as we already have an odd number of nodes, so node majority quorum configuration should be fine and is recommended.

SQL Version: Microsoft SQL Server 2016 (SP2) Enterprise Edition
OS Version: Windows Server 2016 Standard


texts:
I just recently upgraded to Ubuntu 15.04 from 14.04, I guess the MySQL was also upgraded to 5.6. Is there a reason why my database stops working. Rebooting the system is the only fix for it, but it still dies again in a day or two. It's been going on for a few weeks since I updated to 15.04. I also removed and reinstalled MySQL.

2015-09-22 22:16:28 14273 [Warning] Using unique option prefix myisam-recover instead of myisam-recover-options is deprecated and will be removed in a future$
2015-09-22 22:16:28 14273 [Note] Plugin 'FEDERATED' is disabled.
2015-09-22 22:16:28 14273 [ERROR] Function 'innodb' already exists
2015-09-22 22:16:28 14273 [Warning] Couldn't load plugin named 'innodb' with soname 'ha_innodb.so'.
2015-09-22 22:16:28 14273 [ERROR] Function 'federated' already exists
2015-09-22 22:16:28 14273 [Warning] Couldn't load plugin named 'federated' with soname 'ha_federated.so'.
2015-09-22 22:16:28 14273 [ERROR] Function 'blackhole' already exists
2015-09-22 22:16:28 14273 [Warning] Couldn't load plugin named 'blackhole' with soname 'ha_blackhole.so'.
2015-09-22 22:16:28 14273 [ERROR] Function 'archive' already exists
2015-09-22 22:16:28 14273 [Warning] Couldn't load plugin named 'archive' with soname 'ha_archive.so'.
2015-09-22 22:16:28 14273 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-09-22 22:16:28 14273 [Note] InnoDB: The InnoDB memory heap is disabled
2015-09-22 22:16:28 14273 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-09-22 22:16:28 14273 [Note] InnoDB: Memory barrier is not used
2015-09-22 22:16:28 14273 [Note] InnoDB: Compressed tables use zlib 1.2.8
2015-09-22 22:16:28 14273 [Note] InnoDB: Using Linux native AIO
2015-09-22 22:16:28 14273 [Note] InnoDB: Using CPU crc32 instructions
2015-09-22 22:16:28 14273 [Note] InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
2015-09-22 22:16:28 14273 [ERROR] InnoDB: Cannot allocate memory for the buffer pool
2015-09-22 22:16:28 14273 [ERROR] Plugin 'InnoDB' init function returned error.
2015-09-22 22:16:28 14273 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2015-09-22 22:16:28 14273 [ERROR] Unknown/unsupported storage engine: InnoDB
2015-09-22 22:16:28 14273 [ERROR] Aborting


texts: Clustered index in ETL (drop - insert - create?)

texts:
I have a table which is 130 milion rows. Every day I extract data from a source system and insert into this table. The extract is incremental i.e. I only get the new data from the source. The table is a heap with one nonclustered index.
So I am thinking about creating a clustered index. I have read that loading data in clustered table creates page splits and it is better to drop the index, and after the insert recreate the index. Some also seem to say it is better to diable and then rebuild.
I am not sure but I don't think the above advice is solid. Even when I insert into a heap new pages have to be allocated. What makes page splits so bad on a clustered index?
I think perhaps it is better to just insert into the clustered index? It might be slower than to insert in a heap but later it will benefit from a clustered index in joins and calculations.
Is the above advice about dropping and recreating the clustered index a myth?
I would say the overall goal is to improve everything i.e. joins and calculations on the table after the new inserts and possibly maintaining the table and deleting oldest rows in another job and avoiding heap fragmentation. So I can live with inserts becoming slower..
The "inserts" are actually from a "data flow tap" from a ODBC data source in SSIS and they do not happen in a bulk (I think).
Thansk


texts:
I have a lot of records like this (over 6000) in this table Lat and Lng is same and I should select base these fields (lat,lng) :

I need to computing on these records (  and  )

I need to get first record of a Lat Lng(same) and latest (between same date) for compute on time field with filter date ,and get latest record with first record from lat lng next one.

How I can write this query ?

I wrote this query, and I need to get first and last record base date now !

SELECT mDate as date ,xLat as lat,yLng  as lng
FROM ReportAct_tbl ,
 (SELECT mDate as dt,xLat as lt,yLng as lg
  FROM ReportAct_tbl )
WHERE date  = dt and lat = lt and lng = lg
order by date  ASC,lat ASC,ylng ASC


texts: Cannot connect securely to enterprise manager

texts:
I'm working on my classwork and had a problem with connecting to the enterprise manager. After running the DBCA the address given to me was https://10.0.2.15:5500/em, pulling it up on IE I get cannot connect securely to this page. No option to bypass the warning.

lsnrctl status:

    Microsoft Windows [Version 10.0.17763.503]
(c) 2018 Microsoft Corporation. All rights reserved.

C:\Users\>lsnrctl status

LSNRCTL for 64-bit Windows: Version 12.1.0.2.0 - Production on 23-MAY-2019 23:14:16

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 64-bit Windows: Version 12.1.0.2.0 - Production
Start Date                23-MAY-2019 23:10:28
Uptime                    0 days 0 hr. 3 min. 48 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   C:\app\ora_install\product\12.1.0\dbhome_1\network\admin\listener.ora
Listener Log File         C:\app\ora_install\diag\tnslsnr\DESKTOP-VMGOK6K\listener\alert\log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=\\.\pipe\EXTPROC1521ipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=DESKTOP-VMGOK6K)(PORT=5500))(Security=(my_wallet_directory=C:\APP\ORA_INSTALL\admin\orcl121\xdb_wallet))(Presentation=HTTP)(Session=RAW))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=DESKTOP-VMGOK6K)(PORT=9090))(Presentation=HTTP)(Session=RAW))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
  Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl121" has 1 instance(s).
  Instance "orcl121", status READY, has 1 handler(s) for this service...
Service "orcl121XDB" has 1 instance(s).
  Instance "orcl121", status READY, has 1 handler(s) for this service...
The command completed successfully

when I run gethttpsport I get

  ORA-00904: "DBMS_XDB"."GETHTTPSPORT": invalid identifier

gethttpport returns port 5501

I'm running a virtual machine with windows 10 on my MacBook. I'm able to connect to EM on the school provided virtual machine, I would just like to have a local version I can do classwork while I am on a break at work.


texts: set compatibility mode in Azure Sql Database?

texts:
Is it possible to set  compatibility mode in Azure Sql Database  to match SQL Server 2014 ?

We have a existing application using SQL Server 2014 - can i just change the connection string to point to a Azure Sql Database with sql 2014 compatability?


texts: SQL Profiler plus Include Actual Execution Plan

texts:
Is there any option to show "Include Actual Execution Plan" plus query cost and recommended indexes in SQL Profiler?

I see Showplan XML, but it shows only the execution plan without recommended indexes :/

It's working perfect when I run the query from SQL Server Management Studio and Include Actual Execution Plan.


texts: Max function and group by getting differenet count

texts:
Statement 1 :

Select Load_Date , Count(*) Last_Count From <Table_Name> group by Load_Date;

Statement 2 :

Select max(Load_Date) , Count(*) Last_Count From <TABLE_NAME>;

Statement 1 max Date count is different and statement 2 max date count is different.

I need your help to understand the output.


texts: Database not going into single_user mode

texts:
I have a corrupt database with wrong checksum error. When I try to put database into single_user mode to repair it gives following error.
I am using following sql command from management studio to put it on single_user mode.

Alter database testDb set single_user with rollback Immediate;

But it just returns with following

  Msg 5069, Level 16, State 1, Line 1
  ALTER DATABASE statement failed.
  Msg 824, Level 24, State 2, Line 1
  SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xfdd7144d; actual: 0x4ff829f5). It occurred during a read of page (1:314) in database ID 5 at offset 0x00000000274000 in file 'C:\Program Files\Microsoft SQL Server\MSSQL12.SQLEXPRESS\MSSQL\DATA\DreamBackup.mdf'.  Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

when I run dbcc checkdb it returns the following:

  Msg 7985, Level 16, State 2, Line 1
  System table pre-checks: Object ID 3. Could not read and latch page (1:296) with latch type SH. Check statement terminated due to unrepairable error.
  DBCC results for 'dreambackup'.
  Msg 5256, Level 16, State 1, Line 1
  Table error: alloc unit ID -3054820568275288064, page (1:296) contains an incorrect page ID in its page header. The PageId in the page header = (10289:856300870).
  CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
  CHECKDB found 0 allocation errors and 1 consistency errors in database 'DreamBackup'.


texts: I am trying to write a search query on MYSQL to find the users

texts:
I am using MYSQL for a given data model where I have and entity 'concerts' connected to the entity 'Attended' which is connected by the entity 'People'.

Here I am given a one to many and many to one relation respective to the entities I mentioned. I am asked to find the People.Firstname who haven't attended a single concert. I tried implementing this using the INNER JOIN of Concerts and Attended to People.
My Search Query is as follows :

Select People.FirstName from People
    INNER JOIN Attended
        on People.Id = Attended.Customer
    INNER JOIN Concert
        Where Attended.Concert != Concert.Id
            GROUP BY People.FirstName

I get bunch of First names out but Idk for sure if the query I made gives the right output.


texts:
I do

ALTER TABLE bdt.auth
    ADD CONSTRAINT `depositor no.`
      FOREIGN KEY ( `depositor no.` )
         REFERENCES bdt.depositors( `depositor no.` )
            ON DELETE CASCADE
            ON UPDATE CASCADE

I get

  Can't create table 'bdt.#sql-881_140' (errno: 121)

Fields are identical
There is no defined constraints:

SELECT
   constraint_name,
   table_name
FROM
   information_schema.table_constraints
WHERE
   constraint_type = 'FOREIGN KEY'
AND table_schema = DATABASE()
ORDER BY
   constraint_name;

Result

  Empty set (0.01 sec)


texts: execution plan TABLE ACCESS BY INDEX ROWID cardinality

texts:
This is the explain plan for a certain query.
The query contains a PK order by clauses and matches on two other columns on the where clause.

Since PK is used for index full scan, I assume cardinality to be accurate. So I expect for the table access to have the same cardinality: PK values are unique, so there should be exactly 168 unique ROWIDs in index, pointing to exactly 168 rows, not 5633.

In which circumstances can TABLE ACCESS BY INDEX ROWID cardinality exceed that of INDEX FULL SCAN?


texts: What can cause Reads to slow down on SQL Server 2008R2?

texts:
Our primary DB is 15GB in size. The server has 16GB of RAM. We are using the simple recovery model.

Lately we've noticed that our custom apps lookups - just basic reads with adhoc SQL statements - have slowed down noticeably. A couple seconds longer in most cases.

I'm not a SQL Server Admin and am not sure where to look next.

We have monitored the CPU which never seems to peak. Same with the RAM. We have tons of indexes, but I thought that was only an issue with write speed...

Any suggestions on what I could look at next?


texts: Database design - passenger, driver, wallet. How to handle multiple foreign keys with Id and type fields?

texts:
I have to design a database schema for new project. I will use PostgreSQL for the implementation of the schema.

Right now I'm stuck in a scenario where I need to handle foreign keys based on type and id. Here are the tables:

Passenger:

PassengerID
FirstName
Email
DOB
Address

Driver:

DriverID
CNIC
Vehicle
License
Status

Wallet:

WalledID
Amount
UserId
UserType

Problem: I want that every wallet row should have UserId as DriverId or PassengerID and in the type field the name of the table.

How can I achieve this with PostgreSQL? I have heard that it is possible from polymorphic association but don't know how to implement it.