texts:
I have a carts table with items hstore column. An example entry in this column is:

carts.items row#1 = {
                     "1614" => {:quantity=>"100", :price_cents=>1655},
                     "1938" => {:quantity=>"50", :price_cents=>1955},
                     "1983" => {:quantity=>"100", :price_cents=>2255},
                     "1322" => {:quantity=>"10", :price_cents=>4455},
                     "1691" => {:quantity=>"25", :price_cents=>1055},
                     "1734" => {:quantity=>"20", :price_cents=>1255}
                    }

carts.items row#2 = {"1614"=>{:quantity=>"50", :price_cents=>1655}}

So my carts table would look like this:

    id   | items
---------+-------
    1    |   {"1614"=>{:quantity=>"100", :price_cents=>1655}, "1938" => {:quantity=>"50", :price_cents=>1955},"1983"=>{:quantity=>"100", :price_cents=>2255},"1322"=>{:quantity=>"10", :price_cents=>4455},"1691"=>{:quantity=>"25", :price_cents=>1055},"1734"=>{:quantity=>"20", :price_cents=>1255}}
    2    |   {"1614"=>{:quantity=>"50", :price_cents=>1655}}

You will notice that there is one duplicate id (1614) in the hash, but its quantity is different.

I want to write a query that will return a table with the item id counts and the total quantity. It should look like this:

 item_id | count | total
---------+-------+------
   1614  |   2   |  150
   1938  |   1   |  50
   1983  |   1   |  50
   1322  |   1   |  100

Here is the query that I am working with:

SELECT
skeys(carts.items) as item_ids,
COUNT(*) AS count,
svals(carts.items) as items
FROM carts
GROUP BY
skeys(carts.items),
svals(carts.items)

It returns:

 item_id | count | total
---------+-------+------
   1614  |   1   |  {:quantity=>100}
   1614  |   1   |  {:quantity=>50}
   1938  |   1   |  {:quantity=>50}
   1983  |   1   |  {:quantity=>50}
   1322  |   1   |  {:quantity=>100}

I aslo have tried:

SELECT key, count(*) FROM
  (SELECT (each(items)).key FROM carts) AS stat
GROUP BY key
ORDER BY count DESC, key;

Which gives me this:

item_id | count
---------+-------
   1614  |   2
   1938  |   1
   1983  |   1
   1322  |   1


texts: SQL Help - Exclude data with just one distinct value

texts:
I have the following query:

select agentname,CallCenterID, count(leadid) Dials
from LeadCallHistory
where agentname not in ('vdad','system')
    and cast(callCenterStartTime as date) between cast(getdate()-1 as date)
        and cast(getdate()-1 as date)
group by agentname, CallCenterID

What is the best way to exclude rows of data where a CallCenterID field only has 1 unique Agentname?  For example if the query were to return:

agentname    CallCenterID   Dials
jjones       999            100
jjones       1000           500
crobin       1000           1000
jjones       1001           500
crobin       1001           500
swhite       1002           1000

In this example I would like to exclude the data for CallCenterID's 999 and 1002 because they only has 1 unique agentname


texts:
I'm running a scheduled job using pgAgent on PostgresSQL 9.5. The duration of this job seems to grow over subsequent runs, and this slow down doesn't appear to be a direct relationship to the amount of data processed each time.

The job itself is a function, which opens a cursor and records results in another table. The function always processes one hour of data, but that hour may contain more or less records depending on the time of day.

My question is, is there any resource management I need to do when creating a pgAgent job? In other words, does anything persist between runs that should be cleaned up by the developer or is each instance of the job completely sandboxed?


texts:
I have the following tables

Item

ID
Name

Item taxes

ID
percent
item_id

An item can have more than one tax

I want to write a query that will give me all items with no tax. This query needs to be as fast as possible.

I was thinking

select id from Item where Id NOT in (select item_id from Item_taxes)

Is this the best way?


texts: Is it safe to add a new node to a Cassandra cluster while a repair is running?

texts: I'm getting ready to expand an existing Cassandra cluster.  I have repairs scheduled to run on a reoccurring basis.  Do I need to disable repairs when adding a new node to a cluster, or can I bootstrap new nodes while repairs are running elsewhere in the cluster?


texts:
Is there a way to restore ONLY a differential backup on SQL Server?

I have a situation where I only need to view one table that was truncated from an entire database.  And instead of trying to restore the entire massive database in it's entirety, I wanted to try and restore only the differential and check to see the condition of that table.


texts: how to access my password in SSIS 2005 by VB.NET

texts:
A VB.NET component delivered following message:

  Login failed for user MyDBUser

It is about an OleDbConnection.

btw The user works fine togehter with SQL Server Management Studio.

Retaining a password via the connection manager does not work even with enabling Save my password.
In my case, it is not a security issue to retain the password because the SSIS package will not be published for others.
Hence, what's the fastest way -concerning the VB.NET code- to enable an access to the (SQL Server) password?

EDIT:
Workaround: A SSIS variable was attached to the scope of the required control flow.
Me.readVariable was applied to concat the connection string.


texts: How do I perform a batch update on the first n matching rows?

texts:
I need to perform a large number of updates at the same time.  Each update needs to only affect up to a specific number of rows even though the WHERE clause might match to many more rows.  So far, I'm accomplishing this with 'LIMIT' and submitting each query individually.  My queries look like this:

UPDATE data SET c=1 WHERE c=2 AND g=8 LIMIT 1
UPDATE data SET c=2 WHERE c=4 AND g=2 LIMIT 1
UPDATE data SET c=4 WHERE c=3 AND g=5 LIMIT 1
...

This accomplishes exactly what I need, but becomes very slow when the number of updates reaches hundreds or thousands (and the table data is even bigger).  How can I make this faster?

I thought about doing something like this:

CREATE TABLE changes (c_old INT, c_new INT, g INT)

INSERT INTO changes
    (c_old, c_new, g)
VALUES
    (2,1,8),(4,2,2),(3,4,5)...

UPDATE data
INNER JOIN changes ON
    data.c=changes.c_old,
    data.g=changes.g
SET
    data.c=changes.c_new

DROP TABLE changes

Because four queries are probably faster than thousands even though the second query might be very long.

The first problem with that is that it doesn't allow me to accomplish what I was doing with the LIMIT.  This changes every match rather than just the number I need changed.

The second problem is that it just feels messy to create and drop a table every time, but that's something I could live with.

Any ideas?  How can I make this faster?  Is there really no faster way to do this particular thing than submitting each update individually?


texts: Automatic database file creation based on database file size in SQL Server 2008 R2

texts:
Now i am using SQL Server 2008 R2 database for data logging. The name of the database profile is MASTERDB. That profile has one data file called MASTERDB.mdf and one log file called MASTERDB_log.ldf. In this database data are logging in every seconds. Here i wants to maintain
the data file size as 4GB. If the size excited the limit then i wants to create one new data file under the same profile. Is there any possible options  available in SQL Server 2008 R2?.


texts: MySQL Master Slave replication error 1032

texts:
I have enabled MySQL Master-Slave replication, but unfortunately getting the following error.

Last_SQL_Errno: 1032
Last_SQL_Error: Could not execute Update_rows event on table XXXXXXX; Can’t find record in ‘txn_re’port_accrual_redemption, Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log mysql-bin.000119, end_log_pos 12537772


texts: Postgres speed up index creation for large table

texts:
I have a large Postgres table with 2+ billion entries (1.5TB) and mostly non-null, char var columns. To speed up inserts, I dropped the indexes before bulk uploading. However, it is now taking forever for the b-tree indexes to be created. For one of the runs that I cut short, it had spent >12 hours creating the indexes.

Sample table and indexes that I'm trying to make:

        Column         |            Type             | Modifiers
-----------------------+-----------------------------+-----------
 name                  | character varying           | not null
 id                    | character varying           |
 lifecycle_id          | character varying           |
 dt                    | character varying           |
 address               | character varying           |
 ...

Indexes:
"name_idx" PRIMARY KEY, btree (name)

"id_idx" btree (rec_id)

"lifecycle_id_idx" btree (lifecycle_id)

The actual table has 18 columns. I've set the maintenance_work_mem to 15GB. This is running on Postgres 9.6.11 on RDS. The instance class is db.m4.4xlarge.

Since there are three indexes, it would be hard to sort the data before inserting. Would it be faster to just insert the data without dropping the indexes? Any other suggestions for speeding up the index creation?


texts: return percentage of records based on value in column

texts:
I'm working on a sql query that needs to get a percentage of the records based on the value of a particular column.  The percentage is given by the user.  A stripped down version of the query is below

SELECT OrganizationID,
       CustomerID,
       OrderID
  FROM x

The output would look something like

 OrganizationID, CustomerID, OrderID
  123             abc         p456
  123             cba         p457
  123             bac         p458
  456             def         p987
  456             efd         p988

I need to be able to pull say 50 percent of the output for each organizationid. So in this example I'd come back with 2-3 records.  Which records doesnt matter as long as the correct percentage of the records is pulled.

Any ideas on how to go about this?


texts: Single Domain Controller for SQL Server Always On Availability Group

texts:
I was wondering if SQL Server Always On Availability Group supports a single Domain Controller server as opposed to 2 Domain Controllers in every example I've seen. I ask because I suspect that this might be a problem with my HA installation of SQL Server where I use a single Domain Controller.

Azure DSC Extension. HKLM:\SOFTWARE\Microsoft\PowerShell\3\DSC doesn't exist and
Can't create Windows Server Failover Cluster. Static address was not found on any cluster network


texts:
Hi I have written this query as per my project requirement. But it is generating wrong output.
select (to_char((33)/nullif((17400/3600),0),'FM99,999,999'))::character varying as userdatas

currently it is generating 8 as output. But according to calculation it is different,
= 33/(17400/3600)
= 33/(4.8333333333333)
= 33/4.8333333333333
= 6.8275862068966
It should display 7
Can anyone help me with this issue ?


texts: Dimension Help - Deciding Fact or Dimension

texts:
We have the following Dim and Facts:

Customer Dim:  SCD Type 2, Info about the customer ie first purchase date, name, address, etc
Product Dim:  SCD Type 2, about our products

Customer Snapshot Fact:  Monthly Fin facts about the Customer
Product Sales Fact:  Sales by Customer

and will have many more facts that involve the customer Dimension.

We have a legacy DB that was collecting 100's of data fields about a customer and have been asked to DW this data.   There are 100 plus fields related to a customer and may of them are flags that indicate if the customer qualifies for something or not.    The majority of the queries the users will want against any of our fact table may include the filtering and or grouping of these indicators.

The question is should we add 100 plus indicators to our Customer Dim and if not how should the data be structured so this information can be joined with all of our other facts.

Thanks for your help.


texts: Does PostgreSQL optimize adding columns with non-NULL DEFAULTs?

texts:
When adding NOT NULL columns with a DEFAULT value - does PostgreSQL optimize this operation?

In case the table has n rows, a not-optimized alter-table-add-column would yield n writes of the default value - which could be very painful, obviously. With the optimization the DB would instantaneously create the new column, store just one copy of the default value which would be returned when no non-default value is found for that column in a suitable index data structure.

For example Oracle 11g has such an optimization.


texts: Disabling Checking of Schema On Function/Stored Procedure Creation

