texts: PostgreSQL sort by max date and get the difference

texts:
I've got a table with user id, balance (amount only increases) and date created. So, each month I can get last amount by user and date created.
I'm doing it like that:

SELECT balance
FROM balances
WHERE id IN (
    SELECT max(id)
    FROM balances
    WHERE "userId" = 123
    AND datecreated IN (
        SELECT max(datecreated)
        FROM balances
        WHERE "userId" = 123
        AND EXTRACT(YEAR FROM date(datecreated)) = 2018
        AND EXTRACT(MONTH FROM date(datecreated))::integer = 8
    )
)

First question: can it be done somehow better?
Second question: I can find the income by finding difference between current month last balance and previous month last balance.

SELECT (
SELECT balance
FROM balances
WHERE id IN (
    SELECT max(id)
    FROM balances
    WHERE "userId" = 123
    AND datecreated IN (
        SELECT max(datecreated)
        FROM balances
        WHERE "userId" = 123
        AND EXTRACT(YEAR FROM date(datecreated)) = 2018
        AND EXTRACT(MONTH FROM date(datecreated))::integer = 8
    )
) - (
SELECT balance
FROM balances
WHERE id IN (
    SELECT max(id)
    FROM balances
    WHERE "userId" = 123
    AND datecreated IN (
        SELECT max(datecreated)
        FROM balances
        WHERE "userId" = 123
        AND EXTRACT(YEAR FROM date(datecreated)) = 2018
        AND EXTRACT(MONTH FROM date(datecreated))::integer = 7
    )
)
) AS "month_income";

It works when I've got data for previous balance. But How can I select 0 if there is nothing in previous month? Tried to do CASE like:

SELECT
    CASE WHEN balance IS NULL THEN 0
    ELSE balance
    END AS balance
FROM balances
WHERE id IN ( ... )

It does not return anything (even NULL value) when, for example, I'm setting WHERE id IN NULL. Also tried COALESCE (balance, 0). But the problem is that nothing returned.


texts: Error while creating an admin user in replica set

texts:
I have a relatively simple replica set test setup between two VMs and my host machine, with VM1 as primary, VM2 as secondary and the host as arbiter. I have mongodb 2.6.4 installed on all machines. So far it works without any issue, however things go weird when I try to setup authentication. For now it is disabled, and I am just trying to create an admin user so that I can set auth to true and set the keyFile in the configuration.
I run the following commands on the primary (VM1):

use admin
db.createUser(
  {
  user: "admin",
  pwd: "blop",
  roles:
    [
      {
        role: "userAdminAnyDatabase",
        db: "admin"
      }
    ]
  }
)

However when I do that (on the primary, not the secondary), the secondary (VM2) crashes after throwing this error:

ERROR: error: exception cloning object in admin.system.users system.users entry must have either a 'pwd' field or a 'userSource' field, but not both obj:{ _id: "admin.admin", user: "admin", db: "admin", credentials: { MONGODB-CR: "85bc114ccxf991158fd4x8eb134834d8" }, roles: [ { role: "userAdminAnyDatabase", db: "admin" } ] }

What am I doing wrong?

Thanks in advance

EDIT: I found the issue, actually for some reason the mongod instance on VM2 was still on 2.4, which has an incompatible way of creating users and would crash when receiving new-style user data from the primary.


texts: Common database shared to other database

texts:
I am having thoughts on what design should I take on my current application architecture. I am building multiple sites (different products) then requesting to a centralized API for data.

My initial thoughts of the API database would be:

common_db

This database likely contains common entities such as user (id, details, etc.)

first_site_db

This database contains table with data specific for the product site

second_site_db

This database contains table with data specific for the product site

... and so on.

Is this a bad database design practice? Considering this is fine, what particular technique should I implement in order for each product database to get the user details on the common database?


texts: Creating temporary table dynamically using condition

texts:
I am trying to create a temporary table dynamically using the below code, and I know we can't have two create statements for the same table. Is there a workaround for this to put it in a conditional way like the below code.

CREATE PROC test @var1 CHAR(1)
as
BEGIN
IF(@var1 = X)
BEGIN
SELECT * INTO #result
FROM TABLE1
END
IF(@var1 = Y)
BEGIN
SELECT * INTO #result
FROM TABLE2
END
IF(@var1 = Z)
BEGIN
SELECT * INTO #result
FROM TABLE3
END

SELECT * FROM #result r
END

The objective is to finally have a table named #result with columns based on the variable(@var1) value

Edit 1:
Since this is a good candidate for the use of dynamic SQL, as shown below, but I won't be able to use the #result table outside dynamics SQL scope which is what I need.

CREATE PROC test @var1 CHAR(1)
as
BEGIN
-- USING  dynamic sql
DECLARE @sql VARCHAR(MAX)
IF(@var1 = 'X')
BEGIN
SET @sql ='SELECT t.[name],t.[object_id],t.[principal_id] INTO #result
FROM sys.tables t'
END
IF(@var1 = 'Y')
BEGIN
SET @sql ='SELECT t.[name],t.[object_id],t.[principal_id],t.[schema_id] INTO #result
FROM sys.tables t'
END
IF(@var1 = 'Z')
BEGIN
SET @sql ='SELECT t.[name],t.[object_id],t.[principal_id],t.[schema_id],t.[parent_object_id] INTO #result
FROM sys.tables t'
END

EXEC (@sql)
SELECT * FROM #result r
END


texts:
On my understanding, regular run of DBCC CHECKDB is a best practice in order to check integrity of the database.

If there comes a time where a corrupted page existed, and no good back up is available, in general, what are the consequences can occur? Nothing? Corruption will spread?


texts:
I am new to mysql, I have been using SQL in the past. I am trying to insert rows into a table via creating a loop. But struggling with it. Any help?

DECLARE @minid  INT = (SELECT MIN(Product_ID) FROM
    Product_Report_Offer_Interaction_Jan_2012 pn)
DECLARE @maxid  INT = (SELECT max(Product_ID) FROM
    Product_Report_Offer_Interaction_Jan_2012 pn)

DECLARE @topid INT= @minid + 50000
BEGIN

WHILE ( @minid < @maxid )
    BEGIN
        insert into  Product_Report_Offer_Jan_2012 (Month, Product_ID, Offerview,
        EnlargeImage, DynamicPopUp, ShareonFacebook, BuyNowPress, Sendtofriend,
        Printed, StoreLocated, uLike, iPhoneOfferDetails, iPhoneStoreLocated,
        WishlistAd, Tweet, Catalogue1pageview, Catalogue2pageview, SearchListing,
        Featured, RelatedAdImpression, Wishlist)
        select pn.Month, pn.Product_ID, pn.Offerview, pn.EnlargeImage,
        pn.DynamicPopUp, pn.ShareonFacebook, pn.BuyNowPress, pn.Sendtofriend,
        pn.Printed, pn.StoreLocated, pn.uLike, pn.iPhoneOfferDetails,
        pn.iPhoneStoreLocated, pn.WishlistAd, pn.Tweet, pm.Catalogue1pageview,
        pm.Catalogue2pageview, pm.SearchListing, pm.Featured,
        pm.RelatedAdImpression,
        pm.Wishlist
        -- select count(*)
        -- select *
        from Product_Report_Offer_Interaction_Jan_2012 pn
            left join Product_Report_Offer_Impression_Jan_2012 pm on pn.product_ID = pm.product_ID
            where pn.product_id != '0'
                and pn.Product_ID BETWEEN @minid AND @topid

        SET @minid = @minid + 50000 + 1
        SET @topid = @topid + 50000 + 1

  END
END

Thanks jakub


texts:
For background, I am on an instance of SQL Server 2012 Enterprise Edition.

I have three tables that join on the same set of three keys - all of them are INT. All tables have their keys clustered in the following order:

Key-Time (represents a week)
Key-Product
Key-Location

The main table left joins to the other two tables, and the result is fed into a set of windowing functions. The purpose is to sum a specific number of weeks' data based on Product-Location, like so:

SUM(Metric) OVER (PARTITION BY Key-Product, Key-Location ORDER BY Key-Time ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS Result

The where clause limits the time frame by Key-Time, and an outer query discards any results that would not have 3 preceding rows. There are 15 windowing functions calculating different metrics.

The issue comes when sorting for the partitioning function. This is the first part of my query plan:

When the tables are joined, the results are already sorted by Time-Product-Location (the key). The windowing function needs to order by Product-Location then Time, hence the second sort. This query takes just over two minutes to execute for the 22 million rows shown, but in production the tables are much larger.

We have other queries that depend on the Time-Location-Product ordering, so changing the order of the keys won't be an option. Also, the two left join tables contain the metrics data, but the main table must be present to ensure accuracy.

How would I go about speeding this up? Is there room for improvement here?

UPDATE:
Here is the information when I hover over the sort:


texts: Storing shipping details in orders table

texts:
Have orders table and among columns the following: shipping type, shipping service provider, delivery type and shipping address. Let me explain what these are.

User can choose between two shipping types: pick it yourself and use postal service.

If user goes for pick it yourself all other shipping fields are NULL. If he wants to use provider, then he has to specify which one (shipping service provider), does he want goods to be delivered at local postal office or at his home (delivery type) and shipping address.

This table looks good to me as it is, don't think it violates any of the first three normal forms, but want to check am I missing something? That is, should I introduce new table, shipping details for example, and why?


texts: remove almost duplicates based on insert time

texts:
Have a MySql table that records magnetic stripe swipe times from identity cards:

CREATE TABLE `swipes` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `personalid` varchar(20) NOT NULL,
 `swipe_time` datetime NOT NULL,
 `status` tinyint(1) NOT NULL COMMENT '1=registered,0=not',
 `ride_taken` tinyint(1) NOT NULL DEFAULT '0',
 `source` tinyint(4) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 UNIQUE KEY `my_key` (`personalid`,`swipe_time`,`status`,`ride_taken`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

The unique key is there to prevent exact duplicate inserts.
However there is another source of almost duplicates where the card gets swiped twice in quick succession or even for some odd reason within a minute or so when there may be other cards swiped in between. In some ways this is like bounce on electronic switch contacts.

Here's a snippet of rows to illustrate:

    id      personalid      swipe_time      status   ride_taken source
    1661    C08877547   2012-10-21 01:12:08     1        1        3
    1662    C09364782   2012-10-21 01:23:38     1        1        3
    1663    C09364782   2012-10-21 01:23:48     1        1        3
    1664    D09490557   2012-10-21 01:24:39     1        1        3

What I want is a query that will remove entries like id 1663, that are within some specified time of rows with the same personalid, status=1, ride_taken=1 and source

Yes I have searched and inspected both here and on stackoverflow
such as https://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows
for a similar situation but have not found an answer yet. If there is one already, please point me at it.

I'm at a loss how to apply a time range filter for the swipe_time to delete only similar rows within a specified time range.


texts: Why and for what possible reasons 'sa' gets locked?

texts:
Last night all of our systems failed and connections to the database kept dropping. We watched the log today and found out that this was the message:

  Login failed for user 'sa' because the account is currently locked out. The system administrator can unlock it.  (Microsoft SQL Server, Error: 18486)

Of course it can be solved in a minute of Googling around, via some tricks and scripts.

However, the more we search the less we find out about the reason behind this. Why it has happened in the first place at all? What possible reasons caused it, and might cause it again? Was it because of an attack? We have no clue, and any help is appreciated.

Update: in this post it's been argued that a policy check can make this happen. We haven't set a policy so far, and we do not even know where are the policies.

Update2: We realized that some of the threads in some applications kept working, even though other threads and other applications were encountering sa is locked message. Could it be that connection pooling doesn't require re-authentication? How is that possible?


texts: Difference between net start MongoDB and mongod

texts:
According to MongoDB documentation,when running mongodb community edition, it states you can start mongo service using mongod, or net start MongoDB.

Either is required to access the mongo shell.

Can someone explain the difference between using mongod and net start MongoDB


texts:
I'm trying to get MongoDB installed and running on my new Windows 8 computer. However, I cannot seem to be able to connect to MongoDB when using the mongo.exe shell.

I got the following error:

MongoDB shell version: 2.4.8
connecting to: test
Sun Dec 15 14:06:39.835 Error: couldn't connect to server 127.0.0.1:27017 at src/mongo/shell/mongo.js:146
exception: connect failed

How can I connect to MongoDB properly? I already have MongoDB installed, along with having the Data Directory all set up. I also created a specific directory for the MongoDB log files, and installed the MongoDB service.


texts:
I am installing a repository creation utility on a windows 8 x64 bit system and getting the following error:

  Time out while connecting to the database. Current time out value is 30 seconds

When I connect via sqdeveloper, it works fine.
My database connection details are:

- Database type: Oracle Database
- Host name: Bonge
- Port: 1521
- service name: XE
- Username: sys
- Password: XXXXX
- Role: Sysdba

Can anyone help me solve this problem?


texts: DB constraints in face of quickly changing requirements in an agile environment

texts:
I am in a team where requirements are changing often.
I see keeping constraints in application layers makes our life easy.
If I am going to follow the best practice of specifying most constraints into DB, I see hard times for my team members. When a requirement change needs a DB constraint change, we have to migrate the whole data. And migration seems to be a big headache.
What should we do? What is the best practice keeping in view that requirement changes very often?

We want to keep DB schema flexible and yet have data consistency.

What constraints can we still impose on DB while keeping things flexible enough?


texts: Database: Storing multiple Types in single table or multiple intermediate delta tables

texts:
Using Java and Oracle.

We need to update changes in Email, UserID of employee to third party.
Actual table is Employee and intermediate table we keep which we will use for comparison of changes before sending to third party.

Following are database designs coming in mind for intermediate table:

Only Single table:

EmployeeiD|Value|Type|UpdateDate

Value is userid or email, type will be 'email' or 'userid'. Update date is kept so to figure out that which of email or userid was different and update to third party.

Multiple Table:

 Employee_EmailID
      EmpId|EmailID|Updatedate

 Employee_UserID
      EmpId|UserID|Updatedate

Java flow will be:
 - Pick employee from actual table.
 - Pick employee from above intermediate table.
 - Compare differences. Update difference to third party.
 - Update above table with updated value and last update date.

Which one is consider as best way, single table approach or multiple table or is there any standard way to implement the same? There are 10,000 Employees in system.
Also consider approach when 10 intermediate tables are required like phonenumber, salary etc.


texts: sp_help with different object types with the same name

texts:
I have a table with the same name as a user-defined data type (UDDT), and when I use sp_help, only the table definition is shown.

Is there a way to use sp_help or an equivalent to show the definition for the UDDT instead?

sp_help only appears to have an @ObjName parameter, with no way to specify I actually want information about a type.

This script demonstrates the ability to create a table and a user-defined data type with the same name and under the same schema:

CREATE TYPE [dbo].[Address] FROM [nvarchar](40) NULL;
GO

CREATE TABLE [dbo].[Address]
(
    [AddressID] Int IDENTITY(1,1) NOT NULL
);


texts: What is "CSS operation: data query" wait event in an Oracle StatsPack report?


texts: RaiseError on SQL Server stored procedures

texts:
ALTER PROCEDURE sp1
as
begin
IF (@PersonId=0)
    RAISERROR(N'Error already raised. See previous errors.', 16, 1);
    break
end

I want something like the following.

If no value in the variable, how to break the procedure in SQL Server?


texts: How to create sql query results in columns from rows?

texts:
From the following tables:

store(store_id, ...) {1 ..., 2 ...} two stores
film(film_id, ...)
inventory(inventory_id, film_id, store_id)
rental(rental_id, rental_date, inventory_id, return_date, ...)
category(category_id, name)
film_category(film_id, category_id)
film(film_id, rental_duration, rental_rate, ...)
customer(customer_id, store_id, ...)

Can I have a query result on a table just like the following?

CategoryName | "Store 1: available items" | "Store 1: unavaliable items" | "Store 2: avaliable items" | "Store 2: unavaliable items"

Notice that the store fields in the result table are in separate columns not in the same columns !!

I am not looking for the result query, I am looking for how to make them side by side store 1, store 2.


texts:
I'm trying to write a disaster recovery plan, I have one way replication with 1 master(A) and 3 slaves(B,C,D)

     A
    /|\
   / | \
  B  C  D

Let's say my master crash, and I loose all data on that server, I still have 3 slaves, and I'll promote the most up-to-date to be my new master, let say slave B is up to date and C and D are not, at the point I promote B to master my slaves have the follow result for SHOW SLAVE STATUS\G:

slaveB> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
      Relay_Master_Log_File: master-bin.000002
        Exec_Master_Log_Pos: 1476884

slaveC> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
      Relay_Master_Log_File: master-bin.000002
        Exec_Master_Log_Pos: 78684

slaveD> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
      Relay_Master_Log_File: master-bin.000001
        Exec_Master_Log_Pos: 746848

In order to promote B to master, I want to apply all missing transactions on C and D, so before B become the new master and start to receive queries from applications, I've all slaves on a consistent state, how can I find the missed transactions from C and D on binary logs from B (I've the --log-slave-updates option enabled on all server).

For legacy issues, the envoirement use MySQL 5.0


texts: How can i improve my count(distinct id) query in mysql laravel 4.2

texts:
I am using three join and fetching more than 4 million records for now it is 1.5 million.

Below is my query, can you please take a look?

SELECT count(distinct enrollments.userid) AS aggregate
FROM users
INNER JOIN enrollments ON users.userid = enrollments.userid
INNER JOIN classes ON enrollments.classid = classes.classid
INNER JOIN assigned_courses ON assigned_courses.courseid = classes.courseid
WHERE assigned_courses.`oneroster_permission_id` = 147

Here is the explanation of my current query.


texts: How to remove Data Quality Services completely?

texts:
When I setup one of our SQLServer 2012 instances a while back, I didn't realize that I have checked "Data Quality Services" feature. Looking at my installation notes, I find that I didn't end up performing any of the post-install task to install this feature so we didn't end up using this feature.

However, when I run the "Installed SQL Server features discovery report", the report would shows that this feature is configured, even though we don't have any of the DQS databases and any of its objects loaded in the instance.

Can someone tell me how to completely remove this feature so that it doesn't show up anymore in the Microsoft SQL Server 2012 Setup Discovery Report?
As always, your help is greatly appreciated.


texts: How can I correctly choose maximum number of occurrences of a string while grouping by another field?

texts:
I am using Postgresql 9.0. I have the following fields in a table: id, name.

 id    name
 1     John
 1     Mary
 1     Mary
 1     Mary
 1     John
 1     Mary
 3     Paul
 3     Paul
 3     George
 .     .
 .     .

For each id, I want to select the name that occurs the most. How can I do that?

I tried with the following query but it doesn't work:

select id, max(name)
from table
group by id;


texts: Do you think using ObjectID as field name is a bad practice? What can go wrong if I follow this design?

texts: I design a MongoDB collection where I am planning to use mongo ObjectID as a field name. The ObjectID is a PostID and in the array, I track all the userID who liked or disliked the post.


texts: How many rows does datatype int and big int retrieve in their table when you work with fact table in SQL server?


texts: accessing, updating a large MYSQL table advice and guidance

texts:
I'm after some guidance or advice really.  We've built an application and its gone mental with custom since coronavirus.  We are after some advice for something we have never tackled.

The problem we are having is in fact the job table.  We have 1000's of office-based users updating, creating, and editing jobs all day.  We also have then 10's of thousands of apps out in the field connecting all-day to this table to read their rotas and then update it if they clock in or out of locations.

We've put in all relevant indexes and we are still starting to see a slowdown.  To the point some times where we have to restart MYSQL.

After many hours of Googling, we have come across many solutions for big data including solutions on here but we'd like to ask the community what they think or the following idea, or is there a better way?

Idea 1

As the system grows and we get more clients this is only going to get worse.  Being that the apps can only see 1 day back and 7 days forward whereas the rota table stores all of the rota history from the beginning of time we were thinking of creating an exact replica table of the job table.  This table would store all rotas from yesterday and forward 7 days using a cronjob to delete older data and add new data each night.  The apps would then connect to this table to get the jobs list and update the original table if they clock in or out.

If users update fields for jobs yesterday or within 7 days we would use triggers to keep the two tables in sync or maybe do it through code so it was more accurate?

This way we have taken the read load away from our main rota table by pointing the apps at the new table.

Our question is, is this a good way to go, or are we digging a hole we can't climb out of?

Idea 2

Simply archive off jobs from more than a year ago to another table.  If users request to see jobs from over a year ago it simply looks in the other table.  While we like this solution we can see its a sliding scale.  As we get more customers we will have to start archiving sooner and sooner until the problem reappears in a few year's time.

if these ideas are no good does anybody have any other suggestions we can look at?  Partitioning the table is not an option at the minute.


texts: Changing row colour based on a group toggle - SSRS 2014

texts:
One of the tables in my report looks somewhat like:

Row       Colour

Total     grey
+Category white
+Category white
+Category white
+Category white

It's expandable to

Row        Colour

Total      grey
-Category  white
    Item   white
    Item   white
    Item   white
+Category  white
+Category  white

'Total' row is grey, the rest is white. I want to be able to change the color of expanded Category row to light grey, but only when it is expanded, to distinguish between item entries, total for the category and total for all categories.

So the final effect would be:

Row         Colour

Total       grey
-Category   light grey
    Item    white
    Item    white
    Item    white
+Category   white

Apparently there is a textbox.ToggleState property, as shown here
https://technet.microsoft.com/en-us/library/microsoft.reportingservices.reportrendering.textbox.togglestate.aspx

However I do not think it is exposed. ReportItems!name only gives access to the .Value property

I have found this answer on msdn forums

Found out how to do this! Use the 'inscope' function.

inscope("groupname") evaluates to TRUE if the group is toggled to expanded and FALSE if it is collapsed.

But he was describing SSRS 2005 behaviour. I tried this in 2014 - it seems it no longer works. The Inscope function evaluates to true if the field is part of a group, and false otherwise. Toggling seems to have no effect.

So guys, any ideas?


texts: Schema pg_dump failed due to a Lock on a table

texts:
I'm running backup restore on a schema every day and get this every now and then:

pg_dump: Error message from server: ERROR:  relation not found (OID
86157003) DETAIL:  This can be validly caused by a concurrent delete
operation on this object. pg_dump: The command was: LOCK TABLE
myschema.products IN ACCESS SHARE MODE

How can this be avoided? It seems the table was being used at the time, or someone was running something aginst the table.
As far as I understand, pg_dump could run even if users are doing something with the table but it doesn't seem to be the case.
Thanks,


texts: MAX(UPPER(range)) can't be accelerated with a GiST index on the range?

texts: To get the most recent session I need to SELECT MAX(UPPER(range)) AS end FROM sessions WHERE session_id = ?. I have a GiST index on (session_id, range). This query is extremely slow, taking nearly 30 seconds. I added a normal btree index on (session_id, UPPER(range)) and that fixed it down to under a millisecond, but it seems like the index on range should allow for queries on its upper bound. Is there a way to do this with just one index? Am I doing something wrong, either in the query or in the index? Should I use a different type of index other than GiST?


texts:
I am trying to learn Table Partitioning. However, I struggling to understand why SQL Server behaving stage for a simple query.

Test dataset

   /* --------------------------------------------------
-- Create helper function GetNums by Itzik Ben-Gan
-- http://sqlmag.com/sql-server/virtual-auxiliary-table-numbers
-- GetNums is used to insert test data
-------------------------------------------------- */

-- Drop helper function if it already exists
IF OBJECT_ID('GetNums') IS NOT NULL
    DROP FUNCTION GetNums;
GO

-- Create helper function
CREATE FUNCTION GetNums(@n AS BIGINT) RETURNS TABLE AS RETURN
  WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
  SELECT TOP (@n) n FROM Nums ORDER BY n;
GO

/* ------------------------------------------------------------
-- Create example Partitioned Table (Heap)
-- The Partition Column is a DATE column
-- The Partition Function is RANGE RIGHT
-- The Partition Scheme maps all partitions to [PRIMARY]
------------------------------------------------------------ */

-- Drop objects if they already exist
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'Sales')
    DROP TABLE Sales;
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'psSales')
    DROP PARTITION SCHEME psSales;
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'pfSales')
    DROP PARTITION FUNCTION pfSales;

