texts:
I created a user in PostgreSQL, gave it a password using:

alter user username with password 'new_password';

But when I try to login using phpPgAdmin, it keeps saying use/password incorrect, do I have to do something after alter password, like on MySQL where you have to run flush privileges?

Thanks.


texts: What happens during "big" inserts on a table with a clustered composite key?

texts:
My SQL knowledge is limited, so the terms I'll be using are most likely not the right ones.

I have a table that will store test results, for multiple locations.

The tests will be recorded in different databases at different locations (no network connection) and the "master" location will regularly "import" the test results from the other locations.

I plan to have a clustered composite primary key on the LocationId (int) and Date (datetime) columns, in that order. The reasoning is that it should keep all the results for a location together and I will almost never do a query by date range, but rather by date range and location.

The row size will be 80 to 100 bytes and the number of test results should not exceed a few millions. A typical "import" will insert 50 to 100 thousands results from another location.

What will happen during imports? Will SQL "move" the existing rows to maintain the clustering, or will it let the table become "fragmented"? Could this cause a big performance hit if the import is done one row at a time? Should I rather just not bother with the ordering of the rows and just add an identity column as the primary key and an index on the Date column to help with my queries?


texts:
I'm helping a friend to backup his database, but I can't get it right doing standard mysqldump process, when I try to use mysqlcheck, I sort the result: http://pastie.org/9748430

'___') It's seriously scary..

Ok, now the question, how to make mysqldump ignore the error and continue backup till it's complete?

last error:

mysqldump: Couldn't execute 'show create table `xxx`': Table '.\mydb1\xxx' is marked as crashed and should be repaired (145)


texts:
So I work for a smaller company and currently we need to do our data processing with a desktop solution. We're running into memory limitations and the overall solution in most people's eyes (non-technical staff) is that setting up a database for the data and doing our work on a database instead of the massive flat files (SPS files mostly or the lesser known Triple-S) we currently work on.

I would likely be the one that would be working within this database to do all the data cleaning, validation and variable creation and so I'd like to make sure what I imagine is possible and to also see other possible options.

The data we are using is respondent level survey data. We will be updating the database every quarter with about 40,000 respondents and we have about 40,000 vectors of data per respondent (largely sparse data).

One of the more advanced calculations and operations I would need to do is, I would need to determine the standard deviation of a vector of data and then reduce a respondent's response to no more then 2 standard deviations away from the mean.

Also, creating new vectors of data. (When grouping respondents we will often mark them as apart of 1 of many groups and I would need a vector containing this data to work off of and this would most likely become its own table with various other data.)

I guess I'm just looking for some guidance, I have no problem learning SQL or using MySQL or whatever but I don't know if those are the correct solutions or if I should be looking at something else all together. I know what I need to do to the data but I don't know the best platform to do it on.

I apologize if this is too broad a subject but any guidance is appreciated.


texts:
I would like to know about how the OS cache works for postgres table and index files.

Let's say I have a table with 10 years of data, and commonly used data only in the last 1 year. This data is quite big, so each table and index file is divided into several file in PGDATA/base

Let's say 1 index named order_by_date has relfilenode = 1870772348, and it's file consist of 1870772348, 1870772348.1, and 1870772348.2

And for the often queried 1 year data, do ALL files for the order_by_date get pushed to OS cache ? Or it's just 1 file that contains the index to this 1 year data.

How about the index named order_by_customer, will ALL the index files get pushed to the OS cache?

Can someone please explain about how the OS cache works for this condition?

Thanks very much for the explanation.


texts: Memory is not released after the execution stored procedure followed by a process end

texts:
I am running a stored procedure in query builder and closing query builder. However, the memory allocated doesn't get released. The stored procedure uses temp tables and drops them immediately after its use. These tables get created in a loop, get truncated, and dropped immediately. Any table that is created gets dropped immediately. The purpose of the stored procedure is only to make updates after a calculation using these temp tables.
After each execution, about 1.4 GB memory increases and never get released.
QUESTIONS

What factors should be considered when creating temp tables in a stored procedure ?
How can I claim memory after the stored procedure gets executed ?


texts: SQL Server Rollback nested Stored procedures and linked servers

texts:
I have read THIS StackOverflow article, and found it enlightening. However, I am having trouble assimilating that knowledge with my limited knowledge of transactions and linked servers. I am trying to do something very similar to the question in that article (Rollback everything from the parent stored proc, as well as what occurred in the sub-proc, if the sub-proc fails), except that my sub-proc does simple inserts into another SQL Server via a linked server.

Do any of the nuances of transactions and rollbacks change in this instance?

As a side note, we are using AWS EC2 with SQL Server 2008 R2, and we had problems configuring MSDTC a couple months ago. We tried using BEGIN DISTRIBUTED TRANSACTION and such, and could not get MSDTC to play nice despite being able to do simple inserts into the linked server easily without the distributed transaction. Must I use Distributed Transactions, or is the another way around it?


texts:
I have two tables (Order and equipment) and they should be linked together by the order No. the SQL script is as the following:
-- -----------------------------------------------------
-- Table `rr`.`order`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `rr`.`order` (
  `Order_No.` INT UNSIGNED NOT NULL,
  `Order_Type` CHAR(8) NOT NULL,
  `Equipment_Quantity` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`Order_No.`))
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COMMENT = '\\n';

