texts: in MySQL, ORDER BY index only used when I include a where on a different field

texts:
Good day,

I have the following query to show products with details.

SELECT products.id, userid, slug, city, products.updated_at,
       (SELECT group_concat(categories.name)
             from categories join category_user
                on categories.id = category_user.category_id
             WHERE category_user.user_id = userid) as list
FROM products join
    (SELECT users.id as userid , files.slug as slug, locations.city
   FROM users left join files on users.file_id = files.id
   left join locations on users.location_id = locations.id
   ) as users_and_products
on products.creator_id = users_and_products.userid
ORDER BY products.updated_at DESC
LIMIT 50

So we show the 50 most recent products with some details, such as a comma separated list of tags.
There is an index on the field products.updated_at. This query takes 754ms on a database of 16000 products.

If I tell the query to USE INDEX, it is 32 ms. I was wondering why the index is not detected automatically?

Additionally, which i think is the strangest part, is that if I add WHERE products.id > 0, the index IS detected and the query is fast. How does adding this where clause help the DB know which index to use?

Thanks and good day,

Dries


texts: How can I optimize this query and support multiple SKUs?

texts:
My current query only can select one SKU at a time. I can leave salesite_id constant. If there is a way to also have varying salesite_ids that would be good too, but not necessary. Also any suggestions on indexes would be much appreciated also.

SELECT
    available - (
        SELECT COALESCE(sum(quantity), 0)
        FROM product_locks
        WHERE sku = 'sku1'
    ) - (
        SELECT COALESCE(sum(quantity), 0)
        FROM
            orderlineitems
            INNER JOIN responses_authnets ON responses_authnets.id = orderlineitems.response_id
        WHERE
            sku = 'sku1' AND responses_authnets.salesite_id = 'site_id_1'
    ) AS free,
    available AS total,
    sku,
    on_hold
FROM product_inventories
WHERE sku = 'sku1' AND salesite_id = 'site_id_1';


texts: why does mysqldump restore to a smaller size?

texts:
I am using MySQL and mysqldump v8.0.19 on Windows 10. I have an InnoDB database, old. I use mysqldump (and the --no-create-db option) to dump its tables. I then create a new database, new, and import the dumped tables with
mysql -u myName -p new < old.sql

This procedure seems to work in the sense that the tables in old and new seem to have exactly the same data and exactly the same keys. CHECK TABLE reveals no problems with the tables in new, and commands like SELECT * FROM old.table1 UNION SELECT * FROM new.table1 reveal no discrepancies between the old and new tables.
But for all of that, the new tables are smaller. Specifically, this command shows that they're smaller in both data_length and index_length:
SELECT concat( table_schema, '.', table_name ) table_name,
  concat( round( data_length / ( 1024 *1024 ) , 2 ) , 'M' ) data_length,
  concat( round( index_length / ( 1024 *1024 ) , 2 ) , 'M' ) index_length,
  concat( round( round( data_length + index_length ) / ( 1024 *1024 ) , 2 ) , 'M' ) total_size
  FROM information_schema.TABLES
  ORDER BY ( data_length + index_length ) DESC;