-- Create the Partition Function
CREATE PARTITION FUNCTION pfSales (DATE)
AS RANGE right FOR VALUES
('2013-01-01', '2014-01-01', '2015-01-01');

-- Create the Partition Scheme
CREATE PARTITION SCHEME psSales
AS PARTITION pfSales
ALL TO ([Primary]);

-- Create the Partitioned Table (Heap) on the Partition Scheme
CREATE TABLE Sales
(
    SalesDate DATE constraint ck_date check(SalesDate<'2016-01-01'),
    Quantity INT
) ON psSales(SalesDate);

-- Insert test data
INSERT INTO Sales(SalesDate, Quantity)
SELECT DATEADD(DAY,dates.n-1,'2012-01-01') AS SalesDate, qty.n AS Quantity
FROM GetNums(DATEDIFF(DD,'2012-01-01','2016-01-01')) dates
CROSS JOIN GetNums(1000) AS qty;

Qery

select count(1) from sales where SalesDate>'20160101'

Please see the execution plan below.

Execution Plan

Questions

Why SQL doing CONVERT_IMPLICIT
Why SQL is not doing the contradiction detection to avoid the table scan.

Many thanks


texts: Oracle 11g release 2 32 bit client Installation

texts:
I have installed a 32 bit Oracle 11g Release 2 (11.2.0.3) client on my Solaris 64 bit machine. But I am getting:

  Generic Status: Library Open failed, Library Name: libclntsh.so, Platform Status: ld.so.1: fatal:  libclntsh.so: Open failed: No such file or directory