texts:
I'm trying to automate the process that executes changes to the SQL Server 2008 R2 database.  The process I put in place drops and recreates my stored procedures and  functions, as well as run scripts to change the tables/columns/data.  Unfortunately, one of the scripts requires one of the functions to be put in place first.  But I can't run all stored proc/function changes first because it relies on columns being added from the tables/columns/data change scripts first.

I was wondering if it was possible to run stored procedures and functions without SQL Server validating the columns used in the definition of the function/SP?  I tried looking but couldn't find a condition or command to enable this.


texts:
We have a table with 200k rows where we change some flags multiple times per day.
If i have understood correctly an UPDATE on postgres is a DELETE+INSERT on disk.

I'm wondering if this is highly inefficient for our use case. What if each tuple has a big size? Are the entire tuples written again?

I was thinking to move those status flags on a separate table so that we rewrite only small tuples reducing useless i/o. Is this a correct approach or i'm on the wrong track?


texts: Modifying initial values for an AUTO_INCREMENT column

texts:
I am using MYSQL Workbench, and I have a table called Customer and have a primary key customer_id which has the INT data type and is auto incremented ("AI" in Workbench column specifications).

This is the DDL code that workbench generates:

ALTER TABLE infrastructure.mfg
CHANGE COLUMN
 mfg_id mfg_id INT(10) UNSIGNED
NOT NULL AUTO_INCREMENT

My intentions:

I want to start the numbering at "1000" and auto increment by 100.
I want the start number to be for only this table

For example:

1st = 1000
2nd = 1100
3rd = 1200
4th = 1300

How can I do this? Do I use these functions and if so how?

LAST_INSERT_ID()
mysql_insert_id()

If this is not an option can I check the "G" rather than the "AI"?

If I check "G" the MySQL script that is generated is:

ALTER TABLE infrastructure.mfg

CHANGE COLUMN mfg_id
 mfg_id INT(10) GENERATED ALWAYS AS () VIRTUAL

Where is there information on how to use this?


texts: Flexible and robust way to cull older data?

texts:
I am no DBA and that is becoming more apparent daily, as such I am having trouble keeping my query times reasonable. I have about 3.57 Million records ( at about 3.43 GB ) and it takes about 2 minutes to run simple queries. I have done the simple things that I know to do like indexing but most of the data I don't need to keep around and so I was thinking I could have a field called 'days2keep' in which certain entries that I need to keep for longer could be noted in the record itself. I would then have a cron job run daily or weekly to cull any records older than the 'days2keep' field. I have looked around and I have not seen any references to a similar idea. Does it make sense to do it this way or are there pitfalls that I am unaware of?

FWIW: I don't think that it matters but I am considering switching from MySQL to PostgreSQL.

update

After looking around it seems like the more common approach is to do horizontal partitioning of the data but I only need to keep some records for much longer so it seems like my original solution would be best. I am just a little wary of spending the time implementing it without some feedback from more experienced users.

TODO

Thanks to everyone for the insights below. The biggest take away here is that I still have a LOT to learn and understand so here are the steps I plan to take:

1) Analyze my queries to make sure I understand why they are taking so long.
2) Look into multi-column indexing to see if that might solve my problem.
3) Figure out the type of partitioning I might consider (horiz. not an option).
4) Figure out which solution keeps the database management and complexity to a minimum (esp. in regards to backup/restore and replication to dev space). I am using in Django so up until now it has been fairly easy to manage (for a non-DBA person).
5) Pick a solution and implement
6) Ask for a raise
7) drink beer


texts:
How to mark a line comment in mongodb configuration file?

cpu=true #temporary for dev env

Is this the right way?

There is no clue about it in their website page about setting the configuration options.


texts: Does this regex reliably remove square brackets from T-SQL?

texts:
I'm using SQL Server Management Studio 17.4, which supports search-and-replace using a Microsoft version of regular expressions (regex).

I need a regex expression that can reliably remove square brackets from source code, such as large CREATE TABLE statements that have been scripted from existing an database.

I'm currently using this regex in the "find" dialog:

\[((?!\d+)(?!PRIMARY))(([A-Z]|_|[0-9])*?)\]

The "replacement" is $2.

It seems to work very well, however I'm concerned this may eliminate required square brackets under some conditions.

In the regex above, the (?!PRIMARY) piece ensures ON [PRIMARY] does not have the square brackets removed.  Are there other exceptions I need to be aware of?

I'm willing to exclude the possibility of column names being reserved words, such as [GROUP].  The environment I work in has specific naming convention that typically eliminates that possibility.


texts: Boot page flag for cleanly shut down database

texts:
I have been watching the SQL Server: Advanced Corruption Recovery Techniques by Paul Randal on Pluralsights. During module 4, he discusses the fact that if you have a missing log file it is important to know if the database was shutdown cleanly or not. This would determine if SQL Server will run crash recovery or not.
He goes on to mention, that the flag for this can be found in the database boot page (1:9 is my assumption based on the course materials and backed up through this link on microsoft). However, I do not see any obvious flags inside the page using -
DBCC TRACEON (3604);
DBCC PAGE ('stack',1, 9, 2) WITH TABLERESULTS;
DBCC TRACEOFF (3604);

In looking for this online, I am not able to find the information about the Boot page (1:9). I was able to find that sys.databases has a column for is_cleanly_shutdown which stands for:

1 = Database shut down cleanly; no recovery required on startup
0 = Database did not shut down cleanly; recovery is required on startup

However, I would like to follow along with the class, can someone advise what I am looking for in the boot page? Also, how to get the database to show it was cleanly shutdown or the proper steps to do this?
I am running SQL Server 2012.


texts: In SQL Server 2017, after applying Cumulative Update 7, there's a new system stored procedure in master called sp_restore_filelistonly. What's it for?


texts: Optimal query pattern to count related entries in PostgreSQL

texts:
Assume I have the following table schema and data

create table categories(
    id integer primary key,
    parent_id integer
);

insert into categories
values
    (1,0),
    (2,0),
    (3,1),
    (4,1);

I need to count the number of children for each entry, i.e. the result should be

 id | parent_id | count
----+-----------+-------
  1 |         0 |     2
  2 |         0 |     0
  3 |         1 |     0
  4 |         1 |     0

The following query produces the correct result but I'm worried about the performance. I guess that it loops through the table as many time as there are rows in it.

select *, (select count(*) from categories b where a.id=b.parent_id) from categories a

It is possible to optimize it?


texts: SELECT JOIN same table prevent duplicates MySQL 8.x

texts:
I have a table called 'price' with the following rows
idRequest | idPair | idExchangePlatform | lastPrice
    1         1               1               95
    1         1               2               100
    1         1               3               40
    2         1               1               80
    2         1               2               250
    2         1               3               10
    3         1               1               110
    3         1               2               300
    3         1               3               60
    1         2               2               800
    1         2               3               1300
    2         2               2               1110
    2         2               3               1950

i need to make a SELECT QUERY WHERE idRequest and exchangePair match with the same table values but different idExchangePlatform
For example for this set of values:
idRequest | idPair | idExchangePlatform | lastPrice
    1         1               1               95
    1         1               2               100
    1         1               3               40

i need to get the following output:
idRequest | idPair | idEP_a | idEP_b | lastPrice_sum
    1         1         1       2           195
    1         1         1       3           135
    1         1         2       3           140

i'm using this query but it duplicates the result because is matching the idExchangePlatform in twisted order.
select p1.idRequest, p1.idPair, p1.idExchangePlatform AS idEP_a, p2.idExchangePlatform AS idEP_b, (p1.lastPrice + p2.lastPrice) AS lastPrice_sum
from price as p1
JOIN price AS p2
ON p1.idRequest = p2.idRequest AND p1.idPair = p2.idPair AND p1.idExchangePlatform <> p2.idExchangePatform;

This is the output i get from this query:
idRequest | idPair | idEP_a | idEP_b | lastPrice_sum
    1         1         1       2           195
    1         1         1       3           135
    1         1         2       3           140
    1         1         2       1           195
    1         1         3       1           135
    1         1         3       2           140

Is there a way to prevent this with some CLAUSE?


texts:
How to write an insert trigger for:

"If the client wants to pay by using a credit card, its details need to be entered in the record. If a credit card is not being used it must be ensured that the credit card details are blank."

create table  Management.PaymentMethods
(
    PaymentMethodID int CONSTRAINT PMID PRIMARY KEY identity(1000,24),
    Description varchar(22) constraint chkTitle check
        (Description in ('cash','cheque','credit card'))
)

Create table Management.Payments
(
    PaymentID int CONSTRAINT PKIDD PRIMARY KEY identity(100,45),
    EventID int constraint EVId
        foreign key(EventID) references Management.Events(EventID),
    PaymentDate date not null,
    StartDate date not null,
    CONSTRAINT PDK  CHECK (PaymentDate <= StartDate),
    check(PaymentDate > Getdate()),
    PaymentMethodID int constraint Phg
        foreign key(PaymentMethodID) references Management.PaymentMethods(PaymentMethodID),
    CreditCardDetails varchar(100),
    ExpiryDate date constraint PD check(ExpiryDate > Getdate()),
    ChequeNo int,
    ChargePerPerson int constraint chkk check(ChargePerPerson > 0),
    NoOfPeople int not null,
    PaymentAmount as ChargePerPerson * NoOfPeople
)


texts:
I have a Python uWSGI app running behind Nginx using pymongo to communicate with a 4 machine replica set running Mongodb 2.6.3.

The replica set is configured using domain names for each machine mdb-0[1-4].domain.com.

I have set up MMS Monitoring to monitor the replica set and I am getting the following errors from it:

[2014/07/25 16:56:39] [monitoring.handler.logs-mdb-0[1-4].domain.com:27017.error] [monitoring-agent/components/bus.go:486] Failure dialing `mdb-0[1-4].domain.com:27017` for log handler. Trying again after 8s.
Failure on MongoDB dial to `mdb-0[1-4].domain.com:27017`. Err: `no reachable servers`
    at monitoring-agent/components/conf.go:138
    at monitoring-agent/components/bus.go:282
    at monitoring-agent/components/bus.go:315
    at monitoring-agent/components/bus.go:484
    at pkg/runtime/proc.c:1394

And:

[2014/07/25 17:09:43] [monitoring.process-mdb-gen-0[1-4].domain.com:27017.warn] [monitoring-agent/components/bus.go:178] Host `mdb-gen-0[1-4].domain.com:27017` slow to respond. Skipping `mongoBlocking` stats to prevent delays to other Hosts.

Besides this I am getting connections and BSON size errors from the Python app. Each thing is on it's own VM, they are not in a VPN and the ports are set up correctly. I am able to connect to each replica set machine using the mongo command line client from each of the other VMs and my own local machine.

This is the config file for each of the replica set members:

# mongod.conf

systemLog:
    destination: file
    path: "/var/log/mongodb/mongodb.log"
    logAppend: true

storage:
    journal:
        enabled: true
    dbPath: "/data/mongodb"

processManagement:
    pidFilePath: "/data/mongodb/pid"

net:
    port: 27017

security:
    keyFile: "/etc/ssl/mongodb.key"

replication:
    replSetName: general


texts: Searching through dates query not working?

texts:
I'm trying to query the recent week rows by using dates. BUT the query is not returning any result. It takes forever to execute.