Why are the restored tables smaller (and should I be concerned)?
A [https://stackoverflow.com/questions/24707770/mysql-database-dump-restores-to-smaller-size](question like this) was asked six years ago on Stack Overflow, but it never got any replies.


texts: What do I make of unlinked tables in the same database?

texts:
Just a basic question here but it's been bothering me for a while. Do all tables have to be related to another? I'm trying to design a database so far I have 15 tables, 13 of them have relationships with each other creating a regular snowflake pattern, but I have two other tables that only relate to each other, meaning they don't have any relationship to the any of the other 13 tables. Does this mean I should review  my design or is this considered OK?

Thank you for your help in advance.


texts: Field not being sorted in ascending order in Visual Studio 2010

texts:
I have the following SQL:

;WITH times AS (
    SELECT '0830' AS t
    UNION
    SELECT '0930' AS t
    UNION
    SELECT '1030' AS t
    UNION
    SELECT '1130' AS t
    UNION
    SELECT '1230' AS t
)
SELECT t as time
FROM times
;

When I execute this in SSMS (SQL Server Management Studio), it displayed correctly as

0830
0930
1030
1130
1230

However, when I put this into a drop down list inside Visual Studio 2010, the list becomes incorrectly sorted:

1030
1130
1230
830
930

The 0 is disappearing from the 0830 and 0930 and both 0830 to 0930 is being shifted from the 1st two items in the list to the last two items.

How can I make the drop-down show the list in the correct order?


texts: Force spool to avoid creating temp tables?

texts:
I have the following query which join pivoted data of several very big tables and it runs very slow. I tried to created the temp table for each pivoting and it runs much faster. Is it a way to force index/table spooling without create temp tables?

select ...
from T1 ....
left join (select ... from (select ... from T2....) pivot (sum(C1) on C2 in (...))) p1
    on ...
left join (select ... from (select ... from T3....) pivot (sum(...) on ... in (...))) p2
    on ...
left join (select ... from (select ... from T4....) pivot (sum(...) on ... in (...))) p3
    on ...


texts: SQL Server CLR 'Failed to create AppDomain' debugging

texts:
I am having the 'Failed to create AppDomain' issue in a production instance. This is typically attributed to memory pressure(not the case here) or pending .Net updates. The OS has been restarted and the issue persists.
Version: Microsoft SQL Server 2012 - 11.0.5058.0 (Intel X86)
    May 14 2014 16:44:52
    Copyright (c) Microsoft Corporation
    Standard Edition on Windows NT 6.3 <X64> (Build 9600: ) (WOW64) (Hypervisor)

Moving beyond the simple solutions, it seems as if the only other troubleshooting step is look at the created dump file. The .Net subsystem in the OS seems otherwise healthy. I can create programs that reference System.Data and run with no issues.
What other tools exist to troubleshoot issues in the CLR feature of SQL Server?
The full error is-

DESCRIPTION:    Failed to create AppDomain "master.sys[runtime].52".
Could not load file or assembly 'System.Data, Version=4.0.0.0,
Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its
dependencies. Not enough storage is available to process this command.
(Exception from HRESULT: 0x80070008)


texts: Is there a way to find my.cnf in shared hosting?

texts: I'm curious is there a way to find my.cnf in shared hosting or is this file can be configured just by DBAs and no one can set his/her own settings?


texts: What are "sys.extended_index..." indexes?

texts:
A CHECKDB run reports corruption in sys.extended_index_1066482673_384000. What kind of index are represented by the name pattern sys.extended_index_...? The message is:

  The spatial index, XML index or indexed view 'sys.extended_index_1066482673_384000' (object ID ...) does not contain all rows that the view definition produces.  This does not necessarily represent an integrity issue with the data in this database. For more information about troubleshooting DBCC errors on spatial indexes, XML indexes, and indexed views, see SQL Server Books Online.


texts: Fetching Data from joining table for any one from condition satisfied

texts:
I have two table called Activity And Claim. Activity Table contains Activity id and all other Activity Details. Claim Table contains all claim related information for the Activity. One activity have multiple claims and activity id is the foreign key for claim table.

In claim table i am maintaining different status(Approved, Deleted, rejected, Submitted) for each claim.

Currently i am facing one issue for fetching data from activity table if any one of the claim present in claim table and any one of the claim status not in Approved, rejected, submitted.

For an example if an Activity have two different claim and one claim is approved status and another claim in deleted status. So in my sql query should returns empty(because one claim already approved for activity). In another example if the activity have only one claim and status is deleted. This cause my query should fetch the Activity Details(because the activity have only one claim and it's on deleted status).

The query which i am currently using is below

SELECT *
    FROM myactivities_4
    LEFT JOIN claims2 ON claims2.activityid = myactivities_4.activityid
    and claim_status NOT IN ('Submitted', 'Approved','Rejected')

Could any one help me to resolve this issue


texts: Can a surrogate key and a primary key be in the same table?

texts:
For example we have:

Surrogate key: ABC123
Primary key: 1
Name: James

Is this legit in a Data Warehouse table?


texts:
I am looking for an opinion, which I know is usually frowned upon, so my apologies in advance.

I'm currently in an environment where every instance is SQL Server enterprise edition.
The maintenance tasks are being performed by a custom plan. This plan by default does an offline rebuild during the night.

There are no nightly maintenance windows, and as the environment is multinational the "nightly" part of the maintenance doesn't always mean the lowest activity point (however it does in 95% of the cases). And there are a lot of nightly jobs that are currently running slow due partly to aggressive indexing maintenance.
(A reporting query that got stuck waiting for the index rebuild of a 300GB table with customer data led me to this route)

Can I assume that the following will have mostly positive impact:

Add a minimum fragmentation value of 10% for reorganize (currently done every night regardless of %)
Changing minimum rebuild requirements to 30% (currently at 5%)
Changing default behavior to ONLINE
Changing minimum page count to 1000 (currently at 50)

Mainly what I'm asking here is, am I correct in assuming the current maintenance schedule is too aggressive. And that the above 4 changes will have a net positive effect.

I'm mostly worried about the reorganize everything every night for everything... That seems excessive.


texts: Join 3 tables and return only distinct values for max datetime

texts:
I've been trying to do this the whole 2 weeks but seems like to no avail. 3 tables involved here. but am expecting the result would be distinct case no. for the same date & user. otherwise, it must return all.

SELECT DISTINCT A.CASENO,A.DATE,A.TIME,A.TRANNO,B.PCODE,C.PDESC,A.USER
FROM tableA A
RIGHT JOIN tableB B ON A.CASENO=B.CASENO
RIGHT JOIN tableC C ON C.PCODE=B.PCODE
WHERE A.DATE between 140124 and 140331

Result still have duplicates because not same time and tranno. See result on CASENO.= 08088040

A.CASENO   A.DATE   A.TIME   A.TRANNO   B.PCODE   C.PDESC    A.USER
08088040   140124   182516   321         TYQ      PREPLAN1   ANTHONY
08088040   140124   182131   318         TYQ      PREPLAN1   ANTHONY
36360569   140128   111056   431         CVB      POSTT1     MARIA
36360569   140310   113221   433         CVB      POSTT1     MARIA
37386911   140213   150240   230         M2P      PLANAVG    FELISE
37386911   140213   135220   223         M2P      PLANAVG    HECTOR
39222881   140128    94122   104         TYQ      PREPLAN1   ELLA
40895213   140213   164409   104         CVB      POSTT1     WINNIE
51311866   140124   103203   319         M2P      PLANAVG    BRATT

For same date, same user and same pcode and pdesc, caseno. 08088040 came out twice because of the time and tranno difference. here the max tranno would indicate the latest transaction done on the same case no. the max time also shows that the latest datetime action done for that particular case no. was on 24/01/2014 @ 18:25:16. For the same date and user, I just want the latest transaction details.

I've tried added the below script as the first condition but result was 0. It has also caused long delays before I could get the output.

WHERE A.TRANNO=(SELECT MAX(TRANNO) FROM tableA A where A.CASENO=B.CASENO)

FYI, the rests, eg. case no. 37386911 is considered unique because of different user, so I will want that to be returned. Meanwhile, case no.36360569 is also unique record because different date. although same user.

So, how should I achieve this please? Much of the examples I found was only showing for 1 table and 1 criteria to get distinct values.

The script should be those used for SQL DB2. I actually no background at all in SQL. Most of the query I've done earlier was based on trial and error but it was successful because of no complicated conditions like this.

Hopefully, somebody expert here can share the knowledge.


texts: How is ‘tempdb’ getting used when Always On Failover happens?

texts:
How is tempdb utilized as Failover occurs?

Let's say there are two servers, server 1 is primary and server 2 is secondary. Suddenly, Failover happens, so, how does tempdb behave during this situation?


texts: Postgres not smart enough to combine fields in WHERE and ORDER BY for index scan?

texts:
I have the following table in Postgres 9.6:

                                       Table "public.TagNotifications"
    Column    |            Type             |                            Modifiers
--------------+-----------------------------+-----------------------------------------------------------------
 createdAt    | timestamp with time zone    | not null default now()
 updatedAt    | timestamp with time zone    | not null default now()
 id           | integer                     | not null default nextval('"TagNotifications_id_seq"'::regclass)
 tag          | character varying(255)      | not null
 triggerId    | integer                     |
 userId       | integer                     | not null
 comparison   | "TagNotificationComparison" |
 setpoint     | double precision            |
 severity     | "TagNotificationSeverity"   |
 acknowledged | boolean                     | not null default false
 value        | double precision            |
Indexes:
    "TagNotifications_pkey" PRIMARY KEY, btree (id)
    "TagNotificactions_userId_acknowledged_createdAt_tag_id" btree ("userId", acknowledged, "createdAt" DESC, tag, id)
    "TagNotificactions_userId_acknowledged_tag_createdAt_id" btree ("userId", acknowledged, tag, "createdAt" DESC, id)
Foreign-key constraints:
    "TagNotifications_tag_fkey" FOREIGN KEY (tag) REFERENCES "Metadata"(tag) ON UPDATE CASCADE ON DELETE CASCADE
    "TagNotifications_triggerId_fkey" FOREIGN KEY ("triggerId") REFERENCES "TagNotificationTriggers"(id) ON UPDATE CASCADE ON DELETE SET NULL
    "TagNotifications_userId_fkey" FOREIGN KEY ("userId") REFERENCES "Users"(id) ON UPDATE CASCADE ON DELETE CASCADE

I'm trying to ensure good performance for the following query (and similar queries with cursor-based pagination):

EXPLAIN
SELECT * FROM "TagNotifications"
WHERE ("userId" = 2 AND "acknowledged" = false)
ORDER BY "tag" ASC, "createdAt" DESC, "id" ASC
LIMIT 6;
                                     QUERY PLAN
------------------------------------------------------------------------------------
 Limit  (cost=840.10..840.12 rows=6 width=75)
   ->  Sort  (cost=840.10..856.04 rows=6376 width=75)
         Sort Key: tag, "createdAt" DESC, id
         ->  Seq Scan on "TagNotifications"  (cost=0.00..725.81 rows=6376 width=75)
               Filter: ((NOT acknowledged) AND ("userId" = 2))
(5 rows)

Why isn't Postgres smart enough to use the index in this case?  It uses the index if I include "userId" and acknowledged in ORDER BY, but this shouldn't be necessary because of the WHERE condition:

EXPLAIN
SELECT * FROM "TagNotifications"
WHERE ("userId" = 2 AND "acknowledged" = false)
ORDER BY "userId" ASC, "acknowledged" ASC, "tag" ASC, "createdAt" DESC, "id" ASC
LIMIT 6;
                                                                   QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------
 Limit  (cost=0.41..2.27 rows=6 width=75)
   ->  Index Scan using "TagNotificactions_userId_acknowledged_tag_createdAt_id" on "TagNotifications"  (cost=0.41..1974.27 rows=6376 width=75)
         Index Cond: (("userId" = 2) AND (acknowledged = false))
         Filter: (NOT acknowledged)
(4 rows)


texts: How do I insert records into database with foreign keys into SQL Server?

texts:
I'm writing a database in SQL Server, I have this schema

Basing on this, I've prepared UML diagram

I have written SQL code to create tables and alter them with foreign keys

CREATE TABLE Employee
(
    Fname VARCHAR(20),
    Minit CHAR(1),
    Lname VARCHAR(30),
    Ssn VARCHAR(9) CONSTRAINT pk_Employee PRIMARY KEY, -- pk key
    Bdate DATE,
    Address VARCHAR(30),
    Sex CHAR(1),
    Salary INT,
    Super_ssn VARCHAR(9),
    Dno INT,
    CONSTRAINT chk_Employee_Ssn CHECK (LEN(Ssn)=9)
);

CREATE TABLE Department
(
    Dname VARCHAR(30),
    Dnumber INT CONSTRAINT pk_Department PRIMARY KEY, -- pk key
    Mgr_ssn VARCHAR(9),
    Mgr_start_date DATE,
    CONSTRAINT chk_Department_MgrSsn CHECK (LEN(Mgr_ssn)=9)
);

CREATE TABLE Dept_locations
(
    Dnumber INT,
    Dlocation VARCHAR(30),
    CONSTRAINT pk_Dept_locations PRIMARY KEY (Dnumber,Dlocation) -- pk key
);

CREATE TABLE Project
(
    Pname VARCHAR(20),
    Pnumber INT CONSTRAINT pk_Project PRIMARY KEY, -- pk key
    Plocation VARCHAR(30),
    Dnum INT
);

CREATE TABLE Works_on
(
    Essn VARCHAR(9),
    Pno INT,
    Hours DECIMAL(7,2),
    CONSTRAINT pk_WorksOn PRIMARY KEY (Essn,Pno),   -- pk key
    CONSTRAINT chk_WorksOn_Essn CHECK (LEN(Essn)=9)
);

CREATE TABLE Dependent
(
    Essn VARCHAR(9) ,
    Dependent_name VARCHAR(20),
    Sex CHAR(1),
    Bdate DATE,
    Relationship VARCHAR(15),
    CONSTRAINT pk_Dependent PRIMARY KEY (Essn,Dependent_name), -- pk key
    CONSTRAINT chk_Dependent_Essn CHECK (LEN(Essn)=9)
);

ALTER TABLE Dependent ADD
    CONSTRAINT fk_Dependent_Essn FOREIGN KEY (Essn) REFERENCES Employee(Ssn)

ALTER TABLE Department ADD
    CONSTRAINT fk_Department_MgrSsn FOREIGN KEY (Mgr_ssn) REFERENCES Employee(Ssn)

ALTER TABLE Employee ADD
    CONSTRAINT fk_Employee_SuperSsn FOREIGN KEY (Super_ssn) REFERENCES Employee(Ssn),
    CONSTRAINT fk_Employee_Dno FOREIGN KEY (Dno) REFERENCES Department(Dnumber)

ALTER TABLE Dept_locations ADD
    CONSTRAINT fk_DeptLocations_MgrSsn FOREIGN KEY (Dnumber) REFERENCES Department(Dnumber)

ALTER TABLE Project ADD
    CONSTRAINT fk_Project  FOREIGN KEY (Dnum) REFERENCES Department(Dnumber)

ALTER TABLE Works_on ADD
    CONSTRAINT fk_WorksOn_Essn  FOREIGN KEY (Essn) REFERENCES Employee(ssn),
    CONSTRAINT fk_WorksOn_Pno  FOREIGN KEY (Pno) REFERENCES Project(Pnumber)

Now when I'm trying to insert some data into those tables,

example:

INSERT INTO Employee(Fname, Minit, Lname, SSn, Bdate, Address, Sex, Salary, Super_ssn, Dno)
VALUES ('John', 'B', 'Smith', '123456789', '1965-01-09', 'Lazy Town', 'M', 30000, '333445555', 5);

I realize that most of those tables requires foreign keys to any other tables, which makes the insertion very weird. To insert something, I must already have other data in the database. It's like vicious circle.

I have tried to insert some data for any of those tables, using those commends (one after another, not all at once). Every time it failed, because foreign keys can't find records in other tables :<

INSERT INTO Employee(Fname,Minit,Lname,SSn,Bdate,Address,Sex,Salary,Super_ssn,Dno) VALUES
('John','B','Smith','123456789', '1965-01-09', 'Lazy Town', 'M', 30000, '333445555',5);

INSERT INTO Dept_locations(Dnumber,Dlocation) VALUES
    (1, 'Houston');

INSERT INTO Department(Dname,Dnumber,Mgr_ssn,Mgr_start_date) VALUES
    ('Research', 5, '333445555', '1988-05-22');

INSERT INTO Dependent(Essn,Dependent_name,Sex,Bdate,Relationship) VALUES
    ('333445555', 'Alice', 'F', '1986-04-05', 'Daughter');

INSERT INTO Works_on(Essn,Pno,Hours) VALUES
    ('333445555', 3, 10);

INSERT INTO PROJECT(Pname,Pnumber,Plocation,Dnum) VALUES
    ('ProductX', 1, 'Bellaire', 5);

My question: is there any method to insert records in effective and safe way, without errors caused by non existing records?


texts: DB snapshot goes into SUSPECT mode (SQL 2014 on WIN2K8R2)

texts:
I was hoping someone here might have encountered this sort of issue and if so what did you do to fix it?

Here's some details:

DB server: SQL 2014 Enterprise (12.0.4100.1)
OS: Windows 2008R2 Enterprise

Data drive: 800GB

Database to snapshot: 430GB

I can create multiple snapshots of this database and they function perfectly fine until the actual size on disk reaches 38.1GB. At this point there is still well over 150GB of free space on the drive. After that the SnapShot file encounters an IO error and the snapshot enters SUSPECT mode. Here's the error message in the SQL ERRORLOG:

The operating system returned:

  error 665(The requested operation could not be completed due to a file
  system limitation) to SQL Server during a write at offset
  0x00002dc1520000 in file 'E:\SQL Data 1\devDB_seq1501_rba63692865.ss'.
  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.

  E:\SQL Data 1\devDB_seq1501_rba63692865.ss: Operating system error
  665(The requested operation could not be completed due to a file
  system limitation) encountered.

  Database snapshot 'devDB_seq1501_rba63692865' has failed an IO
  operation and is marked suspect. It must be dropped and recreated.

This article: https://support.microsoft.com/en-us/kb/2002606 mentions a similar sort of issue (the 665 error) on a heavily fragmented file system, but I'm not really sure how to approach this.

Any insight into what might be happening here would be greatly appreciated.


texts: We have a process which will regenerate a table from scratch, drop the original table, and move the new table into the original's place. Now this should happen very quickly, but sometimes a website request hits the table after it's been dropped but before the new one is renamed. Apart from coding the website to be more robust when there's a database error, is there an easier way to do this?


texts: Best way to design a database for a bakery store?

texts:
I am building an application that will be used as a Sales Management system for a bakery store. I have built the following tables:

Products has a single row per product. This is a Master Data definition only. It has somethings like 'Pizza`.

Ingredients has rows like Flour and Tomatoes . These are raw input materials required to prepare different products. The bakery store needs to keep track of how much the raw input materials cost.

Product_Ingredients describes what raw materials go in a final product. It could have something like Pizza | Tomatoes and Pizza | Green Peppers, Cup Cake|Strawberry Mix etc.

My question is this:
As a final product, the bakery offers Strawberry Mix for sale.
But in the database it is considered as an ingredient.
I can also add it as a separate Product but here is what makes this case complicated:

In case the bakery runs out of Strawberry mix, they want to grab one from the shelf and convert it from a final product into an ingredient that will be used to make other final products.

How can I go about turning this logic into database objects that make sense?

Here is what I tried:
I created an Inventory table that has a row for each raw material.

When the bakery buys raw material, these are stored in the inventory table normally.
The strawbery mix is inserted into the Products table to get the same treatment as the other final products (sale / discount / tax, etc).

When the bakery staff want to use the mix from the shelf, they will have to "convert" it from final to raw material. Meaning, the application will mark the product unfit for sale, and will also insert a row in the inventory table.

My solution has problems that can have ways around, but I wanted to know if there are better ways (as I am sure) of solving this problem.


texts: Better approach for "LIKE OR LIKE, OR LIKE, OR LIKE, OR LIKE"

texts:
In this question he's having the same problem as I am. I need something like:

select * from blablabla
where product
like '%rock%' or
like '%paper%' or
like '%scisor%' or
like '%car%' or
like '%pasta%'

This is ugly and it's not using indexes.. In this case, this is really the only way to do this ( to select multiple words inside a string ), or should I use FULLTEXT?

As I understand, with fulltext, I can select multiple words inside a string.

This question talks about Full Text as well


texts: Microsoft SQL 2016 database for Malwarebytes Endpoint Protection

texts:
I don't know if this is the correct forum, but hopefully someone can help.

I am a newly promoted entry level Systems Administrator and the first project I was tasked with was to spin up a new server and install Malwarebytes Endpoint Protection utilizing an external database. I got everything spunned up, however, Malwarebytes won't connect to the database. When it prompts for me to choose "Use Embedded Database," or "Use External Database" I choose the "use external." I then type in the database address, which is the server and instance, then the username and password. This is where I get hung up. I get an error message stating "Login failed for user ..." "Installation was unable to access the database."

Now in Microsoft SQL Server Management Studio, under Security --> Logins I've created a user and I've set its Server Roles to dbcreator and sysadmin. However, I've tried the credential I've just created and other credentials that are on our AD domain admin users, and no matter which credential I've tried, it still won't let me login to install. Am I missing a step?


texts:
I have an ms-access database at work that has a table that's a transaction log.

The table contains the following fields:

Date - The date the transaction took place.

Table_Name  - Name of the table the field was changed in.
Table_Field - The field in the table that was changed.
Old_Value - The original value of the field (if any).
New_Value - The new value of the field that was set during this transaction.

Now in a transaction log table like this, are these records normally set by the programming  API of the system that modifies the database, a function of the database itself, or are they tiggered by something like a database trigger?   Or does that depend on the rdbms that is being used?


texts: How to solve a mail issue Failed to initialize sqlcmd library with error number -2147467259?

texts:
I got an error while trying to send an email:

Msg 22050, Level 16, State 1, Line 0
Failed to initialize sqlcmd library with error number -2147467259.

Here is my query:

DECLARE @Table TABLE (
server_name varchar(50),
database_name varchar(20),
byuser varchar(50),
size_mb numeric (20,0),
size_gb decimal (9,2),
backup_started datetime,
backup_finished datetime,
total_time varchar(50),
expiration_days INT,
isdamaged bit,
iscompressed bit,
iscopyonly bit,
databasecreationtime datetime,
physicaldevicename varchar(100),
isolderthanaday bit
);
insert into @Table (server_name,database_name,byuser,size_mb,size_gb,backup_started,backup_finished,total_time,expiration_days,isdamaged,iscompressed,iscopyonly,databasecreationtime,physicaldevicename,isolderthanaday)
EXEC master.dbo.sp_ineachdb
@exclude_list = N'tempdb',
@command =
'SELECT server_name, sysdb.name AS DatabaseName
,bkup.user_name AS [User]
,ceiling(bkup.backup_size /1048576) as ''Size Meg''
,cast((bkup.backup_size /1073741824) as decimal (9,2)) as ''Gig''
,bkup.backup_start_date AS [Backup Started]
,bkup.backup_finish_date AS [Backup Finished (Last BackUp Time)]
,CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))/3600 AS varchar) + '' hours, ''
+ CAST(DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date) - (DATEDIFF(mi, bkup.backup_start_date, bkup.backup_finish_date)/60)*60 AS varchar) + '' minutes, ''
+ CAST((CAST(DATEDIFF(s, bkup.backup_start_date, bkup.backup_finish_date) AS int))%60 AS varchar)+ '' seconds'' AS [Total Time]
,DATEDIFF(DAY,CONVERT(CHAR(8),backup_finish_date,112),CONVERT(CHAR(8),expiration_date,112)) AS expiration_days
,bkup.is_damaged AS isDamaged
,bms.is_compressed AS isCompressed
,bkup.is_copy_only AS isCopyOnly
,bkup.database_creation_date AS DatabaseCreationDate
,bmf.physical_device_name AS PhysicalDeviceName
,CASE WHEN (bkup.backup_start_date is NULL OR bkup.backup_start_date < DATEADD(dd,-1,GetDate()) ) THEN 1 ELSE 0 END AS isOlderThan24Hours
FROM master.dbo.sysdatabases sysdb
LEFT OUTER JOIN msdb.dbo.backupset bkup ON bkup.database_name = sysdb.name
INNER JOIN msdb.dbo.backupmediafamily AS bmf ON bkup.media_set_id = bmf.media_set_id
LEFT outer JOIN sys.backup_devices AS bd ON bmf.device_type = bd.type
LEFT outer JOIN msdb.dbo.backupmediaset AS bms ON bkup.media_set_id = bms.media_set_id
Where backup_finish_date > DATEADD(DAY, -1, (getdate()))
AND bkup.type = ''D''
and sysdb.name = DB_NAME()
ORDER BY backup_start_date DESC, backup_finish_date;'
EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Notifications',
    @recipients = 'example@example.com',
    @subject = 'Full backup in last 24 hours',
    @query = N'select * from @Table',
    @attach_query_result_as_file = 1,
    @query_result_header= 1,
    @query_attachment_filename = 'test.csv',
    @importance = 'High',
    @query_result_no_padding = 1,
    @query_result_separator = ',';