-- -----------------------------------------------------
-- Table `rr`.`equipment`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `rr`.`equipment` (
  `S_N` INT UNSIGNED NOT NULL,
  `OrderNo.` INT UNSIGNED NOT NULL,
   `V_N` INT UNSIGNED NOT NULL,
  `LS_N` INT UNSIGNED NOT NULL,
  `FK_EQ_Inspection_ID` INT UNSIGNED NOT NULL,
  `FK_EQ_Reinspect_ID` INT UNSIGNED NOT NULL,
  `FK_EQ_Scentencing_Notification_No` INT UNSIGNED NOT NULL,
  `FK_EQ_Repair_ID` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`S_N`, `OrderNo.`, `FK_EQ_Scentencing_Notification_No`),
  INDEX `OrderNo._idx` (`OrderNo.` ASC) VISIBLE,
  INDEX `FK_EQ_Notification_No_idx` (`FK_EQ_Inspection_ID` ASC) VISIBLE,
  INDEX `FK_EQ_Confirmation No._idx` (`FK_EQ_Reinspect_ID` ASC) VISIBLE,
  INDEX `fk_equipment_scentencing1_idx` (`FK_EQ_Scentencing_Notification_No` ASC) VISIBLE,
  INDEX `FK_EQ_Repair_ID_idx` (`FK_EQ_Repair_ID` ASC) VISIBLE,
  CONSTRAINT `FK_EQ_Reinspect_ID`
    FOREIGN KEY (`FK_EQ_Reinspect_ID`)
    REFERENCES `rr`.`re-inspect` (`Reinspect_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `Inspection_ID`
    FOREIGN KEY (`FK_EQ_Inspection_ID`)
    REFERENCES `rr`.`inspection` (`Inspection_ID`),
  CONSTRAINT `OrderNo.`
    FOREIGN KEY (`OrderNo.`)
    REFERENCES `rr`.`order` (`Order_No.`),
  CONSTRAINT `FK_EQ_Scentencing_Notification_No`
    FOREIGN KEY (`FK_EQ_Scentencing_Notification_No`)
    REFERENCES `rr`.`scentencing` (`Scentencing_Notification_No`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `FK_EQ_Repair_ID`
    FOREIGN KEY (`FK_EQ_Repair_ID`)
    REFERENCES `rr`.`repair` (`Repair_ID`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;

USE `rr`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `rr`.`before_equipments_insert`
BEFORE INSERT ON `rr`.`equipment`
FOR EACH ROW
BEGIN
 IF NEW.V_N = 1 THEN
     SET new.LS_N = 0;
 END IF;

END$$

USE `rr`$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `rr`.`before_equipments_UPDATE`
BEFORE UPDATE ON `rr`.`equipment`
FOR EACH ROW
BEGIN
 IF NEW.V_N = 1 THEN
     SET new.LS_N = 0;
 END IF;

END$$

by checking the 'equipment' table, it includes 'Order_No.' as a foreign key from the 'Order' table in addition to other foreign keys from other different tables.
I entered the data of one row for the 'ORDER' table, I was expecting that the value of foreign key 'Order_No.' in the 'equipment' table would read automatically and that did not happen. so how to do that?
Also, when I enter row of data in the 'equipment' table, will I ignore the other foreign keys as they will be linked through their tables?


texts: Best Way to Partition By City / State in MySQL 5.5

texts:
I have a table with ~20M rows and every query against it will include state or city or a combination so I was thinking of partitioning the table using a combo of the two fields like so:

ALTER TABLE `test`.`test`
PARTITION BY RANGE COLUMNS (city, state) (
    PARTITION p0 VALUES LESS THAN ('a','AE'),
    PARTITION p1 VALUES LESS THAN ('c','AE'),
    PARTITION p2 VALUES LESS THAN ('e','AE'),
    PARTITION p3 VALUES LESS THAN ('g','AE'),
    PARTITION p4 VALUES LESS THAN ('i','AE'),
    PARTITION p5 VALUES LESS THAN ('k','AE'),
    PARTITION p6 VALUES LESS THAN ('m','AE'),
    PARTITION p7 VALUES LESS THAN ('o','AE'),
    PARTITION p8 VALUES LESS THAN ('q','AE'),
    PARTITION p9 VALUES LESS THAN ('s','AE'),
    PARTITION p10 VALUES LESS THAN ('u','AE'),
    PARTITION p11 VALUES LESS THAN ('w','AE'),
    PARTITION p12 VALUES LESS THAN ('y','AE'),
    PARTITION p13 VALUES LESS THAN ('a','AK'),
    PARTITION p14 VALUES LESS THAN ('c','AK'),
    PARTITION p15 VALUES LESS THAN ('e','AK'),
    PARTITION p16 VALUES LESS THAN ('g','AK'),
    PARTITION p17 VALUES LESS THAN ('i','AK'),
    PARTITION p18 VALUES LESS THAN ('k','AK'),
    PARTITION p19 VALUES LESS THAN ('m','AK'),
    PARTITION p20 VALUES LESS THAN ('o','AK'),
    PARTITION p21 VALUES LESS THAN ('q','AK'),
    PARTITION p22 VALUES LESS THAN ('s','AK'),
    PARTITION p23 VALUES LESS THAN ('u','AK'),
    PARTITION p24 VALUES LESS THAN ('w','AK'),
    PARTITION p25 VALUES LESS THAN ('y','AK'),
    PARTITION p26 VALUES LESS THAN ('a','AL'),
    PARTITION p27 VALUES LESS THAN ('c','AL'),
    PARTITION p28 VALUES LESS THAN ('e','AL'),
    PARTITION p29 VALUES LESS THAN ('g','AL'),
    PARTITION p30 VALUES LESS THAN ('i','AL'),
    PARTITION p31 VALUES LESS THAN ('k','AL'),
    PARTITION p32 VALUES LESS THAN ('m','AL'),
    PARTITION p33 VALUES LESS THAN ('o','AL'),
    PARTITION p34 VALUES LESS THAN ('q','AL'),
    PARTITION p35 VALUES LESS THAN ('s','AL'),
    PARTITION p36 VALUES LESS THAN ('u','AL'),
    PARTITION p37 VALUES LESS THAN ('w','AL'),
    PARTITION p38 VALUES LESS THAN ('y','AL'),
    PARTITION p39 VALUES LESS THAN ('a','AR'),
    PARTITION p40 VALUES LESS THAN ('c','AR'),
    PARTITION p41 VALUES LESS THAN ('e','AR'),
    PARTITION p42 VALUES LESS THAN ('g','AR'),
    PARTITION p43 VALUES LESS THAN ('i','AR'),
    PARTITION p44 VALUES LESS THAN ('k','AR'),
    PARTITION p45 VALUES LESS THAN ('m','AR'),
    PARTITION p46 VALUES LESS THAN ('o','AR'),
    PARTITION p47 VALUES LESS THAN ('q','AR'),
    PARTITION p48 VALUES LESS THAN ('s','AR'),
    PARTITION p49 VALUES LESS THAN ('u','AR'),
    PARTITION p50 VALUES LESS THAN ('w','AR').....

However, this fails to work with this error:

ERROR 1493 (HY000): VALUES LESS THAN value must be strictly increasing for each partition

What would be a valid solution for partitioning this table?


texts: How to Limit Access in a Database System

texts:
I have a question related to the limiting of the access to a database network for the users that are allowed to log on to it.

The problem is that the databases are huge (they contain hundred of millions of records) and without a few precautions, letting the logged-in users do whatever they want creates chaos.

For instance, allowing a simple SELECT query to run without any constraints might unnecessarily clog the access to the databases for other users.

So far, the following recommandations have been made in order to limit the access:

Using the "SET QUERY_GOVERNOR_COST_LIMIT 900" clause in the query windows in order to limit the usage of the database up to a quarter of hour.
Limiting any SELECT queries using "TOP 15" clauses in order to limit the retrieval of records from those hundred milion records to a bare sample of fifteen records.
Using the "WITH (NOLOCK)" and "WITH (READUNCOMMITTED)" clauses in order to prevent the databases from being locked. Of course, there are other differences between these two clauses that have been already discussed elsewhere.
Using scheduled tasks that programatically kill any unatended connections at a specific moment of time when supposedly the users should have already closed all their work, so that the relational database management system does not have any unnecessary load left over the termination of the user interaction with it.

Are there any other suggestions that might affect the speed of the database usage?

Of course, most of the allowed usage is strictly limited to querying. Manual updating and inserting of records is very infrequent. The creation of temporary tables is not recommended and it is almost prohibited.

Any other type of usage can be remotely isolated by using other relational database management systems, where - through link servers - data can be brought in and manipulated without further restrictions.


texts:
I was having a discussion today about Dynamic Ports with one of my co-workers and could use some help clarifying how they work.

First question: If the IPALL TCP Dynmaic Ports setting is a specific number (say 1971) does that signify that you have a static port of 1971 or a dynamic port that is currently 1971 and may change at some point in the future.

Second question: This is the one I'm most curious about.  We have an instance that has had the same port (the value in the IPALL TCP Dynmaic Ports setting) for several years through dozens of instance restarts.  What actually causes the dynamic port to actually change after an instance restart?


texts:
I have a SQL trigger which adds the old value ,new value and time of update for column based on update, insert, update or delete.

To update the time we used SYSDATEand inserted record from trigger. This trigger was working fine for a long time. Few weeks back the server is upgraded to Exadata from 11g and now are experiencing some issues. The column inserted by SYSDATE is expected to update GMT time. But only in some cases it does insert central time and this behavior is intermittent. We also tried updating the table from same machine and experienced the same issue. So now we suspect it may be Exadata upgrade, but we are not sure. So does anyone has experienced similar issue or have any idea why this is happening.


texts:
I want to create a concurrently index in postgresql inside a trigger like following code

    CREATE OR REPLACE FUNCTION hour_production_index_partition(partition_name text) RETURNS VOID AS
$BODY$
BEGIN
    -- Ensure we have all the necessary indices in this partition;
    EXECUTE 'CREATE INDEX CONCURRENTLY IF NOT EXISTS ' || partition_name || '_domain_write_date_idx ON ' || partition_name || ' (fk_domain, write_date)';
    END;
$BODY$
LANGUAGE plpgsql;

The problem is that when I execute that statement, postgresql complains sending an error like

  WARN  SqlExceptionHelper - SQL Error: 0, SQLState: 25001 ERROR
  SqlExceptionHelper - ERROR: CREATE INDEX CONCURRENTLY cannot run
  inside a transaction block   Where: SQL statement "CREATE INDEX
  CONCURRENTLY IF NOT EXISTS
  hour_production_1_2018_07_01_domain_write_date_idx ON
  hour_production_1_2018_07_01 (fk_domain, write_date)"

I need to create this index in this way because hour_production table is dinamically break into small parts.


texts: How to lock all tables except some specific table?

texts:
I am trying to set a stage server which replicate some tables like articles, user etc from master. If I start the slave server as read_only I am not able to my stage site up because there are some tables like cache or session which needs to be rewritten.

So I am trying to use replicate_do_table and then locking those tables manually using below snippets and let other tables like cache and session rewritable.

In short I want read_only lock for all the tables in a database excluding some specific tables to read-write access.

I found below snippet on mysql website. But how can I select only specific tables without writing all the table names manually inside LOCK TABLES

LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type: {
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
}

Note: I am using innodb storage engine and mariadb 10.3


texts: How to perform partial PIT Recovery without using a staging database in MySQL?

texts:
I have a full physical backup and binlogs available for my MySQL instance.
Is there a way to perform a partial point in time recovery (meaning restoring in time a subpart of the whole database, i.e. some specific tables) without mean of a staging database, by simply selecting the desired binlogs?


texts: Oracle 11g user created with Create session but able to update the table?

texts:
I created a readonly user with only permission granted was create session and read only privilege, using the link:
https://stackoverflow.com/questions/7502438/oracle-how-to-create-a-readonly-user

Now when I login with the new user I created I am able to update the table.
Can anyone suggest me what I could have done wrong?


texts: What is the least amount of privileges (Administrator, db_owner, etc ) for a user to perform Backup and Restore in SQL Server?

texts:
What is the least amount of privileges (Administrator, db_owner, etc) for a user to perform following in SQL Server ?

Backup
Restore


texts: Database revision needed for sports events database

texts:
This is not a code review but more of a database schema review.

I have a sports events website that I am doing in my spare time. I need assistance with the database design. I am about 80% completed. It sounds simple, but it’s more than just the event name and start and end dates.

Let me give you the scenario. I am busy developing a website where I want to display different types of events on this website like mountain biking, road cycling, running, trail running, triathlon, biathlon, etc on it. The reason behind me wanting to do this is to keep my skills up to date and let me experiment with technologies that I don’t normally use at work. I am a mountain biker myself and sometimes it becomes a mission looking for events because every event has its own website. So I thought about creating a centralised database and website to list all these types of events.

The more detail I want to display per race the more complex the database becomes. I created the first table and called it Events. And event has only a name, like:

Die Burge MTB Challenge
Resolution Health Ride the Rhino

The Die Burger MTB Challenge is a mountain bike race that takes place on 1 day. There are different distances that a rider can enter, for example 75km, 60km, 50km, etc. Only individuals can enter, no teams are allowed. Each distance has its entry fee. The start and end venue are at the same venue.

The Resolution Health Ride the Rhino takes place over 3 days. There are different categories of difficulty available for a rider to enter, namely the Black Rhino, White Rhino and Junior Rhino. All 3 categories takes place over 3 days. The Black Rhino consists of the following distances, 80.7km, 89km and 90km. The White Rhino also consists of 3 shorter distances as well as the Junior Rhino. Individuals and teams can enter for the Black Rhino and White Rhino. Only teams can enter for the Junior Rhino. The fees are per individual or team, for example R3800 for an individual and R7600 for a team. There are early bird entry fees and late entry fees. There are also different packages that you can choose, like All Inclusive package that contain a lot of stuff like accommodation, food, showers, etc and a self-catering package where you need to organise your own accommodation. The start and end venues are different each day. Day 1 point start at Venue A and end at Venue B. Day 2 might start at Venue B and end at Venue B, and Day 3 might start at Venue B and end at Venue C.

So I broke it down to create a another table called Races. This would include something like “75km Route” for the one day event or “Black Rhino” for the multi day event. I broke it down even further and added a disciplines table. The disciplines table just tells you what you will be doing on the day. For example, the race type might be triathlon, and triathlon consists of swimming, cycling and running.

This is what I currently have. I know I am missing lots, but if you could maybe guide me on the right path then I would appreciate it. Please feel free to chop and change as you like.


texts: SQL Replication using Two Availability Group

texts: Is it possible to setup SQL Replication with Two Availability Group Cluster? one is Publisher(or Distributor if not in different server) and the other AG is Subscriber?


texts: Schema Migration for application upgrades

texts:
I have an application which is running with an application schema on Oracle DB. The application needs to be upgraded to new version of Schema on Oracle DB with changes in Schema structure (Table Structure, Values, Entity Relations). Which tool/utility to use to reduce the downtime?
Currently we are migrating the complete data using sql procedures and it is taking huge time (12 Hours). We want to reduce drastically the migration time at DB level.


texts: Slow update on large table with subquery

texts:
With SourceTable having >15MM records and Bad_Phrase having >3K records, the following query takes almost 10 hours to run on SQL Server 2005 SP4.

UPDATE [SourceTable]
SET
    Bad_Count=
             (
               SELECT
                  COUNT(*)
               FROM Bad_Phrase
               WHERE
                  [SourceTable].Name like '%'+Bad_Phrase.PHRASE+'%'
             )

In English, this query is counting the number of distinct phrases listed in Bad_Phrase that are a substring of the field Name in the SourceTable and then placing that result in the field Bad_Count.

I would like some suggestions on how to have this query run considerably faster.


texts: Does partitioning a MySQL table also increase index creation performance?

texts:
This is something I was wondering since I'm planning to partition a aggregation table with 10 mil records where rebuilding indexes cause table locks for some time.

Because partitions are bluntly put just multiple tables that have their own index and our system only inserts new records into a single partition that is kept small (Only data from the current day is added).

So when rebuilding or adding indexes will the table lock occur on all partitions or only the partitions it is rebuilding at that moment?

Also because you have to add your partition key to the primary key are there other troubles that I might run into when partitioning a existing table?


texts:
I have the following scenario:

table:

    +----+-----------+
    | id | name      |
    +----+-----------+
    | 1  | zx name1  |
    | 2  | zx name7  |
    | 3  | zx name3  |
    | 4  | name5     |
    | 5  | name2     |
    | 6  | name3     |
    | 7  | zx name4  |
    +----+-----------+

I want to order the results like this:

first results will be those having zx in name ASC
second the rows that does not have zx in name in order of id

The ordered result set should look like this:

    +----+-----------+
    | id | name      |
    +----+-----------+
    | 1  | zx name1  |
    | 3  | zx name3  |
    | 7  | zx name4  |
    | 2  | zx name7  |
    | 4  | name5     |
    | 5  | name2     |
    | 6  | name3     |
    +----+-----------+

I tried the following queries:

    select id, name from table
    where id < 100
    order by (name like 'zx%'), id

    select id, name from table
    where id < 100
    order by case when name like 'zx%' then name else id end

and many variations of this but with no luck.

Can you please give me some hints

Thank you


texts:
The following database restore command is failing because of too many command-line arguments (first is "151019_testbase.sql"). This is effected from a shell session within the directory of the application (I initially had it calling the backup file from a subdirectory _private/151019_testbase.sql but changing that does not alter the behaviour.

pg_restore -c -1 -v v4_development 151019_testbase.sql

The documentation has examples where multiple options are set pg_restore -C -d postgres db.dump, so that's not it (apparently).
Note, this attempt is with option 1 (one) for running a single BEGIN/COMMIT

I do not see the source of the error.

I also attempted to issue the command running psql v4_development first (to also chef on previous state) and postgresql returns ERROR:  syntax error at or near "pg_restore"


texts: MariaDB - breaking up a multi join into CTEs

texts:
So I have the following query:

SELECT
    i.mongodb_id
FROM
    display_list_lists dll
JOIN
    display_lists dl
    ON
        dll.display_list_id = dl._id
JOIN
    list_items li
    ON
    dll.list_id = li.list_id
JOIN
    items i
    ON
    li.item_id = i._id
WHERE
    dl.mongodb_id = ?
    AND
    dl.account_id = ?

since, I only need the display_lists._id in order to then find the other details, would it be better to somehow use CTEs instead?

Broken up, it would look like this:

SELECT
    _id
FROM
    display_lists dl
WHERE
    dl.mongodb_id = ?
    AND
    dl.account_id = ?;

SELECT
    *
FROM
    display_list_lists dll
WHERE
    dll.display_list_id = ?; // previous _id

SELECT
    *
FROM
    list_items li
WHERE
    li.list_id IN (?); // use list ids from previous results

SELECT
    *
FROM
    items i
WHERE
    i._id IN (?); // use item ids from previous results


texts:
I'm having an performance issue with a query, the query is something like:
Select *
from requests
where start_date > {start_date}
and end_date < {end_date}
and employee in {employee_id_list}

The problem is that this employee list is huge, and come from another service and could contain up to 10.000 employee_id.
Are there any better way of doing this than using the employee_id_list as parameter for this query?


texts: Why did a query change its execution plan and how to anticipate the change?

texts:
We have a job using a dynamic query in production for years now and its execution took 5-6 seconds up until our last code deploy. Now the same query takes days to finish. It's a relatively simple nested select that joins 5-6 tables, the largest of which have a few hundred thousand rows (others are mostly small lookup tables).

This same query runs on different database instances very quickly (with much more data and exactly the same codebase).

I'm well aware that tools like explain plan only show predicted execution plan, but the explain plan on this particular instance is very poor (for comparisson, it doesn't use an index which seems to be used on all other instances).

My question is - without changes in query, table structure the query uses or a significant change in the amount of data read, why has the execution plan changed so drastically and how can I anticipate for such a change?

added info
The query in question starts with a SELECT DISTINCT... We noticed that on this db instance, if we remove the distinct (which then doesn't necessarily give us the correct results) the execution plan is very similar to the original, much quicker one.

Edit:
Satistics were run straight after the last code deploy.


texts:
I created a test table to try and test of course if index is working with a help of EXPLAIN EXTENDED command, here is the table:

id | first_id | second_id | details

and have 7 rows and data given are:

id | first_id | second_id | details
1  | 1        |           | hello
2  | 2        |           | hello2
3  | 1        |           | hello3
4  |          | 1         | hello4
5  |          | 2         | hello5
6  | 1        |           | hello6
7  |          | 1         | hello7

And my index is ALTER TABLE test ADD INDEX test (``first_id``) (Don't know how to add the back tick properly)

Then I tried to run this query:

EXPLAIN EXTENDED SELECT * FROM `test` WHERE first_id = 1

And got this result:

From what I understand here, How can I tell if an index is being used to sort in MySQL?, mysql does not using the index.

Here is what I think the reason why mysql does not used the index.

The rows are too short
I made a mistake

If #1 is true, then how many rows do I need until mysql use the index? And if #2 is true, where I go wrong?


texts: SQL Server tail log backup fails where it shouldn't

texts:
I am trying to set up a demo of backing up the tail of the log in case of disk failure, but it doesn't work as expected.

The setup:

Set AdventureWorks backup model to "Full", create a full, a differential and a log backup (always with some writing in between)
Move the only primary data file to a USB drive:

ALTER DATABASE [AdventureWorks2012] SET OFFLINE

ALTER DATABASE [AdventureWorks2012] MODIFY FILE ( NAME = AdventureWorks2012_Data, FILENAME = 'E:\AdventureWorks2012_Data.mdf')

-- Copy file physically here

ALTER DATABASE [AdventureWorks2012] SET ONLINE
Check if file is opened on USB drive (rename fails because file is locked), then disconnect USB drive
INSERT some more data (to prove that it still works and gets restored with the log tail)
Try tail log backup:

BACKUP LOG [AdventureWorks2012] TO
 DISK = N'C:\sqldata\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.bak'
 WITH  NAME = N'AW Tail Log Backup', NORECOVERY, CONTINUE_AFTER_ERROR

This last step fails with the following message:

  Msg 3013, Level 16, State 1, Line 1 BACKUP LOG is terminating
  abnormally.

  Msg 823, Level 24, State 3, Line 1 The operating system
  returned error 21(Das Gerät ist nicht bereit.) to SQL Server during a
  write at offset 0x00000002860000 in file
  'E:\AdventureWorks2012_Data.mdf'. Additional messages in the SQL
  Server error log and system event log may provide more detail. This is
  a severe system-level 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.

So why does SQL Server insist on writing to the data file during a tail log backup where the whole point is that you only need the log file for this? Have I missed a backup parameter or some obscure trace flag?

BTW: I can still do a log backup if I use NO_TRUNCATE as the only parameter, but that's no real tail backup because it lacks the NORECOVERYportion and thus still allows users to change data after that. If I add NORECOVERY to that, it fails again with the same message as above. If I try to restore with the NO_TRUNCATE log it complains that I have to take a tail log backup first...


texts:
If I have skip_log_bin =1 is there any reason for not having innodb_flush_log_at_trx_commit = 0?

If I have understand correctly, innodb_flush_log_at_trx_commit, reads data from log file and stores them to disk in a way according to its value.

So if I am not using log (skip_log_bin=1) why should I have value other from 0 on  innodb_flush_log_at_trx_commit ?


texts: Access: Making a Pivot Table with Multiple, Same Values as Columns

texts:
First post ever, thanks for having me!

Currently we have an Access database connected to two access control devices tracking hours for different statuses: Check In, Check Out, Break Out, Break In, Overtime In, Overtime Out.

How our devices manage this though, is that they record each time independently, so in the Access database we have different rows telling the username, date and time, status, device and other interesting stuff that sadly don't help with the problem. What I want to focus on though is time, which I want to put in separate columns using Access' CrossTab mode, with TRANSFORM and PIVOT and what else.

The main issue is that the employees have many breaks during the day and sometimes many overtime schedules and I have no way to tell which is the first or the second break/overtime but the time when those happened.

Here is an example of what I have with Dave:

Name    | Date       | Auth     | Time  |
------------------------------------------------------------------
Dave    | 21.01.2019 | CheckOut | 18:00 |
Dave    | 21.01.2019 | BreakIn  | 16:45 |
Dave    | 21.01.2019 | BreakOut | 16:30 |
Dave    | 21.01.2019 | BreakIn  | 14:00 |
Dave    | 21.01.2019 | BreakOut | 13:00 |
Dave    | 21.01.2019 | BreakIn  | 10:00 |
Dave    | 21.01.2019 | BreakOut | 09:45 |
Dave    | 21.01.2019 | CheckIn  | 07:30 |

And here is what I'm trying to achieve with that:

Name    | Date       | CheckIn | BreakOut1 | BreakIn1 | BreakOut2 | BreakIn2 | BreakOut3 | BreakIn3 | CheckOut |
----------------------------------------------------------------------------------------------------------------
Dave    | 21.01.2019 |  07:30  |   09:45   |   10:00  |   13:00   |   14:00  |   16:30   |   16:45  |  18:00   |

Right now I managed to obtain somewhat what I planned to have, but I was only able to output one column for each Auth. If I manage to get the data that way I might be able to calculate exactly how many hours an employee has worked.

Do you think that using a CrossTab is a good idea to do such? If so could you please help me reach the objective? Not really looking for snippets of instant solutions, rather many ways to think on how to achieve it. I have a standard expertise with Access and database in general so one can never stop learning!

Thanks in advance, have a great day.

Leo


texts: Oracle SQL Developer 3.2.09 is consuming lot of memory on windows 7

texts:
Oracle SQL Developer  Version 3.2.09 is consuming lot of memory on windows 7.

I can see, after 1/2 days of usage and without closing the this application, the memory consumption is going upto 700 MB.

So, in this case, I need to close the application and again restart.

Any remedies for this can be helpful.

Details of SQL Developer are:

Oracle SQL Developer  Version 3.2.09

Build MAIN-09.30

JDK we are using is JDK 1.6


texts: Oracle Database Gateway for SQL Server - Connect to a SQL Server Mirroring Session

texts:
An Oracle Database server is using Oracle Database Gateway for SQL Server to define a Link Server to a Microsoft SQL Server. For the database on Microsoft SQL Server, Mirroring has been configured. So the SQL database has a failover partner.

1) How can I connect to the SQL Server Mirroring session?
2) Does Oracle Database Gateway for SQL Server has a configuration to introduce the failover partner in a SQL Server Mirroring session?

SQL Server documentations shares these keywords for these providers:

OLEDB Provider → FailoverPartner
ODBC Provider → Failover_Partner
ActiveX Data Objects (ADO)  → Failover Partner

For example an ODBC connection string to a SQL Server Mirroring session would be like this:

"Server=Partner_A; Failover_Partner=Partner_B; Database=AdventureWorks; Network=dbmssocn"

But it doesn't say anything about Oracle Database Gateway. I also couldn't find anything about introducing mirror server (failover partner) in oracle documentations.


texts:
In the past I thought I'd deleted hypothetical indexes using either a DROP INDEX statement for clustered indexes and DROP STATISTICS statement for non-clustered indexes.

I have a database that is full of DTA remnants that I would like to cleanup; however, when I try to drop the object I always receive an error telling me that I cannot drop the object "because it does not exist or you do not have permission". I am a full sysadmin on the server so would expect to have rights to do anything.

I've tried this with both DROP STATS and DROP INDEX statements but both give me the same error.

Has anyone deleted these before and is there a trick I'm missing?

Addendum

Poking around in this, I just noticed that if I R-Click on the object, both the 'Script As' and 'DELETE' options are greyed out.


texts: SQL Server replication error "profile name is not valid"

texts:
Today I started getting the below error message on one of the distribution agent.  The publication has one push and on pull subscription.  The error appeared on the push subscription only.  The publication server has 5 other publication each having pull and push subscription to the same servers and all of them works fine.

I tried changing the distributor agent profile and restarted the distribution job but didn't work.

Message
2016-12-05 00:41:45.429 Copyright (c) 2014 Microsoft Corporation
2016-12-05 00:41:45.429 Microsoft SQL Server Replication Agent: distrib
2016-12-05 00:41:45.429
2016-12-05 00:41:45.429 The timestamps prepended to the output lines are expressed in terms of UTC time.
2016-12-05 00:41:45.429 User-specified agent parameter values:
            -Subscriber SubscriberServer
            -SubscriberDB DBName
            -Publisher Publisher1
            -Distributor DistributerServer
            -DistributorSecurityMode 1
            -Publication DBName_HA_Pub
            -PublisherDB DBName
            -Continuous
            -XJOBID 0x0D2A9EB2B6FBDF4CAE11F0A700401787
            -XJOBNAME Publisher_instance_-DBName-DBName_HA_Pub-SubscriberServer-48
            -XSTEPID 2
            -XSUBSYSTEM Distribution
            -XSERVER DistributerServer
            -XCMDLINE 0
            -XCancelEventHandle 0000000000006888
            -XParentProcessHandle 00000000000083E8
2016-12-05 00:41:45.429 Startup Delay: 668 (msecs)
2016-12-05 00:41:46.101 Connecting to Distributor 'DistributerServer'
2016-12-05 00:41:46.179 Parameter values obtained from agent profile:
            -bcpbatchsize 2147473647
            -commitbatchsize 100
            -commitbatchthreshold 1000
            -historyverboselevel 2
            -keepalivemessageinterval 300
            -logintimeout 15
            -maxbcpthreads 1
            -maxdeliveredtransactions 0
            -pollinginterval 5000
            -querytimeout 1800
            -skiperrors
            -transactionsperhistory 100
2016-12-05 00:41:46.179 Connecting to Subscriber 'SubscriberServer'
2016-12-05 00:41:46.226 Initializing
2016-12-05 00:41:46.288
42000 profile name is not valid 14607
2016-12-05 00:41:46.288
42000 profile name is not valid 14607
2016-12-05 00:41:46.288
42000 profile name is not valid 14607
2016-12-05 00:41:46.304 Disconnecting from Subscriber 'SubscriberServer'
2016-12-05 00:41:46.304 Connecting to Subscriber 'SubscriberServer'
2016-12-05 00:41:46.304 Error executing a batch of commands. Retrying individual commands.
2016-12-05 00:41:51.351 Disconnecting from Subscriber 'SubscriberServer'
2016-12-05 00:41:51.351 Connecting to Subscriber 'SubscriberServer'
2016-12-05 00:41:51.367 Error executing a batch of commands. Retrying individual commands.
2016-12-05 00:42:01.390 Disconnecting from Subscriber 'SubscriberServer'
2016-12-05 00:42:01.390 Connecting to Subscriber 'SubscriberServer'
2016-12-05 00:42:01.406 Error executing a batch of commands. Retrying individual commands.
2016-12-05 00:42:16.423 Disconnecting from Subscriber 'SubscriberServer'
2016-12-05 00:42:16.423 Connecting to Subscriber 'SubscriberServer'
2016-12-05 00:42:16.454 Error executing a batch of commands. Retrying individual commands.
2016-12-05 00:42:36.474 Disconnecting from Subscriber 'SubscriberServer'
2016-12-05 00:42:36.474 Connecting to Subscriber 'SubscriberServer'
2016-12-05 00:42:36.505 Error executing a batch of commands. Retrying individual commands.
2016-12-05 00:43:01.524 Disconnecting from Subscriber 'SubscriberServer'
2016-12-05 00:43:01.524 Connecting to Subscriber 'SubscriberServer'
2016-12-05 00:43:01.540 Error executing a batch of commands. Retrying individual commands.
2016-12-05 00:43:01.556 Agent message code 14607. profile name is not valid
2016-12-05 00:43:01.571 Category:COMMAND
Source:  Failed Command
Number:
Message: if @@trancount > 0 rollback tran
2016-12-05 00:43:01.571 Category:NULL
Source:  Microsoft SQL Server Native DBName 11.0
Number:  14607
Message: profile name is not valid


texts:
I'm trying to copy data from one table to another table in a different database. After right clicking I have got an option Tasks, under it there are only the two options:

Export Data...
Import Data...

... but no option for Copy Data....

How can I solve this issue?

Thanks in advance.


texts: Why can't I get a lock on an InnoDB table?

texts:
I have problem with lock table command: LOCK TABLE v3_cam_date WRITE;
the command always running with state:   "Waiting for table metadata lock".

Try to find out which thread was hold the lock on the table v3_cam_date by using command:

SHOW OPEN TABLES FROM reportingdb
 LIKE '%v3_cam_date';

Database    Table         In_use    Name_locked

reportingdb v3_cam_date 0   0

(In_use=0 => have no any lock on the table).
And I can't get write lock only on the table v3_cam_date while can get write lock on other tables.

Try to SHOW FULL PROCESSLIST;
return about 30 thread with idle state.

Detail List
InnoDB Status
MySQL : 5.5.13
Engine: InnoDB

Thanks,


texts: Cannot remotely connect to SQL Server 2012 VM on Azure

texts:
I have a SQL Server 2012 instance running on a virtual machine in Azure. I have tried to connect to it using the public virtual IP address; however, I get an error saying:

  The server was not found or not accessible

I have checked that the allow remote connection tickbox is checked, and have allowed the .exe through the firewall. I was wondering if it's because I need to add some endpoints to my VM instance through Azure?


texts: How to SELECT rows with time duration stored in varchar column?

texts:
There is a column in table defined like this :

duration varchar(10)

It has value for duration of a MP3 file stored in it like : 52:19  (Minutes : Seconds)

How can I create a SQL query, for selecting rows with duration between 3:00 and 6:45 (i.e. SELECT songs neither too short nor too long)

The database is hosted on some 3rd party server so I cannot modify table structure.


texts: What are main differences between Database Administrators and Software Engineers?  To what extent should a Software Engineer know the details of the underlying database?  Where is the border between these two professions?


texts: Can you DENY access to a certain schema for a user with dbcreator role?

texts:
I have a database with two schemas core and dyna.

I am using dacpacs to update the database.
I want the user account to only be able to interact with tables (etc) in the core schema.

This is because the tables (10k+) in the dyna schema are generated dynamically and wont be updated by the dacpac, however the dacpack wants to check every single one to see if it will make an update; and this is killing the deployment performance. (6+ hrs in prod vs 10 min in test)

To be able to deploy dacpacs the user must me in the dbcreator (or sysadmin) role.

We have been using a user with the sysadmin role, for deployment, but we are wondering if:
We can create a user in the dbcreator role, but DENY them access to the dyna schema?

So is it possible to: DENY access to a certain schema for a user with dbcreator role?


texts: SQL Server : Index Seek with very high Scan Count

texts:
I have a query that joins 2 tables:

Documents table, that has DocID as Unique Clustered Index
DocumentsRows table, that has DocID, RowID as Unique Clustered Index

When I join those 2 tables, I usually do:

SELECT <somefields>
FROM Documents
INNER JOIN DocumentsRows ON Documents.DocID = DocumentsRows.DocID
WHERE <something>

If I run the query with STATISTICS IO ON and SHOWPLAN, I see that the engine choose Index Seek on DocumentsRows but the Number of Executions is equal to the Scan Count for the same table.

  Table 'DocumentsRows'. Scan count 2277, logical reads 12591, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
  Table 'Documents'. Scan count 5, logical reads 17526, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I know that since I'm not joining on the entire index, the seek does not retrieve only 1 value (in fact it retrieves all the rows for a specific document), then why does SQL Server choose to seek on that index instead of just scan it?

Why should an Index Seek have a so high scan count? How can I solve this?


texts: Read data with group by 100000 distinct values on 36 million rows

texts:
I am looking for sub second solution for reading data from a billion row table with 8 different columns.

I tried using mysql but it gives sub-par performance. My machine config is :12 core cpu 1.4 Ghz xeon 64GB RAM.

I tried using vertica, citus with postgres, scyllaDB and spark but nothing worked.

Query:

select
    bd,
    p_s,
    sum(v2) v2,
    sum(v1) v1,
    sum(case WHEN tf='N' THEN v2 ELSE 0 end) ty_nv,
    sum(case WHEN tf='E' THEN v1+v2 ELSE 0 end) ly_ev,
    sum(case WHEN tf='S' AND v2>0 AND v1<=0 THEN v2 ELSE 0 end) ty_NBLY_Bv2,
    sum(case WHEN tf='S' AND v2<0 THEN v2 ELSE 0 end) ty_same_ntv,
    sum(case WHEN tf='S' AND v2<=0 AND v1>0 THEN v1 ELSE 0 end) ly_BLY_NBv2,
    sum(case WHEN tf='S' AND v1<0 THEN v1 ELSE 0 end) ly_same_ntv,
    sum(case WHEN tf='S' THEN v2 ELSE 0 end) ty_sv,
    sum(case WHEN tf='S' THEN v1 ELSE 0 end) ly_sv,
    sum(case WHEN tf='S' AND v2>0 AND v1>0 THEN v2 ELSE 0 end) AS ty_BLY_Bv2,
    sum(case WHEN tf='S' AND v2>0 AND v1>0 THEN v1 ELSE 0 end) AS ly_BLY_Bv2,
    sum(case WHEN tf='S' AND v2>0 AND v1>0 THEN (v2-v1) ELSE 0 end) ty_lysiv
FROM  (
    select
        month,
        bd,
        p_s,
        si,
        sum(v2) v2,
        sum(v1) v1,
        (case when sum(sf) > 0 then 'S' else (case when sum(nf) > 0 then 'N' else  (case when sum(ef) > 0 then 'E' else null end)end)end) as tf,
        (case when sum(ly_sf) > 0 then 'S' else (case when sum(ly_nf) > 0 then 'N' else  (case when sum(ly_ef) > 0 then 'E' else null end)end)end) as ly_f
    from
        temp
    group by month, si,p_s,bd
    ) as a
group by p_s,bd

Table Structure is:

CREATE TABLE `temp` (
    `year` int(11) DEFAULT NULL,
    `month` varchar(3) DEFAULT NULL,
    `si` bigint(20) DEFAULT NULL,
    `category` varchar(100) DEFAULT NULL,
    `bd` varchar(100) DEFAULT NULL,
    `pack_type` varchar(100) DEFAULT NULL,
    `pack_segment` varchar(100) DEFAULT NULL,
    `p_s` int(11) DEFAULT NULL,
    `v2` double DEFAULT NULL,
    `v1` double DEFAULT NULL,
    `sf` int(11) DEFAULT NULL,
    `nf` int(11) DEFAULT NULL,
    `ef` int(11) DEFAULT NULL,
    `ly_sf` int(11) DEFAULT NULL,
    `ly_nf` int(11) DEFAULT NULL,
    `ly_ef` int(11) DEFAULT NULL )

Explain Plan of the Query:

"GroupAggregate  (cost=14717923.03..15097409.03 rows=40000 width=60)"
"  ->  Sort  (cost=14717923.03..14727169.03 rows=3698400 width=60)"
"        Sort Key: a.p_s, a.bd”
"        ->  Subquery Scan on a  (cost=12565877.39..13897301.39 rows=3698400 width=60)"
"              ->  GroupAggregate  (cost=12565877.39..13860317.39 rows=3698400 width=64)"
"                    ->  Sort  (cost=12565877.39..12658337.39 rows=36984000 width=64)"
"                          Sort Key: temp.month, temp.si, temp.p_s, temp.bd”
"                          ->  Seq Scan on temp  (cost=0.00..964340.00 rows=36984000 width=64)"


texts: How can I prove NOLOCK is the source of deadlocking problems?

texts:
I'm not trying to start a windows/mac type discussion.

Personally, I don't need any convincing that NOLOCK is not a good idea as a reflexive practice. It seems when you're developing everything should be purposeful not reactionary (/amen)

So... programmer-in-charge insists NOLOCK is the way to go. Recommends with all ad-hoc queries and whenever querying production. I haven't seen a stored procedure without nolock hints on every table.

Don't want to be that guy who comes in and tells everyone a core belief is all wrong without something to back it up.

Just looking at the comment sessions under the various blog posts, sending a link may not be enough. Long-held beliefs etc... Some people aren't convinced it's a problem. See: Comments section under every nolock blog post I've read.

Currently some other DBAs are wrestling with some mysterious deadlocking. How does one determine whether NOLOCKs are the source?

It's been suggested looking at XML from traces etc, but this won't explicitly state that the deadlocks are causing the problem, will it? I've never seen error messages that straight forward. is that true?

How else could these deadlocks be pinned on this?

DDL statements like CREATE would be a clue. Is there any output that I could point to or some piece of data I could find that would help corroborate my theory before I raise the alarm?

Or am I running trace flags or extended events to identify what's running when the deadlocks occur and then deducing from DDL statements?

Looking at all the different ways data can get messed up with nolock hints, it seems like a hard problem to decisively pin down.


texts: Is it possible to pass boolean true/false (as opposed to 1/0) into MariaDB with STRICT_TRANS_TABLES?

texts:
I've recently done a new server install, and it seems on the old one I had sql_mode='' in my my.cnf file.  I've removed that now, so I have the default STRICT_TRANS_TABLES and the other default options.
Sadly though, that STT declaration is breaking my php PDO code where I explicity force type on my booleans, and send true/false through to the database.
I guess before these were being silently converted to 1/0.
Are these my only options:

change my PHP variables ($isArchived etc) from the semantic niceness of true/false to 1/0.

reinstate sql_mode = ''

Or am I missing something?  My columns are defined as tinyint(1) - that used to be the best we could do with MySQL, and I don't THINK there's a boolean type, even now.  What's the best practice?  Thank you!
edit:
I realise now it's just false it fails on.  It happily converts true to 1, but for some reason sees false as '' and balks.  Could this be a problem in my PHP code rather than MySQL...


texts: Is there a possibility to run 2 minor Postgres version in parallel (Postgres 10.x)?

texts:
I have Postgres db instance 10.6 running in production say node-b: rhel 7.6 while I am building another cluster (not instance) of 2 nodes. Out of these 2 nodes one posgres node will reside on this machine. Somehow other node node-a has postgres 10.7 running there already for some more instances.

Is there a possibility to run 2 postgres minor version, in my case 10.7 and 10.6 on  production machine node-b in parallel?

I tried testing it by installing from yum on node-b and found yum will override/change the existing installation. I am afraid it will kill the running postgres and hence did not proceeded further.

Off-course I can first upgrade node-b postgres production instance to 10.7 and then all-together can work on same version 10.7 across cluster but that I keep the last option. It has its own approval and all usual process track attached.

I recollect that it is a possibility to build from code (make,configure,install and ...) of particular version of 10.7 on node-b but I am not sure would it work as the underlying directory under /usr will still be same?


texts: Why first_lsn of full backup and 1st transaction log backup are same?

texts: shouldn't first_lsn of the 1st transaction log backup be 36000000022400001 instead of 36000000022100001? In this case it equals to first_lsn of full backup.


texts:
If I write in SQL Server :

SELECT number FROM master..spt_values WHERE Type = 'P' ORDER BY Number

I get a list of sequential numbers which I can already use, instead of writing a table with sequential numbers.

This can be much helpful for small joins, row numbering etc.

I was wondering if there are other hidden tables like this one which can help me do other things as well? For example, I might want to select someNonImportantTextColmn from master..unknown and to apply a function of mine.

Where can I find a complete list of these "help tables"?


texts: How do convert a 66,862,521 row table from MyISAM to InnoDB without going offline for several hours?

texts:
is it possible (and how) to convert a huge MyISAM table into InnoDB without taking the application offline. It requires to insert a couple of rows into that table every second but it is possible to suspend it for about 2 minutes.

Obviously ALTER TABLE ... engine=innodb will not work. Therefor I had the plan to create a new table with the innodb engine and copy the content into it. And in the end, suspend the application log thread and RENAME TABLE.

Unfortunately even doing the copying in small batches of 100 rows generates significant lag after some time.

Edit: Existing rows are never changed, this table is used for logging.


texts:
What are valid connection strings for SSMS login box?

Every once in a while I will have to connect to an instance of SQL Server with some specific configuration setting or in a DMZ where no host names are available or I'll have to connect to the Dedicated Admin Connection (DAC) via SSMS or SQLCMD. Because I rarely connect to a SQL Server via specific port or with the DAC, I'll tend to forget what the exact syntax options are to get a connection up and running. I'll then have to search the internet again for the umpteenth time to retrieve the exact connection strings and will regularly be frustrated that I can't find the information required.

Research

I'll read all the questions and answers previously posted on Serverfault, Stackoverflow, and DBA.

Serverfault

[1] DAC connection port SQL Server 2005 SP3
[2] Remote SQL server connection failure
[3] SQL Server 2008 R2 remote connection

Stackoverflow

[4] How to specify a port number in SQL Server connection string?

DBA

[5] DAC connection error

Microsoft

Additionally, I'll search the Microsoft site for relevant articles:

[6] How to configure SQL Server to listen on a specific port
[7] Configure the Windows Firewall to Allow SQL Server Access
[8] Surface Area Configuration
[9] How to configure SQL server to listen on different ports on different IP addresses?
[10] Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager)
[11] Diagnostic Connection for Database Administrators

Brent Ozar

And Kendra Little has an article for some interesting aspects of DAC.
[12] The Dedicated Admin Connection: Why You Want It, When You Need It, and How To Tell Who’s Using It

On a side note: sometimes the DAC connection is also referred to as the ADMIN connection.

I find that in the end there is never an actual summary of simple connection strings to input into an SSMS login box, nor what to add to a SQLCMD.

This question and the following answers are intended to sum up what I found so far and to put the information into one single knowledge base article.

Actual Question

Given the following settings for a SQL Server Standard Edition:

Servername.....:    SERVERNAME
IP address.....:    123.1.2.3

1st instance (default instance):
CNAME/Alias....:    SERVERNAME-I01
Name...........:    MSSQLSERVER
Port...........:    1433 (TCP)
IP address.....:    123.1.2.3
DAC Port.......:    1434 (TCP)

2nd instance (default ip & port configuration according to [7]):
CNAME/Alias....:    SERVERNAME-I02
Name...........:    Instance2
Port...........:    dynamic (TCP)
IP address.....:    123.1.2.3
DAC port.......:    dynamic (TCP)

3rd instance (dedicated IP and port):
CNAME/Alias....:    SERVERNAME-I03
Name...........:    Instance3
Port...........:    1433 (TCP)
IP address.....:    123.1.2.4
DAC port.......:    dynamic (TCP)

Browser Service:    1434 (UDP)
Browser service:    ON

...what are valid connection strings for each instance that I can enter into the SSMS login box

...with the host name?
...an alias (CNAME)?
...with an ip address?
...for a "Dedicated Admin Connection" (DAC aka ADMIN:)?

Assumptions

The server firewall is either deactivated or configured according to the Microsoft documentation mentioned in 6, 7 or 8. This is also true for any network firewall inside the LAN (including the connection to a DMZ server).
The additional instances, apart from the default instance, have manually been configured to listen on different IP addresses according to 9 and in this example have been configured to listen on fixed ports 10.
In some cases DAC will have been enable according to 11 and 12.


texts:
I'm trying to create a logical replication between 2 Postgres 10 databases in separate clusters. All the process seem to be working, no error and \dRs+ shows the subscription correctly. The master database is 3TB size approx. How much does it take to start seeing records in the replica database?

The replication process I followed was:
Set wal_level to logical.
Restart the database.

Create a user and grant replication privileges.
wal_keep_segments: 512
Change pg_hba.conf for this replication user.
Create a publication for all tables.
CREATE PUBLICATION mypub FOR ALL TABLES

Then on the replica:

Create a database and the tables.
Create the subscription.
CREATE SUBSCRIPTION mysub CONNECTION 'dbname=testdb host=192.168.1.30 user=rep password=Password port=5432' PUBLICATION mypub;


texts:
What I'm attempting to do is LEFT OUTER JOIN a table, but I only want the last inserted record of the joined table to affect the results. Currently each table row within the joined table seems to be affecting the results of my query.

My current LEFT OUTER JOIN is similar to:

LEFT OUTER JOIN tablethree AS t3 ON t3.sid = t2.sid

Inside of tablethree I have two different results. Both of them are appearing to affect my results, but what I would like is for the most recently entered result to affect my end results.

My full query looks similar to (only using * here to save time):

SELECT t1.*
FROM tableone AS t1
INNER JOIN tabletwo AS t2
  ON t1.cid = t2.id
LEFT OUTER JOIN tablethree AS t3
  ON t3.sid = t2.sid
WHERE t1.fieldone = 1
  && t1.odate NOT BETWEEN t3.startdate AND t3.enddate

The tablethree start date and end date that I want reflect 03-01-2014 and 03-31-2014, but the second result in that table is 04-01-2014 and 04-30-2014 and this appears to be affecting the results of the query.

My Question is: How can I change the LEFT OUTER JOIN to only use the most recently entered result and not each result found to affect my final outcome of results?


texts:
Table size 45 million rows
Schema: item name, categories and subcategories, order quantity and price.

What is the fastest way to put each item into an appropriate bucket?

For example: price range given will be: 500- 600
We have 10 buckets: 500-510, 510-520, ...590-600
We also have filters on particular category and subcategory, i.e. specific category and subcategory have to be only considered in a query.

By bucketing, it means that all items which falls in a said range, and belongs to the particular category and subcategory, there order quantity will be summed.

What will be an optimal way to construct the query? We have a query which does all the processing in one go using case and when. However, this takes around 18 sec or more mostly.

This has to be optimized. I have tried adding indexes, which is not helping much.


texts:
For security reasons I need a server-side (running Debian 6.0 Squeeze) logging of all queries that may have changed the content of a MySQL DB (v. 5.1) and the user who issued it. I had to rule out

the General Query Log because of performance issues (it logs everything and that's too much IO)
the Binary Log because it doesn't log the user's name.
use a tool like ngrep to catch the network traffic and filter for UPDATE, DELETE etc. because this will get me in a mess with transactions and I can't know if a received query has really been executed.

I couldn't find any settings that would have let me change the behavior of the MySQL-inherent logs, so I'm looking for other solutions. I've come up with two possibilities so far:

write the general query log to a named pipe and attaching a filter and writer to the other end of the pipe – but I'm concerned about the performance of this…
transmitting the relevant logs separately to the server but that way I'd have to send the queries twice (once for the DB and again for logging), it would be difficult to assure the logs are in sync with the DB (transactions, locks etc.), and for security reasons it may not be wise to trust the client to really send the logs

Backgound:

the users access the DB via a Java Desktop Application that opens an SSH tunnel to the MySQL server
I'm using EclipseLink as persistence provider
the application makes heavy use of transactions
the server is running in a shared environment

Do you have a better idea on how to perform my logging?


texts: Migrating SQL database to a new SQL server

texts:
Thanks for viewing my question. I currently have a database on SQL Server 2005, which is being migrated to SQL Server 2008. Each SQL Server is named differently in SSMS. The old is named GARDB1, and the new GARDB2008. GARDB2008 is in production with everything running smoothly besides this is the last database I need to move over. Now, obviously both servers have different IP addresses, but I can add the old IP to the new server after migration.

My problem is in connecting too MANY different reports we have connected to the database after migration. The connection strings do not include the IP address, only the name GARDB1. There is also a website attached to this database.

How can I migrate this database to the new server while keeping everything connected? It is okay to have downtime while doing this. I tried creating a new alias in SQL configuration manager, but this did not help. What am I missing?

Thanks for any help!

Josh


texts: Mysql 5.5 to Mysql 5.6 upgrade Insert query is breaking

texts:
Assume column with the following definition

product_id      INT   NULL     NO_DEFAULT
product_id_nn   INT   NOT NULL NO_DEFAULT

and query

insert into table (product_id,product_id_nn) values ('','')

the above query works in Mysql 5.5. it will insert NULL value for product_id and0for product_id_nn.

the same query breaks in Mysql 5.6 and informs you that wrong type data is getting passed.

Yes, I am 100% accepting Mysql 5.6 statement, however why it was working in 5.5 and as you expect it's huge old php application breaking the functionality with 5.6

Any setting to make it work like 5.5?


texts: What are the advantages of a database design with single table for MtM relationships?

texts:
The database that backs our software product has a table design like the following:

Positions
ID    Name    Parts
1     One     12345
2     Two     12346
3     Three   12347

Collections
ID      TableID  Collection
12345   1;1      1;2
12346   1;1      3;4
12347   1;2;2    5;1;2

Parts
ID     Name
1      TestOne
2      TestTwo
3      TestThree
4      TestFour
5      TestFive

SubParts
1      SubPartOne
2      SubPartOne

From the above example, each Position has a collection of Parts, but these are mapped generically (without foreign key constraints) into the Collections table. The Collections table keeps track of all relationships between all objects, not just between the example tables shown above, and will be used any time a collection is used.

That means that if I want to get the Position with ID 1, and all of its parts. I have to do three queries:

SELECT * FROM Positions WHERE ID = 1
SELECT * FROM Collections WHERE ID = 12345

Split the string by the semicolons

SELECT * FROM Parts WHERE ID = 1 OR ID = 2

The advantages of the current approach are:

A collection of something can actually be selected from more than table. For example, if SubPart inherits from Part, and position contains a list of Parts, this will be handled OK.

The disadvantages of the current approach are:

Speed? We need to do many queries to load data.
The Collections table is the largest or second largest table in our database.
No support from ORM frameworks? We are looking at switching our persistency layer to use EF or NHibernate etc. but I don't believe our current design will be supported by any of these frameworks.

My questions are:

Is there advantages or disadvantages to this approach that I haven't listed?
Is this design familiar to people and if so, does it have a name?
If the design is familiar, is it supported by out of the box ORM frameworks?


texts:
One of our developers are trying to run the below query on a development server, which involves pulling data from a linked server, production. The query ran for more than 14 hours before it was stopped.

I looked at the execution plan in SQL Sentry Plan Explorer - please find the execution plan below.

How can this query be tuned for better performance? Are there any glaring errors in the query? Are there any pointers, blogs posts that will help me improve this query?

Both the servers involved are SQL Server 2005

SELECT A.SETID
,A.CUST_ID
,A.CNTCT_SEQ_NUM
,A.NAME1
,A.TITLE
,C.DESCR
FROM PS_CUST_CONTACT A
,[linksrv].[prodDB].dbo.PS_BO_ROLE Z
,[linksrv].[prodDB].dbo.PS_RD_PERSON B
,[linksrv].[prodDB].dbo.PS_BO_ROLE_TYPE C
WHERE Z.BO_ID = B.BO_ID
AND Z.ROLE_TYPE_ID = C.ROLE_TYPE_ID
AND Z.ROLE_END_DT >= GETDATE()
AND A.EFFDT = (
    SELECT MAX(EFFDT)
    FROM PS_CUST_CONTACT CUST_CONTACT
    WHERE CUST_CONTACT.SETID = A.SETID
        AND CUST_CONTACT.CUST_ID = A.CUST_ID
        AND CUST_CONTACT.CNTCT_SEQ_NUM = A.CNTCT_SEQ_NUM
        AND CUST_CONTACT.EFFDT <= { FN CURDATE() }
    )
AND A.EFF_STATUS = 'A'
AND B.PERSON_ID IN (
    SELECT A1.PERSON_ID
    FROM PS_CONTACT A1
        ,PS_CONTACT_CUST B1
    WHERE A1.EFFDT = (
            SELECT MAX(A_ED.EFFDT)
            FROM PS_CONTACT A_ED
            WHERE A1.SETID = A_ED.SETID
                AND A1.CONTACT_ID = A_ED.CONTACT_ID
                AND A_ED.EFFDT <= SUBSTRING(CONVERT(CHAR, GETDATE(), 121), 1, 10)
            )
        AND A1.SETID = B1.SETID
        AND A1.CONTACT_ID = B1.CONTACT_ID
        AND B1.EFFDT = (
            SELECT MAX(B_ED.EFFDT)
            FROM PS_CONTACT_CUST B_ED
            WHERE B1.SETID = B_ED.SETID
                AND B1.CONTACT_ID = B_ED.CONTACT_ID
                AND B_ED.EFFDT <= A.EFFDT
            )
        AND A.CNTCT_SEQ_NUM = B1.CNTCT_SEQ_NUM
        AND A.SETID = B1.CUSTOMER_SETID
        AND A.CUST_ID = B1.CUST_ID
    )


texts:
I am sure I should either know this or have been able to determine this, but I am investigating an issue with user allocated space quotas and am wondering how to determine the default (if there is one).

Our users are running into a problem with a database set up by another individual and apparently none of them were set up with specific quotas.  Is there some default quota that users are set to and if so, how can I determine what that is or change it.  At this point, I am setting them individually, but was not sure why they were never established originally.


texts: How to filter out Scalar Valued User-Defined Function usage from SQL Server Audit Data?

texts:
We have a SQL Server database which has a database audit specification which audits all execute actions on the database.

CREATE DATABASE AUDIT SPECIFICATION [dbAudit]
FOR SERVER AUDIT [servAudit]
ADD (EXECUTE ON DATABASE::[DatabaseName] BY [public])

We've found that some queries will write to the audit log the use of a scalar function for every row in a result set.  When this happens, the log fills up before we can ETL it into it's final resting place and we have a gap in our logging.

Unfortunately due to compliance reasons, we cannot simply stop auditing every EXECUTE statement.

Our first thought for approach to this problem is to use WHERE clause on the Server Audit to filter out the activity.  The code looked like this:

WHERE [object_id] not in (Select object_id from sys.objects where type = 'FN' )

Unfortunately, SQL Server doesn't allow relational IN operator (probably because it doesn't want to query every time it has to write to the audit log).

We would like to avoid writing a stored proc which hard codes the object_id in the WHERE clause, but that is our current thinking on the best way to approach this problem.  Is there an alternate approach that we should consider?

We've noticed that when the scalar function is in use in a recursive CTE, then it causes the query to write to the audit log for every row in the result set.

There are some Scalar Valued Functions that are delivered by a vendor which we cannot delete or move to an alternate database.


texts:
Try run PostgreSQL + WAL-E + Minio.

Minio: https://github.com/minio/

yum install -y https://download.postgresql.org/pub/repos/yum/9.6/redhat/rhel-7-x86_64/pgdg-centos96-9.6-3.noarch.rpm
yum install -y postgresql96 postgresql96-server wal-e python-gevent lzop pv python34-pip
/usr/pgsql-9.6/bin/postgresql96-setup initdb
systemctl enable postgresql-9.6
systemctl start postgresql-9.6
mkdir -p /etc/wal-e.d/env
echo "AWS_ACCESS_KEY_ID" > /etc/wal-e.d/env/AWS_ACCESS_KEY_ID
echo "AWS_SECRET_ACCESS_KEY" > /etc/wal-e.d/env/AWS_SECRET_ACCESS_KEY
echo 's3://10.233.60.57:9000' >  /etc/wal-e.d/env/WALE_S3_ENDPOINT
echo 's3://minio/pg-backups' >  /etc/wal-e.d/env/WALE_S3_PREFIX
chown -R root:postgres /etc/wal-e.d
pip3 install envdir gevent boto
systemctl restart postgresql-9.6
sudo su - postgres

Minio configured as:

I create large ~1 GB random dataset in PostgreSQL
https://gist.github.com/ololobus/5b25c432f208d7eb31051a5f238dffff

But WAL don`t have in Minio.

Log WAL-E

envdir /etc/wal-e.d/env wal-e backup-push /var/lib/pgsql/9.6/data
wal_e.main   INFO     MSG: starting WAL-E
        DETAIL: The subcommand is "backup-push".
        STRUCTURED: time=2019-02-12T07:41:01.239297-00 pid=18672
wal_e.operator.backup INFO     MSG: start upload postgres version metadata
        DETAIL: Uploading to s3://minio/pg-backups/basebackups_005/base_000000010000000000000092_00000040/extended_version.txt.
        STRUCTURED: time=2019-02-12T07:43:31.367410-00 pid=18672
wal_e.operator.backup WARNING  MSG: blocking on sending WAL segments
        DETAIL: The backup was not completed successfully, but we have to wait anyway.  See README: TODO about pg_cancel_backup
        STRUCTURED: time=2019-02-12T07:43:31.370409-00 pid=18672
NOTICE:  pg_stop_backup cleanup done, waiting for required WAL segments to be archived


texts: Duplicated SQL Server Execution Times For Dynamic Query

texts: I'm getting duplicated SQL Server Execution Times from SET STATISTICS IO,TIME ON with dynamic queries. Is this a normal behavior?


texts: TempDB Version Store used by DB STARTUP background process

texts:
I have two availability group on the same SQL Server 2012 Instance (synchronous commit).
Trace flags 1118 and 1117 are both enabled.
TempDB has been growing for about two weeks: it starts from 36GB and now it's 130GB (4 data files).
I checked if there are any active transactions with DBCC OPENTRAN command but nothing.
I run the following query:
SELECT
    db_name(spu.database_id) as database_name,
    ast.session_id,
    ast.transaction_id,
    ast.transaction_sequence_num,
    ast.elapsed_time_seconds,
    b.program_name,
    b.open_tran,
    b.status,
    ses.row_count,
    (spu.user_objects_alloc_page_count * 8) AS user_objects_kb,
    (spu.user_objects_dealloc_page_count * 8) AS user_objects_deallocated_kb,
    (spu.internal_objects_alloc_page_count * 8) AS internal_objects_kb,
    (spu.internal_objects_dealloc_page_count * 8) AS internal_objects_deallocated_kb,
    loginame,
    last_request_start_time,
    last_request_end_time,
    cmd,
    lastwaittype,
    dbid
FROM sys.dm_tran_active_snapshot_database_transactions ast
  --JOIN sys.dm_tran_active_transactions at on at.transaction_id = ast.transaction_id
  JOIN sys.dm_exec_sessions ses ON ses.session_id = ast.session_id
  JOIN sys.dm_db_session_space_usage spu ON spu.session_id = ses.session_id
  JOIN sys.sysprocesses b on ast.session_id = b.spid
ORDER BY elapsed_time_seconds DESC

The output shows two rows with:

transaction_id = 0
cmd = "DB STARTUP"
lastwaittype = REDO_THREAD_PENDING_WORK

and one of them has 1202046 elapsed_time_seconds: 13 days of elapsed time roughly corresponds to the beginning of version store problems.
Each row is related to a specific database with DBID 7.
I think, this active snapshot prevent version store cleanup but I can't kill them (transaction_id = 0) and I can't be sure of that: I know version store has 100 GB allocated (I used the following command) but I don't know who use it and who produce the snapshot:
SELECT
SUM (user_object_reserved_page_count)*8/1024.0/1024.0 as user_obj_GB,
SUM (internal_object_reserved_page_count)*8/1024.0/1024.0 as internal_obj_GB,
SUM (version_store_reserved_page_count)*8/1024.0/1024.0  as version_store_GB,
SUM (unallocated_extent_page_count)*8/1024.0/1024.0 as freespace_GB,
SUM (mixed_extent_page_count)*8/1024.0/1024.0 as mixedextent_GB
FROM sys.dm_db_file_space_usage


texts: Memory Settings for SQL Server, SSAS, and SSRS

texts:
I started a new job about 4 months back and was surprised to learn that there has NEVER been a SQL Server DBA here, just developers who don't really know what they're doing (like me). I am not a DBA by any means but I've convinced my employer to send me to a DBA course in a few months after noticing really poor performance of our SQL Server instance. Until then, I was hoping someone could help me configure the memory settings on our server. Our server has 56.0 GB of RAM and it is running SQL Server, Analysis Services, and Reporting Services.

Currently the server is sitting at 98% memory usage with SQL Server, SSAS and SSRS using roughly 50GB, 1.5 GB, and 0.5GB respectively.

SSRS is hardly used, only running about 15 or so legacy reports once a day, every day. SSAS is about 1/3 as busy as SQL Server and is definitely not doing work all day like the SQL server instance.

Additionally, the SQL Server has the following memory settings:

Minimum server memory: 0
Maximum server memory: 2147483647

the SSAS Server has:

Memory \ HardMemoryLimit:0
Memory \ LowMemoryLimit: 65
Memory \ TotalMemoryLimit: 80
Memory \ VertiPaqMemoryLimit: 60 (we are using the mulitdimensional model, not tabular)

and the reportserver.config file has:

<MemorySafetyMargin>80</MemorySafetyMargin>
<MemoryThreshold>90</MemoryThreshold>

I have adjusted the Maximum server memory setting based on the following recommendation I got from somewhere:

  To prevent Microsoft SQL Server from consuming too much memory, you
  can use the following formula to determine the recommended maximum
  server memory: (1) Reserve 4GB from the first 16GB of RAM and then 1GB
  from each additional 8GB of RAM for the operating system and other
  applications. (2) Configure the remaining memory as the maximum server
  memory allocated for the Microsoft SQL Server buffer pool.

but do i need to possibly lower this with SSAS and SSRS on the server too?
Can you recommend values for SSAS and SSRS too?
Oh, all instances are the 2012 version.

Thank-you.


texts: TSQL Generic Row number theoretical question

texts:
To start, We have a pretty complicated system that's been a compilation of over 20 years of code... And the code needs to work with SQL2005 all the way to SQL2014 (can't use the offset Fetch Next)