I checked my DataBase where date is saved in 1446976737 format which seems unix timestamp. BUT in my filters user is entering human readable dates such as 2018-11-11. Now by executing the below query i'm not getting result. Is there anything wrong with my query?

SELECT  `e`.`id` AS `id`, `e`.`first_name` AS `first_name`,
        `e`.`gender` AS `gender`,
        `e`.`email` AS `email`, `e`.`phone` AS `phone`, `e`.`age` AS `age`,
        `e`.`version` AS `version`, `e`.`evaluation_status` AS `evaluation_status`,
        `e`.`ip_address` AS `ip_address`, `e`.`date_created` AS `date_created`,
        `e`.`date_updated` AS `date_updated`
    FROM  `evaluation_client` AS `e`
    WHERE
       AND  `e`.`date_created` >= 2018-11-11
       AND  `e`.`date_created` <= 2018-11-18;

From the answers and comment i also tried the below query but still it's taking 3 minutes to load the data is it normal? How can i make it faster?

SELECT  `e`.`id` AS `id`, `e`.`first_name` AS `first_name`,
        `e`.`gender` AS `gender`,
        `e`.`email` AS `email`, `e`.`phone` AS `phone`, `e`.`age` AS `age`,
        `e`.`version` AS `version`, `e`.`evaluation_status` AS `evaluation_status`,
        `e`.`ip_address` AS `ip_address`, `e`.`date_created` AS `date_created`,
        `e`.`date_updated` AS `date_updated`
    FROM  `evaluation_client` AS `e`
    WHERE
        `e`.`date_created` >= unix_timestamp("2015-11-11")
       AND  `e`.`date_created` <= unix_timestamp("2015-11-19")

    ORDER BY
       `e`.`date_created` DESC;


texts: Automated SP_WhoIsActive SQL Activity Capturing Issue

texts:
I have an SQL Agent Job on a production server that keeps failing with the below messages. It is supposed to be capturing SQL Server activity using the SP_WHOISACTIVE stored proc, at regularly scheduled intervals.

  Executed as user:  Warning: Null value is eliminated by an aggregate
  or other SET operation. [SQLSTATE 01003] (Message 8153)

  Warning: Null value is eliminated by an aggregate or other SET
  operation. [SQLSTATE 01003] (Message 8153)

  Warning: Null value is eliminated by an aggregate or other SET
  operation. [SQLSTATE 01003] (Message 8153)

  Violation of PRIMARY KEY constraint 'PK_WhoIsActive'. Cannot insert
  duplicate key in object 'monitoring.WhoIsActive'. The duplicate key
  value is (Jan 20 2017  8:25AM, 109). [SQLSTATE 23000] (Error 2627)

  The statement has been terminated. [SQLSTATE 01000] (Error 3621).

  The step failed.

Any idea what may be causing this?

What steps should I follow to fix this error?


texts: How to permanently enable Read Access for MongoDB Secondary Node?

texts:
Upon trying to connect mongodb secondary node from application :

08:11:2018 16:01:00.552 - error: A hook (orm) failed to load!
08:11:2018 16:01:00.558 - error:  Error: Failed to connect to MongoDB.  Are you sure your configured Mongo instance is running?
 Error details:
 MongoError: no valid seed servers in list

I can access the secondary DB by manually entering the command rs.slaveOk(); , but the same wont suffice as I need the application to have dynamic read access everytime the DB is bieng queried


texts: PostgreSQL - Speeding up Index scan in left join

texts:
I have two tables: Order Lines, Orders.

Orders table has a Status column, which is text. It can have values like 'Draft', 'Approved', 'Cancelled' etc. I need a query to return pending order lines for all approved orders. There is an index for pending lines in order Lines table, which performs quickly. When I add index on Status column for Orders, it is used while querying order table. When I join (left outer) Order Lines with Orders, and apply filter of Approved orders, index on Orders wouldn't be used, sequential scan on Orders is used instead.

It appears that the planner uses sequential scan, since most of the Orders have Approved status. This slows down the joined query. What is the best way to speed up this query?


texts:
We have a Server running Windows Server 2008 32-bit (Enterprise Edition) and SQL Server 2008 (SP2) 32-bit (Enterprise Edition) installed.

The server has 32 GB Memory and we assigned 22 GB to SQL Server but its using only 1.5 GB.

We have to enable the AWE Option on SQL Server but:

Do we have to enable\configure any options on Windows before enabling the option on SQL Server?
How much memory can be assigned to SQL Server on the Enterprise Windows (Max)?
Is there any other thing we should take care of before applying since its a production server?


texts: Transaction log filling the drive if mirroring fails

texts:
I am having two SQL Server 2016 machines with mirroring set up. This is supposed to be hot-standby scheme.

HDD size is 1TB, and database size is around 600GB (just one DB). This is 90 days worth of archived data, everything older than 90 days is getting deleted every night (automatically through an external application which is using/filling the database in the first place). So 600GB is the peak DB size, it will not go beyond as it is being cleaned up regularly.

The problem is with the transaction log if one server fails, or if mirroring gets suspended for any other reason. If I understood the principle correctly, healthy server will retain the transaction logs as long as it doesn't get information from partner that everything is OK. So if mirroring fails, the HDD will get filled within several hours.

Is there any suitable technique to prevent this? I have backups of logs every 15 minutes and everything works fine, but if mirroring gets suspended, backups are not worth much, as the log will keep growing in spite of the backups. And the situation on site is a bit specific, there are no engineers, only operators who are accessing this data once or twice per day, so it's impossible to react straight away. It can take more than 24 hours to have someone attend the problem.

Only thing I could think of is some sort of trigger that would remove the mirroring completely once it was suspended for some time (or maybe if it is suspended and HDD space is too low). This will prevent the healthy server from crashing completely, but someone will again have to come to site and set the mirroring up from scratch. And due to bad design from the start, DB size is bigger than half of the HDD size, so I can't even create local backup/restore, I would have to do everything via the 100Mbps NAS that belongs to the client. And this would take more time than it would take the transaction log to fill the drive again.

Now I am thinking to set up a script that would execute every half an hour for example, and remove mirroring if log size is higher than let's say 200GB. Any prettier way to do this? Also, what will happen with mirroring if log hits the maximum size? All transactions are small and can be rolled back quickly, so I am also thinking to set the maximum log size a bit higher than previous threshold (250GB maybe) as an additional precaution. Any downside to this?

Edit 1: I want to put this in initial question as well, maybe my question was not clear enough, but I do not have issues with mirroring getting suspended, it is working perfectly (for now), I am just preparing for worst case. Main goal is full redundancy, and without any precautions, application will become unusable because of full hard drive if just one computers fails, it will happen within hours.


texts:
I have this simple table and user records.
I just want to get users with ilike or something else to get better order in PostgreSQL 10.12.
CREATE TABLE users(
   ID INT PRIMARY KEY      NOT NULL,
   username           CHAR(50) NOT NULL
);

INSERT INTO users (id, username) VALUES(1,'mandela');
INSERT INTO users (id, username) VALUES(2,'delpiero');
INSERT INTO users (id, username) VALUES(3, 'gondela');
INSERT INTO users (id, username) VALUES(4, 'del');
INSERT INTO users (id, username) VALUES(5, 'dell');
INSERT INTO users (id, username) VALUES(6, 'andel');

When I query "del" I want to get "del" user whose id is 4 in first place. it's ok with ordering by length. But still it is not what I want. The order should be like this. Second order must be "dell", third order must be "delpiero", fourth "andel", fifth "gondela" and sixth "mandela".
select * from users where username ilike '%del%' order by length(users.username) asc;

Here is SQL Fiddle


texts: Limiting total number of records in a table with a nightly job

texts:
We have a table that stores audit records (Log in, log out that kind of thing), we already have a nightly job that archives any data that is older than 3 months.
However every week or two we get spikes in traffic and the table grows rapidly (compared to the tables normal growth).   We have also noticed if we get too many rows in the table the performance goes off a cliff.
So what I'm looking for is an optimal way to keep a maximum of 3 million rows in the table as part of either as part of the nightly job above or as a separate nightly job.   This job may be deleting upwards of half a million records a night, so performance is what we are looking for as the table is always being written to.
The table has a primary key of a long identity, and we are running SQL Server 2012.
We have another work thread to fix our performance issues with this table, this is just a stop gap until that work has been completed.


texts: Is it possible to change the browser that pgadmin4 uses?

texts:
I have Debian 10, pgadmin4, and KDE. When I open pgadmin4, it opens with Firefox. I have several other browsers installed. If I try to paste the link in any other browser, I get this error message:

Unauthorized

The server could not verify that you are authorized to access the URL requested. You either supplied the wrong credentials (e.g. a bad password), or your browser doesn't understand how to supply the credentials required.

Is there any way I can open it in another browser like Waterfox or Palemoon? My default browser is Waterfox. I can't find a pgadmin4.conf file to edit or a way to change it in the settings.


texts: SSIS inserts NULL instead of erroring out

texts:
I have a package which imports data from excel to sql server.

Excel file has a column with first x rows are numeric and the remaining values are varchar
Derived column task to convert to string
Destination table is varchar

Here's where I am scratching my head.. When I look at the advanced properties for the excel source task, the in/out datatype is picked up as double precision float.  The entire data set successfully loads into my destination table however all the string values are populated as NULL, instead of erroring out at the excel source task.

I can ultimately solve this issue by formatting the column in excel as text but my question is, why the package does not error out? Please let me know if I'm missing any important details.


texts:
Hello i need to perform the following query

Query

update Taxonomy
set sourceId = (
select id from TaxonomyMapping a where a.oldId =
    (
        select cm.en_ID
        from TaxonomyMapping ta
        join CategoryMapping cm on ta.oldId = cm.language_ID where ta.oldId = oldId limit 1
    )
) where id > -1;

The tables are as following:

Taxonomy(id, oldId, sourceId, name, language)

TaxonomyMapping(id, oldId, sourceId, name, language) Exact copy of Taxonomy

CategoryMapping(en_ID, language_ID)

What im trying to accomplish
The original taxonomy table has got categories in a certain language there are translations of it, but the need they are known in Category mapping, now i need to add the english translation to the sourceId, Can somebody help me with this? at the moment it fills one id for all im suspecting that the oldId(ta.oldId = oldId) i use is not the oldId of the table to update. Is it possible to do a query like this or should i search for another solution?


texts: Transforming start and end times to simple graph

texts:
I've been struggeling with this problem for a while now.

I have a table sessions which stores the start and end times of sessions.

What I am trying to archive is a simple graph that shows the number of active sessions for the last 24 hours in a reasonable interval.

My current solution is to just query the number of active sessions every 15 minutes and store them in a seperate table with just a timestamp and a number column. This is basically duplicate data and not very exact.

The following query produces the needed result, but is very inefficient:

CREATE TEMPORARY TABLE timings (tstamp timestamp);
INSERT INTO timings (tstamp) VALUES (CURRENT_TIMESTAMP - 0 MINUTE), (CURRENT_TIMESTAMP - 15 MINUTE), [...];
SELECT tstamp, (SELECT COUNT(*) FROM sessions WHERE tstamp BETWEEN starttime AND endtime) AS 'numSessions' FROM timings;