Database: Oracle 11g Release 2 11.2.0.3 64 bit
Database Client: Oracle 11g Release 2 11.2.0.3 32 bit
Application SW: Documentum Content Server 32 bit
OS: Solaris Sparc 64 bit

The client and application are on same box and the database is on a different box.


texts: what does quotechar mean in mysql while importing data?

texts:
Someone sent me a xlsx excel file and I opened it with excel and saved as csv with utf8 encoding.

I use mysql workbench import wizard to import an excel-made utf8 csv file to a database table. But the imported result missed some data (less than it should have).

And I think it has something to do with the quotechar.

By default the quotechar is double quote but I have some data like this (mixing single quote and double quote):

8'10" foo bar
4" x 6" foo foo bar

I've try to omit the value but it can't (see the error from the pic).

So here I want to figure out:

What does quotechar mean here? How does it work? Why does it matter? Can't it just import everything from the csv file?
How can I import the data correctly while my data mixes single quote and double quote (later I need to retrieve them and use as search keywords, so it'd be better to keep the original form)?

my data looks like this in excel:


texts: SQL Server 2016 AlwaysOn VDI_CLIENT_WORKER command

texts:
I've setup an AlwaysOn AG on SQL Server 2016 SP1 Standard. Then I created an AG and added a database with autoseeding (synchronous mode). I used SSMS 2017 to create my AG and to add the database. Everything works fine.

But when I check the wait stats I get waits of type VDI_CLIENT_OTHER (80%) on the primary with an average resource time of 42 seconds. After some research I found out that the waits are generated by 4 sessions that execute the command VDI_CLIENT_WORKER. As I understand the wait means that a thread is waiting for work when seeding a new AG. But what I do not understand is why I have those waits because my AG is ready and why do I have 4 sessions that execute the VDI_CLIENT_WORKER command?
I found out that each scheduler has one VDI_CLIENT_WORKER

Can somebody try to explain what VDI_CLIENT_WORKER-command does and how can I solve the problem of the many VDI_CLIENT_OTHER waits?


texts: Is there a way to limit bandwidth of a backup/restore from a network location?

texts:
I have moved the location of sql server backups\restores to a file server on the network at an offsite data center.  Unfortunately now my backups/restores for the local reporting server is causing overages to the average bandwidth we pay for from the data center provider per month. Is there a way in sql server to limit bandwidth used for restores/backups?

For clarification, the offsite Data Center is the Production environment for the DB's and I am restoring db's to a local db environment for internal reporting.  DB backups are also being copied to an alternate location for D/R purposes.  The new part in the equation is the restores across the network for internal reporting.  That's what pushed me over my bandwidth limit.


texts: Help Outputing MySQL General Log to Named Pipe

texts:
I have a cPanel, Centos 7 webserver using MySQL 5.6, in which I am trying to output the MySQL general log to a named pipe I have created. I have not had any issues creating the pipe and can write to other replacement log files in the MySQL directory without issues.

However when I create a named pipe and set this as the log file (How do I output MySQL logs to syslog?) I get this error in my errorlog on a restart:

[ERROR] Could not open /var/lib/mysql/server1.log for logging (error 2). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.

I have set the owner and group of the named pipe to the mysql user and the permissions to 660 (and even 777 to be sure). These same permissions seem to work fine for a normal log file.

Any suggestions as to what I have done wrong?


texts: SQL Server Reporting Services 2017: Install Cumulative Update

texts:
SQL Server Reporting Services are no longer part of the installation media since SQL Server 2017.

How to update an existing Installation of SSRS 2017 to the most current version?


texts:
I need to build a very simple mobile survey app. The app will ask for different questions where people can give one of 3 possible answers, which will be always "yes", "no" or "maybe".

To save these questions and answers I need a simple scheme with 2 tables like:

question(id:int, question:varchar, createdAt:timestamp)
answer(id:int, questionId:int, answer:enum, createdAt:timestamp)

I'm asking myself if I should go for a good ol' MySql for that or if I should choose a NoSQL database like MongoDB.

I have following minimal requirements:

Concurrent (non locking), fast inserts should be possible
Fast read queries to do some analytics
Indexing of table data
Several millions of entries should be no problem (hard disk space is the limit :)
Backups should easily be possible
Convenient date/time functions

I think MySQL should do the trick, but I've never tried out databases like MongoDB before. The app will leverage JSON as data exchange format and I know that MongoDB plays quite well with that.

Any advice is much appreciated!

Best regards


texts:
How do you select all values when @Troop is NULL. And if there's a better way to do this all together then what would that be?

WHERE DateModified BETWEEN @BeginDate AND @EndDate
    AND
    d.CreatorLEA =
        CASE
        WHEN (@Troop IS NOT NULL) THEN @Troop
        ELSE -----SOMETHING HERE?------
        END


texts:
I have slave setup and looks like it is running fine.

But does not replicate.

My Master is an IP of my Hosting and my slave in my Remote Server.

My BD master:

Variable    Valor
File    mysqld-bin.000019
Position    120
Binlog_Do_DB    asistencias,caja,colegios,intranet,intranet2,papeletas,planillas,postulacion,resoluciones,visitas
Binlog_Ignore_DB    mysql,test,app_convoca,intranetugel,moodle_ugel04,postfix,root_cwp,roundcube

My slave:

Variable    Valor
Slave_IO_State  Waiting for master to send event
Master_Host xxx.xx.xx.xxx
Master_User esclavo_hosting
Master_Port 3306
Connect_Retry   60
Master_Log_File mysqld-bin.000019
Read_Master_Log_Pos 120
Relay_Log_File  DB-01-relay-bin.000003
Relay_Log_Pos   284
Relay_Master_Log_File   mysqld-bin.000019
Slave_IO_Running    Yes
Slave_SQL_Running   Yes
Replicate_Do_DB
Replicate_Ignore_DB
Replicate_Do_Table
Replicate_Ignore_Table
Replicate_Wild_Do_Table
Replicate_Wild_Ignore_Table
Last_Errno  0
Last_Error
Skip_Counter    0
Exec_Master_Log_Pos 120
Relay_Log_Space 621
Until_Condition None
Until_Log_File
Until_Log_Pos   0
Master_SSL_Allowed  No
Master_SSL_CA_File
Master_SSL_CA_Path
Master_SSL_Cert
Master_SSL_Cipher
Master_SSL_Key
Seconds_Behind_Master   0

Show connected slaves

Mostrar esclavos conectados
ID del servidor Servidor
1

But it does not replicate


texts:
I have a distant database to replicate having huge data but I have access only to synonyms and I would like to use Oracle export/import tool to import these data into tables of local database.

What is the best solution to do this ?

To sum up:

Distant db :Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit

Synoymns (through dblink) :

HUGE_TABLE :  about 10M rows
HUGE_TABLE_WITH_BLOBS : about 1M rows, 10GB

Local db: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit

Tables:

HUGE_TABLE : empty (to fill)
HUGE_TABLE_WITH_BLOBS : empty (to fill)


texts:
How can I make sure that a certain table is not selected as an article in a publication? So that this table is never replicated (even by error).
The replication for the database is setup by different people and one particular table should never be included in the replication. Can we make that table non selectable in the publication wizard (the step where the articles are added to the publication) or is there any way we can use the database schema to define that this table is not available as article in a publication?

Type of replication used: merge replication

Thanks!


texts: T-SQL query to merge all rows into one row

texts:
I have a T-SQL query, which pulls details from several locations (Something like below):

IF OBJECT_ID('tempdb..#mytable') IS NOT NULL DROP TABLE #mytable;

CREATE table #mytable
    ([Locno] varchar(6), [Stories] varchar(6), [Type] varchar(13))
;

INSERT INTO #mytable
    ([Locno], [Stories], [Type])
VALUES
    ('54', '2', 'TH'),
    ('65', '5', 'OB')
;

SELECT * FROM #mytable

Locno | Stories | Type
54    | 2       | TH
65    | 5       | OB

I want to bring all the rows into a single row. It's not a PIVOT or CONCATENATE, but something like below (I might have n number of locations):

Locno_1 | Stories_1 | Type_1 | Locno_2 | Stories_2 | Type_2
    54  | 2         | TH     |  65     | 5         | OB

Can someone guide me how to do the same?
TIA


texts: MYSQL: Select rows with identical values in one column, and specific values in another

texts:
I have a MySQL table with the following pattern:

| customer_id | store_id | status_id |
|-------------|----------|-----------|
| 12345       | 12       | 5         |
| 12345       | 8        | 3         |
| 12345       | 6        | 8         |
| 14567       | 5        | 3         |
| 14567       | 5        | 6         |
| 16543       | 3        | 4         |
| 19876       | 3        | 5         |
| 19876       | 6        | 8         |

Say, I want to retrieve all rows...

with identical customer IDs
which in turn have different store IDs
which all should have a status ID higher than 4

The correct output for the example table would therefore be:

| customer_id | store_id | status_id |
|-------------|----------|-----------|
| 19876       | 3        | 5         |
| 19876       | 6        | 8         |

Currently, I successfully wrote down a query to select all rows with an identical customer_id:

SELECT *
FROM table
WHERE customer_id IN (
    SELECT customer_id
    FROM table
    GROUP BY customer_id
    HAVING COUNT(*) > 1
)

This query would pick me all rows with customer_id = 12345 as well, but since one of the status_id of these rows is smaller than 4, I don't want this customer_id.

As an additional question, how would the final query look like when ported to Doctrine/Symfony?


texts: PostgreSQL CREATE EXTENSION file not found error on Mac OS

texts:
On PostgreSQL 9.2.3 when trying this:

CREATE EXTENSION btree_gist;

I get this error:

  ERROR: could not open extension control file "/opt/local/share/postgresql92/extension/btree_gist.control": No such file or directory

Running SELECT pg_available_extensions(); shows that indeed is not available.


texts: I cannot write into SQL Server 2008 R2 express edition from C#

texts:
There is no error, the dataset is getting updated, but the database is not updated.

I am using Windows authentication in connection string to the database. I have given accept changes on update.


texts: Galera node not accepting root password after restart

texts:
I have a Mariadb galera cluster running in Kubernetes with 2 Nodes.

Recently, as part of a process of redeploying the mariadb chart, one of them (mariadb-1), was restarted.

Trying to perform the following mysql -p${MYSQL_ROOT_PASSWORD} -e 'SELECT 1'; results in this: ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

The root password, as far as I can tell, was not changed as part of the redeployment process, and the other node (mariadb-0) still works fine, using the SAME password.

I did get 2 warnings during startup

2019-12-10 12:26:31 0 [Warning] WSREP: access file(/var/lib/mysql//gvwstate.dat) failed(No such file or directory)
2019-12-10 12:26:32 0 [Warning] 'proxies_priv' entry '@% root@mariadb-0' ignored in --skip-name-resolve mode.

Any ideas on what might have caused this and how to fix this?


texts: Execute `sp_spaceused` for all tables in all databases

texts:
How to execute sp_spaceused for all tables in all databases?

i.e. how to execute sp_spaceused inside sp_msforeachdb.

e.g:

 sp_msforeachdb 'sp_msforeachtable'sp_spaceused"?"''


texts: I have changed table engines from MyISAM to Aria, should I change the database engine too?

texts:
I have changed all tables in my mariadb database from MyISAM to Aria (using adminer as tool), but the database engine is still shown as InnoDB. Should I change this to Aria, too?

I did not choose InnoDB at any time. Guess it is a default value.

At the end of the tables list, the 'all together (insgesamt)' says "InnoDB":

useronline      Aria    utf8_unicode_ci
users           Aria    utf8_unicode_ci
19 insgesamt    InnoDB  utf8_unicode_ci

edit:

mysql version:

$ mysql --version
mysql  Ver 15.1 Distrib 10.0.15-MariaDB, for Linux (x86_64) using readline 5.1

and the output of SHOW CREATE TABLE albums;:

CREATE TABLE `albums` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(11) unsigned NOT NULL,
  `title` varchar(500) COLLATE utf8_unicode_ci NOT NULL,
  `text` text COLLATE utf8_unicode_ci NOT NULL,
  `created_at` datetime NOT NULL,
  `updated_at` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=Aria AUTO_INCREMENT=13 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci PAGE_CHECKSUM=1


texts: Same queries, different servers, different exec plans, stats are up to date

texts:
I've got the same query running on two different servers, and getting vastly different performances.  I have updated all of the stats on dependent objects and it has not fixed the issue.  I'm lost on where to turn to next.

Here's what the plans look like, the top beign the dex execution (the one that works), and the bottom being the test execution (the slow one).

edit:
here's what the sql looks like:

ALTER procedure [ardb].[NewProjects]
    -- Add the parameters for the stored procedure here
as
begin
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    set NOCOUNT on ;

    -- Insert statements for procedure here

;with b as
(
    select distinct
        coalesce(a.STUDY_NUMBER,b.STUDY_NUMBER, c.study_number) as StudyNumber
    from
    (
        SELECT     SUBSTRING(PAPROJNUMBER, 1, 5) AS STUDY_NUMBER
        FROM         appdb.PROD.dbo.PA01201 AS PA01201_1
        where PAPROJNUMBER>'0'
            and PAPROJNUMBER not like '%[a-z]%'
    ) a
        full outer join
        (
            SELECT     SUBSTRING(PAPROJNUMBER, 1, 5) AS STUDY_NUMBER
            FROM        appdb.MO1.dbo.PA01201 AS PA01201_1
            where PAPROJNUMBER>'0'
                and PAPROJNUMBER not like '%[a-z]%'
        ) b
            on a.STUDY_NUMBER=b.STUDY_NUMBER
        full outer join
        (
            SELECT     STUDY_NUMBER
            FROM         appdb.ptwdb.dbo.tblARCompletedStudies
            where STUDY_NUMBER>'0'
        ) c
            on a.STUDY_NUMBER=c.study_number
                and b.STUDY_NUMBER=c.study_number
)

select
    snStudyNumber as ProjectNumber,
    c.clName as CompanyName,
    q.quQuoteNumber as QuoteNumber,
    q.quQuoteID as QuoteID,
    q.quDateWon as DateWon,
    s.snPTWCompletionDate as PTWCompletionDate
from CDB.cdb.Quotes q
    inner join CDB.cdb.LineItems l
        on q.PK_quID=l.FK_quID
    inner join CDB.cdb.StudyNumbers s
        on l.FK_snID=s.PK_snId
    inner join cdb.cdb.Clients c
        on q.FK_clID=c.PK_clID
    left outer join ardb.projects p
        on s.snStudyNumber=p.prProjectNumber
    left outer join b
        on s.snStudyNumber=b.studynumber
where q.quDateWon>''
    and s.snPTWCompletionDate >''
    and p.PK_prID is null
    and l.liCreationDate>'12/31/06'
    and b.studynumber is null
union all

select CAST(ProjectNumber AS int) as ProjectNumber,
    null as CompanyName,
    null as QuoteNumber,
    null as QuoteID,
    null as DateWon,
    null as PTWCompletionDate
from history.ProjectsToProcess a
    left outer join ardb.projects p
        on a.ProjectNumber=p.prProjectNumber
where p.PK_prID is null

order by ProjectNumber desc

end

xml plans: here


texts: Moving data from staging table to production table uses up to 200GB of Log space

texts:
I have multiple staging tables containing data that needs to be moved to production tables. The process of moving this data is done within a transaction to make it possible to rollback everything in case the process fails.
The most log consuming operations are:

Deleting old data from production tables
Inserting new data into production tables

The query used to delete the old data is:
DELETE FROM PROD_TABLE WHERE CONDITION

A simple truncate is not possible since not all the data can be deleted.
The data to be deleted and the data to be moved is about 200 Million records.
Is there anyway to improve the process to reduce the log consomption.


texts: Can I restrict a SQL Server spatial field to hold values of specified spatial types and SRIDs?

texts:
I am trying to understand geography data types so I can integrate my database more closely with GIS processes. From my reading, it appears that a geography field can hold many different types of objects.

If this is the case, how do I prevent someone from loading, say, polygons into a field that I want to use to retain points, or loading points in the incorrect coordinate system? Will I have to check the data type and projection each time I write a query so I don't get erroneous results?


texts: Import 2 column CSV files to some sort of DB.... preliminary planning

texts:
Every day I have a CSV file generated by a script. It has two columns. Column 1 is a name, column 2 is the size of their mailbox.

I have a years worth of these files. I would like to be able to import them into a database (We have SQL in house, or I could install MySQL, or anything else for that matter)

I want to be able to see growth patterns for these users over time. Basic reports, which is another problem that I'll solve at a later time. For now I just want the data in a DB instead of hundreds of flat files.

What kind of DB is good for this? Simple is best. I'm not a DB guy.
What would you do? This is mostly a learning project for me.


texts:
In a multi-tenant only one DB, one column is used to isolate customers data, that is customer_id.

Is it possible to get the storage size of a certain customer in all tables?


texts: Can you search using an index while indexes are being updated in oracle11g?

texts:
I am using oracle 11g. I noticed some performance problems that causes hanging on some search operations.

Can I still search based on an index while that index is being updated in the database?


texts:
While index rebuild will require at least same amount of space in the data file as current index index (as a copy of the index will be created)

My reading on SQL Server BOL related to reorg it only involves leaf level, and only needs a single 8KB Page,

Can anybody explains how reorg works (internals) in simple technical terms..?


texts: Windows 10, SQLServer Express (2019), Excel 2016 import to SQLServer error Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine

texts:
Environment:

Workstation: 16GB RAM. 500MB SSD

Windows 10 Pro (64-bit)

Microsoft Office 2010? 2016? (Home/Student?) Not sure how to tell exactly. Office reports the version as 18.2005.1191.0 ; Excel 2016

SQL Server 2019 (Express Edition) Just installed 2 weeks ago.

Not available on workstation (though some supporting libraries may be present, e.g. .NET framework 3.5 && 4.8 for Windows 10):

Visual Studio

C# compiler

etc.
Task:

Import a USA-state-government-supplied .csv (converted by me to an Excel spreadsheet) into SQLServer.

The spreadsheet ~20 columns and ~500K rows of data to be imported into 1 fairly simple SQL Server table. No stored procedures. No constraints, 1 Primary (Identity) Key.
Microsoft SQL Server Management Studio query windows limits an insert statement to 1,000 records/rows at a time. I did load 2,000 rows successfully. I do not want to break the Excel spreadsheet into 500 pieces to load the entire data set. So, I thought it would be best to use the database Import and Export Wizard to load the spreadsheet.
Error/roadblock (steps to reproduce):

Within Microsoft SQL Server Management Studio,

I select the database.

I select Tasks > Import Data ...

I follow the wizard's prompts, selecting Data Source -> Microsoft Excel; Excel file path (path to local spreadsheet file); Excel version -> Microsoft Excel 97-2003 (or Microsoft Excel 2007-2010 or Microsoft Excel 2013 or Excel 2016); First row has column names; Next

An error is displayed:

TITLE: SQL Server Import and Export Wizard
The operation could not be completed.
ADDITIONAL INFORMATION:
The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine. (System.Data)

How can I fix this?
Additional info:

ODBC Data Source Administrator (64-bit) Drivers tab has entries for

Microsoft Excel Driver 16.00.4513.1000 (ACEODBC.DLL)

SQL Server 10.00.18362.01 (SQLSVRV32.DLL)

ODBC Data Source Administrator (32-bit) Drivers tab has entries  for

Microsoft Excel Driver 10.00.18362.01 (ODBCJT32.DLL)

SQL Server 10.00.18362.01 (SQLSVRV32.DLL)

Finally:
I saw this: https://datasavvy.me/2017/07/20/installing-the-microsoft-ace-oledb-12-0-provider-for-both-64-bit-and-32-bit-processing/
I tried the steps for Option B, but they did not change my outcome.


texts: Show Dollar Amount By Name On A Month To Month Basis

texts:
I want to show dollar amount by name on a month to month basis, but my syntax gives me an entry for each different dollar amount.  Below is what I get from my query (subset of data)

Name  Value   Month
A     200     2016-01-01 00:00:00-05
A     400     2016-01-03 00:00:00-05

However, this is what I want (ONE entry for salesman and dollar amount for each month)

Name  Value   Month
A     600     2016-01-01 00:00:00-05

What syntax will get me these results?
This is my non-working syntax:

SELECT
na.name As "Name"
,SUM(jl.dollaramt) As "Income"
,DATE_TRUNC('MONTH',jl.dateofwork) As "Month"
from workprovided jl
INNER JOIN people na
ON jl.wpid = na.personid
WHERE CAST(jl.dateofwork As Date) BETWEEN '01/01/2016' AND '04/09/2016'
GROUP BY na.name, jl.dateofwork
ORDER BY na.name ASC


texts: Update of db yields "Error Code: 1215. Cannot add foreign key constraint"

texts:
A phpmyadmin export of a remote MySQL db is used to update a local instance of the db. Disable foreign key checks is selected.  Yet running the export either as a phpmyadmin import or as a script in Workbench results in

Error Code: 1215. Cannot add foreign key constraint

Background: the schema was created by Doctrine in a Symfony 2.8 application.
The import errors out on:
CREATE TABLE `center` (
  `id` int(11) NOT NULL,
  `center` varchar(20) DEFAULT NULL,
  `county_id` int(11) DEFAULT NULL,
  `enabled` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

A standalone script that demonstrates the error, copied out of the export, is:
Edit:
If the following script is used in a db with no tables it will not error out if run 2 or more times.  Only in the context of the full db will errors be thrown.
SET FOREIGN_KEY_CHECKS=0;
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!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 utf8mb4 */;
--
-- Table structure for table `center`
--

DROP TABLE IF EXISTS `center`;
CREATE TABLE `center` (
  `id` int(11) NOT NULL,
  `center` varchar(20) DEFAULT NULL,
  `county_id` int(11) DEFAULT NULL,
  `enabled` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- RELATIONS FOR TABLE `center`:
--   `county_id`
--       `county` -> `id`
--

--
-- Dumping data for table `center`
--

INSERT INTO `center` (`id`, `center`, `county_id`, `enabled`) VALUES
(1, 'Tahoe City', 1, 1),
(2, 'Squaw Valley', 1, 0),
(3, 'Kings Beach', 1, 1),
(4, 'Soda Springs', 1, 0),
(5, 'Truckee', 2, 1),
(6, 'Incline Village', 3, 1),
(7, 'N/A', 8, 0);

--
-- Table structure for table `county`
--

DROP TABLE IF EXISTS `county`;
CREATE TABLE `county` (
  `id` int(11) NOT NULL,
  `county` varchar(15) NOT NULL,
  `enabled` tinyint(1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- RELATIONS FOR TABLE `county`:
--

--
-- Dumping data for table `county`
--

INSERT INTO `county` (`id`, `county`, `enabled`) VALUES
(1, 'Placer', 1),
(2, 'Nevada', 1),
(3, 'Washoe', 1),
(4, 'El Dorado', 1),
(5, 'Douglas', 1),
(6, 'Carson', 1),
(7, 'Alpine', 1),
(8, 'N/A', 0);

--
-- Indexes for table `center`
--
ALTER TABLE `center`
  ADD PRIMARY KEY (`id`),
  ADD KEY `IDX_40F0EB2485E73F45` (`county_id`);

--
-- Indexes for table `county`
--
ALTER TABLE `county`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `center`
  ADD CONSTRAINT `FK_40F0EB2485E73F45` FOREIGN KEY (`county_id`) REFERENCES `county` (`id`);

SET FOREIGN_KEY_CHECKS=1;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;


texts:
My query takes a lot of time, 22 seconds and above. After 22 seconds, we get more than 900 000 records. We have created indexes on the table which are used in the query:

select * from ContactMaster
where ContactId in (
    select  ContactId
    from ListContactMaster
    where ListMasterID in (816,975,945)
    and ContactId not in (
        select  ContactId
        from ListContactMaster
        where ListMasterID in (0)
    ) and (
        isactive='true'
        or isactive is null
    )
)
and isactive = 'true'
and (
    isdeleted='false'
    or isdeleted is null
)
and emailaddress not in (
    select emailaddress
    from supresscontacts
)
and ContactMaster.ContactId not in (
    select DomineSupress.contactid
    from DomineSupress
    where DomineSupress.CampaignId=1186
)

Indexes:

IX_ListContactMaster_ContactId,
IX_ListContactMaster_IsActive_ContactId,
IX_ListContactMaster_ListMasterID,
IX_ListContactMaster_ListMasterID_ContactId,
IX_ContactMaster_ContactId_IsActive_IsDeleted

The ContactMaster table has more than 50 fields.


texts: MySQL excessive tmp disk tables after Upgrade to 5.6

texts:
After upgrading two MySQL-Servers which run Master-Slave replication from Percona MySQL 5.5 to Percona MySQL 5.6 an excessive amount of temporary Tables gets created on the slave.

The Master seems to have a similar rate as before.

On the slave we went from an average of roughly 35 per second to about 1000 per second. (these are very rough averages).
We have about 3200 Queries per second on this server.

root:/var/lib/mysql# mysql -e "show global status like 'Created_tmp%_tables';" -e "show global status like 'Uptime%';"

+-------------------------+---------+
| Variable_name           | Value   |
+-------------------------+---------+
| Created_tmp_disk_tables | 1485718 |
| Created_tmp_tables      | 1505629 |
+-------------------------+---------+
+---------------------------+-------+
| Variable_name             | Value |
+---------------------------+-------+
| Uptime                    | 1523  |
| Uptime_since_flush_status | 1523  |
+---------------------------+-------+

As you can see, nearly all of the temporary tables are created on disk (~98%).
Or rather a RAM-Disk for increased performance.

The configuration in my.cnf hasn't really changed.
At most some Options have been renamed to the full length name.

Tweaking tmp_table_size, max_heap_table_size, join_buffer_size and sort_buffer_size doesn't seem to make any difference.

So my question now is what could cause this behaviour?

I've read the Upgrade notes, searched this site and generally googled this issue, but didn't come up with anything that would help me.

If I've forgotten some much needed information, please tell me.

Any help is greatly appreciatet.


texts: How to backup the current code of a stored procedure and its permissions as well?

texts:
I often get requests to alter stored procedures, in both test, development and production environments.

I would like a simple way to save the current code of the stored procedure, before the changes, into a table in my "tablebackups" database - which is a database where I save records prior to update or delete them.

I am thinking about something like this:

sp_helptext 'sp_myprocedure'

the idea of usage is something like this:

select * into tablebackups.dbo.my_procedure_20150827_1220
from ss_save_my_procedure 'my_procedure'

has someone got a solution for this already developed?


texts: SQL query to get the leaf record in a parent-child relationship

texts:
I use PostgreSQL. I have 2 tables, products and product_renewals.

Products

+----+--------+
| id |  name  |
+----+--------+
|  1 | Foo    |
|  2 | Bar    |
|  3 | Foo    |
|  4 | Foo    |
+----+--------+

ProductRenewals

+----+--------+--------+
| id | old_id | new_id |
+----+--------+--------+
|  1 | 1      | 3      |
|  2 | 3      | 4      |
+----+--------+--------+

Each time a product is renewed, a renewal record is creating with the reference to the old and the new product.

What I'd like to be able to build is a query that, given a Product ID, returns the ID of the latest product in the renewal chain. If the product is not renewed (or is already the most recent one), then that ID is returned.
What the database has to do is to walk the renewal chain from parent to child, down to the leaf.

Examples:

Case 1) Given ProductID 1, it returns 4 (4 renews 3 that renews 1)
Case 2) Given ProductID 3, it returns 4 (4 renews 3)
Case 3) Given ProductID 4, it returns 4 (4 is not renewed)
Case 4) Given ProductID 2, it returns 2 (2 is not renewed)

I would be ok if Case 3 and Case 4 returns nil (for simplicity).

I tried to build something and it seems to me I need a recursive query, but honestly I did not get a lot out of the attempts. Can you provide some guidance or reference?


texts: Create a table with the same constraints,triggers,indexes, and statistics for house-keeping table

texts:
I have a current database with few hundred tables, say, tables A,B,C... and I want to create the same tables with the same constraints,triggers,indexes, and stats as the original tables.

I know I could use generate script to do this but is there a faster way?

Do I have to rename the "new" tables with different names as well as their constraints,indexes etc?


texts:
I've got a list of ledger account numbers. These numbers go like this:

5 (main account, #5)
501 (sub account of main account 5, #01)
50101 (sub account of this account, #01)
etc.

So if I have a list and want to order this by account number, then I'd like to create this order:

5
501
50101
50102
50103
6
601
60101
60102
60103

etc.

However, any ORDER BY will give me:

5
6
501
601

etc.

So what is the proper way of working with this kind of numbering? I imagine I can get another column for ordering, but I really prefer to use the account numbers as IDs that I use for ordering, as not to make things too complicated.


texts: Insert String Value To Binary(64) Column

texts:
I am attempting to use a stored procedure to insert data into a table.  The DDL of my table is like this:

CREATE TABLE [dbo].[data1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [fname] [varchar](100) NOT NULL,
    [lname] [varchar](100) NOT NULL,
    [phone] [varchar](10) NULL,
    [company] [varchar](100) NOT NULL,
    [email] [varchar](100) NOT NULL,
    [pass] [binary](64) NOT NULL,
    [registrationdate] [datetime] NULL,
PRIMARY KEY CLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[data1] ON
GO
INSERT [dbo].[data1] ([ID], [fname], [lname], [phone], [company], [email], [pass], [registrationdate]) VALUES (1, N'Bob', N'Tomato', N'5555555555', N'Test Company', N'test1234@test.com', 0x477265656E0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000, NULL)
GO
SET IDENTITY_INSERT [dbo].[data1] OFF
GO

And this is the stored procedure that I am attempting to execute:

Declare @FName varchar(100) = 'aa'
Declare @Lname varchar(100) = 'bb'
Declare @Phone varchar(100) = 'cc'
Declare @Company varchar(100) = 'dd'
Declare @Email varchar(100) = 'ee'
Declare @Pass binary(64) = 'redsocks'

INSERT INTO dbo.appinfo (fname, lname, phone, company, email, pass)
VALUES (@FName, @Lname, @Phone, @Company, @Email, CONVERT(VARBINARY(64), @Pass, 0))

but I get this error:

  Msg 257, Level 16, State 3, Line 20
  Implicit conversion from data type varchar to binary is not allowed. Use the CONVERT function to run this query.

Is this statement not converting the value appropriately?

CONVERT(VARBINARY(64), @Pass, 0))


texts:
I have a user table and group table, and a many to many relationship between them.

I have a vote table (one to many relation between group and vote) And a subitem table (one to many between vote and subitem)

And I have a sub_item_actions table which is supposed to record which user performed what kind of action on which subitem. It has columns:

userid
grp_id
subitemid
vote_id
action type (enum)

For action type, it has the following values: delete update cast uncast

User can cast and uncast a subitem. For a user to 'belong' to a subitem , the latest cast or uncast action that the user has performed on a subitem should be of type cast. This means user has last cast on a subitem and has not uncast it yet.

I want to get all users that 'belong' to a sub item.

This is the query I am using :

select
    act.*
from (
       select sub_item_id, user_id, max(created_at) as newestAction
       from vote_sub_item_action use
       where sub_item_id = 10
         AND action_type IN ('ACTION_CAST_VOTE', 'action_uncast_vote')
       GROUP BY user_id
     ) as x
   inner join
     vote_sub_item_action as act
   on  act.user_id = x.user_id
   and act.created_at = newestAction
   and act.action_type = 'action_cast_vote'
   and act.sub_item_id = x.sub_item_id;

How should I index this table ?

I am using mysql (version 5.7)


texts: Non-standard number data types in Oracle

texts:
I'm not sure that this question belongs to this StackExchange.
I've asked this question on StackOverflow:

Besides the "usual" number data type where precision is greater then scale there are many "non-standard" number data types where scale greater then precision or where scale is negative.
For example
NUMBER(2, 5) means that there are 5 digits in the fractional part, 3
of them is obligatory zeros. NUMBER(2,-6) Here the scale is -6, which
means the value is rounded to millions and the precision is 2, so 2
significant digits can be stored. Can somebody provide examples of
using such data types in practice?

Some clarifications:
I am very interested in the motivation for using such data types (NUMBER(2, 5) and NUMBER(2,-6)).
What tasks can such a table design solve?


texts:
We have a table with tens of millions of time series rows. We index by three foreign keys to relations. Let's hypothetically say medical observation row to doctor, acl, and person. Main thing being a 3 part key where acl is based on the user and the other two based on entity relationship.

What's a good physical architecture in db2 to make this run fast? (sub second) It currently takes 10+ seconds to query.

RAM usage is around 25-30%
Read hits to buffer pool are around -1300%
Log hits to buffer pool are 100% (are we doing too many transactions when we don't need to - i.e. change to read uncommitted)

I wonder about having a separate table space for this with a separate buffer pool for example.

Also would you consider partitioning?


texts: Table frequency metric Count (minimum 2 hits every 5 seconds using a datestamp within 24 hours)

texts: I need to find out how many times a table has an insert within a 24 hour period per 5 seconds, if the table is inserted at least twice (2x) or more for each 5 seconds of the day, it should be counted. So for every 5 seconds (using a datestamp), how many times was the table inserted at least 2x  within a 24 hour period per 5 seconds.  The max the count can be 17280, and that's only if there was 2 or inserts for each 5 seconds.


texts: Any better way to rewrite the following "create table"?

texts:
I have the following table:

CREATE TABLE Employee
(
    UID VARCHAR(10) NOT NULL,
    Name VARCHAR(20) NOT NULL,
    Type ENUM('Class_A', 'Class_B', 'Class_C') NOT NULL,
    Class_A_Speed ENUM('Fast', 'Medium', 'Slow') DEFAULT NULL

    PRIMARY KEY (UID)
);

Type defines three categories of employee (think departments). For "Class A" employees, we further categorize them based on how fast they work.

My concern is that, for all "Class A" employees, they should have a non-NULL speed; the other two classes should have Class_A_Speed set to NULL.

I can add constraints but I rather avoid it. I am trying to learn and this design seems very hacky to me...


texts: Mongostat shows 2 updates on Primaries when insert data


texts: Migrating Oracle to MySQL: MySql Workbench Not Available

texts:
I am currently trying to migrate information from a query of a remote Oracle database to a local MySQL database. I have poked around and have found it has been frequently suggested to use the Migration tool in MySQL Workbench. However, I am developing through ssh and only have control of the linux cloud environment I'm working in through the terminal.

Any suggestions would be greatly appreciated.


texts: How to find cause of SQL-Server Full-Text Index Auto-Population Error?

texts:
I have recently added a few full text indices to a database and AUTO populating is not working as I would have expected.

Sample Table Definition

CREATE TABLE [wrk].[WorkOrder] (
    [id]            UNIQUEIDENTIFIER  NOT NULL,
    ...
    [customer]      VARCHAR(250)      NOT NULL,
    ...
    CONSTRAINT [PK_WorkOrder] PRIMARY KEY ([id]),
);

Sample Index Definition

CREATE FULLTEXT INDEX ON [wrk].[WorkOrder] ([customer] LANGUAGE 1033)
KEY INDEX [PK_WorkOrder] ON [DefaultCatalog] WITH STOPLIST = OFF, CHANGE_TRACKING AUTO;

Sample Data

A6A1CEE3-4AA8-41EF-B681-E24B8B5DA03C    AltaGas Extraction and Transmission LP
F0B757FB-0A48-4AB0-9411-72AAF64E677B    Caltex Resources Ltd.
D4339B95-9AEA-4C79-ACBE-9194ED8FA1D5    Rock Energy Inc.

Other Details

SQL Server 2008 R2 Standard Edition
SQL Server & SQL Full-text Daemon Launcher running as  same privileged account.
Windows Server 2008 R2 Standard 64 bit VM
Indices are built using the DefaultCatalog
Indices are using English language.
Catalog is accent sensitive.

If I look at the SQLFT logs, I am seeing that new entries typically go through the following flow:

...
2015-06-05 18:18:05.99 Informational: Full-text Auto population initialized for table or indexed view '[wrk].[WorkOrder]' (table or indexed view ID '844074293', database ID '7'). Population sub-tasks: 1.
2015-06-05 18:18:09.02 Error '0x80070005' occurred during full-text index population for table or indexed view '[wrk].[WorkOrder]' (table or indexed view ID '844074293', database ID '7'), full-text key value 'F0B757FB-0A48-4AB0-9411-72AAF64E677B'. Attempt will be made to reindex it.
2015-06-05 18:18:09.02 Informational: Full-text Auto population completed for table or indexed view '[wrk].[WorkOrder]' (table or indexed view ID '844074293', database ID '7'). Number of documents processed: 1. Number of documents failed: 1. Number of documents that will be retried: 1.
2015-06-05 18:18:10.01 A full-text retry pass of Auto population started for table or indexed view '[wrk].[WorkOrder]'. Table or indexed view ID is '844074293'. Database ID is '7'.
...

If I search for the record associated with F0B757FB-0A48-4AB0-9411-72AAF64E677B no results are returned. If I manually rebuild the index, I will be able to search the data as expected.

How do I find out why the auto-population failed (i.e., reason for retry pass failure)? Do I have something configured incorrectly to have auto-population work as expected?


texts: Very Simple SELECT Takes Too Long (300k rows, 60s)

texts:
Scenario
I have a table that holds aggregated data to support a PowerBI report.  The table is only 8 columns, and uses INT, VARCHAR and DATETIMEOFFSET types.  There's a regular PK int identity column.  There is a clustered index on the PK.
There are 300k rows
I'm on db.t3.small instance on AWS RDS (2 vCPU, 2GB RAM)
My CPU is not spiking, and the DB is not being used for anything else at all -- this server is a clone for testing.
Schema:
id: PK, int, not null
column1: varchar(16), not null
column2: varchar(50), not null
column3: varchar(50), not null
column4: int, not null
column5: int, not null
column6: datetimeoffset(7), not null
column7: int, null

Problem
Just a simple select takes a full minute, and that seems slow since there's no joining or even calculations.  It's literally just selecting those rows.
SELECT * FROM report_MyTableName

Questions

Is that a reasonable amount of time to wait?  It just seems like such a long time to wait for a relatively few/moderate number of rows.
Why does it show that there are 6 selects? there's no view or joining involved so I assumed 1 select.
Why are there 7 round trips to the server?
Is there a practical way to improve performance for this query?
I ran a query on a similar table (with 214k rows) and it took < 2s on my local SQL Server instance on my laptop.  I also had a coworker run a similar query on a different DB server (which admittedly has more horsepower) and it took < 10 seconds.

Data

If I've left out anything important let me know.  I'm happy to update.
Requested Info
Here's the output from COUNT(*)

And here's the output when I ran with STATISTICS IO, TIME
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

(306214 rows affected)
Table 'report_MyTableName'. Scan count 1, logical reads 3098, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(2 rows affected)

(1 row affected)

 SQL Server Execution Times:
   CPU time = 218 ms,  elapsed time = 42203 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

Completion time: 2021-01-28T17:06:36.4403170-05:00

Actual execution plan


texts: Auto created Statistics vs Manually created Statistics

texts:
Is there any difference between the Statistics that got created automatically by Query optimizer and the one that created with CREATE STATISTICS statement.

Will the manually created statistics get auto updated when AUTO_UPDATE_STATISTICS is ON. Is there any advantage one over another. I went through the Google and MSDN didn't get much about the difference.

Why am asking here is we are maintaining a script that we used to create statistics manually after each create table script. But in few scripts we missed to keep the statistics script so few statistics got generated automatically. Will this have an impact

References

STATISTICS
UPDATE STATISTICS


texts: Does my entity-relationship diagram reflect the relevant business rules?

texts:
I'm new to database design and I need some critiques, feedback, suggestions, advice, etc. regarding a) some business rules, b) the entity-relationship diagram I have developed to represent them, and c) whether or not they "match".

Business rules

The relevant business rules are as follow:

Users can rate multiple businesses
A business can fall under more than one category
Users can comment on multiple businesses
Businesses can have zero to many ratings
Businesses can have zero to many comments
A business can only be located in one and only one city
A city can have multiple businesses
A category can have multiple businesses
Users can have one or more checkins for businesses
Businesses can have zero to many checkins

My entity-relationship diagram

And I have represented them in the diagram below:


texts: Finding the row with the latest date for each row type

texts:
I've got an SQL database (SQLAzure  V12 version running on Azure) that looks like the following:

DECLARE @VehicleData TABLE (
    id int PRIMARY KEY,
    Registration nvarchar(50) NOT NULL,
    [DateTime] DateTime NOT NULL,
    [Event] nvarchar(20) NOT NULL,
    [Data] nvarchar(200) NULL,
    INDEX IX1 CLUSTERED(Registration, Datetime),
    INDEX IX2 NONCLUSTERED(Registration, Event, DateTime)
    )

An auxiliary table looks like this:

DECLARE @EventList TABLE (
    id int PRIMARY KEY,
    [EventName] nvarchar(20) NOT NULL
    )

The table is filled with location data taken from tracking units on multiple vehicles. Each row might have a particular [Event] type, e.g. Update, HarshBraking, Delivery etc.

I have a query that needs to get the latest date for each event for a particular vehicle BEFORE A SPECIFIED DATE.

I'm using the following query to do that:

@Registration nvarchar(20),
@DateTime datetime

SELECT V.Registration, V.DateTime, V.Event, V.Data
    FROM VehicleData V
    INNER JOIN (
        SELECT Registration, MAX(DateTime) LatestDate, Event
        FROM VehicleData
        WHERE Registration = @Registration AND DateTime < @DateTime
        AND Event in (SELECT Name FROM EventList)
        GROUP BY Registration, Event ) AS L
    ON V.Registration = L.Registration AND V.Event = L.Event AND V.DateTime = L.LatestDate

Note the line AND Event in (SELECT Name FROM EventList) was added later as it forced the query to use the IX2 index, which seemed to improve the query speed.

The query takes some time to run (around 10 seconds or so) and I would ideally like to see if I can optimise it further to take the speed down to less than a second (as it is tied to a GUI action).

How can I optimise this query further?

Edit You can download the execution plan from http://pastebin.com/9nVZzYPL. Just paste the contents into a .sqlplan file and then load it into SSMS to view the plan. Query 1 is the only thing of interest here. The other queries are "post processing" operations that don't contribute much to the overall query time.


texts:
!ERD

My question is two-fold.

First, this is my first design and I am using the Mere Mortals book to help me.  He states, "...each table represents a single, specific subject."  So should I combine the Statistics table and Designer tables into the Project table since the subject is Project?

Secondly, the two fields of WhyClientChoseUs and ClientLossReason are opposed to one another and only one of these two fields would be filled out. Is that OK?  And should this go in the Project table and not the Client table?

Thanks in advance.


texts: Start mongod instance on replica set as background job

texts:
I have setup a Mongodb replica set with 1 secondary node. When i execute on secondary

sudo mongod --replSet rs0 --dbpath /data --port 27017 --oplogSize 700

The primary and secondary nodes are linked and it works perfectly but the connection logs are running in open terminal. I would like to run the above task as background job. The option of -d doesn't seem to work here.


texts: How can I insure data transfer from a client to an SQL database server if the network connection goes down?

texts:
My scenario is likely common: Many product test stations sending test data to a centralized (master) database. (In my case the product being tested is a high frequency transceiver)
My goal: Keep the test stations running if the network connection goes down and the test stations cannot connect to the database. i.e. I need a way to save test data locally, and then to write it to the database when the test station server (the database client) can connect to the database server again. Once the data is written to the central DB server I want to delete it from the test station server.
What I'm looking for: An overall approach and the terminology I need to know so that I can find what I need to read and understand. I don't need a lot of details beyond that, but feel free if you like! :)
What I know already: SQL, SQL Server, Database Design, SSIS, SQL Server Agent.
What I've found so far: Suggestions on this site and elsewhere to use SSIS and replication. (I doubt I'll be using SQL Server. I'll most likely use an open source DB.) (Edit: From what J.D. has said I will likely try to talk my employer into paying for SQL Server once I understand how its features allow me to do what I have outlined in this question)
What I would do if left to my own devices:

Create a centralized database on a server.
Create local databases on the clients, the test stations, with the same schema as the DB server. I'd need a way to propagate schema changes from the DB server to all of the clients)
Have my hardware test application write to the local DB.
Have a process running somewhere that transfers data to the central DB, deleting the local data as it goes.

I know I can write code to do this, but I suspect that I'd be reinventing the wheel, and my wheel would likely be sub-optimal. I'll read more on replication to see if that solves my problem.
Thanks, for any help!


texts: As a MongoDB DBA, what do I need to know about the meltdown/spectre vulnerabilities?

texts:
If you didn't hear, a set of related vulnerabilities were recently discovered that impact virtually all processors sold over the last decade. You can find more technical detail about the meltdown/spectre vulnerabilities on InfoSec.SE.

As a MongoDB DBA, what do I need to understand about this?

What are the potential performance impacts? What are the proper patching guide lines? What are cloud providers doing in regards to this vulnerability?

Related Questions:

As a MS SQL DBA, what do I need to know about the meltdown/spectre vulnerabilities?


texts: script to spool all tables in a schema to xml format in Oracle 12c

texts:
I've written the following script, spooltables.sql, which I'm calling from SQLcl as $> @spooltables.sql

The aim is to loop through all tables in a given schema and output them to separate XML files.

set term off;
set feed off;
set sqlformat xml;
begin
for table_list in (select table_name from dba_tables WHERE owner='THIS_SCHEMA') loop

   dbms_output.put_line('spool /exports/'||table_list.table_name||'.xml');
   dbms_output.put_line('select /*+ parallel */* from '||table_list.table_name||';');

end loop;
end;
spool off;

This doesn't execute, it just prompts for a new line in the SQLcl terminal.

I'm not really experienced writing these Oracle loops, can anyone tell me what I need to do to get it working as required?

So I've tried the following based on the below answer:

    spool spooltables.sql
    set term off;
    set feed off;
    set sqlformat xml;
    begin for table_list in (select table_name from dba_tables WHERE owner='THIS_SCHEMA') loop
    dbms_output.put_line('spool /exports/'||table_list.table_name||'.xml');
dbms_output.put_line('select /*+ parallel / from '||table_list.table_name||';');
    end loop;
    end;
    /
    spool off;

    $> @spooltables.sql

The sql file executes but seems to execute immediately without doing anything - the file also gets emptied.


texts:
I am not able to find a translation for Mysql documentation

  http://dev.mysql.com/doc/

I read English quite well, but it is not the case for all my colleagues.


texts: Convert Ruby regex to Postgres regex, for selecting invalid email addresses

texts:
I've done some internet trawling, and some reading of the Postgres regex docs, but I'm at the stage where I think asking a question here is the best thing to do.

I have the following Ruby regex:

/\A[^@\s]+@([^@\s]+\.)+[^@\W]+\z/

Which I am trying to convert into a Postgres regex.
So far I have come up with:

'\\A[^@\\s]\+@([^@\\s]+\\.)\+[^@\\W]\+\\z'

As I found this in one article online:

  Thus, + is a metacharacter in PostgreSQL, whereas \+ is a plain “plus” character.

and this in another:

  The backslash is used to escape characters in PostgreSQL strings. So a regular expression like \w that contains a backslash becomes \\w when written as a literal string in a PostgreSQL statement. To match a single literal backslash, you’ll need the regex \\ which becomes \\\\ in PostgreSQL.

The total query I'm running is this:

SELECT email
FROM users
WHERE email !~ '\\A[^@\\s]\+@([^@\\s]+\\.)\+[^@\\W]\+\\z'

And I'm expecting it to return zero results, bit it looks like it is selecting all email addresses.

I'm expecting no results because I've used the operator !~ (I've also tried !~*), which I think returns non-matches, and because I'm pretty sure that all the email addresses in my database do match the Ruby regex I pasted at the top of this question. (This in turn is because we use Devise in a Rails app to validate all user emails, with the default email validation settings)

So I've come to the conclusion that I've not converted my regex format/syntax correctly for Postgres, but also that I don't know what else I'm missing. Please can you help me understand whether this is possible, and if so, what else I need to change.

Once I have successfully translated that regex, I have a second Ruby regex which I also want to translate into Postgres, which is this one:

/\A[a-zA-Z0-9.!\#$%&'*+\/=?^_`{|}~-]+@[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?(?:\.[a-zA-Z0-9](?:[a-zA-Z0-9-]{0,61}[a-zA-Z0-9])?)*\z/

This is because I want to find all the user emails that don't have a valid email address according to the spec here: https://html.spec.whatwg.org/multipage/input.html#valid-e-mail-address

Thanks so much in advance


texts: Oracle case insensitive passwords seemingly ignoring SEC_CASE_SENSITIVE_LOGON

texts:
I'm trying to change the SYS password of an Oracle 11g database to be case-insensitive for legacy application reasons. My research suggests all I need to do is set the SEC_CASE_SENSITIVE_LOGON flag, and change the SYS password so it will become case-insensitive.

I connect via SQL*Plus with the current password, and make these changes:

C:\Users\KWalsh>sqlplus sys/PASSWORD@database3 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 5 11:19:12 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> ALTER SYSTEM SET SEC_CASE_SENSITIVE_LOGON = FALSE;

System altered.

SQL> alter user sys identified by password;

User altered.

SQL> exit;
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Pr
oduction
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\KWalsh>

Now I try reconnecting with the initial, ALL-CAPS password. I believe it should allow this with case sensitivity disabled, but this happens:

C:\Users\KWalsh>sqlplus sys/PASSWORD@database3 as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Wed Mar 5 11:23:07 2014

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

ERROR:
ORA-01017: invalid username/password; logon denied

Enter user-name:

The lowercase password permits access to the database. Any ideas what I'm doing wrong?


texts: Mariadb Galera Cluster using Xtradb backup is not working

texts:
I am trying out the first test of MariaDB Galera cluster setup without success.

This is the error in the log

innobackupex: Created backup directory /tmp
tar: -: Cannot write: Broken pipe
tar: Error is not recoverable: exiting now
innobackupex: 'tar chf -' returned with exit code 2.
innobackupex: got a fatal error with the following stacktrace: at /usr//bin/innobackupex line 4882
        main::backup_file_via_stream('/tmp', 'backup-my.cnf') called at /usr//bin/innobackupex line 4931
        main::backup_file('/tmp', 'backup-my.cnf', '/tmp/backup-my.cnf') called at /usr//bin/innobackupex line 4955
        main::write_to_backup_file('/tmp/backup-my.cnf', '# This MySQL options file was generated by innobackupex.\x{a}\x{a}# T...') called at /usr//bin/innobackupex line 3762
        main::write_backup_config_file('/tmp/backup-my.cnf') called at /usr//bin/innobackupex line 3689
        main::init() called at /usr//bin/innobackupex line 1566
innobackupex: Error: Failed to stream '/tmp/backup-my.cnf': 2 at /usr//bin/innobackupex line 4882.

Joiner my.cnf

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]
datadir=/var/lib/mysql
log_error=/etc/log/mysql/mysql.err
log_bin=/etc/log/mysql/mysql-bin.log
slow_query_log_file=/etc/log/mysql/mysql-slow.log
# this is only for the mysqld standalone daemon
[mysqld]
user=mysql
innodb_log_file_size=100M

# * Galera-related settings
#
[galera]
# Mandatory settings
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://1.1.1.1 #master
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0
wsrep_cluster_name=rms
wsrep_node_address=1.1.1.2
wsrep_node_name=slave
wsrep_sst_method=xtrabackup
wsrep_sst_auth=sst:sst
# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.0 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.0]

Donor my.cnf

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]
datadir=/var/lib/mysql
log_error=/etc/log/mysql/mysql.err
log_bin=/etc/log/mysql/mysql-bin.log
slow_query_log_file=/etc/log/mysql/mysql-slow.log

wsrep_sst_receive_address=1.1.1.2
# this is only for the mysqld standalone daemon
[mysqld]
user=mysql
innodb_log_file_size=100M
#
# * Galera-related settings
#
[galera]
# Mandatory settings
wsrep_provider=/usr/lib64/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://
binlog_format=row
default_storage_engine=InnoDB
innodb_autoinc_lock_mode=2
bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0
wsrep_cluster_name=rms
wsrep_node_address=1.1.1.1
wsrep_node_name=master
wsrep_sst_method=xtrabackup
wsrep_sst_auth=sst:sst

# this is only for embedded server
[embedded]

# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]

# This group is only read by MariaDB-10.0 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.0]

I have been trying this without success from past 10 hours, please any help is appreciated.
Using CentOS 6

When I execute this in donor it's working fine [to test xtrabackup] innobackupex --user=sst --password=sst /tmp/


texts:
I have 2 tables:
users:
id
------
1

permissions:
id | action | userId
---|--------|--------
 1 |  read  |    1
 2 |  write |    1

When I run the following query (return users with its permissions):
select
    users.*,
    (string_agg(permissions.action::text, ',') as permissions
from
    "users"
join "permissions" "permissions" on
    "userId" = "users"."id"
    and "userId" = 1
group by
    "users"."id"
order by
    "users"."id" asc
limit 1

I get:
id | permissions
---|-------------
 1 |  read,write

Which is great (this is what I need) but I expected it to return:
id | permissions
---|-------------
 1 |  read

Because of the query has limit 1 constraint.
Anyone can shed some light on what happens in the background?


texts: Is there a way to track page splits on an InnoDB table?

texts:
Is there a way to track the number of page splits on an InnoDB table? I've inherited a couple high-volume tables with integer primary keys in which values are often inserted out of order and I'd like some indication of how much my insert performance is suffering due to page splits from out-of-order inserts. I know SQL Server exposes metrics on that, but I've been unable to find anything in the MySQL docs about that.

Here's the schema for the table with the highest insert volume. Even though the PK is defined as AUTO_INCREMENT, the keys are manually inserted out of order:

CREATE TABLE `data1` (
`data1_id` bigint(20) NOT NULL AUTO_INCREMENT,
`uuid` char(36) DEFAULT NULL,
`headline` varchar(255) DEFAULT NULL,
`status` varchar(10) DEFAULT NULL,
`starred` tinyint(1) DEFAULT '0',
`invalid` tinyint(1) NOT NULL DEFAULT '0',
`reason_invalid` varchar(255) DEFAULT NULL,
`created_by` bigint(20) NOT NULL,
`associated_team_id` bigint(20) DEFAULT NULL,
`calculated_result` bigint(20) DEFAULT NULL,
`created_at` datetime NOT NULL,
`updated_at` datetime NOT NULL,
`version` int(11) NOT NULL,
`updated_from_legacy` tinyint(1) DEFAULT '0',
`customer_id` bigint(19) DEFAULT NULL,
`result_description` varchar(16) DEFAULT NULL,
`sort_date` datetime DEFAULT NULL,
PRIMARY KEY (`data1_id`),
KEY `ix_data1_createdBy_16` (`created_by`),
KEY `ix_data1_calculated_result_17` (`calculated_result_id`),
KEY `created_at` (`created_at`),
KEY `updated_at` (`updated_at`),
KEY `headline` (`headline`),
KEY `starred` (`starred`),
KEY `status` (`status`),
KEY `fk_data1_customer_id` (`customer_id`),
KEY `result_description` (`review_disposition`),
KEY `associated_team_id` (`associated_team_id`,`status`,`invalid`),
KEY `sort_date` (`sort_date`),
KEY `associated_team_id_2` (`associated_team_id`,`sort_date`,`status`,`invalid`),
CONSTRAINT `fk_data1_customer_id` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`customer_id`),
CONSTRAINT `fk_data1_createdBy_16` FOREIGN KEY (`created_by`) REFERENCES `users` (`user_id`),
CONSTRAINT `fk_data1_calculated_restult_17` FOREIGN KEY (`calculated_result_id`) REFERENCES `calculated_results` (`calculated_result_id`),
CONSTRAINT `data1_ibfk_1` FOREIGN KEY (`associated_team_id`) REFERENCES `teams` (`team_id`) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8


texts: SQL Server database level roles for creating tables

texts:
Is there a way to create or change a special role for creating tables? Our software developer team has db_datareader and db_datawriter roles but they can not create new tables.

I read this article related to database level roles. I don't want to grant db_ddladmin role because there is too much permissions in this role.

So my question is how can i give creating and deleting tables permissions for a database? Views, procedures and functions are okay too. All I need is I don't want to grant permissions related to security, login or backup operations.


texts: Is there a limit to the number of database connections from a single machine in PostgreSQL?

texts:
We are planning on setting up multiple front ends behind a load balancer.

I think our database can handle the load because I'm currently measuring a database TPS of about 450  for approximately 42000 web requests (93000 transactions).

Our requirements are 278 messages/second average for the front end. I can only get around 92 non-idle queries running in the database, at a given time, and I was wondering if there's some limit to the number of connections from a single machine.

I have tried upping various connection pools and thread pools.  I was able to up the query count from 33 to 92, but I'm unable to get higher.  I am not even sure how many queries from a single machine are reasonable.  I read connections should be (2 * core_count) + effective_spindle_count.

I have been getting errors from the front end stating that it failed in getting a connection ("The connection attempt failed"), but I don't know yet if this is a failure from the front end (by hitting a limit) or the database (a configuration on the database).

I believe the max # of connections for the database is 2000 last I checked.

Is there another limit to the number of connections the database will accept from one machine?

The database is running Linux 2.6.18 on x64.

I guess I'm also wondering if the number of connections is limiting.  I don't think it is, but we did get the connection attempt failures.  The front end is running at 300% CPU (8 processors) and 25% memory. I would think we could get more bang out of both our front end and our database, but with two test servers, we normally get 250 tests/second (maxed out at 254/s for one server and 334/sec for the other ).


texts:
I have the following tables in postgres

 ChartOfAccounts
| Id          | int     |
| AccountName | varchar |
| ParentId    | int     | FK self references Id
| Level       | int     | Check constraint Level >= 1 && Level <= 4

 Transactions
| Id          | int     |
| Amount      | int     |
| AccountId   | int     | FK references Accounts(Id)

The Chart of Accounts is a tree structure four levels deep, and a transaction will only ever reference the fourth level.

I need to be able to find the balance (sum of all trasaction amounts) of any or multiple accounts in the Chart of Accounts, no matter what level.

I've been able to come up with specific queries that will find the balance of a 4th level account and another for the 3th level, but not one that I can use for any level.

I also wonder if this query could be too slow when you have hundreds of thousands or even millions of rows in the Transactions table, but I'm guessing as long as its well indexed it will be ok.

edit:

The user can only add level 4 accounts, levels 1 to 3 are locked in and can't be changed.

I want to be able to query these balances by account_id, heres the query I currently have for level 3 account balance.

SELECT a.parent_id, SUM(t.amount) balance
FROM transaction t
JOIN account a ON t.account_id = a.id
WHERE a.parent_id IN (1, 4, 7) -- IDs of the accounts which I want the balance
GROUP BY a.parent_id;

The expected output is

| account_id | balance |
| 1          | 1500    |
| 4          | -2500   |
| 7          | 550     |


texts: Summing the count of two columns in postgres that contain the same value

texts:
I want to sum the total number of occurrences of one value across two columns in the same table.
So, an example would be:

id |node1  |node2
1  |  111  |   123
2  |  122  |   124
3  |  111  |   125
4  |  122  |   111
5  |  124  |   111
6  |  126  |   111

So in this case I want to get the following result:

node   | node_count
111    |     5
122    |     2
123    |     1
124    |     2
125    |     1
126    |     1

Additionally, I want to only include the nodes that have a count > 1 so my final result would be:

node   | node_count
111    |     5
122    |     2
124    |     2

I didn't think this would work but I've tried the following on the table:

SELECT count(node1+node2), node1 as node
FROM table1
WHERE node1 = node2
GROUP BY node1
HAVING count(node1+node2) > 1;

So I then tried creating a temporary table so that I could use the WHERE clause as follows

SELECT count(table1.node1+tableTemp.node2), table1.node1
FROM table1, tableTemp
WHERE table1.node1 = tableTemp.node2
GROUP BY table1.node1
HAVING count(table1.node1+tableTemp.node2) > 1;

But this only seems to return the count of node1. I have also tried the variation of count(table1.node1) + count(tableTemp.node2) but this doesn't work. I've also tried using a combination of SUM and COUNT-sub-queries to no avail.
Can anyone point me in the correct direction? Cheers.