one of the procedures that I'm trying to write is to be able to take parameters such as an action (add, delete, modify), a source(which is a query), a target (which is a table) and keys (which will be used to join the source and target to do the action)... and this for any source to any table within our application... This being said, I have been able to create the procedure. Everything worked fine until we hit a big source and target (15 million rows).

one update statement was too big and an update statement for every row was too long... So I've managed to get the update to work in chunks of 500K rows in a test environment which performs way better but in my test lab I knew the source and the target so I was using a specific field from the source and Target to make sure I take the next 500K rows.

pseudo-code of my tests look like this.

DECLARE @BatchSize INT = 500000, @ROWCNT INT, @ROWTOTAL INT
WHILE 1 = 1
BEGIN
   UPDATE  TOP (@BatchSize) PRI
                SET {List of columns to update}
                FROM ({Source}) BAT
                JOIN {Target} PRI ON {List of Keys}
                WHERE PRI.F1 <> BAT.F1 -- F1 is updated by the list of columns to update
    IF @@ROWCOUNT < @BatchSize BREAK
END

The problem I'm having is that I want to use similar code but since I don't know what fields exist in the list, I can't use the WHERE PRI.F1 <> BAT.F1 which means that My loop would always pickup the same 500K rows (top 500K).

To counter that problem, I would need to use the ROW_NUMBER function but in my eyes it would double the execution time since I would have to create a temporary table that includes the row_number value and logic that will take the next 500k and so on.