I am using MariaDB 10.1. Version 10.2 adds CTEs and updating is a possibility.


texts: SQL server Database statistics degrading after execution

texts:
I have been experiencing timeouts while querying my SQL Server DB in particular one table. I tried updating the statistics and it fixed the performance issues for a couple of days.

I started digging deeper and found out that there is a scheduled synchronization task that retrieves data from a web-service and updates the database. This task uses Entity framework for this purpose. Several colleagues tried to fix this but so far no luck.
I wanted to take a step back.

Throw EF out of the picture
this should not be a piece of C#/EF code to do this but is a perfect job for an ETL tool. A delta update should (according to my understanding of DB statistics do the job). So far, every time the scheduled task runs, everything is being updated.

Can someone provide me with more information or insights in this situation for clues so that I can try to fix the code before bringing in new tools and solutions.


texts: Can MySQL access a remote SQL Server database through ODBC or similar connector? I've seen many articles on the reverse path, accessing MySQL from SQL Server.


texts:
I've just upgraded the SQL client tools on my PC to SQL Server 2016.

I no longer have a "Team" menu option in SSDT.

Under Tools > Options > Source control there is a drop down box for Source Control plug-in. But there are no options.

In my previous version SQL 2012 SSDT (VS 2010) I have the option of selecting "Visual Studio Team Foundation Server"

How do I get SSDT to connect to TFS?


texts: How does SQL Server determine the expression type when mixing Unicode and non-Unicode strings?

texts:
When you say a + b in T-SQL and a and b are of a string type (e.g. varchar(...) or nvarchar(...)): How is the resulting type determined?

I found no clear rules for this when experimenting. It seems to depend on whether literals are involved.

Also, how is the length determined? I found out that the type of 'x' + 'y' is varchar(2). Apparently, the length of the literal is being tracked.

Here's a little experiment that I ran:

USE tempdb

DECLARE @v VARCHAR(400) = 'x'
DECLARE @n NVARCHAR(400) = 'x'

SELECT
    '' AS [varchar]
    , N'' AS [nvarchar]

    , ('x' + N'x') AS ['x' + N'x']
    , (N'x' + 'x') AS [N'x' + 'x']
    , (ISNULL('x', N'x')) AS [ISNULL('x', N'x')]
    , (ISNULL(N'x', 'x')) AS [ISNULL(N'x', 'x')]
    , (COALESCE('x', N'x')) AS [COALESCE('x', N'x')]
    , (COALESCE(N'x', 'x')) AS [COALESCE(N'x', 'x')]

    , (@v + @n) AS [@v + @n]
    , (@n + @v) AS [@n + @v]
    , (ISNULL(@v, @n)) AS [ISNULL(@v, @n)]
    , (ISNULL(@n, @v)) AS [ISNULL(@n, @v)]
    , (COALESCE(@v, @n)) AS [COALESCE(@v, @n)]
    , (COALESCE(@n, @v)) AS [COALESCE(@n, @v)]
INTO StringConversionTest

The documentation calls out the behavior of ISNULL and COALESCE so I understand that part. But the + operator seems to behave under a more complex set of rules.


texts: Which database transaction log will increase?

texts:
I found a query miss written in one of our databases like this

Use database2
go

delete database1.dbo.table1
from table1 as t1
join table2 on t1.column1 = t2.column2 and t2.column3 = 1

I am having a hard time getting my head around it as it looks like it will delete EVERYTHING from database1.dbo.table1 if column3 in table1 or table2 = 1 and if the join condition is successful (Please correct me if I am wrong). It is not meant to even touch database1.

Also, if that happens, which database transaction log will get huge, database1 or database2? Query runs in Database2 but delete is happening for table in database1.

Thanks for your input.


texts: How is parallelism threshold (seconds) used if query CPU cost is actually unitless

texts:
How does the optimizer decide whether or not to use parallelism when the parallelism threshold is specified in seconds (on specific hardware, see below) but the estimated CPU costs are supposedly unit-less?  Is the estimate treated as if it were seconds or is there a conversion happening internally based on some hardware metrics?

From MS:
cost threshold for parallelism Option

  The cost refers to an estimated elapsed time in seconds required to
  run the serial plan on a specific hardware configuration

how-do-i-read-query-cost-and-is-it-always-a-percentage

sql-server-execution-plan-estimated-io-cost-estimated-cpu-cost-no-unit


texts: Selecting only from multiple tables in a transaction

texts:
I need to be able to extract rows from multiple tables in a way that I don't get half changed transactions from other users whilst I'm extracting data. The extraction will take some time (over 100 tables to extract data from).

The purpose is to be able to copy a single users data from one database to another (mysql - non linked, not federated). From a single userID I can collect all the data from the different tables - exporting to a TABBED delimited file.

It would seem that using START TRANSACTION WITH CONSISTENT SNAPSHOT; would be the way to go, but before I go off developing something, is this going to achieve what I want?

Using mysql and all tables are using the INNODB engine.


texts:
Experts

I am working on a query to get Year over Year data comparison from current year to prior year.

For example, if current year is Jan 1st - now, I would calc that dynamically in my query but also bring in the previous year data for the same time frame (does that make sense?).

Here is my projection query that brings in current year and prior year but the data is dynamic yet static if that makes sense. CY is focusing on all of YTD while PY is doing entire PY.

How would I match PY to mimic what CY is doing essentially giving me all the data that I am needing from PY to do the comparison.

I would appreciate your help in advance.

        current_4wk_sales.store_no as 'store_no',
        current_4wk_sales.store_name as 'store_name',
        current_4wk_sales.zee_name as 'franchisee',
        current_4wk_sales.marketing_source as 'region',
        current_4wk_sales.cy_projections as 'cy_projections',
        prev_wk_sales.prev_year_sales as 'prev_year_sales',
        round(((current_4wk_sales.cy_projections - prev_wk_sales.prev_year_sales) / prev_wk_sales.prev_year_sales), 2) * 100 as 'percent_chng'