I tried setting max byte size for mail to 10MB but that didn't help. The problem is associated with @query paramteter. What would you suggest?

P.S sp_ineachdb is a customized procedure


texts: Mysql 8.0.22 master slave replication: why slave is trying to create user?

texts:
I am following this repo (https://github.com/alexanderfefelov/docker-mysql-replication) to setup one master one slave replication.  I am not able to get it working.  The master seems to be OK, but the slave has the following error:

mysql_slave1.1.ydo3yuvbyibc@64g-480g-node2    | 2020-12-22T23:19:42.284061Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.22'  socket: '/var/run/mysqld/mysqld.sock'  port: 3306  MySQL Community Server - GPL.
mysql_slave1.1.ydo3yuvbyibc@64g-480g-node2    | 2020-12-22T23:19:42.285009Z 5 [System] [MY-010562] [Repl] Slave I/O thread for channel '': connected to master 'replicator@mysql_master:3306',replication started in log 'log-bin.000023' at position 156
mysql_slave1.1.ydo3yuvbyibc@64g-480g-node2    | 2020-12-22T23:19:42.289356Z 6 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Error 'Operation CREATE USER failed for 'replicator'@'%'' on query. Default database: ''. Query: 'CREATE USER 'replicator'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19'', Error_code: MY-001396
mysql_slave1.1.ydo3yuvbyibc@64g-480g-node2    | 2020-12-22T23:19:42.289425Z 6 [Warning] [MY-010584] [Repl] Slave: Operation CREATE USER failed for 'replicator'@'%' Error_code: MY-001396
mysql_slave1.1.ydo3yuvbyibc@64g-480g-node2    | 2020-12-22T23:19:42.289442Z 6 [ERROR] [MY-010586] [Repl] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'log-bin.000016' position 3107328

The master already created user replicator during start up, why slave also tries to create this user?
Thanks!


texts: Somehow the auditing of select statements was enabled in our oracle database. We have several data processing apps that are generating upwards of 15 GB per day in the audit trail. Now we cannot turn it off because it seems that the noaudit command needs to match the original audit command, which of course no one knows. Does anyone know how to undo this? Can we turn off all auditing and then turn on the usual assortment of audit actions (alter, create, drop, etc.)? We have ran all kinds of noaduit permutations: “noaudit select table by USER;” and “noaudit select any table;” and yet it still posts select statements to the audit trail.


texts: Fast Bulk insert - return ID and other items

texts:
I have a C# console app which is inserting single rows into a SensorLog table, and returning the insertedId
I then use this insertedId to insert/update other tables

The DB is now getting around 50,000 single row inserts a min (800-900 a sec) on the SensorLog table, and while the DB can seem to handle this when everything is running ok, there have been recent times where the servers inserting the data get saturated with network activity (they are also doing other things) response times are very slow - even dragging a window across screen is very slow on RDP connection...then things start to fail.

I now want to move to batch inserting every 1 min.
The database is very busy and 24/7, most queries are done against the LIVE data on the SensorLog table
The insert needs to be as quick as possible.
The current record size of the SensorLog table is around 6.5 billion rows

Here is an example of a row in SensorLog table:

Id: 60000000
DeviceId: 1234
SensorTime: 1/1/2020 12:00:00
EventId: 1
Reading: 34

Here is an example of the Sensors table which always needs latest record info (via update)

Id: 1
DeviceId: 1234
SensorOwner: 59
LatestRecordId:  60000000
LatestRecordTime: 1/1/2020 12:00:00

Here is an example of the SensorsEvents table which is inserted into if SensorLog.EventId > 1

Id: 1
DeviceId: 1234
Fk_SensorLog_Id: 60000000
AlarmThreshold:  500
UserComments: blah

In the 1 min period, each sensor might report 1 to 20 times, so i would need to bulk insert these into SensorLog table, but only update the Sensors table with the newest only (LatestRecordId/LatestRecordTime)
There could 22,000 unique sensors reporting in this 1 min period.

So to recap, I need to move from single insert to batch insert, while being able to update/insert into the other tables (with the ID)

Things i've considered

I've looked into bulk inserting at C# level and return the Id's to C#, however I need to somehow marry the inserted database Id with the original dataset in C# to then update the Sensors table and insert into SensorsEvents table. I'm not sure if this is possible, or how to do this....and the performance implications of this....probably a bad approach
I've also thought about inserting into a #TempTable and then having an agent etc which then bulk inserts into the SensorLog table and all the other tables.

I'm assuming option 2 seems to be the best here since everything can be done at database level without back and forth to other servers

To summarise, I have 2 questions

Given the info above, does this solution seem OK, or are there any other approaches I should consider?
Given the above, what is the fastest way to bulk insert, and return the inserted ID back to the original data set (#temptable) so I can then update/insert to other tables

I would welcome any other solutions/suggestions that I have not considered.
Efficiency and performance to be able to scale to much higher numbers is my main concern

Sql server 2017 enterprise.


texts: Why is autovacuum running during a VACUUM FREEZE on the whole database?

texts:
I have a 14.7 TB Postgres(9.1) database that has to endure heavy writes for prolonged periods of time. I know that I need to closely manage my transaction ids to prevent db lockup. Recently I noticed the queries slowing, and saw that there were multiple autovacuums running on our massive read-only tables, with the "(to prevent wraparound)" subscript. I stopped the running software and executed a vacuumdb -F -a command. However, when I run select current_query from pg_stat_activity I see that the autovacuum processes are still running, even during a manual vacuum. I tried killing the autovacuums with select pg_cancel_backend(pid) and they died but then immediately restart again. My questions:

Is autovacuum supposed to continue to run during a manual db vacuum?
How do I effectively get these autovacuum processes to stop?
Why would these autovacuums continue to run on a read-only table? What is there to vacuum?


texts: Why is the padding on months in Oracle 9 characters?

texts:
I ran two simple sql statements

select length(to_char(sysdate, 'fmMonth')) from dual;
select length(to_char(sysdate, 'Month')) from dual;

The length of the first statement was 4 (It's June) and the length of the second statement was 9.  Why does it pad 5 extra characters unless you specifically ask it not to?

The only reason I could come up with was that the month with the most amount of characters is september (nls_date_language set 'English') which has 9 character.

Second question is if you changed your nls_date_language variable would you also change the amount of padding?


texts: How to convert a MySQL database to PostgreSQL?

texts: Is there any command line tool for converting the MySQL database to PostgreSQL? I'd also like to know if there is a way to convert the database using the normal mysqldump command.


texts: Optimize two sql sentences adding indices

texts:
I'm optimizing my SQL Server database using SQL Server Management Studio Activity Monitor.

The two more expensive sql sentences are:

set @codesToPrintCount =
    (select count(CodeId)
        from Code
        where CommissioningFlag = 255
        and AggregationLevelId = @codeLevel);

SET @code = (SELECT TOP 1 Serial
                FROM Code
                WHERE CommissioningFlag = 255
                    and AggregationLevelId = @codeLevel);

This is Code table sql script:

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            NVARCHAR (20)  NOT NULL,
    [AggregationLevelId]  TINYINT      NOT NULL,
    [CommissioningFlag] TINYINT        NOT NULL,
    [ ... ]
    CONSTRAINT [PK_CODE] PRIMARY KEY CLUSTERED ([CodeId] ASC),
    CONSTRAINT [UC_CODE_SERIAL] UNIQUE NONCLUSTERED ([Serial] ASC),
    CONSTRAINT [FK_Code_AggregationLevelConfiguration]
           FOREIGN KEY ([AggregationLevelId])
            REFERENCES [dbo].[AggregationLevelConfiguration] ([AggregationLevelConfigurationId])
)

I don't know how I can speed up these sentences.

There are more rows in Code that table have the same value on AggregationLevelId column than columns with the value 255 on CommissioningFlag. In other words, on Code table there 1.050.000 rows with AggregationLevelId equals to @codeLevel and 32 rows or less with the value 255 in CommissioningFlag column.

Is it a good idea add two indices on this table? One for CommissioningFlag and another one for AggregationLevelId.

At this moment there are 1.100.000 rows on Code table and they get 23ms and 78ms respectably to execute.

By the way, these two sentences are in a stored procedure.


texts: Distinct column in an inner join query sql

texts:
I have two tables, linked through Artists Firstnames and surnames. One table has the artists BIOG (only one result per Artist.), the other table has the Artist's images (a lot of images per artist).
I want to run a DISTINCT query showing the Artist's Image AND Biog.
I am getting plenty of records with the code below, but cannot get the DISTINCT to filter out duplicates. I want the DISTINCT to work only on the Artist firstname and surname, but for it to disregard the images, which are all distinct.

Here is the code:

SELECT ArchiveCatalogues.ARTIST,
   ArchiveCatalogues.IMAGE,
   ArchiveCatalogues.FIRSTNAME,
   ArchiveCatalogues.SUFFIX,
   ArchiveCatalogues.TITLE,
   artist.surname,
   artist.firstnames,
   artist.dates,
   artist.honorific,
   artist.biog
FROM ArchiveCatalogues
INNER JOIN artist
ON (ArchiveCatalogues.ARTIST = artist.surname)
AND (ArchiveCatalogues.FIRSTNAME = artist.firstnames)

Any image would be acceptable at this stage. I was thinking that the main query should be the one that queries the table with the BIOGS, since each artist is distinct on this table (no artist is mentioned more than once.) Then, all I need to do is pull the image in from the other table.


texts:
I have the IBM drivers installed, created a data source that successfully tests from SSRS front end.

Create a report from my machine which also has the drivers installed and the ODBC testing correctly.

I get the following error

ERROR [HY000] [Informix][Informix ODBC Driver][Informix]Unspecified System Error =  -23101.

I run

Select * from sysmaster:informix.sysdbslocale

which returns en_US.819 for the database i want.

Run Setnet32 and set CLIENT_LOCALE and DB_LOCALE to en_US.819

The client/server vars in the ODBC setting are set the same also.

I'm not sure what else to try.


texts: Is long running query always more heavy than short running query

texts:
Perhaps a stupid question but since I don't know how the databaseengine really works under the hood I don't know the answer.

More specifik I've learnt that the sql always tweaks the query so that it is the most optimized for the server. Not only just optimizing speed but also resource management.

So could could it be that my tuning of the query has a greater impact on server resources than the original long running query and can I see that somehow?

If so what are the usual ways to bring down the server trying to tune a query?

Eg. if I have a query that runs for 10m and I optimize to 30s could those 30s have a greater impact on the server performance than the 10min query?


texts: DatetimeFieldOverflow when inserting more than three decimals precision

texts:
TIMESTAMP type should be fine (docs)

  timestamp [ (p) ] [ without time zone ]   8 bytes     both date and time (no time zone)   1 microsecond / 14 digits

Create the table like:

CREATE TABLE IF NOT EXISTS l12(
    timestamp TIMESTAMP(6) NOT NULL,

But what I get is

psycopg2.errors.DatetimeFieldOverflow: date/time field value out of range: "2019-05-23 12:16:50.954276"

When I remove the last three numbers 2019-05-23 12:16:50.954276--> 2019-05-23 12:16:50.954 the insert works.

So is it possible to save a datetime with six decimals precision into one postgresql field?


texts:
I have a rather atypical database situation, so am finding researching it a bit difficult.

The question first: what kind of maintenance strategy do I need to put in place?

The setup:

The whole system is mounted on a vehicle, so at the end of the shift, everything gets turned off (so no scheduling jobs 'during the night').
However, we can ask the user to regularly click on a button somewhere to invoke a maintenance 'job' if necessary.
It is used for recording data (lots of) while the vehicle is moving (currently pumping IObservable data through a queued implementation of IDataReader into an SqlBulkCopy - one set for each table).
Having thought about this quite a lot, I think that we don't need Full Recovery: if anything goes pear-shaped, it's going to take more time to restore a backup than it would to just go and record again.
It is installed on the other side of the world, in a different time zone, and the users aren't DBAs.

My main concern is the log file: will it grow uncontrollably, or become 'fragmented' (or whatever the right term is).


texts:
First of all, My SHOW SLAVE STATUS shows

Seconds_Behind_Master: 0

I can compare my slave with master, n see that replication is perfectly synchronized.

Now here comes the issue,

When I run SHOW PROCESSLIST, it shows,

+-----+-------------+-----------+------+---------+------------+-----------------------------------------------------------------------+------------------+
| Id  | User        | Host      | db   | Command | Time       | State                                                                 | Info             |
+-----+-------------+-----------+------+---------+------------+-----------------------------------------------------------------------+------------------+
| 591 | system user |           | NULL | Connect |      29542 | Waiting for master to send event                                      | NULL             |
| 592 | system user |           | NULL | Connect | 4294966950 | Has read all relay log; waiting for the slave I/O thread to update it | NULL             |

you can see the time < 29542 > and < 4294966950 >.

I read in MySQL documentation that
in SLAVE SQL THREAD,
" The value in the Time column can show how late the slave is compared to the master. "

My replication server is consuming too much memory without any load,
is this the reason behind this ?
How can I rectify it ?


texts: Cannot connect remotely to a named instance

texts:
So, I have two servers (ServerA and ServerB) that I've set up with Windows Server 2008 R2 and SQL Server 2008 R2. My goal is to replicate between these two in a failover fashion (with ServerC as a witness instance, but that's for later).

Both servers are configured in the same way. Firewall rules added, SQL Server installed with the same settings. Network via TCP/IP and named pipes enabled. I can connect with SSMS to ServerA fine, but I get the following when connecting to ServerB:

  A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (.Net SqlClient Data Provider)

Now, the instance name is correct and I can successfully connect to ServerA with the instance name (they're the same on both servers). If I leave out the instance name, I get another error saying it just timed out.

The only thing that works is running SSMS on the local machine via shared memory, but obviously that doesn't solve the problem. I feel like I'm missing something obvious. Help?


texts: What are the various Informix column data types?

texts:
I am writing a query where I need to know if a column is of the "BLOB" type of column. According to this documentation  41 is the number for "BLOB" types of columns, in general. However, when I query the syscolumns table I see that columns, which store BLOB data, actually have values of 297, in my case. That page linked above does not even have number "297" anywhere on its page. I found another page, titled "Data Type Constants" but here again, number "297" is not even present anywhere on the page.
It appears that documentation's list of data types is not complete. Is there a (more) complete list of column types in Informix?


texts: Adding COMPRESSION_DELAY to an existing COLUMNSTORE INDEX

texts:
When I create an COLUMNSTORE index I can specify COMPRESSION_DELAY = x [minutes] in the CREATE-Statement. It delays the movement of the rows in an closed segment from the delta store to the compressed columnstore storage. Reason to do so: your import process is not just a simple INSERT but has some UPDATEs and maybe DELETEs too.

Is there a way to set this value afterwards without recreating the whole index (which would take very long on our big table)?

I tested already REBUILD and REORGANIZE, but they both do not understand the COMPRESSION_DELAY option in the WITH().


texts: Re enable Windows Authetication in SQL Server

texts:
My old employee has disabled Windows Authentication in our server. Now I'm not able to access the SQL Server even though I have Administrator access to the server. I need to reset the sa password.

I tried logging in using single user mode as Remus described but I get the following error:

  Login failed for user 'SERVER\Administrator'.
  Reason: The account is disabled. (Microsoft SQL Server, Error: 18470)


texts:
I m using following sql query for creating db backup

mysqldump -u[username] -p[password] -h[hostname] [database] | gzip > [backup file path]

This generates zipped .sql file in path specified but instead of containing sql dump it contains following comment in it everytime

-- MySQL dump 10.13  Distrib 5.5.23, for Linux (x86_64)
--
-- Host: localhost    Database: zm3_quizzes
-- ------------------------------------------------------
-- Server version   5.5.23-55

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

Why this is happening and how do I get backup of my database?

Thanks in advance.


texts:
MySQL 5.6: I'm experimenting with copying data from one table to another in different ways. The source table is on a remote server and has about 500,000 rows - I use the federated engine to connect. I first tried this:
mysql > create table tgt as select * from src;

This is very fast, takes only a few seconds, but it gives warnings:
...
| Warning | 1299 | Invalid TIMESTAMP value in column 'created_timestamp' at row 265975 |
| Warning | 1299 | Invalid TIMESTAMP value in column 'created_timestamp' at row 265976 |
...
64 rows in set (0.00 sec)

I tried instead to do it with a stored procedure, opening a cursor, fetching rows and inserting them, but it takes forever; I canceled after 10 min.
So, is there a way to locate the rows that cause the problem? I tried select ... limit #first_row,#last_row;, but it doesn't appear to work, and I'm not sure if it is entirely reliable any way.


texts: How to use wal_level in Postgres to set the recovery mode to simple for a database like how Microsoft SQL Server does it?

texts:
In Microsoft SQL Server, ALTER DATABASE CURRENT SET RECOVERY SIMPLE WITH NO_WAIT; will set the recovery mode for a database to simple, so the transaction log file space can be re-used.
In Postgres, wal_level is available for that purpose.

Is wal_level's minimal equivalent to SQL Server's RECOVERY SIMPLE?
How to set wal_level in Postgres command? - so far, Google Search tells me that wal_level is set in a configuration file.
Is it possible to set wal_level to minimal for a database? If yes, how?

Thank you.


texts:
I have several dozen servers and would like to be able to check configurations on them ever quarter or so to make sure nothing has changed. I am having difficulty finding how to read a few configurations. Those settings I'm having problems with are in the SQL Server Agent properties:

Under the Alert System tab:
   Enable Mail profile
   Mail system
   Mail Profile
   Enable Fail-safe operator
   Operator
   Notify Using

Under the History tab:
   Limit size of job history log
   Remove agent history

Hopefully someone can point me in the direction of where I can run a cross server query or better yet powershell query to check all my servers configurations. Thanks!


texts: How to handle many columns / variable schema?

texts:
I apologize in advance in case this question has been asked already.

I'm working on revamping a reporting application used within my company. The requirements are:

Support addition of new fields (done through web app) and allow users to select those fields when building reports. Currently there are 300 of these, and right now their values are stored in a single SQL Server table with 300 columns. Users have to be able to select these new fields in report builder. In other words, the schema is dynamic.
Improve report generation performance.

What's the best way to approach this? My thought process was to split up these 300 (and potentially more) columns into multiple tables (normalization), but I'm not sure that's the right approach given there doesn't seem to be a logical way of grouping data without ending up with 20+ tables.

Another option would be to store values in rows (key, attribute, attribute-value) then do a pivot, but I'm not sure that would perform well. This option would handle the dynamic schema nicely, but the pivot statements would have to be built programmatically before a user can consume data (views).

What would be the ideal approach?

Thanks!


texts:
i was facing heavy load on my server approx two years back and that time i have tried to optimize every single issues like query raid battery my.cnf etc and after that the server load was always under one inspite of heavy load. for last one year i was regularly handling more than 10000 users at any point of time without any issue but suddenly everthing changed last week. whm started showing heavy load on server.i guessed that raid battery backup is not working and requested my host to replace it and they replaced it and after that i was expecting the situation to improve but nothing changed and my host informed that your battery was fine but we have replaced it.
then i thought my temp might be getting exhausted but its completely under control

   Every 0.5s: df -h /tmp                                                                                Sat Mar  8 03:51:29 2014

     Filesystem      Size  Used Avail Use% Mounted on
     /usr/tmpDSK     4.0G  376M  3.4G  10% /tmp

used slow.log to register the slow query and only few query are showing however i have set the slow log time to 5 sec.
then i tried mysqltuner.pl and as i said i had already optimized to best of my knowledge two years back now its not recommending anything

my raid array was at 96% few days back and today it is showing 80%

      Unit  UnitType  Status         %RCmpl  %V/I/M  Stripe  Size(GB)  Cache  AVrfy
      ------------------------------------------------------------------------------
      u0    RAID-5    VERIFYING      -       80%     256K    3725.27   RiW    ON

       VPort Status         Unit Size      Type  Phy Encl-Slot    Model
       ------------------------------------------------------------------------------
       p0    OK             u0   1.82 TB   SATA  0   -            WDC xxxxxxxxxx-02W3
       p1    OK             u0   1.82 TB   SATA  1   -            WDC xxxxxxxxxx-01U1
       p2    OK             u0   1.82 TB   SATA  2   -            WDC xxxxxxxxxx-02W0

      Name  OnlineState  BBUReady  Status    Volt     Temp     Hours  LastCapTest
      ---------------------------------------------------------------------------
       bbu   On           Yes       OK        OK       OK       0      xx-xxx-xxxx

i am getting this from whm which shows heavy load from mysql

  20155 (Trace) (Kill)  mysql   0     181.3   19.9  /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --log-error=/var/lib/mysql/server.xxxxxxxx.com.err --open-files-limit=8376 --pid-file=/var/lib/mysql/server.xxxxxxx.com.pid

any suggestion to resolve this issue.


texts:
Issue with 2016 developer edition install on new windows 10 machine.  Not finding any docs or similar issues posted.

  An error occurred for a dependency of the feature causing the setup process for the feature to fail.

  Component name:                SQL Server 2008 Setup Support Files

  Component error code:          1935

  Error description:             An error occurred during the installation of assembly 'Microsoft.VC80.ATL,version="8.0.50727.1833",publicKeyToken="1fc8b3b9a1e18e3b",processorArchitecture="x86",type="win32"'. Please refer to Help and Support for more information. HRESULT: 0x800736B3.


texts: Can't restore database: A transport-level error has occurred when receiving results from the server

texts:
This is not a good day for me. I have a backup (master.bak) for SQL Server 2005 Express SP4 (v.9.0.500) that I want to restore to my master db instance. I am running SQL Server in single-user mode (sqlservr.exe -m), connect to the instance, then I do a Task... Restore. This is the error I get:

  TITLE: Microsoft SQL Server Management Studio Express

  Restore failed for Server 'GINO\SQLEXPRESS'.
  (Microsoft.SqlServer.Express.Smo)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476

  ------------------------------ ADDITIONAL INFORMATION:

  Failed to retrieve data for this request.
  (Microsoft.SqlServer.Express.SmoEnum)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.Express.ConnectionInfo)

  A transport-level error has occurred when receiving results from the
  server. (provider: Shared Memory Provider, error: 0 - The pipe has
  been ended.) (Microsoft SQL Server, Error: 109)

  For help, click:
  http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=109&LinkId=20476

Please help me restore my backup database so that I can continue along with my business!


texts:
CREATE OR REPLACE TRIGGER LOAN_RP
AFTER INSERT OR UPDATE ON FUND_DISBURSE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
V_FA          FUND_DISBURSE.FUND_AMT%TYPE;
V_RC          NUMBER (10);
V_AC          FUND_DISBURSE.AREA_CODE%TYPE;
V_BC          FUND_DISBURSE.BRANCH_CODE%TYPE;
V_CC          FUND_DISBURSE.COMP_CODE%TYPE;
V_DN          FUND_DISBURSE.DAFA_NO%TYPE;
V_PRN         NUMBER (15,2);
V_SC          NUMBER (15,2);
V_INSN        NUMBER (2) :=0;
V_INSD        FUND_DISBURSE.DISBURSE_DATE%TYPE;
V_RAMT        NUMBER (15,2);
BEGIN
select AREA_CODE, BRANCH_CODE, COMP_CODE,DAFA_NO,DISBURSE_DATE,FUND_AMT
into
V_AC,V_BC,V_CC,V_DN,V_INSD,V_FA
from FUND_DISBURSE
WHERE AREA_CODE = :NEW.AREA_CODE
and BRANCH_CODE = :NEW.BRANCH_CODE
and COMP_CODE = :NEW.COMP_CODE
and DAFA_NO = :NEW.DAFA_NO
AND DISBURSE_DATE =:NEW.DISBURSE_DATE
AND FUND_AMT = :NEW.FUND_AMT;
V_RAMT := V_FA*1.13;
V_INSD := (V_INSD-7) + 14;
for i in 1..50 loop
V_INSD := V_INSD+7;
V_RC := V_FA/1000*25;
V_PRN := V_RC/1.13;
V_SC := V_RC - V_PRN;
V_INSN := V_INSN + 1;
V_RAMT := V_RAMT - V_RC;
DBMS_OUTPUT.PUT_LINE(V_RC);
INSERT INTO F_REPAY_SCHEDULE (AREA_CODE,BRANCH_CODE, COMP_CODE,
DAFA_NO,INSTALL_NO,INSTALL_DATE,INSTALL_AMT, PRN_AMT, SC_AMT)
VALUES (:new.area_code,:new.branch_code,:new.comp_code,:new.dafa_no,V_INSN, V_INSD,
V_RC,
V_PRN,V_SC );
EXIT WHEN V_RAMT < V_RC;
END LOOP;
END;
/

Trigger complied, but can not insert any record.
 table is mutating, trigger/function may not see it

How can I solve it. Please suggest.

Sorry again. Thanks to ***mat and all* for helping me**.
I have understood.
Here my new code after correction.

CREATE OR REPLACE TRIGGER LOAN_RP
AFTER INSERT OR UPDATE ON FUND_DISBURSE
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
V_RC          NUMBER (10);
V_PRN         NUMBER (15,2);
V_SC          NUMBER (15,2);
V_INSN        NUMBER (2) :=0;
V_RAMT        NUMBER (15,2);
v_insd        date;
BEGIN
V_RAMT := :NEW.FUND_AMT*1.13;
V_INSD := (:NEW.DISBURSE_DATE -7 ) + 14;
LOOP
V_INSD := V_INSD+7;
V_RC   := :NEW.FUND_AMT/1000*25;
V_PRN  := V_RC/1.13;
V_SC   := V_RC - V_PRN;
V_INSN := V_INSN + 1;
V_RAMT := V_RAMT - V_RC;
DBMS_OUTPUT.PUT_LINE(V_RC);
INSERT INTO F_REPAY_SCHEDULE (AREA_CODE,BRANCH_CODE, COMP_CODE,
DAFA_NO,INSTALL_NO,INSTALL_DATE,INSTALL_AMT, PRN_AMT, SC_AMT)
VALUES (:new.area_code,:new.branch_code,:new.comp_code,:new.dafa_no,V_INSN, V_INSD,
V_RC, V_PRN,V_SC);
EXIT WHEN V_RAMT < V_RC;
END LOOP;
END;
/


texts: Check the result of a math expression in mysql

texts:
Let's say I have a table, which has four columns (a, b, oper and c) and some primary key column. oper means arithmetic operation (+ - * /) here.
a  b  oper  c
-------------
2  3  +     5
4  2  /     3
6  1  *     9
8  5  -     3

As, we can see in some cases, a <oper> b != c. So, my question is how to filter out such cases?
I've heard of execute, which is used for executing statements, but I don't know how to use it inside where clause.
Also, I'm not generalizing the oper to any arithmetic operation, but it would be nice to know, if any function exists.


texts:
We're moving to a different cloud provider so need to migrate our database with as little downtime as possible.

Here's our current setup:

master
└───slave1
└───slave2
└───slaveBackup

A dump of slaveBackup was imported into our new master database, newMaster. slaveBackup logs changes to its bin logs using log-slave-updates and binlog_do_db to only log the correct database which is identically named throughout.

To reduce downtime we now want to add newMaster as a slave to slaveBackup, thus creating the following chain while we migrate:

master
└───slave1
└───slave2
└───slaveBackup
    └───newMaster
        └───newSlave1
        └───newSlave2

newMaster is replicated as intended to newSlave1 and newSlave2, all are identical.

We recorded the bin log file and position on slaveBackup made immediately after the dump and used this to add newMaster as a slave of slaveBackup:

CHANGE MASTER TO
    MASTER_HOST = 'slaveBackup',
    MASTER_USER = 'slave',
    MASTER_PASSWORD = '',
    MASTER_LOG_FILE = 'mariadb-bin.00000X',
    MASTER_LOG_POS = X;

Starting the slave worked perfectly and in time using SHOW SLAVE STATUS\G we could see it catch up to its master.

Here's where it gets weird. Despite the slave newMaster crunching through the bin logs, no rows are being inserted into the new database. What's weirder during this time Seconds_Behind_Master was always 0.

The logs were definitely being processed as the slave user is different on newMaster ('slave'@'X.X.X.X') vs slaveBackup ('slave'@'Y.Y.Y.Y') and a moderation made after the dump of this user caused newMaster to error and required SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; to recover.

Now the slave is fully caught up with master in terms of bin log file and position yet not data has been inserted into its database.

Why is this?

The bin logs on slaveBackup definitely have the correct USE DATABASENAME; statements before each insert/update, hence why I'm completely stumped.

To clarify the replication chain:

master -> slaveBackup -> newMaster -> newSlave1/2


texts: MySQL workbench won't import my sql file

texts:
I have an SQL file im trying to import via mysqlworkbench but I keep getting the error:

Access denied for user 'wordpressuser79'@'localhost' (using password: NO)

Operation failed with exitcode 1

All other migrations have worked out just fine, problem seems to be that its not using the password but I cannot find anywhere to fill this in. All other connections I made in mysqlworkbench have worked fine and imported SQL files as it should but somehow this one refuses.


texts: For Oracle 10g and above: except in cases where a local index cannot be used and a global index is needed (e.g. unique constraint), is there any reason to favor a global index over a local index?


texts: Optimising expensive join/subquery by filtering with conditionals

texts:
This is a question about short-circuiting expensive JOINs or sub-queries in Postgresql (9.5 or 9.6). I'm also be interested in hearing how people in general solve the check-then-execute problem.

I'm writing a lot of queries that should only conditionally return a result, such as if the (web) user owns the record or if the record was modified. I'm trying to prevent building expensive views inside Postgresql and multiple back-and-forth queries to check for conditions in the application itself, so I attempt to write queries that first select the correct record and show which conditions failed, and only execute the view if the conditions pass.

For instance, this checks if the (application) user owns a record before returning it:

SELECT is_owner, is_newer, json
FROM (
     SELECT id, owner = '053bffbc-c41e-dad4-853b-ea91fc42ea18' "is_owner"
          , modified >= created "is_newer"
     FROM datasets
     WHERE id = '056e4eed-ee63-2add-e981-0c86b8b6a66f'
) cond
LEFT JOIN LATERAL (
     SELECT id
     FROM datasets
     WHERE is_owner and is_newer
) authed
    ON cond.id = authed.id
LEFT JOIN LATERAL (
     SELECT json
     FROM view_dataset
     WHERE id = authed.id
) dataset
    ON true;

Resulting in (is owner):

is_owner | is_newer | json
t          t          {...}

And a negative result (not owner):

is_owner | is_newer | json
f          t          NULL

So the application knows which error to return but we don't have to build or parse the view if the conditions don't pass.

However, EXPLAIN ANALYSE shows Postgresql still executes the view query in the last LEFT LATERAL JOIN even though the middle JOIN doesn't have any results, and I can't get it to short-circuit to prevent the (expensive) view_dataset SELECT from running. If I set json to null the query skips everything but the first SELECT; but if it is set to a value from the last query, it will always execute all SELECTs, so I guess the query planner thinks it has to get a result for that json field in the top SELECT query and doesn't short-circuit the JOINs.

I wonder if I can force Postgresql to drop the expensive view query.

I have also tried a CTE, which does seem to skip the JOIN query:

WITH cond as (
    SELECT id, owner = '053bffbc-c41e-dad4-853b-ea91fc42ea18' "is_owner", modified >= created "is_newer" FROM datasets WHERE id = '056e4eed-ee63-2add-e981-0c86b8b6a66f'
)
SELECT cond.id, cond.is_owner, cond.is_newer, json FROM
    (SELECT id FROM cond WHERE cond.is_owner and cond.is_newer) filtered
    LEFT JOIN LATERAL
    (SELECT id, json from view_dataset) dataset
    USING (id)
    RIGHT JOIN cond
    USING(id);

... but this query and variations are at least 2x slower.

So my question is how to maximise performance by short-circuiting JOINs or subqueries based on conditions; and I'm also interested to hear if somebody has other ideas how to implement some check-first-then-execute pattern such as checking for record ownership.


texts: Rebuilding an entire Microsoft SQL Server Database

texts:
Is there a suggested way to rebuild an entire SQL Server database from another database in SQL Server 2012 SP2?

Let me explain... we have a database that we are trying to enable partial containment on and then add it to our AlwaysOn cluster. We are getting nothing but deadlock errors while trying to set the containment to partial. The database has gone from SQL Server 2008 to 2008R2 to 2012 (no SP, SP1, SP2) and had TDE for a while.

Microsoft support has been looking at it for a couple of days, but it's not looking very promising and I think we're now leaning towards some form of corruption. Our application and everything else seems to be able to still access it just fine, it's just setting this one little flag isn't working. Very strange issue.

The only reference I found online that shows the same exact issue is from a Technet forum post in Portuguese (translated link), but it was never resolved.

At this point, I can't wait any longer and I just want to rebuild this data in a new database. I suppose I could script the entire thing out, but this DB is around 20GB already, so that would be one nasty script.

Does anyone have a suggestion on how to recreate a new database based off of another - without the standard Backup / Restore? Is scripting it out the right way to go?


texts: Can I get the union of the a Postgres fulltext index?

texts:
I have a fulltext index on a table. Is it possible to retrieve the set of terms used in the index (gist or gin)? With weights if possible?

To clarify:

If I have the following table:

create table "test" (id integer, thing tsvector);

I then make a GIST index on it:

create index thing_index on test using gist (thing);

Then some data:

insert into test (id, thing)
values (1, 'one'),(2, 'two'), (3, 'three'), (4, 'one'), (5, 'two');

The index thing_index is going contain the following mapping:

'one' => {1, 4}
'two' => {2, 5}
'three' => {3}

I want to obtain the following response from the index:

'one',
'two',
'three'

Maybe even with rankings:

'one' => 2
'two' => 2
'three' => 1

I know I can do this myself by scanning and building my own index, but I want to get it out of Postgres if possible.


texts: Hook to run code when Postgres DB launches

texts:
I need to run code when a database/catalog in a Postgres cluster comes online, before any client has connected.

Specifically I need to do some cleanup such as dropping/truncating a particular table.

Is there a place for my code to be automatically run with a guarantee it will finish before any incoming client connection is accepted?


texts: Archive backups with a PowerShell script launched by SQL Server Agent

texts:
I would like to run a Powershell script via the SQL Server Agent.
This script works fine if I run it manually through the command prompt, but the job history in SQL Server Management Studio tells me a syntax error on line 8.

Is there a difference in the SQL Server Agent runtime environment?

Here is my script:

$ArchivesMPath = "E:\ArchivesBDD\Mensuelles"
$SauvegardeMPath = "E:\SauvegardeBDD\Mensuelle"
$ArchivesQPath = "E:\ArchivesBDD\Quotidiennes"
$SauvegardeQPath = "E:\SauvegardeBDD\Quotidienne"
$DAYSOFWEEK = "Dimanche", "Lundi", "Mardi", "Mercredi", "Jeudi", "Vendredi", "Samedi"
$MONTHS = "Decembre", "Janvier", "Fevrier", "Mars", "Avril", "Mai", "Juin", "Juillet", "Aout", "Septembre", "Octobre", "Novembre", "Decembre"

$DAILYDIR = "$ArchivesQPath\$($DAYSOFWEEK[(get-date).AddDays(-1).DayOfWeek.value__])"
if ([System.IO.Directory]::Exists($DAILYDIR)){
    Remove-Item $DAILYDIR -Force -Recurse
}
New-Item -Path $DAILYDIR -ItemType directory
Move-Item -Path "$SauvegardeQPath\*" -Destination $DAILYDIR

if ((get-date).AddDays(-1).day -eq 1){
    $MONTHLYDIR = "$ArchivesMPath\$($MONTHS[(get-date).month])"
    if ([System.IO.Directory]::Exists($MONTHLYDIR)){
        Remove-Item $MONTHLYDIR -Force -Recurse
    }
    New-Item -Path $MONTHLYDIR -ItemType directory
    Move-Item -Path "$SauvegardeMPath\*" -Destination $MONTHLYDIR
}

Thank you in advance for your help


texts: get average of number of likes in mysql from two tables

texts:
let's say I have a posts table:

post_id    user_id    text
1             2        abcd
2             1        wert
3             3        yhj

and I have a ranks table:

post_id     liker_user_id      score
1              3                 3
3              4                 1
3              3                 5

I want to get avg(score) for each post so I came up with this simple query:

SELECT posts.post_id, posts.text , avg(ranks.score) as `ranking` FROM posts LEFT JOIN ranks ON ranks.post_id = posts.post_id GROUP BY posts.post_id;

so this is fine but the problem is, it is possible that a post does not have any ranking in the ranks table, so there will be nothing in the ranking field and it gives me something like this:

post_id   text   ranking
1         abcd     3
2         wert
3         yhj      3

note that there is nothing in ranking for post_id = 2.
I want a zero for such results, like this:

post_id   text   ranking
1         abcd     3
2         wert     0
3         yhj      3

how can I do this in MySQL?


texts: Store the resulting list of a SELECT in a variable to count AND use the result

texts:
I have stored procedure that uses a cursor and for every fetch a duplicate SELECT, once to determine the number of IDs found (SELECT COUNT(ID) FROM TableA WHERE something.. @cur_var) and if the COUNT(ID) is bigger then a specified value, an UPDATE is issued for another Table WHERE the above IDs are used (SELECT ID FROM TableA WHERE something.. @cur_var)..

The idea is to have the SELECT stored in a variable (pseudo-code idlist = SELECT COUNT(ID) FROM TableA WHERE something.. @cur_var). Then the length of this list is used in the IF (pseudo-code IFlength(idlist)>100.
In this case the list is used again in the WHERE statement (pseudo-code again, UPDATE TableB SET value = @cur_var WHERE ID IN idlist)

I hope to be specific enough, I'd like to save the result of a SELECT in a variable. Then use the result in WHERE IN variable and the number of rows in the variable to use in an IF.

Working on Microsoft SQL Server Enterprise Edition v9.00.3042.00, i.e. SQL Server 2005 Service Pack 2

EDIT

I've tried to keep the procedure as reasonable as possible without giving away any private information..

My intention is to remove the duplicate code. The SELECT used in the IF and the SELECT used in the WHERE from the UPDATE are the same, except that I first need the COUNT(ID) and then the actual IDs

DECLARE @cur_id int;
SET @cur_id = 0;

DECLARE Cur CURSOR FOR
    --this is actually a more complex SELECT but basically gives a list of IDs
    SELECT ID FROM TableC ORDER BY ID DESC

    OPEN Cur;
    FETCH NEXT FROM Cur INTO @cur_id;

WHILE @@FETCH_STATUS = 0
BEGIN

    --here I check if the count of found IDs (with value=NULL and IN the SELECT) is bigger than 5
    IF
    (
        SELECT COUNT(ID) FROM TableA WHERE value=NULL AND ID IN
        (
            SELECT DISTINCT(ID) FROM TableB WHERE ID = @cur_id
        )
    ) > 5
    BEGIN
        --if its bigger, set the value for every ID that is part of the ID list (the same select, but above the count, below no count)
        UPDATE TableB
        SET value = @cur_id
        WHERE ID IN
        (
            SELECT ID FROM TableA WHERE value=NULL AND ID IN
            (
                SELECT DISTINCT(ID) FROM TableB WHERE ID = @cur_id
            )
        )
    END

    FETCH NEXT FROM Cur INTO @cur_id;
END

EDIT 2:

This is the closest possible solution for the duplicate SELECT i have above..

DECLARE @TempIDs TABLE
(
   ID int
);

INSERT INTO
    @TempIDs
--this is the select i have twice in the procedure above
SELECT
    ID
FROM
    TableA

SELECT COUNT(*) from @TempIDs
SELECT * FROM @TempIDs

In this case I the SELECT only once, but followed by two other SELECTS later, once using * and once COUNT(*).

Is there no way to use @TempIDs like this? (pseudo-code):

IF COUNT_ROWS(@TempIDs) > 5
BEGIN
    UPDATE TableB SET value = "wow" WHERE ID IN (@TempIDs)
END


texts: MongoDB allow access only from local server

texts:
I am utilizing MongoDB as a database platform into my application.
As a security measure I need to allow my database to be accessible only from my server.
Looking forward for appropriate solution.


texts: Find a row with value zero in any column

texts:
I’ve 10,000 rows with 64 columns.

If any column in any row has the value “0”, that row needs to be selected. Does anyone have a ready-made query? It would be a benefit to my project.

The values can be null. The value cannot be negative. It is an int value. The version is SQL Server 2016.


texts: MongoDB: replication headroom gone below 1 days (avg/sec)

texts:
getting alert that "Replication Headroom has gone below 1 days(avg/sec)".
when checked OPS manager for replication lag, max lag I can see that is of 16 seconds. Oplog size has been set to 50gigs. also when checked rs.status(), there is no difference in time for "optimeDate" and "lastHeartbeat".
I'm not understanding how to resolve this alert. Also is there any way I can sync secondary and reduce replication lag to zero. Please suggest.
Concept explaining shrinking replication headroom/replication lags with its workaround welcomed.
Thanks!


texts: Difference in architecture between SQL Server 2005 and SQL Server 2012

texts:
I've been asked to identify the architecture differences between Microsoft SQL Server 2005 and 2012. I've been pointed out to http://technet.microsoft.com/en-us/library/bb545450.aspx but I haven't seen anything talking about the architecture.

So what are the differences? I'm specially interested on security-related differences.


texts: Rackspace Cloud Databases: incremental backup

texts:
Rackspace Cloud Databases are a little bit special in that we do not have access to the bin_log file, they are not servers in the traditional sense. I do have a couple of Cloud Servers with them but I want to do incremental backups on my own and am currently looking into ways to do this specifically for Cloud Databases, so far without success.

What options do we have if we want to do incremental backups on Cloud Databases?

Note: I do not want to start a discussion about which method is best, I just want to know which methods are available for this scenario.


texts: How to find consecutive count last 3 months count and last 6 months counts

texts:
Column names: Site_key. Billing_date ,Reason_type

Things to Find:

Consecutive count for last 5 months:
Last 3 month Count
Last 6 month Count

I have to find Consecutive count for last 5 months for a site_key based on the condition Reason_TYpe.
There are 4 distinct Reason_type:

Zero_bill
Same_Units
Units_Deviate
Average_Bill\

I need to find for a particular site key say for ex: 3, billing_date is say 20-11-2015, and  reason type ='Zero_Bill'

How many consecutive previous scenarios were there were site had Zero_Bill (excluding current month)
How many such Scenarios in last 3 months (including current months)
How many such Scenarios in last 6 months (including current months)

Sample data:

 site_key |    billing_date     | Reason_type
----------+---------------------+---------------------
        3 | 2015-01-03 00:00:00 | AVERAGE_BILL
        3 | 2015-02-03 00:00:00 | ZERO_Bill
        3 | 2015-03-01 00:00:00 | ZERO_Bill
        3 | 2015-04-03 00:00:00 | AVERAGE_BILL
        3 | 2015-05-03 00:00:00 | AVERAGE_BILL
        3 | 2015-06-03 00:00:00 | ZERO_BILL
        3 | 2015-07-03 00:00:00 | ZERO_BILL
        3 | 2015-08-03 00:00:00 | AVERAGE_BILL
        3 | 2015-10-06 00:00:00 | ZERO_BILL

Expected Output:

 site_key |    billing_date     | Reason_type            |LAST_Consecutive| Last_3_Months | Last_6_months
----------+---------------------+----------------------- +----------------+---------------+--------------------
        3 | 2015-01-03 00:00:00 | AVERAGE_BILL           |0               |1              |1
        3 | 2015-02-03 00:00:00 | ZERO_Bill              |0               |1              |1
        3 | 2015-03-01 00:00:00 | ZERO_Bill              |1               |2              |2
        3 | 2015-04-03 00:00:00 | AVERAGE_BILL           |0               |2              |2
        3 | 2015-05-03 00:00:00 | AVERAGE_BILL           |1               |2              |2
        3 | 2015-06-03 00:00:00 | ZERO_BILL              |0               |2              |3
        3 | 2015-07-03 00:00:00 | ZERO_BILL              |1               |2              |4
        3 | 2015-08-03 00:00:00 | AVERAGE_BILL           |0               |1              |3
        3 | 2015-10-06 00:00:00 | ZERO_BILL              |0               |3              |3

What I tried:
For Consecutive count:

Select site_key,
       billing_date,
       Reason_type,
       rank() over (partition by site_key,billing_date,reason_type order by billing_date) as consecutive_count
from schema.AVG_SCN
where site_key=3
order  by 1,2;

For Last_3_Months and Last_6_Months:

Select site_key,Billing_Date,Case when cnt>3 then 3 else cnt end as Count_OF_3, Case when cnt>6 then 6 else cnt end as Count_OF_6
from (
Select site_key,billing_date,Reason_TYPE,count(*) over (partition by site_key order by billing_date) as cnt from schema.AVG_SCN where reason_type='Zero_Bill' group by billing_date,site_key,Reason_TYPE
union all
Select site_key,billing_date,Reason_TYPE,count(*) over (partition by site_key order by billing_date) as cnt from Schema.AVG_SCN where reason_type='Average_Bill'  group by billing_date,site_key,Reason_TYPE
union all
Select site_key,billing_date,Reason_TYPE,count(*) over (partition by site_key order by billing_date) as cnt from schema.AVG_SCN where reason_type='Units_Deviate'  group by billing_date,site_key,Reason_TYPE
union all
Select site_key,billing_date,Reason_TYPE,count(*) over (partition by site_key order by billing_date) as cnt from schema.AVG_SCN where reason_type='Same_Units'  group by billing_date,site_key,Reason_TYPE) as a;


texts: Manually increase AUTO_INCREMENT column by one

texts:
I was wondering if there is a simple way to manually increase the auto increment vaulue of a MySQL column by one.

The use case would be to keep the indexes in sync of two tables. In my case they usually both get a new entry at the same time. But sometimes only one of them gets a new entry. This causes the IDs to desync, which is not what I want. Due to limitations in the code I cannot retrieve the ID of any inserted rows, as there's a fair amount of limitations on the API I use to communicate with the database. I can however execute queries to my hearts content. So a simple query to bump the value by one would be much apprechiated!


texts: CREATE DATABASE permission denied in database 'master'

texts:
I've developed a desktop application using WPF and Entity Framework code-first.

In my application the user can create multiple databases at run-time.

But Entity Framework can't create database if the user isn't admin on his machine.

I need to find a solution that could be done with coding. Management Studio is not installed on the user's machine.

What do you suggest?


texts: Is it possible to run multiple versions of MySQL Workbench?

texts:
I'm doing an application migration from MySQL 5.5 to workbench v8... 5.5 supports MySQL Workbench up to version 6.3... However, MySQL server 5.5 only supports up to version 6.3 of the workbench.

I have an instance of MySQL server 8.0 on the same machine also that I need to manage.  Is it possible to have two different versions of MySQL Workbench installed on a Windows 10 dev machine at the same time?


texts:
I don't know if this question better suits here or in SO ...

This is a script that I'd like to launch (the code of the function was copied from a question on SO):

\c mydb

create or replace function truncate_tables(username in varchar) returns void as $$
declare
    stmt RECORD;
    statements cursor for select tablename from pg_tables where tableowner = username;
begin
    for stmt in statements loop
        execute 'truncate table ' || quote_ident(stmt.tablename) || ' CASCADE ;';
    end loop;
end;
$$ language 'plpgsql';

I get the following error:

ERROR: syntax at or near "$1"    LINE1:   $1
QUERY $1
CONTEXT: SQL statement in PL/PgSQL function "truncate_tables" near line 5

I am new to Postgres and PL/pgSQL and don't know what this error message means.


texts:
I have a problem with a UNION query in MySQL. We have 10 millions players on our website and we would like to select players with a multi-criterias system. For exemple, selecting US people, men, more than 35 years of age.

We are using "vertical partionning": 1 table per criter. For example:

* user_country
- id_user
- id_country

We would like to do this kind of query:

SELECT id_inscri FROM userdata_langue
WHERE id_langue='43'
  UNION
SELECT id_inscri FROM userdata_sexe
WHERE sexe='2'
  UNION
SELECT id_inscri FROM userdata_nb_jour
WHERE nb_jour>='31'
  UNION
SELECT id_inscri FROM userdata_last
WHERE last<='2013-04-07'
  AND last>='2013-04-03' ;

How do I optimize that?

----- More details

Explain output of the query:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY userdata_langue ref id_langue   id_langue   1   const       398846  Using index
2   UNION   userdata_sexe   ref sexe    sexe    1   const   1667137 Using index
3   UNION   userdata_nb_jour    range   nb_jour nb_jour 2   NULL    5830    Using where; Using index
4   UNION   userdata_last   range   last    last    3   NULL    371614  Using where; Using index
NULL    UNION RESULT    <union1,2,3,4>  ALL NULL    NULL    NULL    NULL    NULL

SHOW CREATE TABLE

Table   Create Table
userdata_langue CREATE TABLE `userdata_langue` (
 `id_inscri` bigint(20) NOT NULL,
 `id_langue` tinyint(3) unsigned NOT NULL,
 PRIMARY KEY (`id_inscri`),
 KEY `id_langue` (`id_langue`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1


texts:
I have a table with 100 columns and I know there is an 8k limit before a table overflows in SQL Server

I'm using the following query's to have a look at the table stats

dbcc showcontig ('Products') with tableresults

and

SELECT * FROM sys.dm_db_index_physical_stats (DB_ID(N'tablename'), OBJECT_ID(N'Products'), NULL, NULL , 'DETAILED'

I notice, with the second query as follows:

index_type_desc,    alloc_unit_type_desc, page_count
CLUSTERED INDEX,    LOB_DATA            , 2

If I am reading it correctly there is only 2 pages of overflow?

MinimumRecordSize,  MaximumRecordSize,  AverageRecordSize
628,                7928,               950.048

Is there further metrics to say (in a more concise way)? If I really need to go to the extra effort of vertically partitioning this table?

At the moment, it looks like i am no where near the 8k column limit for native column size, and the majority of my data is all in Row Data

Is it a fair assumption to say query performance should not be impacted all that much?

P.S.: I know this is a long question, however some good concise metrics would be a good start for me


texts: How does PARALLEL_MIN_PERCENT affect parallel execution?

texts:
From the reference for PARALLEL_MIN_PERCENT:

PARALLEL_MIN_PERCENT lets you specify the minimum percentage of the requested number of parallel execution processes required for parallel execution. Setting this parameter ensures that parallel operations will not execute unless adequate resources are available. The default value of 0 means that no minimum percentage of processes has been set.
Consider the following settings:
PARALLEL_MIN_PERCENT = 50
PARALLEL_MIN_SERVERS = 5
PARALLEL_MAX_SERVERS = 10
If 8 of the 10 parallel execution processes are busy, only 2 processes are available. If you then request a query with a degree of parallelism of 8, the minimum 50% will not be met.

From Initializing and Tuning Parameters for Parallel Execution:

[PARALLEL_MIN_SERVERS] Specifies the minimum percentage of requested parallel execution processes required for parallel execution. With the default value of 0, a parallel statement executes serial if no parallel server processes are available.

What happens when the minimum 50% of servers is not available for the query when it runs? Is the query is queued, does it execute serially, or does it throw an error such as insufficient parallel query slaves available?


texts: What databases can I use to represent ordered sets?

texts:
In a SQL database, the only way to represent an arbitrarily ordered set is to give every record an "Order" and every time you update or move an item around in this ordering you have to update or somehow maintain the entire list of ranks with a nightly job or something like that.

For example, I can represent the ordered set [C, B, D, A] in this way in a SQL database:

ID  Name   Order
1   A      4
2   B      2
3   C      1
4   D      3

If I want to move an item to a different position in the set, or prepend a new item, I may have to update a lot of items. In general there is a lot of maintenance overhead with this approach.

Querying the data once it is in the database is not an issue for SQL, the issue is the significant maintenance overhead of reordering the set. There is no simple operation in SQL to move an item to a new position in the set. The ordering is arbitrary and user-defined.

I realize that this can be accomplished using SQL, it's just very clunky to perform certain operations like prepending items, or moving an item to a new position. Even this example operation of reversing the order of the set requires a pretty lengthy, complex, query. The type of database I'm looking for might support such an operation natively, or at least more elegantly.

So, if I am designing an application (like Trello, for example) that very heavily involves ordered sets, it seems SQL is not the ideal database technology for me. Are there any databases whose syntax support ordered sets in a more natural way?

These are some CQL3 queries from the Cassandra documentation that seem close to what I'm looking for. This prepends an item to an ordered set.

UPDATE users SET top_places = [ 'the shire' ] + top_places WHERE user_id = 'frodo';

This one will set the value of the item at position 2 in the set. I suspect I could use this to easily perform arbitrary swaps/reorders.

UPDATE users SET top_places[2] = 'riddermark' WHERE user_id = 'frodo';

Unfortunately the documentation also states

  And while we may (or may not) relax that rule a bit in the future,
  this still means that collections are not meant to be excessively
  large. They are not a replacement for a proper modelisation into
  tables.

which seems to suggest that ordered sets are not (yet) first class citizens in CQL3.


texts:
I'm trying to write a dynamic after trigger function that does an update based on the TG_TABLE_NAME, but it seems that plpgsql's CASE cannot do "LIKE". Is it called differently in plpgsql, or impossible?
If not possible, my second idea would be to do a SUBSTR, or are there better ways?
CREATE OR REPLACE FUNCTION upd_sys_table()
RETURNS TRIGGER AS $$
DECLARE
    lm BIGINT := (EXTRACT(epoch FROM timezone('UTC', now()))*1000)::bigint;
BEGIN
    CASE TG_TABLE_NAME
        WHEN  LIKE 'candy\_%' THEN
        BEGIN
            UPDATE sys_table SET last_modified = lm, modified_by = NEW.modified_by WHERE sys_table_name = 'xvw_candy';
        END;
        WHEN  LIKE 'fruit\_%' THEN
        BEGIN
            UPDATE sys_table SET last_modified = lm, modified_by = NEW.modified_by WHERE sys_table_name = 'xvw_fruit';
        END;
    ELSE
        RAISE NOTICE 'No match';
    END CASE;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

This simply updates a record in a table called sys_table, which keeps track of a last-modified date for a group of tables. The group of tables are prefixed with either candy_ and fruit_ (yes, I made that up).


texts: Finding reason for transaction log 'tempdb' full due to 'ACTIVE_TRANSACTION'

texts: Our application stopped working suddenly due to The transaction log for database 'tempdb' is full due to 'ACTIVE_TRANSACTION'.. Now, it start working again. But how can I know the exact reason for this error came?


texts:
I have been using Postgres and MySQL for a long time now. There are a few routine maintenance chores that must be performed on a regular basis to keep a PostgreSQL server running smoothly. As as example

  PostgreSQL's VACUUM command must be run on a regular basis for several
  reasons:

  To recover disk space occupied by updated or deleted rows.
  To update data statistics used by the PostgreSQL query planner.
  To protect against loss of very old data due to transaction ID
  wraparound.

Read more

But using MySQL database we don't have to do any of the above. Simply we don't have to do any maintenance on a MySQL database.

My problems are,

Why is that we don't have to do any maintenance on a MySQL database?
Is there a way to carry out maintenance on a MySQL database?

Given that I'm looking for a technical answer. As an example "database backup" is not the  answer I'm looking for therefore below is not acceptable

https://stackoverflow.com/questions/7478849/general-mysql-database-maintenance-advice


texts: What things can influence log_shipping_monitor_secondary - last_restored_latency values?

texts:
Looking at msdb.dbo.log_shipping_monitor_secondary last_restored_latency values for each log shipped db.
last_restored_latency - The amount of time, in minutes, that elapsed between when the log backup was created on the primary and when it was restored on the secondary.

Usually this value is 0 or fairly close to 0.  Every once in awhile see a higher number in this column (850).  What are the things to look for that can cause high values for last_restored_latency?


texts: Take query results and insert into new table

texts:
So I have the following query:

DECLARE @tbl TABLE(value int)

*--The same function but with the number of the random numbers
;WITH Nbrs (n) AS (
        SELECT 1 UNION ALL
        SELECT 1 + n FROM Nbrs WHERE n < 6 )
INSERT INTO @tbl
(
    value
)
SELECT
    Nbrs.n AS Sequence
FROM Nbrs
;WITH Nbrs ( n ) AS (
        SELECT CAST(1 as BIGINT) UNION ALL
        SELECT 1 + n FROM Nbrs WHERE n < 480000)
SELECT
    tblOrderRandomNumbers.[1] AS First,
    tblOrderRandomNumbers.[2] AS Second,
    tblOrderRandomNumbers.[3] AS Third,
    tblOrderRandomNumbers.[4] AS Fourth,
    tblOrderRandomNumbers.[5] AS Fifth,
    tblOrderRandomNumbers.[6] AS Sixth
FROM
    Nbrs
    --This cross join. Joins with the declared table
    CROSS JOIN
        (
            SELECT
                [1], [2], [3], [4], [5], [6]
            FROM
            (
            SELECT
                Random,
                ROW_NUMBER() OVER(ORDER BY tblRandom.Random ASC) AS RowNumber
            FROM
                (
                SELECT
                    (ABS(CHECKSUM(NewId())) % 49 + 1) AS Random
                FROM
                    @tbl AS tblNumbers
                ) AS tblRandom
            )AS tblSortedRadom
            --A pivot makes the rows to columns. Using the row index over order of the random number
            PIVOT
            (
                AVG(Random)
            FOR RowNumber IN ([1], [2], [3], [4],[5],[6])
            ) as pivottable
        ) AS tblOrderRandomNumbers
OPTION ( MAXRECURSION 0 )*

I would like to automatically insert these results into a table, anyone have some advice?


texts:
I have some tables that are partitioned and have several indexes on a replicated slave. After copying the snap shot (verified safe) to a new slave and upgrading mysqld from 5.1.42 to 5.5.15 and restarting replication, I'm getting InnoDB crashes with the error message "Invalid pointer ..."

These errors have happened across 2 servers with different hardware and O/S. After running:

ALTER TABLE .... COALESCE PARTION n;

the problem goes away for that table.

My question is larger in scope, though, and that is "How do you identify InnoDB table corruption?" or rephrased "How do you assess InnoDB table health?" Is "CHECK TABLE" the only tool available to identify problems pre-crash?

Not sure if it matters, but the crashes occurred running:
Version: '5.5.15-55-log'  socket: '/opt/mysql.sock'  port: 3306  Percona Server (GPL), Release rel21.0, Revision 158


texts:
by expect script we create the database hive and some other commands

in case we run the expect script again on the machine that hive already created

then we get - ERROR hive already exists

so how to verify database hive already created ?

And by this verification we can escape the expect script

# su - postgres
 Last login: Sun Aug 13 11:12:03 UTC 2017 on pts/0
 -bash-4.2$ psql
 psql (9.2.13)
 Type "help" for help.

postgres=# CREATE DATABASE hive;
ERROR:  database "hive" already exists

my expect script: ( from my bash script )

set timeout -1

#exec the scripts

spawn timeout 60 ssh root@IP
expect "#"
spawn su - postgres
expect "$"
send "psql\n"
expect "=#"
send "CREATE DATABASE hive;\n"
.
.
.
.


texts: reportserver will not join basic availabiltiy group

texts:
I am running into a strange situation that I have not been able to resolve.  I have a 2 node Basic AG running sql server 2016 standard.  I am trying to add the reportserver DB to an AG, but it keeps failing on joining the DB to the secondary node.

I have done the below:

fully recovered the DB on node1 (primary), made sure its in Full recovery mode
took a fresh backup, AND log backup immediately after it
restored both the full and log backup to Node2 (secondary) leaving it in restoring state.

Back on primary I created an AG via the GUI, but when I get to the end it sits on joining the DB to the AG in node2.  I get a few different errors, which essentially mean the same thing:

 "the remote copy of the db "reportserver" has not been rolled forward to a point in time that is encompassed in the local copy"

"the mirror db "reportserver" has insufficient transaction log data to preserve the log backup chain."

I know these mean that should be additional log backups to restore, but I had just prior to this taken the full AND log backup and restored both of them to the secondary, so there should be no other backups to restore...  The only thing I can think of is that something is still writing to the log on the primary, but as far as I am aware, even if that is the case, joining the DB to the AG should still work as the currently un-backed up portion of the log should be streamed to the secondary replica during the sync process.  I skipped the listener configuration for the time being.  I also stopped the SSRS service on both nodes prior to doing this.

Has anyone encountered this before and had success fixing it?  Is it because it is the reportserverDB, and it can't be joined to the same AG as reportservertempDB?

I have tried automatic seeding, and join only mode, and the strange thing is that automatic seeding gives me NO errors during the AG setup, but the DB does not ever show up on Node2. When i do join only, that is when I get the errors above.  I'm pretty much baffled by this.  I have 6 other DB's on these nodes that are each in their own basic AG, and I have not run into any issues with them.


texts:
I have about 35 tables. I want to use them in the same query. I do not know how it possible.
I have three tables:
land

lid
name
meter
address
picid

owner

ownerid
name
family
mobile
lid

Every land can have a maximum of 12 owners.
pic

picid
path
picinfo

The result that I need:

lid, name, meter, address, path, picinfo, owner_name_1, owner_family_1,
owner_mobile_1, owner_name_2, owner_family_2, owner_mobile_2, ....
owner_name_12, owner_family_12, owner_mobile_12


texts:
I'm currently reviewing a case where an SSRS server is experiencing frequent blocking and deadlocking.
The reports are not business-critical, and they are frequently killed to alleviate the blocking.

However, this same SQL Server has some critical databases on it (it's sharing a sql server with a large sharepoint farm), that have been showing declining performance.
The plan from my predecessor is as follows:

Migrate 4TB worth of databases from A to B
Migrate Reporting server (originally A) from B to C
Migrate Reporting server (originally C) from C to B

The reasoning being that we will have better hardware (B), be able to get rid of an old server (A), and remove the reporting server (ends up at C) from the critical database server (now B).

However, I'm not entirely convinced that this will impact performance much (save for the newer hardware).
I've been monitoring the blocks for the better part of a week now, and it's consistently the WriteLockSession blocking a ReadChunkSegment. Which as far as I can tell from documentation (google) is perfectly fine.

How could I determine that/if a blocked process is causing slowdown on a different database?
If I could show that the performance issues are unrelated (reporting and other databases) other than the shared hardware, I would like to set up shared datasets. As most of the reports, and all of the long-running reports, are querying the same small subset of data.