Can anyone think of any other way (using physical addresses or something else) to be able to pick up the next 500K rows without having to create a temp table?

your're input is greatly appreciated.
Thank you
JG


texts: How to enforce integrity when joined tables share a foreign key?

texts:
Consider the following table structure:

UserGroup(ie. 'Admin')
    ID = 'Admin'
    ApplicationFK = 'App1'

JoinTable
    UserGroupFK = 'Admin'
    PermissionFK = 'Approve'
    PermissionValue = 'READ'

Permission(ie. 'Approve')
    ID = 'Approve'
    ApplicationFK = 'App2'

UserPermission
    UserFK = 'John'
    PermissionFK = 'Approve'
    PermissionValue = 'WRITE'

User
    ID = 'John'

Where you have a jointable between UserGroup and Permission, with both UserGroup and Permission having foreign keys to Application.

Is there any way to enforce integrity, to avoid the situation above, where the ApplicationFKs don't match? I cannot simply remove one of the Foreign Keys, as it is possible to have a Permission without any UserGroups, and possible to have a UserGroup without any permissions.


texts:
I have a database that has financial transactions (eg 105.30) stored in a longtext datatype. I can not change the schema, and it is mariadb 10.0.38

I chose decimal(13,2) for my casting, but when I aggregate on one of the columns, I do get some inaccuracies. What should I do to get the numbers spot on?