from (
    select store.store_uin,
           store.store_no,
           store.store_name,
           store.zee_name,
           store.marketing_source,
           round((((sum(ro.total_sales - ro.taxes) / 100)) / (DAYOFYEAR(current_date() - INTERVAL 2 DAY))) * 365, 2) as 'cy_projections'

    from cba_data_warehouse.raw_tm_data ro
    join cba_data_warehouse.storeinfo store on ro.store_no = store.store_no
    where posted_date between MAKEDATE(EXTRACT(YEAR FROM CURDATE()),1) and LAST_DAY(DATE_ADD(NOW(), INTERVAL 12-MONTH(NOW()) MONTH)) and ro.repair_order_status_id in (5,6)
    group by store.store_uin, store.store_no, store.store_name
    order by store.store_no asc
) as current_4wk_sales
left join (
    select
        store.store_uin,
        store.store_no,
        store.store_name,
        store.zee_name,
        store.marketing_source,
        round(sum(ro.total_sales - ro.taxes) / 100,2) as prev_year_sales

    from cba_data_warehouse.raw_tm_data ro
    join cba_data_warehouse.storeinfo store on ro.store_no = store.store_no
    where ro.repair_order_status_id in (5, 6)
    and ro.posted_date between MAKEDATE(year(now() - INTERVAL 1 YEAR),1) and DATE(CURDATE()- INTERVAL DAYOFYEAR(CURDATE()) DAY)
    group by store.store_uin, store.store_no, store.store_name
    order by store.store_no asc
) prev_wk_sales on prev_wk_sales.store_uin = current_4wk_sales.store_uin
                and prev_wk_sales.store_no = current_4wk_sales.store_no
                and prev_wk_sales.store_name = current_4wk_sales.store_name```

Thanks in advance!


texts:
Background

We are using SQL Server 2008 and we had a merge replication configured (before i joined the company). After that they stopped using it and the didn't delete it and configured transactional replication over it leaving orphan jobs and a noisy error with the number 14151 as it keeps showing in my error log.

After a few search I understood that the main cause of this error is the old merge replication and that I had to delete it.

Problem

Problem is shown in the snapshot attached. So could you help me please get rid of this noisy error.

[![enter image description here][3]][3]


texts: Other way to disconnect user/client in mySql 5.6?

texts:
Hello everyone is there another way of disconnecting user/client aside from:

kill id

I have a close button which when i click the button it will close the connection on the selected user... but the problem in my side is that connection ID is changed every process... i can't determine which connection ID should be killed.

this syntax also didn't work using laravel db query..

DB::select( " SELECT CONCAT('KILL ',  $userCon, ';') FROM information_schema.processlist WHERE user = $id;");

Hope anyone could help me... Thanks in advance.

Note: I used Laravel in my Web.


texts:
I have a InfluxDB that is limited to 40 GB, it's being filled by AppMetrics .NET integration performance metrics on each event in the code, usually hundreds of events per second.
My problem is that this setup was running for a month and now has reached a state where it is at the limit and no new data/snapshots can be created.
My question is there a way to process the DB in a way only 15 min aggregations are left for data older than 1 day?


texts: How to INSERT into mySQL without buffering, or force immediate buffer commit?

texts:
I have a script I am running that will allow the user to add test junk to the mySQL DB for testing backups. The script offers the choice to add records of size X until the DB reaches a target size. The script works, but always overshoots the target size because when you get the DB size

SELECT Round(Sum(data_length + index_length) / 1024 / 1024, 1) \"DB Size in MB\"
FROM information_schema.tables
WHERE table_schema='dbName'
GROUP BY table_schema;

it doesn't seem to account for the buffer. So the script eventually detects I've reached the target size, but only when the buffer happens to flush (during debugging, I added 1MB text blocks one at a time, and the DB size reported as 2.5MB a few hundred times before jumping up to ~450MB).

I did try to FLUSH TABLES between each insert, but this just made the DB size constantly report as 0.0 even with 7000+ rows.

How can I ensure I am getting an accurate DB size after each insert (preferably without disabling innodb for the whole DB or restarting mysql)?

Edit: I am using TEXT for the data columns.
COMMIT before pulling DB size doesn't work (still wind up at ~450MB).
SQL_NO_CACHE on SELECT statement above doesn't work (still wind up at ~450MB).


texts: SQL Server 2019 Database stuck In Recovery state after restart

texts:
SQL Server was restarted by mistake, when it came online, database came back in "In Recovery" mode.
Check from error it says "Recovery of database 'DB1' (5) is 8% complete (approximately 27146 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
It says it will 8 hours to bring this 2tb database online.
Any quick way to fix this, as we didnt had anything open in LOG files, so even if they r ignored, its no impact.
We want to bring this database ONLINE quickly


texts: How to query-and-increase a value (counter) in a thread-safe way? (avoid race conditions)

texts:
In a table where each row has a counter (just an integer value), I need to get the current value and increase it at the same time.

Effectively, I want to do this:

SELECT counter FROM table WHERE id=123
UPDATE table SET counter=counter+1 WHERE id=123

But doing this as two queries is obviously not thread-safe: multiple processes doing the same thing (on the same row) may get the same counter value. I need them all to be unique, so each process would get the actual current value and increase it by one.

I can think of a construction where I implement a manual lock per row, but I wonder if there's an easier way of doing this?


texts: T-SQL CTE Compare previous non-NULL and current row to filter out by difference value

texts:
My sample database (in fact a CTE statement data) looks like this:

eventdate   val
2012-03-23  3965
2012-03-26  3979
2012-03-27  3974
2012-03-28  3965
2012-03-29  3967
2012-03-30  3959
2012-04-02  3951
2012-04-03  3961
2012-04-04  3944
2012-04-05  3935
2012-04-09  3901
2012-04-10  3822

I want to remove the values that are less than 12 in difference. This is my query and output:

SELECT
eventdate,
CASE
WHEN ABS(val - LAG(val) OVER (ORDER BY eventdate)) <= 12
THEN NULL
ELSE val
END AS val
FROM tbl_1
ORDER BY eventdate

Output:

eventdate   val
2012-03-23  3965
2012-03-26  3979
2012-03-27  NULL
2012-03-28  NULL
2012-03-29  NULL
2012-03-30  NULL
2012-04-02  NULL
2012-04-03  NULL
2012-04-04  3944
2012-04-05  NULL
2012-04-09  3901
2012-04-10  3822

The problem is that it compares between current and previous row. I need to compare between current and previous non-NULL value, I mean the last value that was not NULLed on the previous step.

This is what I need:

eventdate   val
2012-03-23  3965
2012-03-26  3979
2012-03-27  NULL
2012-03-28  3965
2012-03-29  NULL
2012-03-30  NULL
2012-04-02  3951
2012-04-03  NULL
2012-04-04  NULL
2012-04-05  3935
2012-04-09  3901
2012-04-10  3822

I tried the following.
I made a self-referenced CTE query as above replacing THEN NULL with THEN LAG(val) OVER (ORDER BY eventdate) to copy last appropriate value and compare with next. Then remove duplicates. But it deadly loops on OPTION (MAXRECURSION 0) with no results at all.

Seems this could be done by CURSOR only.
I need the result set as CTE statement (within the CTE), to use it in next query.

As far as I looked for CURSOR examples they all are done as a final SELECT to CTE.
This is not an option for me!

I am running SQL Server 2014.

Many thanks for your help or thoughts!


texts: Error 976 Severity: 14, State: 1. Always on Clustering

texts:
This has been happening in both the replicas (secondaries) at the same time ....Nothing shows on the primary replica.

Error: 976, Severity: 14, State: 1.
The target database is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access.
To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.
For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online

Any ideas on where to start or what might be the problem?


texts: Is it safe to disable InnoDB doublewrite buffering when setting Innodb_page_size = filesystem page size?

texts: As I understand, doublewrite buffering is needed to avoid partial write, because Innodb_page_size default is 16K, while filesystem page size normallly is 4K; I wonder if I set "Innodb_page_size =  filesystem page size", then I could disable  InnoDB doublewrite buffering safely?


texts: What to do about upgrading "main" cluster from 8.4 to 9.1 when a 9.1 "main" already exists?

texts:
I have an existing 8.4 cluster (named main) that I want to upgrade to 9.1.  However, when I go to upgrade it, I get an error because 9.1 already has a cluster named main (apparently a default cluster that is created when I did a sudo apt-get install postgresql-9.1-postgis):
$ pg_upgradecluster 8.4 main
Error: target cluster 9.1/main already exists

What should I do?
Remove the default 9.1/main cluster?  How?
Rename the 9.1/main cluster?  How?
Is there anything special about the Postgresql default main cluster?
Ubuntu 11.10 Oneiric, Postgresql 8.4.8 and 9.1.4
Update: I just found this AskUbuntu question-and-answer that says to first do a pg_dropcluster --stop 9.1 main  I tried that and it worked.  So now I'm wondering if there's any data, configuration settings, etc. in the default 9.1 main cluster that I might be losing by dropping it?


texts: Can a single field reference two PRIMARY KEY (or UNIQUE) fields of two different tables?

texts:
These are my tables:

CREATE TABLE uregister(
u_id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
name VARCHAR(150) NOT NULL,
u_mail VARCHAR(150) NOT NULL,
u_password VARCHAR(8) NOT NULL,
PRIMARY KEY(u_id,u_mail)
);

CREATE TABLE dregister(
d_id GENERATED BY DEFAULT AS IDENTITY NOT NULL,
name VARCHAR(150) NOT NULL,
d_mail VARCHAR(150) NOT NULL,
d_subject VARCHAR(150) NOT NULL,
d_experience INT NOT NULL,
d_occupation VARCHAR(150) NOT NULL,
d_password VARCHAR(8) NOT NULL,
PRIMARY KEY(d_id,d_mail, d_subject)
);

CREATE TABLE question(
q_id INT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
question VARCHAR(500) NOT NULL,
email VARCHAR(150) NOT NULL,
PRIMARY KEY(q_id, email),
FOREIGN KEY(email)  REFERENCES uregister(u_mail),
FOREIGN KEY(email)  REFERENCES dregister(d_mail)
);

Can an email field refer to two different PRIMARY KEY (or UNIQUE) fields at a time? Under this schema, only one user or one domain can ask a given question?


texts: how to show a specific value for a non existing value in second table in join?

texts:
I have 2 tables, City(city_id, city_name) and count_staff_in_city(city_id, count_staff). the first table keeps the name of the city and its id, and the second table keeps the number of staffs working in each city. I want to join this 2 table in this way: if City.city_id exists in count_staff_in_city.city_id then show its value in select. if City.city_id does not exists in count_staff_in_city, then 0 should appear in output for this city.

how can I do this??


texts: Postgresql wal saving for standby servers that go offline

texts: I use postgresql 9.1 replication to a lot of workstations that have postgresql on hot standby mode. However sometimes these workstations go offline and disconnect from the master server. If they go offline for more than say a day they stop replicating, because the wal segments are no longer available. My question is how do I properly configure the master and the standby servers so that they can still replicate even after being disconnected from each other for several days.


texts: In Rails: How is existing data affected by data type change?

texts:
I'm working on a Rails app and would like to change the datatype for an existing column. It's currently a DateTime type, and I want to change it to a Date type. I found a way to do this here, but in this case, the person was not worried about preexisting data.

Right now, I plan to generate a migration...

rails g migration change_my_column_in_my_talbe

...and make the following change:

class ChangeMyColumnInMyTable < ActiveRecord::Migration
  def change
    change_column :my_table, :my_column, :date
  end
end

My question is: will the existing data be converted to a Date type, or will I need to create a rake task to convert the values for all of my existing DateTime values?

I found a similar question in which the conversion was from Boolean to String, and it seemed like the change would be automatic for existing data. I just want to be sure before I jump into making this change.

I'm using Rails version 4.2.0 and MySQL version 5.6.27 Homebrew. Any advice on this issue would be greatly appreciated!


texts: Is it possible to commit a transaction that is waiting to RollBack?

texts:
I'm using Aurora MySql 5.6.10a and a query that was loading a table with 700 million rows crashed and now it's rolling back. I turned autocommit off to try to speed the data load up, but now it's giving me a huge headache.

When i run SELECT * FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`; it returns the following:

3175241 ROLLING BACK    0000-00-00 00:00:00         335482132
3098119 RUNNING         0000-00-00 00:00:00         441106325

Is it possible to revert this rollback and commit those changes? At least, can I commit the running query?

Thank you in advance!


texts: What is the smallest and fastest possible query in MySQL?

texts:
What would be the fastest possible query to run in MySQL, that will allow monitoring of the basic service functioning?

A few examples I can think of:

SELECT NOW();

or

SHOW GLOBAL STATUS LIKE 'Uptime';

etc..

The purpose is to execute the SQL every few minutes, so the performance is key.


texts: Error using SQL Server 2019 Import and Export Data (64-bit)

texts:
I'm getting an error immediately upon starting the SQL Server 2019 Import and Export (64-bit) wizard, and I'm looking for ideas.  The error is:
The SSIS Data Flow Task could not be created. Verify that the DTSPipeline.dll is available and registered. The wizard cannot continue and it will terminate.

Additional information:

Cannot create a task from XML for task "", type ", {D9DA2996-AD06-4166-9C0C-C53E29834AF2}" due to error 0xC0000002 "".
 ({50E060E2-93A0-4F72-9DB6-F692E561312A})

A screen shot of the error is below.
I have tried everything I can think of or find online, including:

register DTSPipeline.dll and DTS.dll with regsvr32 from the 150 folder
install latest CU of SQL Server 2019
repair SQL Server 2019 using the installer
uninstall everything that mentions SQL Server from the control panel
re-install SQL Server 2019 after uninstalling everything
try again prior to installing latest CU
try again after installing latest CU after re-install of SQL Server 2019
try with Run As Administrator
re-install Microsoft.DataTools.IntegrationServices.exe
install latest SSMS

Nothing changes it. Even after totally removing SQL Server 2019 and re-installing.
I can't think of what else to try.

SSIS is installed


texts: Migrating Azure SQL database to Azure sql Managed instance

texts:
What are different options to move database from "Azure sql server" to "Azure sql managed instance", Looks like below options are not possible
1) Azure migration service - Not supporting azure sql server as source
2) bacpac and use sqlpackage to import, this is not working and getting struck with no result

only options i see is through Azure data factory with self hosted integration runtime

is there any better options to move from "Azure sql server" to "Azure managed instance"


texts: Can Checksum alerts make it to the end user?

texts:
I've been reading

https://www.brentozar.com/archive/2017/02/lets-corrupt-sql-server-database-together/#comment-2539669

and I've got some databases with Torn Page Detection that I need to change to Checksum. I've already run (and scheduled) my CheckDB's and we've got no corruption.

Question: If I enable checksum, and a page gets corrupted, can any of my end users get errors in their applications?


texts: Product quantity bound to colors and sizes table structures

texts:
I've been thinking about how I'll be able to create products with sizes and colors which will match the max quantity of a product (or their total will be the max.quantity ).

Let's say I got a product which has a total 12 quantity.

In those 12 there are colors such as RED,BLUE,GREEN. AND there will be 3 sizes such as SMALL, MEDIUM, LARGE

Like:

3x RED SMALL
1x RED LARGE

2x BLUE SMALL
2x BLUE MEDIUM
1x BLUE LARGE

1x GREEN SMALL
1x GREEN MEDIUM
1x GREEN LARGE
Total:12X

Now, sometimes some products wont have any colors, only sizes. Sometimes only colors, no sizes. Sometimes no color neither sizes.

What is the best way of structuring tables to acheive this?

I think like:

Products:
PID,name,(maxqty?)

Sizes:
SID, name

Colors:
CID,name

Used_sizes_colors:
PID,SID,CID,qty

How would you do this?


texts: MySQL Multiple IF statements within after insert trigger fails

texts:
I have been working on this for about 2 days and am at my wits end. SOS StackExchange Gurus! Can anybody help me PLEASE?!

Okay, so I've got the following two triggers set up - one works the other doesn't. They are almost identical but for the latter containing two IF statements the other containing only one. Can anyone help remove the 2014 error I keep getting?

This trigger WORKS:

DELIMITER $$

DROP TRIGGER IF EXISTS AFTER_INSERT_wp_bp_xprofile_data;
CREATE TRIGGER `AFTER_INSERT_wp_bp_xprofile_data`
AFTER INSERT ON wp_bp_xprofile_data
FOR EACH ROW

BEGIN

DECLARE cou_id, voc_id tinyint(3);
DECLARE cou_change, voc_change int(100);

    SELECT COUNT(*)
        INTO cou_change FROM wp_bp_xprofile_data a
        JOIN dim_country b
        ON b.bp_xprofile_fields_parent_id = a.field_id
        WHERE b.bp_xprofile_fields_parent_id =NEW.field_id;

    SELECT COUNT(*)
        INTO voc_change FROM wp_bp_xprofile_data c
        JOIN dim_vocation d
        ON d.bp_xprofile_fields_parent_id = c.field_id
        WHERE c.bp_xprofile_fields_parent_id =NEW.field_id;

    IF

/*COUNTRY*/
        cou_change > 0

    THEN
        SELECT country_id
        INTO cou_id
        FROM dim_country x
        JOIN wp_bp_xprofile_fields y
            on x.bp_xprofile_fields_id = y.id
        JOIN wp_bp_xprofile_data w
            on w.field_id = y.parent_id
        WHERE y.name=NEW.value
            and w.id=NEW.id;

     UPDATE fact_user_profile_detail z
        SET z.country_id = cou_id
        WHERE z.user_id = NEW.user_id;

     END IF;
END; $$

DELIMITER ;

This trigger DOES NOT :

DELIMITER $$

DROP TRIGGER IF EXISTS AFTER_INSERT_wp_bp_xprofile_data;
CREATE TRIGGER AFTER_INSERT_wp_bp_xprofile_data
AFTER INSERT ON wp_bp_xprofile_data
FOR EACH ROW

BEGIN

DECLARE cou_id, voc_id tinyint(3);
DECLARE cou_change, voc_change int(100);

    SELECT COUNT(*)
        INTO cou_change FROM wp_bp_xprofile_data a
        JOIN dim_country b
        ON b.bp_xprofile_fields_parent_id = a.field_id
        WHERE b.bp_xprofile_fields_parent_id =NEW.field_id;

    SELECT COUNT(*)
        INTO voc_change FROM wp_bp_xprofile_data c
        JOIN dim_vocation d
        ON d.bp_xprofile_fields_parent_id = c.field_id
        WHERE c.bp_xprofile_fields_parent_id =NEW.field_id;

    IF

/*COUNTRY*/
        cou_change > 0

    THEN
        SELECT country_id
        INTO cou_id
        FROM dim_country x
        JOIN wp_bp_xprofile_fields y
            on x.bp_xprofile_fields_id = y.id
        JOIN wp_bp_xprofile_data w
            on w.field_id = y.parent_id
        WHERE y.name=NEW.value
            and w.id=NEW.id;

     UPDATE fact_user_profile_detail z
        set z.country_id = cou_id
        WHERE z.user_id = NEW.user_id;

     ELSE IF

  /*VOCATION*/
       voc_change > 0

      then
           SELECT vocation_id
            INTO voc_id
            FROM dim_vocation xx
            JOIN wp_bp_xprofile_fields yy
                on xx.bp_xprofile_fields_id = yy.id
            JOIN wp_bp_xprofile_data ww
                on ww.field_id = yy.parent_id
            WHERE yy.name = NEW.value
                and ww.id = NEW.id;

           UPDATE fact_user_profile_detail zz
            set zz.vocation_id = voc_id
            WHERE zz.user_id = NEW.user_id;

     END IF;
END;
$$
DELIMITER ;


texts: Having issues with a CTE in a Nested Join

texts:
I'm unfortunately dealing with someone else's code here, but needing a little push.

What I have happening is that I have a CTE that is needed to run in a nested join.

...Start of CTE
INNER JOIN (
;WITH FixedQty
AS
(
SELECT CASE
       WHEN WeekJobStartDate < @BegRange
       THEN SUM(WipJobQty) * -1
       ELSE SUM(WipJobQty)
       END AS FixedAllocationQty
      ,StockCode
      ,WeekJobStartDate
  FROM [vwWipDet]
 WHERE WeekJobStartDate <= @EndRange
GROUP BY StockCode, WeekJobStartDate
)
SELECT SUM(FixedAllocationQty) AS WipJobQty
      ,StockCode
  FROM FixedQty
GROUP BY StockCode
    ) AS WipDet ON InvMaster.StockCode = WipDet.StockCode   ...

I've tried removing the semi-colon in front of the CTE, but then get a syntax error stating it is expecting that the previous statement must be terminated with a semi-colon.

I beating my head against the keyboard on this one.


texts: Storing a symmetric matrix in postgresql

texts:
I have several matrices of distances between objects, like this:

Method 1
          A         B         C         D ...
A 0.0000000         .         .         .
B 0.5458965 0.0000000         .         .
C 0.9758267 0.9673959 0.0000000         .
D 0.9714434 0.9589730 0.5715958 0.0000000
...

Method 2
          A         B         C         D ...
A 0.0000000         .         .         .
B 0.1063699 0.0000000         .         .
C 0.4997017 0.4857148 0.0000000         .
D 0.4968287 0.4752771 0.0639429 0.0000000
...

Method 3
...

To give a sense of scale, each matrix is about 10,000 x 10,000 and I have five different methods of distance calculation.

I also have a Postgres database that has a table that stores information about objects A, B, C, D, etc., so I'm looking at SQL solutions specifically.

Let's say a user selects object B in my app. I then display a table of the closest objects to B. Since the distance matrices are on different scales, I convert the values to ranks and use the average of the ranks to sort the results.

What is an efficient way to store this data? The obvious way would be like this:

method obj1 obj2         d
     1    A    B 0.5458965
     1    A    C 0.9758267
...

Then, I just query for obj1='B' OR obj2='B', group by method, calculate rank(d), and aggregate the ranks by mean. Is this a reasonable schema for this problem?


texts: Recommendations for building a big search engine

texts:
I am creating a image search engine that would probably have millions of images tagged with keywords. Basically it would be something like Google Image search.

So I want to ask for any tips on designing the database of the application.

What kind of database system to use? How would the images table look like to store that huge quantity? How do I create the relation between the keywords and the image to be able to do a very quick search?

The programming will be done using a PHP Framework.

Thank you,
Alex.


texts: Select unique value pairs based on two participants that occur more than once inside a table

texts:
We are using MySQL where we have deals table that contains references to two users stored in two columns User_id1 & User_id2. There is no guaranty in which order those are stored. For example the records below represent a deal between two unique pair of users

id ... User_id1 User_id2
-- --- -------- --------
 1 ...       11       20
 2 ...       20       11
 3 ...      116     3459
 4 ...       20       11

We need an SQL query to migrate this data into two new tables below

Participants

chat_id user_id
------- -------
      1      11
      1      20
      2     116
      2    3459

Chat_rooms

id
--
 1
 2

For each unique pair of users in deals table, we need to create a unique conversation record in Chat_rooms table and add those two users as participants to the Participants table with the reference to the created Chat_room ID

I'm hopping to do this with SQL, potentially using some temporary tables.


texts: Selecting rows that might contain null values based on column comparison

texts:
Given a table called table1 whose first few rows look like this:

Idkey DateA     DateB       FieldA    FieldB
1     '2011-06' '2011-03'   20        A
1     '2011-05' '2011-03'   20        A
1     '2011-04' '2011-03'   20        A
1     '2011-03' '2011-03'   20        A
1     '2011-02'  <null>     <null>    <null>
1     '2011-01'  <null>     <null>    <null>
2     '2011-08'  '2011-06'  30        B
2     '2011-07'  '2011-06'  30        B
2     '2011-06'  '2011-06'  30        B
2     '2011-05'  <null>     <null>    <null>

I would like to select all rows for unique Idkeys values starting from the point when DateA = DateB and DateB is in the year 2011. That is, I would like:

Idkey DateA     DateB       FieldA    FieldB
1     '2011-03' '2011-03'   20        A
1     '2011-02'  <null>     <null>    <null>
1     '2011-01'  <null>     <null>    <null>
2     '2011-06'  '2011-06'  30        B
2     '2011-05'  <null>     <null>    <null>

I tried something like:

with subt as (
  select Idkey from table1
  where DateB between '2011-01' AND '2011-12'
  group by Idkey
  order by 1
)

select Idkey, DateA, DateB, FieldA, FieldB,
from table1 b
join subt
on subt.Idkey = b.Idkey
where b.DateA <= nvl(DateB,b.DateA);

But it is not giving me what I want. Any ideas?


texts: Missing view privileges in USER_TAB_PRIVS

texts:
I can't figure out how to view the grant privileges I've given users on views --

FIRSTUSER@SQL> create view remotetabletest
  2  as select * from A_TABLE where PKEY = '123456'
  3  /

FIRSTUSER@SQL> grant select on remotetabletest to USERB;
FIRSTUSER@SQL> exit

[... login as USERB ...]

USERB@SQL> select count(*) from FIRSTUSER.remotetabletest;

  COUNT(*)
----------
        1

USERB@SQL> select * from user_tab_privs where TABLE_NAME = 'remotetabletest';

no rows selected

Normally, if this were a table, querying user_tab_privs would return a line about 'GRANT SELECT', but for views I get nothing.

I also tried querying dba_tab_privs from SYS: nothing.

(Also, in reality, I'm creating the view over a DB Link, but that doesn't seem related to my problem here.)


texts:
I have accidentally updated over 3,000 records in one of my tables hosted on Azure. This was not a transaction and I did not have any back ups for this table.

I've read around and found ApexSQL Log but when I connect I try to connect I see "SQL server Azure is not supported"

I understand that this tool examines logs to reproduce the data, is there a manual technique to do is, or an alternative compatible tool?

I also found this tutorial but after running it on Azure I see 'Invalid object name 'fn_dblog'

Really grateful for you support.

Thanks.


texts: How to migrate from SQL Server 2000 to SQL Server 2008 R2 Express Edition

texts:
I have been trying to migrate a database from SQL Server 2000 (part of SBS 2003) to SQL Server 2008 R2 Express Edition

I naively tried using the SQL Server Export and Import wizard. This has worked to a degree, but seemed to try and import views as tables, and left stored procedures out altogether.

What is the least painful way of performing this sort of migration?


texts: SSISDeploy command exception: Failed to load the ispac file

texts:
I'm trying to run the bello SSISDeploy.exe command:
SSISDeploy.exe -s:"C:\git\test\Integration Services\bin\Development\Integration Services.ispac" -d:catalog;/SSISDB/TEST/DEVOPS;"TEST03,123" -at:win

But I'm getting this exception:
Failed to load the ispac file 'C:\git\test\Integration Services\bin\Development\Integration Services.ispac': Specified part does not exist in the package.

any idea what is wrong? I was able to the same using ISDeploymentWizard.exe


texts: Postgresql 9 speeding up indexed inserts (JPA)

texts:
I have an application which generates a lot of data which needs to be inserted quickly (something around 13million records). I use JPA 2.0/Hibernate with Postgres 9.1, and I managed to achieve quite a good performance (around 25k inserts per second) with multi-threading and batching of inserts every few thousand inserts or so, completing a whole run in around 8mins.

However, I noticed that I had a few of the foreign keys which had an index missing, which I would really wish to have both from an analysis point of view to drill down in the data, and also to delete data to a specific run. Unfortunately when I added in these 3 indexes to the table that is getting most inserts, performance dropped down drastically to around 3k per second.