eg
SELECT Country, SUM(CAST(mycolumn AS DECIMAL(13,2)))
GROUP BY Country


texts: Quorum configuration warning for cross subnet AAG failover

texts: We have setup a 3 node AAG in our staging environment as a POC. The 3rd node has been configured in a different subnet and also as an asychronous replica. The Windows Server version is 2012 R2 and the SQL Server version is 2014 SP2. After I changed the availability mode to synchronous commit and tried to perform a manual failover across subnets, this is what I get. I had removed the node weight of the 3rd node. Am I  missing something here? Please advice.


texts: Select number of records until the sum is less than 'n' - Access SQL

texts:
I am working in Microsoft Access. My requirement is: user will give any percentage value and I have to find the number of IDs which form the percentage of the 'Value' column. For eg in the below dataset (it is sorted by descending of value column which is also required), the sum of all values is '8409131'.

ID        NAME    VALUE
1000000090  A   2295175
1000000974  B   1942753
1000015555  C   1887965
1000004864  D   1310400
1000015557  E   972838

If I enter 75%, the value is 65170765.25, so i need to return all the IDs which forms the '65170765', less than or equals to. So in this case below are the sum of values which are less than 65170765.

ID      NAME    VALUE
1000000090  A   2295175
1000000974  B   1942753
1000015555  C   1887965