Is there any way to avoid this performance slow down? I know that one option is to drop the indexes before a run and recreate them in the end. Another more clumsy option is to generate the data of the biggest table in a file instead and use COPY. I guess I can only do it on the largest table in the relation, due to the foreign key values which I would need to know (generated through sequences).

Both alternatives seem to be hacks. Is there any other solution, maybe a bit less intrusive on the application? Some setting to tell postgres to defer indexing or something of that sort?

Any ideas welcome.


texts:
I try to bring to tables together in a join-like way. Consider the column id_ as "on clause" key. In the tables below, there are more id_s in table 1 than 2. I would like to have all ids and values of both tables. If there is not a respective id_, the value is 0 or null.
CREATE TABLE IF NOT EXISTS t1 (
   id_ INTEGER  NOT NULL,
   value1 INT NOT NULL
)

CREATE TABLE IF NOT EXISTS t2 (
   id_ INTEGER  NOT NULL,
   value2 INT NOT NULL
)

INSERT INTO t1 (id_, value1) VALUES
(1, 10),
(2, 20),
(3, 30)

INSERT INTO t2 (id_, value2) VALUES
(2, 40),
(3, 60)
(4, 80)

I use the platform Metabase, a BI tool.


texts: Best practice - use of derived table or use the original table in join?

texts:
Imagine I have the following tables in Teradata :

TableA :  Field1|Field2|Field3
TableB :  Field1|Field4|Field5

I am trying to join these two tables

Method 1 :

SEL TableA.Field3, TableB.Field4
FROM TableA
LEFT JOIN TableB
 ON TableA.Field1 = TableB.Field1

WHERE TableB.Field5<>0

Method 2 :

SEL TableA.Field3, TableB.Field4
FROM TableA
LEFT JOIN TableB
   ON TableA.Field1 = TableB.Field1
  AND TableB.Field5<>0

Method 3 :

   SEL TableA.Field3, TableB.Field4
    FROM TableA
    LEFT JOIN (
                SEL Field1 ,Field4
                FROM TableB
                WHERE Field5<>0
              ) DTable
    ON TableA.Field1 = DTable.Field1

Question :
I always go for Method 3. But recently my colleague advised it is not the best coding practice as it is using the subquery in join.
Is there anything like a coding standard for joins ?


texts: fn_listextendedproperty fails in single_user mode

texts:
I have an open single-user session on a SQL Server 2016.

All other queries work, but fn_listextendedproperty fails with Database 'EIP_TIM' is already open and can only have one user at a time.

It looks a bit like it tries to create a second connection internally.

The Activity Monitor looks like this:

(the selected connection is my current one)

Note that there have been schema-modifying statements after alter database EIP_TIM set single_user.

If I run just exactly the following block, then it succeeds.

alter database EIP_TIM set multi_user;

alter database EIP_TIM set single_user with rollback immediate;

   select count (*) from tabObject;
   SELECT * FROM ::fn_listextendedproperty(N'MS_Description' , N'SCHEMA',N'dbo', N'TABLE', N'tabAttribImage', N'COLUMN',N'AttribImageID');

But this is all part of a larger update script, and in that situation it fails.

I know this question is at the moment a bit generic. I first wanted to ask if there are any known issues with fn_listextendedproperty in combination with single_user mode.

If you need any other information, please let me know.


texts: How to change "Initial File Size" before restoring

texts:
I have backup of size 10GB. When I restore DB on server it will take around 100GB due to initial file size set to 100GB. I could change it and shrink to around 14GB.

Now I want to restore db on my local machine, but the problem is I don't have enough free space.

Is there any option to restore db/override this setting? I do not want to recreate DB from scripts and then move data using SSIS/Redgate Compare or similiar.


texts: How to identify a client application whose host_process_id points to sqlserver.exe

texts:
I am in the process of trying to identify a client application which uses one of the SQL Servers I support (2008 R2).  Periodic queries against sys.dm_exec_sessions regularly return sessions whose login_name is, variously, either one of two non-system SQL-authenticated accounts, or a single, specific, non-system local Windows account, on the machine that hosts the SQL Server instance.  The value in program_name is 'Microsoft SQL Server'; the value in host_name is the name of the server in question; and the value in host_process_id points to the sqlserver.exe process on the same server (as cross-referenced via Task Manager).

My question is, what mechanism  could be initiating these sessions using the host_process_id associated with the engine executable itself?  I realize program_name is configurable by any application, but I didn't think that was true for host_process_id.  A SQL Agent job would connect from a separate executable, as would sqlcmd or the data collection utility.  To my knowledge, we don't use Service Broker for any user applications.  Could it be coming from another engine instance as a linked-server connection?   I feel like I'm missing something that should have been obvious.  How can I track this down?

There are multiple session_ids, all greater than 50, and all with user-created login names, not 'sa' or any account typically used by an internal process. The sessions also run queries against application tables in user databases.


texts: Long full scan hasn't returned but session is gone from V$SESSION


texts: Enable autocomplete in an sqlite3 interactive shell

texts:
I am using sqlite3 on a machine where I can use tab completion (ie .read abc<tab> will autocomplete to .read abcdefghij.db. I would like to know how to enable this on my personal machine.

Edit

Both machines are ubuntu linux and the shell is bash. I am referring to autocompletion in the sqlite interactive prompt.


texts: SQL Server Express: difference between connecting to localhost and localhost\sqlexpress

texts:
I am new to SQL Server and am following some tutorials.

I have not yet created any databases. In SQL Server 2012 Management Studio I click on "connect" -> "Database Engine". I noticed that, using the server name field, I can type localhost and then connect or localhost\sqlexpress and it also connects successfully.

I don't understand what the difference is, and practically what the impact would be to using one over the other. Could somebody please provide an explanation?

In the image below you can see I am connected to both at the same time. One has databases and one does not. What is the difference between these two instances? Why does one have the databases I created and the other does not?


texts:
I'm using SQL Server

and in this case i have the following tables:

Restock: Foreign key to an R&D Staff member
R&D_Staff: subtype from Staff
Sales_Staff: subtype from Staff
Shipping_Staff:  subtype from Staff
Staff: supertype

Should the foreign key be pointing to Staff (Supertype) or to R&D_Staff (Subtype)

Thanks in advance


texts: MySQL replication problem: slave has become out of sync

texts:
I have a Master & Slave setup. Embarrassingly I managed to execute some data changing statements (INSERT and UPDATEs across 3 tables) on the Slave. Obviously this stopped the Slave replication.

I don't need the updates I accidentally made on the Slave. How can I get it back in sync with the Master?


texts: Why would SQL Server automatically remove manually created indexes?

texts:
What causes Microsoft SQL Server to remove manually created indexes automatically? Every index I create it removes within a few hours.

Here is the code I use to create the indexes. Affected views run much faster.  The index disappears / is removed within a few hours. The affected views run impossibly slow or fail.

CREATE NONCLUSTERED INDEX IX_Tablename_fieldname
ON dbo.Tablename (fieldname)
WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
      ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


texts: Oracle data pump import parallel configuration

texts:
I am trying to track down a cause for a difference in oracle impdp processing, and I am not finding anything, so I am wondering if anyone here can explain the cause of the differences.

At times I have seen times where using the parallel=x parameter in oracle datapump will cause either multiple tables to be inserted at once, up to the value of 'x', or other times will use parallel threads to import a single table.

I have not been able to track down what might be causing the difference in performance, and I am wondering if anyone has an explanation, or even just a direction to point me to.  It would be helpful to determine why it runs in a given matter.

For instance right now I am monitoring a single table, data_only import that was started with parallel=8 in the command, but the import job is only using a single thread to do the import, as shown by the following hint the insert query /+ PARALLEL("XXXXX",1)+/.  If the process would use the maximum 8 threads specified to should run much faster.


texts: How "sp_readerrorlog error 22004" on SQL2014 can be related to port number?

texts:
Here is the script:

SELECT @@VERSION
GO
EXEC sys.xp_readerrorlog;
GO

Here is the result:

Microsoft SQL Server 2014 - 12.0.4213.0 (X64)
    Jun  9 2015 12:06:16
    Copyright (c) Microsoft Corporation
    Enterprise Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

(1 row(s) affected)

Msg 22004, Level 16, State 1, Line 2
Failed to open loopback connection. Please see event log for more information.
Msg 22004, Level 16, State 1, Line 2
Error log location not found.

------------------------------------------------------------------------------------
=== Event Log Error info
------------------------------------------------------------------------------------
The description for Event ID 17052 from source MSSQLSERVER cannot be found.
Either the component that raises this event is not installed on your local computer or the installation is corrupted. You can install or repair the component on the local computer.
If the event originated on another computer, the display information had to be saved with the event.
The following information was included with the event:
Severity: 16 Error:10061, OS: 10061 [Microsoft][SQL Server Native Client 11.0]TCP Provider:
No connection could be made because the target machine actively refused it.
the message resource is present but the message is not found in the string/message table

The problem occurs ONLY when I switch default port to another value.
When return its value back to 1433 everything goes back to normal.

During the execution could capture following waits:

wait_type: MSQL_XP
last_wait_type: PREEMPTIVE_OS_GETPROCADDRESS

Can see it only on my SQL2014 on Win8.1 VM. Error persists even when run query locally as local admin

So, how the port number can be related to that error?

UPDATE:
Tried following locally:

sqlcmd -E -S "localhost,1453" -Q "EXEC sys.xp_readerrorlog;"
sqlcmd -E -S "127.0.0.1,1453" -Q "EXEC sys.xp_readerrorlog;"

Get the same error message.

In addition to SQL 2014 installed SQL 2016.
With SS2016 behavior is little different: It produces an error in ANY case, even if I set SQL port to 1433. I guess because it is "Named" instance.

So far see only that extra evidence within the ERRORLOG file that SQL Server loads xpstar.dll during the request (I assume it is normal):

Attempting to load library 'xpstar.dll' into memory.
This is an informational message only. No user action is required.
Using 'xpstar.dll' version '2015.130.1601' to execute extended stored procedure 'xp_readerrorlog'.
This is an informational message only; no user action is required.


texts: Why does INSERT SELECT into an unrelated table block when SELECT doesn't? Mysql 5.6

texts:
Simplified, I have a table 'app' with a primary auto increment field app_id, and one field created_at datetime not null.

I have another table, 'app_subset', with a single key 'app_id'.

If I run a query like this:

SELECT app_id FROM app;

then while that is running, I execute:

INSERT INTO app (created_at) VALUES (NOW());

The insert executes immediately, not waiting for the select to finish.

If, however, I run a query like this:

INSERT IGNORE INTO app_subset SELECT app_id FROM app;

and then run the same insert into app:

INSERT INTO app (created_at) VALUES (NOW());

now the INSERT INTO app query blocks, waiting for the INSERT IGNORE to finish.

I'm trying to understand why this is the case.  I understand that the results of my select will be changed by the INSERT INTO app, but it seems like if the original SELECT doesn't block, then an INSERT using the results of that select shouldn't block either.  I'd like to be able to set things up so this is the case in some fashion, or at least fully understand why it is happening so I can anticipate similar issues in the future.

from Rolando's answer below, it sounds like READ UNCOMMITED should work, and indeed when I execute:

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

The INSERT IGNORE .. SELECT no longer causes my INSERT INTO app to block.

I am curious if there is a way to do it as a single line (something like:)

INSERT IGNORE INTO app_subset SELECT app_id FROM app LOCK IN NONSHARING MODE;


texts: Understanding about SQL index on DateTime Column

texts:
I have about 1 million records. When searching through the date range, it is surprisingly slow. Then I do the google search and someone suggested to do the index on the "DateTime" column. I am not convinced because I always thought index is for O(1) search - which means equality search; and since my query is a date range search, how can it be possible to improve the performance?
But, out of curiosity, I tried anyway and to my surprise, it does improve the performance significantly.
Here is my table
tblOrder
--------
OrderId (Primary Key)
CurrencyId
ProductId
...
...
...
OrderDate DATETIME

I am using linqtosql and the code is:
return (from rec in tblOrders.AsNoTracking()
        where (rec.CurrencyId == CurrencyId) && (rec.OrderDate >= startDate) && (rec.OrderDate < endDate)
        select rec).ToList();

I created a composite index on both CurrencyId and OrderDate. The result is a huge improvement. My question is, how is it possible? Why do indexes work on range search (not equal but >= or <=).
Can someone explain to me how does SQL index work in regards to date range search?
Thank you...


texts: Is InnoDB Engine up to speed against Memory Engine?

texts:
I am exploring the efficiency of different database engines in MySQL version 5.5.18 to see which is best suited for use with a range query on a dataset of 5 million rows:

SELECT P.col1, P.col2, P.col3, P.col4, P.col5, P.col6, P.col7, P.col8, P.col9
 , P.col10, P.col10 * R.col3 as 'combi'
FROM PRODUCT P INNER JOIN RATE R ON R.col2 = P.col2
WHERE P.col3 = 'y'
 AND P.col4 >= 1000
 AND P.col5 >= 5
 AND P.col6 BETWEEN 10 AND 100
 AND P.col7 >= 0
 AND P.col8 >= 7
 AND P.col9 >= NOW()
 AND P.col10 * R.col3 BETWEEN 50 AND 80
ORDER BY P.col8 DESC LIMIT 100;

Based on some discussion on Stackoverflow, I learnt that I may be able to load the database into the RAM by setting InnoDB parameter innodb_buffer_pool_size larger than the size of the dataset. Following the adjustment of this parameter, I am disappointed that the speed of the query wasn't even faster than MyISAM on most occasions (average of 3s versus 0.3s) and 100 times slower than Memory.

Upon further examination of the MySQL manual, it stated the following two points:

InnoDB maintains a storage area called the buffer pool for caching
data and indexes in memory.
The larger the buffer pool, the more InnoDB acts like an in-memory
database, reading data from disk once and then accessing the data
from memory during subsequent reads.

It seems to me that innodb_buffer_pool_size is more for caching the results of repeated queries than to provide a hot copy of the database from which to run queries. Is my understanding correct or am I missing out something that would allow InnoDB Engine to match Memory Engine, performance-wise for non-repeating range queries such as the one above?

UPDATE:

Frederick Cheung pointed out correctly that InnoDB buffer pool does cache a hot copy of the database. What I missed out was a loading procedure found in the link given by Rolando:

Scan the entire contents (all data and index pages) of the table on each startup to preload the content into memory with SELECT * FROM <table> ORDER BY <pkey fields> for each table followed by SELECT <indexed fields> FROM <table> ORDER BY <index fields> for each index.

Upon further testing, I am able to bring down the query times (0.3 to 1.0s) to a level comparable to that of MyISAM Engine. But this is still about seven times slower than that obtained with Memory Engine.

Since both engines are querying the database from RAM, can anyone tell me why InnoDB Engine is still not up to speed against Memory Engine?


texts: stored procedure returns ERROR 1305 (42000): FUNCTION does not exist

texts:
I have a stored procedure and function as below

CREATE  PROCEDURE ProcName()
begin
....
DECLARE curs CURSOR FOR  select A.PList, B.nid from persons A inner join articles B on
 A.aid=B.id;
....
REPEAT
    FETCH curs INTO var1, var2;
    ....
    SELECT SPLITER(var1,"|",a) into @Name;
    ....
UNTIL bDone END REPEAT;
CLOSE curs;
END

CREATE FUNCTION SPLITER(x VARCHAR(255),  delim VARCHAR(12),  pos INT)
RETURNS varchar(255) CHARSET latin1
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),delim, '')