Is this possible to achieve my requirement in Access SQL?

My plan is to make a running total column to find sum of first two rows and then sum of that value with next row. But in Access, I am not able to figure out  how to create incremental rows in select query also to achieve this.

Query I tried:

SELECT T1.ID, T1.NAME, T1.VALUE,(T1.VALUE + T2.VALUE)  FROM
(SELECT  OBLIGOR_ID , RUN_MANAGER.BANK_NAME AS BANK_NAME, RUN_MANAGER.REPORTING_PERIOD, SUM(VALUE) As VALUE
FROM DATA
INNER JOIN RUN_MANAGER
ON DATA.RUN_NUMBER=RUN_MANAGER.RUN_NUMBER
WHERE  RUN_MANAGER.NAME='A'
GROUP BY ID,RUN_MANAGER.NAME
ORDER BY SUM(VALUE) DESC) AS T1
INNER JOIN
(SELECT  OBLIGOR_ID , RUN_MANAGER.BANK_NAME AS BANK_NAME, RUN_MANAGER.REPORTING_PERIOD, SUM(VALUE) As VALUE
FROM DATA
INNER JOIN RUN_MANAGER
ON DATA.RUN_NUMBER=RUN_MANAGER.RUN_NUMBER
WHERE  RUN_MANAGER.NAME='A'
GROUP BY ID,RUN_MANAGER.NAME
ORDER BY SUM(VALUE) DESC) AS T2
ON T1.ID=T2.ID+1