now the select statement of the cursor returns about 400,000 records, and thus once i
call ProcName it returns ERROR 1305 (42000): FUNCTION myDb.SPLITER does not exist
if i changed the query of cursor to handle 50,000 only (as limit) the procedure works fine!!
any idea of how to overcome this issue?
Thanks for your help


texts: What's the maximum number of tables that can be referenced in a query in MySQL?

texts:
In the days of SQL Server 2000 and 2005, the maximum number of tables in a query was 256.

Does MySQL have a similar limitation?

I know that such query of such size means that either there's something wrong with database design or some data warehousing solution should be used, but let's forget about this for a while and focus on theoretical capabilities of MySQL.


texts: Manual failover error, not in correct state

texts:
I'm doing some tests to build one database with one mirror database and one witness.

so far, I'm failing in the first test,
I want to kill the witness, then the Main database, and then put the Mirror database working as Main database.

So when I killed the witness,and then the Main database and did

ALTER DATABASE [MyDataBase] SET PARTNER FORCE_SERVICE_ALLOW_DATA_LOSS

but I got:

Msg 1455, Level 16, State 106, Line 1
The database mirroring service cannot be forced for database "MyDataBase" because the database is not in the correct state to become the principal database.

I've been watching this tutorial and in that video everything goes just has planed.

You can see here my databases and my query to see if I'm missing something.

I'm having the same problem with both principal and mirror up, I'm unable to force mirror to turn to principal.

What am I missing?

edit:
I can put the mirror database up with these commands

ALTER DATABASE [MyDatabase] SET PARTNER OFF
RESTORE DATABASE [MyDatabase] WITH RECOVERY

but if I do this I loose my mirroring settings, and when the other database comes up, is not connected to this one.


texts: How should I configure mySQL 5.7 on Ubuntu for a physical Hot Backup?

texts:
I recent upgraded my Ubuntu 14.04 server to Ubuntu 16.04. I managed to get mySQL upgraded to 5.7. I don't currently have anything in the mySQL instance. Since I have been an Oracle DBA for almost 20 years, I want to configure mySQL with some of the same features. I want to turn on logging for all transactions, and setup hot backups, hopefully using open source software. I'm also not sure which database engine to use.

I have been looking through the documentation, and I am just not finding the steps for setting up a mySQL instance to work they way I think it should work. I know I can do a logical backup, but that doesn't allow me to do point in time recovery. I know I can use all of the default settings, but if I am able to market my skills as a mySQL DBA, I want to use as many of the advanced features as possible.

Thanks.


texts:
I am using SQL Server 2005 and I see the following message in event log (event id 9001)

"The log for database xxxx is not available."

After doing some research I found that it could be a corrupted database or possibly I/O related problem.
I tried running DBCC CHECKDB and it did not report any problem. At the end it shows following message:

"CHECKDB found 0 allocation errors and 0 consistency errors"

I just want to know if there could be any damage to the database or log and what other steps I have to perform to verify it?


texts:
Possible Duplicate:
  Running queries with SQLCMD vs. Running queries with SSMS

I have a number of scripts that query the data from different tables and consequently update them. I have defined a batch file and run them in sqlcmd command.
It seems that when I run these scripts directly in SQL management studio, they run quicker than when they are called from the batch file.

Is there any reason for this behavior?

Thanks in advance.


texts:
today I was reconfiguring some stuff on a PostgreSQL 9.4 install on a Debian 8. After being done I typed

# pg_ctcluster 9.4 main restart

as usual, but this time it wouldn't start back up. The error message directed me towards the log of the cluster, which was empty.

# pg_ctcluster 9.4 main start

Didn't work either.
So to track the error down I started the cluster manually with

$ /usr/lib/postgresql/9.4/bin/postgres -d 3 -D /var/lib/postgresql/9.4/main -c config_file=/etc/postgresql/9.4/main/postgresql.conf

as the user postgres. That worked fine. So the cluster works, the start-up script itself must be the culprit then.
So in hopes to get more detailed error messages I tried to start it like so

# pg_ctcluster --foreground 9.4 main start

which, to my surprise, worked just fine without hiccups.

What can I do about that now? What else is there to check that might throw off the start-up script?
I wouldn't like to modify the pg_ctlcluster script tbh.


texts: Sending an automated email through a nightly job

texts:
I had the following SQL query:

select substring(PostOfficeBox,1,4) as 'Postcode'
, COUNT(CASE WHEN New_accounttype = 1 THEN New_AccountType ELSE NULL END) as 'New Connections'
, COUNT(CASE WHEN New_accounttype = 2 THEN New_AccountType ELSE NULL END) as 'Domestic Metered'
, COUNT(CASE WHEN New_accounttype = 3 THEN New_AccountType ELSE NULL END) as 'Commercial Metered Low'
, COUNT(CASE WHEN New_accounttype = 4 THEN New_AccountType ELSE NULL END) as 'Commerical Metered High'
, COUNT(CASE WHEN New_accounttype = 5 THEN New_AccountType ELSE NULL END) as 'Domestic Keypad'
, COUNT(CASE WHEN New_accounttype = 6 THEN New_AccountType ELSE NULL END) as 'Generator'
, COUNT(CASE WHEN New_accounttype = 7 THEN New_AccountType ELSE NULL END) as 'Commercial Keypad'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where New_AccountStage = 7
and AddressTypeCode = 1
and substring(PostOfficeBox,1,2) = 'BT'
group by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)
order by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)

I now am trying to set this up as a nightly job to run and email myself the results but have had problems when trying to convert this to an automated stored procedure:

EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'support',
@recipients = 'test@mail.co.uk',
@subject = 'Post Code Analysis',
@query = N'select substring(PostOfficeBox,1,4) as ''Postcode''
, COUNT(CASE WHEN New_accounttype = 1 THEN New_AccountType ELSE NULL END) as ''New Connections''
, COUNT(CASE WHEN New_accounttype = 2 THEN New_AccountType ELSE NULL END) as ''Domestic Metered''
, COUNT(CASE WHEN New_accounttype = 3 THEN New_AccountType ELSE NULL END) as ''Commercial Metered Low''
, COUNT(CASE WHEN New_accounttype = 4 THEN New_AccountType ELSE NULL END) as ''Commerical Metered High''
, COUNT(CASE WHEN New_accounttype = 5 THEN New_AccountType ELSE NULL END) as ''Domestic Keypad''
, COUNT(CASE WHEN New_accounttype = 6 THEN New_AccountType ELSE NULL END) as ''Generator''
, COUNT(CASE WHEN New_accounttype = 7 THEN New_AccountType ELSE NULL END) as ''Commercial Keypad''
from AccountExtensionBase as a
JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where New_AccountStage = 7
and AddressTypeCode = 1
and substring(PostOfficeBox,1,2) = ''BT''
group by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)
order by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4);',
@attach_query_result_as_file = 1,
@query_attachment_filename = 'pca-test.txt'

When I run this query it is saying 'AccountExtensionBase' is not a valid object but it is contained within the TestCRM database that my SSMS is set to. Any help would be greatly appreciated,