This is not a duplicate question. The problem is, this question is based on Access SQL and also I do not have any incremental ascending rows.


texts: How to see query executed for a specific user from V$SQL

texts:
I am trying to have the queries used for a specific user at the end of the day. But it seems the queries in this view  V$SQL are deleted every short time. I am not sure how much time. Also i dont know why they are deleted if i still see some records with older LAST_ACTIVE_TIME is there a way to save them before they are deleted. For that when are they deleted?or other suggestion. Hope you can help me.

Thank you!


texts: What index strategy to use to improve multi-condition SELECT?

texts:
As far as I understand, indexes should be selected on fields with high cardinality of data - e.g. sex only has two values ( male and female ) so low cardinality, whereas something like sha256sum field would be many varied strings, so high cardinality of data. That makes sense that it improves searching with simple SELECT * from table_foo WHERE foo = 'bar'; type of query. But if I have something like SELECT * from table_foo WHERE foo = 'bar' and baz > 42; how do I construct an index to improve performance ? Do i provide multiple fields to index ? Do I create two different indexes ?

I'm working with SQLite, but the topic isn't SQLite specific. However, SQLite is minimalist so if there's certain things to be kept in mind for this specific database that'd be also good to know.


texts: How to recreate Oracle db from .ora and .dbf?

texts:
I have a number of files left from a previous Oracle db and I would like to create a new one from the files.

The files include:

init_locdb.ora
16 .dbf files
spfilelocdb.ora
log files
.ctl

Could you tell me is it possible to recreate a new Oracle database from the files so it is identical (almost identical) to the previous one?


texts: Create multiple variable columns from single column value

texts:
The following code gets the number of days for a specific month set in @month

DECLARE @month tinyint
, @Days varchar(max)

SET @month = 1 -January

;WITH DaysInMonth
AS
(
    SELECT DATEADD(month, @month, DATEADD(month, -MONTH(GETDATE()), DATEADD(day, -DAY(GETDATE()) + 1, CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime)))) D
    UNION ALL
    SELECT DATEADD(day, 1, D)
    FROM DaysInMonth
    WHERE D < DATEADD(day, -1, DATEADD(month, 1, DATEADD(month, @month, DATEADD(month, -MONTH(GETDATE()), DATEADD(day, -DAY(GETDATE()) + 1, CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime))))))
)
SELECT
   @Days = substring((SELECT ( ', ' + CONVERT(varchar(2), D, 104))
                           FROM DaysInMonth
                           FOR XML PATH( '' )
                          ), 3, 1000 )  FROM DaysInMonth

SELECT @Days

I am getting the following output in a single column:

01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31

How can I get these values in different columns?.

The main aim for get this query is to join or union with another query that contains records marked with 1 or 0 set to a specific date. The final query will output a result similar to the one a have below but, for the full month sent in the parameter.

July    28       29      30         31          01      02          03
NULL    Monday  Tuesday Wednesday   Thursday    Friday  Saturday    Sunday
Jhon    1        0      1           0           1       0           0

Any other solution to get around similar output is welcome.Thank you


texts:
I have three tables:

Table City has Two columns:

City_ID, CityName <City_ID is Primary Key>

Table Accommodation has Two Columns:

Accommodation_ID, CategoryType <Accommodaton_ID is Primary Key>

Table Instance has four columns:

Instance_ID, Description, City_ID, Accommodation_ID <Instance_ID is Primary Key; City_ID is Foreign Key, Accommodation_ID is Foreign Key>

I am trying to run an SQL query to "Get all Description as long as "CityName=Melbourne" and "CategoryType=Hotel".

My query returns multiple rows of same values:
See my sql code below:

SELECT C.Description, A.CityName, B.CategoryType
FROM Instance AS C, City as A, Accommodation as B
where A.City_ID = (select City_ID from City where CityName = 'Melbourne') AND A.City_ID = C.City_ID AND B.CategoryType = 'Hotel';

I am using MS Access
Please what am I doing wrongly..
Thank you


texts: Drizzle database system design questions - architecture &c

texts:
Given that I have some time on my hands at the moment, I was wandering through old Drizzle database stuff - I particularly like the microkernel design principles that underlie the whole Zeitgeist of the system. Think microservices - showing once again that there's nothing (OK, rarely) ever anything truly new in ICT!
Much of the documentation seems to have disappeared - certainly the primary sources appear to have gone (drizzle.org is no more...), so it's a bit of warcheology (= my own port-manteau word for "web archeology").
The best document (there are a few YouTube videos also) that I could find about the system was here which (for me anyway) raises a a couple of interesting questions.
From the last paragraph from Brian Aker's blog (at the beginning of the page) we have this:

Adopt external libraries, use open source mechanics for contributions,
keep to an open style of communication, and remove the line between
internal and external development.

which is great, but then in the following list of Drizzle features (2nd point), we have this:

No shipped libraries

which I assume means Boost and similar? But, doesn't this conflict with what Aker wrote in his blog?
Another bit I don't understand is (point 9):

Optimized field types

What are they?
And then there's this (point 12):

No database data formatting

What does this mean exactly?
I know that I've asked a couple of questions in here in breach of the dba.stackexchange principle that posts should contain one and only one question - but I believe that in this case, my questions form a coherent ensemble and the site wouldn't benefit from splitting them up (bascially clutter!).


texts:
I've been working on solving the problem shown here, and a question arose

Here's the schema I created, my statement of the problem, and my test data:

Schema: Students

CREATE TABLE Students (group_id text, sql_quotient float);
INSERT INTO Students(group_id, sql_quotient)
VALUES
    ( 'A', 25 ),
    ( 'B', 30 ),
    ( 'C', 40 ),
    ( 'A', 35 ),
    ( 'B', 20 );

Task: Display max average sql_quotient among all the groups.

group_id is guaranteed to be a Single character of range from A-Z.
Here for group A, avg is 30; for B, avg is 25; and for C, avg is 40;
hence, 40 should be displayed.

I tried the following 2 queries; both give me the right answer.

Query 1

select max(round(b.avg_quotient,2)) as answer
         from
          (SELECT AVG(sql_quotient) as avg_quotient FROM Students GROUP BY group_id) as b;

Runtime = 0.002378 sec

Query 2

select max(round(b.avg_quotient,2)) as answer
     from
      (SELECT AVG(sql_quotient) as avg_quotient FROM Students GROUP BY substr(group_id,1,1) )as b;

Runtime = 0.000459 sec

The difference - the first query groups the data by group_id; the second by `substr(group_id,1,1).

As the second query applies an additional function, I would expect it to take longer. However, as you can see above, runtime of query no.2 is remarkably less than query no. 1.

My question: why does query 2 have a lower runtime than query 1, even though query 2 has one extra function (substr()).

Notes:

The schema is already defined. I don't know why the datatype of Id is text instead of char(1), and for the purposes of my question it's irrelevant.
I'm looking for the reason for the difference of runtime between these 2 queries, not the another query for same problem.
The Students table is created anew for each run, so this isn't a case of the data having to be read from disk on the first run, and being in memory on the second. To prove this, I ran the queries four more times, running Query 2, then Query 1, then 2 again, then 1 again. The run times were:

Q2, 1st: 0.000493
Q1, 1st: 0.002779
Q2, 2nd: 0.000499
Q1, 2nd: 0.002787


texts: Unable to execute the Teradata stored procedure due to syntax errors

texts:
ERROR: CALL Failed. 3707:  SP_PARTICIPANTS_BY_AGE:Syntax error, expected something like a name or a Unicode delimited identifier between '(' and ')'.

Below is the query used

REPLACE  PROCEDURE  SANDBOX.SP_PARTICIPANTS_BY_AGE ()

DYNAMIC RESULT SETS 1
SQL SECURITY INVOKER

BEGIN

    DECLARE c  CURSOR WITH RETURN ONLY FOR
        WITH
            AgeData as (
                SELECT
                usr.USER_ID
                ,usr.BIRTH_DATE
                ,floor(months_between(CURRENT_DATE , usr.BIRTH_DATE )/12) AS AgeCalc
                FROM DV1HDSV.SPIRE_USERS usr
            )

        SELECT
            MA.AGE_CATEGORY
            ,COUNT (DISTINCT fit.USER_ID) AS PARTICIPANT_COUNT
            ,CASE AGE_CATEGORY
            WHEN 'Under 30' THEN 1
            ELSE ROW_NUMBER() OVER (ORDER BY AGE_CATEGORY)
            END AS DATAORDER
        FROM
            (
                SELECT
                USER_ID
                ,AgeCalc
                ,CASE
                WHEN AgeCalc <=30 THEN 'Under 30'
                WHEN AgeCalc BETWEEN 31 AND 40 THEN '31-40'
                WHEN AgeCalc BETWEEN 41 AND 50 THEN '41-50'
                WHEN AgeCalc BETWEEN 51 AND 60 THEN '51-60'
                WHEN AgeCalc > 60 THEN 'Above 60'
                END AS AGE_CATEGORY
                FROM AgeData
            ) MA
            LEFT JOIN DV1HDSV.SPIRE_FITNESS_RECORDS fit
            ON fit.USER_ID = MA.USER_ID
        WHERE AGE_CATEGORY IS NOT NULL
        GROUP BY MA.AGE_CATEGORY
        ORDER BY DATAORDER;

    OPEN c;

END;


texts: PostgreSQL partitioning vs. using multiple DBs

texts:
I'm building a DB that will have several tables partitioned into 64 partitions. Although I'm using PG 10, I'm using the inheritance method as I need greater control over each table than what is currently possible with declarative partitioning.

I have a hash function in C that determines which partition to target for all ops. It's based on the first 3 columns.

The routing uses dynamic SQL since I figured that is faster than a 64-entry CASE statement.

Then it dawned on me that perhaps there is another way to do this. What if I had 64 instances of the DB running on n servers, and the web services tier directed the query to the applicable node?

This way I'd need no partitioning at all inside any DB, hence none of the complexity, overhead, or dynamic SQL.

However, the single DB would become 64, which would require more total resources and would introduce other complexities. Nonetheless, the 64 could operate on several physical servers, which provides scaling benefits.

Replication gets more complicated.

Could anyone give me any advice on this, please?


texts:
I create a user to make a dump twice a week  but when I try to execute mysqldump I have this error

mysqldump: Couldn't execute 'SHOW FIELDS FROM `contabilidad_amortizaciones_completas_por_id_vista`': execute command denied to user 'dump'@'%' for routine 'bbbdd57_antes_del_cambio.calcula_dotacion_acumulada_fc' (1370)

The grants to this user are these

GRANT SELECT, SHOW DATABASES, LOCK TABLES, EVENT ON *.* TO 'dump'@'%'

The funcion call 'bbbdd57_antes_del_cambio.calcula_dotacion_acumulada_fc' have this code:

CREATE DEFINER = 'root'@'%'
FUNCTION
bbbdd57_antes_del_cambio.calcula_dotacion_acumulada_fc(_id_amortizacion int)
RETURNS decimal(10,2)
  BEGIN
    SET @resultado = 0;
    ( SELECT SUM(R.acumulado) FROM (
             SELECT MAX(`cadm`.`acumulado`) AS `acumulado`,
              cadm.id_amortizacion
              FROM `contabilidad_amortizaciones_detalles_meses` `cadm`
              WHERE ((`cadm`.`id_amortizacion` = _id_amortizacion)
              AND (`cadm`.`contabilizado` = 1))
              GROUP BY `cadm`.`anio_detalle`, `cadm`.`contabilizado`
            ) AS R
         GROUP BY R.id_amortizacion INTO @resultado
    );
 RETURN @resultado;
 END

I have other instances with other users-dump with this permissions and I dont have any problem.

Thanks


texts: Merging multiple databases as separate schemas in PostgreSQL

texts:
I have to combine data from 4 different databases. Each having its own design.

What i want to do is to Create a new database with my desired schema. and one schema per (to be merged) database

Then export tables from all other databases and import this data into a unique corresponding schemas schema inside my final database.

How do i export table data from Postgresql Databases.

After having one database with multiple schemas i will use cross schema queries to read and write data to target schema.

Thanks in anticipation.


texts: SQL Server 2014: Using gMSA for xp_cmdshell Proxy Account?

texts:
We are running SQL Server 2014. We want to use a gMSA as the proxy account for xp_cmdshell.

I tried running the following:

EXEC sys.sp_xp_cmdshell_proxy_account 'gmsaNameGoesHere$', ''

However, it expects a password.

I tried running the following:

CREATE CREDENTIAL ##xp_cmdshell_proxy_account## WITH IDENTITY = 'gmsaNameGoesHere$'

It runs! However, when the user tries to run xp_cmdshell via the proxy account, they're told:

  Msg 15153, Level 16, State 1, Procedure xp_cmdshell, Line 3 The
  xp_cmdshell proxy account information cannot be retrieved or is
  invalid. Verify that the '##xp_cmdshell_proxy_account##' credential
  exists and contains valid information.

Is it possible to use a gMSA as the proxy account for xp_cmdshell in SQL Server 2014? If so, how do I set it up as the proxy account?

Thanks!


texts: Linked Server Provider Options - Do I need to restart the SQL service (or server) after modifying any of these options?

texts:
Under Server Options -> Linked Server there are the Providers that each of my linked servers use.

If I right click on a specific Provider and modify the Provider Options, such as "Dynamic parameter" or "Nested queries", do I need to restart my SQL service or server for the changes to take effect?


texts: How to move 200 materialized views without having to re-create in the new home

texts:
I am moving a database with 200 materialized views into a new server.  All of them are 'fast refresh' materialized views , means they have corresponding materialized view logs, in this case sitting on another database.

I have spent countless hours tuning my 200 materialized views and would hate to re-create them in the new server (database).

Is there a way to move them without having to re-create them ?


texts:
What does an SQL:StmtStarting event originated from application mean? Does it mean a raw SQL statement executed directly from the application?

For example, in the case of an SP:StmtStarting, it means that the current statement is inside an stored procedure, and thus not directly executed by the application, but by the stored procedure.


texts: How to subtract records from a table based on their existence in another

texts:
I have an Access 2010 table Assignment

key Empl    Number
key Year    Number
key Week    Number
key Proj    Number

It assigns employees to zero or more projects for given work weeks,
thus, all four columns make up the composite key.
I have another table, Import, having identical structure, containing data to be used to update Assignment according to different options.

Option 1
Keep all existing assignments, add completely new imports, and ignore any imports that already exist.

I can manage that using a simple append query:

INSERT INTO Assignment
SELECT *
FROM Import

So far, so good.

Option 2
As above, except where there's an employee-year-week match between assignment and import, replace the existing assignment with the import.

One approach to achieve that is to first delete all records with an employee-year-week match, then append the imports (as above).
The result should be to replace exact matches as-is, replace partial (employee-year-week) matches with new projects, and add completely new assignments.

I can isolate the matches via this query, called Existing:

SELECT a.*
FROM Import AS i INNER JOIN Assignment AS a
ON a.Empl = i.Empl AND a.Year = i.Year AND a.Week = i.Week

How can I delete records from Assignment that are also in Existing?

I've tried variations upon this

DELETE
FROM Assignment
WHERE IN (Existing)

all resulting in error messages.

By the way, it seems that, if there were such an animal, I could use a MINUS (or DIFFERENCE) query:

Assignment
MINUS
Existing

A minor variation on the approach would be to first delete the records with both an employee-year-week match and a project mismatch, then append the imports, as in Option 1. (Fewer deletions and additions, but same result.)

I can isolate those matches via this query, called Existing_exp:

SELECT a.*
FROM Import AS i INNER JOIN Assignment AS a
ON a.Empl = i.Empl AND a.Year = i.Year AND a.Week = i.Week AND a.Proj <> i.Proj

but I'm still left with the task of how to delete records from Assignment that are in Existing_exp!


texts: Search for current month data in postgresql 9.2

texts:
I am trying to get the ticket data in a 9.2 database only from the current month.

The field called data_cadastro is DATETIME.

 id_ticket |    data_cadastro
-----------+---------------------
      2521 | 2017-10-31 08:11:48

how should I do ?