texts: MySQL - maximum of sum over different months

texts:
I have a table called commodities where I have nature_of_goodscolumn in it. nature_of_goods contains all the different types of goods like machinery, household etc.

I have another column called as created at which contains the date and time in timestamp format.

I need to find the maximum goods took place in particular month.

I have tried the following query:

select max(mycount)
from   (select   nature_of_goods, count(nature_of_goods) AS mycount
        from     commodities
        group by month(created_at));


texts:
I need to restore a SQL Server 2000 database to a backup of about 2 days ago. I have just backed it up and have tried to restore it, but it says it is in use.

What is the best way to find out who is still using it and then how to disconnect them?

I imagine taking the DB offline is a simple way of doing it, but probably not the safest?


texts: Why are my domain and username being prepended to stored procedures I create?

texts:
In Microsoft SQL Server Management Studio, every time I write a stored procedure such as ThisIsAStoredProcedure, it ends up showing in the list of stored procedures as COMPANYDOMAIN\MYUSERNAME.ThisIsAStoredProcedure.

I'd like it to be dbo.ThisIsAStoredProcedure instead.

I've tried right clicking and renaming, but this doesn't work.

I have three concerns:

Why is this happening?
How can I change the existing stored procedures with my domain\username prefix to dbo.procedurename?
How can I change the configuration to name procedures dbo.procedurename by default?


texts: Table contains multiple names for same company

texts:
I have a table of monetary transactions that has a field for who the money was paid to or received from.  Transactions are recorded using info from paper invoices, paper reciepts, internet reciepts, or whatever other type of reciept is turned in or available.

Some (many?) companies use different names for themselves in different places.  The name that shows up on a credit card statement is likely  to be different (slightly or greatly) for the name they print on their POS paper receipts, etc..  The people entering the data can't always be relied on to remember all the naming variations on all the different companies.

So in our db, one company may have transaction records under several different names.

I'd like to have a form where I can enter known naming variations for companies and have the db keep track of multiple names that are really the same company, so later I can query and get far more trustworthy data returned.  Also, I need to be able to query using any of a company's "AKA's" and bring back all their transactions no matter which name was used when the transactions were entered.

The problem is that I'm not sure about the table structure to use. There could be 3 or more AKA's for one company.  How can I do this?

Thank you.


texts:
I'm trying to remove column from DB and got a strange error:

  Error starting at line 147 in command:
      alter table config drop column dte
      Error report:
      SQL Error: ORA-12899: value too large for column "OWNER"."CONFIG"."DTE" (actual: 3, maximum: 1)
      12899. 00000 -  "value too large for column %s (actual: %s, maximum: %s)"

  *Cause: An attempt was made to insert or update a column with a value
                 which is too wide for the width of the destination column.
                 The name of the column is given, along with the actual width
                 of the value, and the maximum allowed width of the column.
                 Note that widths are reported in characters if character length
                 semantics are in effect for the column, otherwise widths are
                 reported in bytes.

  *Action:   Examine the SQL statement for correctness.  Check source
                 and destination column data types.
                 Either make the destination column wider, or use a subset
                 of the source column (i.e. use substring).

I don't have triggers on this table. Could anyone advice on it?
Thanks!

UPD: we were able to delete it using
alter table ... set unused ...
and
alter table drop unused columns
after it. But still have no idea why simple drop doesn't work.


texts: If a rowstore index is stored in a b-tree data structure, what kind of data structure is used for a columnstore index?

texts: My understanding of how regular rowstore indexes are stored is in a type of b-tree data structure, but I was wondering because of the different nature of columnstore indexes, what type of underlying data structure is used?


texts: Using clustered columnstore indexes in conjunction with non-clustered columnstore indexes

texts:
Table A has approximately 500 million records in it and is of the form similar to the following

| ID | FirstName | LastName | MiddleName |
------------------------------------------
|  1 |      John |      Doe |     Quincy |

Et cetera. ID is unique. Table A also has a clustered columnstore index on it.

Table B has approximately 5 billion records and is of the form similar to the following

| ID | Age | Gender |     Cousin |
----------------------------------
|  1 |  26 |      0 |     Carlos |
|  1 |   . |      0 |     Thomas |
|  1 |   . |      0 |     Andrew |
|  2 |   . |      0 |  Gabrielle |
|  2 |   . |      0 |    Douglas |
|  2 |   . |      0 |    Anthony |
|  2 |   . |      0 |      Caleb |
|  3 |   . |      0 |      Scott |
|  3 |  32 |      0 |     Jordan |

Et cetera. ID on Table B joins to ID on Table A and Table B also has a clustered columnstore index on it.

The joins and queries are pretty quick. I can join the two tables and including filters on them can yield approximately ~1.1 million records which get inserted into a temp table.

However; my end result requires that I pivot the data so that record of ID 1 is a single record, like such

| ID | FirstName | LastName | MiddleName | Age | Gender | Cousins
-----------------------------------------------------------------------------------------------
|  1 |      John |      Doe |     Quincy |  26 |      0 | Carlos, Thomas, Andrew              |
|  2 |  Person 2 | Person 2 |   Person 2 |   . |      0 | Gabrielle, Douglass, Anthony, Caleb |

I can achieve this with a cross apply like so

SELECT ID, FirstName, LastName, MiddleName, Age, Gender, r.ColumnName
FROM #tempTable
CROSS APPLY (
    SELECT r.ColumnName + ', '
    FROM #tempTable r
    WHERE e.ActionDateID = r.ActionDateID AND e.RecordNumberID = r.RecordNumberID
    FOR XML PATH('')
) r (ColumnName)

But the performance is awful. Especially when #tempTable has approximately ~1.1 million records. Has anyone a means of getting the same results but with better execution times? For this example, the cross apply takes ~35 seconds.


texts:
I would like to monitor the script which takes more than 25 Sec. When i use filter i am not able to see the duration taken by the script. How can we get the time taken to execute the query.

My Requirement is simple:
Get all the scripts which takes more than 25 Sec(Time taken for execution of each statement is required).


texts: In Oracle, if parallel_max_servers is set to be significantly less than the DOP can the explain plan be sub-optimal?

texts:
For tables that have DEGREE DEFAULT, the degree of parallelism (DOP) is calculated by Oracle using number of CPUs and number of threads per CPU amongst other things. As I understand it, when costing execution plans Oracle will assume the default DOP for tables with DEGREE DEFAULT.

At execution time, however, if parallel_max_servers is less than the default DOP then the number of parallel servers will be restricted and - I presume - the final execution may not go as intended by the costed execution plan and a less parallel execution plan may be preferable.

Is this correct?


texts: mysqldump drop create use case sensitivity

texts:
Dumped sql from MySQL is showing inconsistencies in the name of the database.

Using the following bog standard method to dump a database:

mysqldump --add-drop-database -u someuser --databases Geo-ASty

I get the following output:

...
/*!40000 DROP DATABASE IF EXISTS `Geo-ASty`*/;

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `geo-asty` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci */;

USE `Geo-ASty`;
...

Note the case for the database which changes for the CREATE DATABASE statement. I kid you not.

I wouldn't care if it worked but importing this sql back in causes errors:

ERROR 1049 (42000) at line 26: Unknown database 'Geo-ASty'

I'm using MySQL 5.5 and exporting from a Mac.


texts: Conversion of a varchar data type to a datetime data type resulted in an out-of-range value

texts:
I am trying to run a simple query to get all rows created in November:

SELECT COUNT(*)
FROM dbo.profile
WHERE [Created] BETWEEN '2014-11-01 00:00:00.000'
AND '2014-11-30 23:59:59.997';

SMSS returns:

  The conversion of a varchar data type to a datetime data type resulted
  in an out-of-range value.

I do not understand why the data is being converted from varchar to datetime when 'Created' is set to datetime:

Do I need to tell the server that 'Created' is datetime? If not, why am I getting this varchar message?

Edit: The value in the database was YYYY-MM-DD. Reply from @SqlZim below says that I need to use convert() to tell sql what format the date is in the db - and to replace the space character with the letter T:

select count(*)
from dbo.profile
where [created] between convert(datetime,'2014-11-01T00:00:00.000')
and convert(datetime,'2014-11-30T23:59:59.997');`


texts:
Is there an equivalent to OPENROWSET in Oracle?

From OPENROWSET (Transact-SQL):
This method is an alternative to accessing tables in a linked server and is a one-time, ad hoc method of connecting and accessing remote data by using OLE DB.


texts:
I have a query I need to make more efficient. Trying a couple different things, one of which is SHOW PROFILE . That shows that the most massive timesink by far is "preparing".

As far as I understand, that state indicates that the database is planning, preparing and generally thinking about how to get the results most efficiently which leads me to belive that it can be reduced by providing hints, indices or rewriting the query.

Exactly how those hints, indicies or rewrites should be made escapes me though.

How do I go about optimizing "preparing". All I have now is a single profile row and a search enginge that directs me to articles about prepared statements.

PS: I realise that the "correct" answer probably is "Rewrite everything into normalised tables, relations and make a better query", which is what I'm actually doing.


texts:
I'm attempting to setup log shipping between two differnet domains.  When I attempt to connect to the secondary server instance the primary server fails to connect.  If I turn off the windows firewall on the secondary server then I'm able to connect.

I have opened up the following tcp ports on the secondary server windows firewall
1433, 1434, 137, 139, 445

I have opened up the following udp ports on the secondary server windows firewall
137, 138

Are there any other ports or firewall settings that I've missed?


texts: Change in bind_ip value is not reflecting

texts:
I have mongo installed on Centos instance in AWS and I am connecting to the mongoDB server from my system. To connect from remote system we need to change bind_ip value to 0.0.0.0 in  /etc/mongod.conf file and have done the same thing. Still I am not able to connect to the mongoDB from my system. When I checked weather the changed bind_ip  is reflecting or not using  netstat -a command the result is

tcp  0  0   localhost:27017  0.0.0.0:*    LISTEN

but, to connect from remote system it should be

tcp  0  0   0.0.0.0:27017  0.0.0.0:*    LISTEN

So the change in bind_ip value is not reflecting. Can anyone help on this?


texts: Performance differences between executing via Linked Server vs. SSMS

texts:
We've had an issue reported where a stored procedure was behaving badly when called from some .Net code. On the face of it, that sounds similar to this case, which turned out to be due to EDMX, but in this case, we're just executing a stored procedure.

In the case today, when connected directly to the server holding the database, via SSMS, the query took 2 seconds; when running the query from the client application it was about 5 minutes, and when running the query from a linked server, it also took around 5 minutes. The degradation in performance "suddenly happens" without any changes having been committed.

From a comment on a previous answer, the suggestion wasn't due to query plans, even though recompiling the stored procedure did "fix" the performance for now. i.e. Recompiling the stored procedure makes it equally performant from both SSMS in direct vs linked scenario, and also to the .Net client. I am passing through exactly the same parameters through for comparison.

The stored procedure does a small amount of dynamic SQL (to unwrap a comma-separated list of IDs into a temp table, of the form INSERT INTO #Temp SELECT id FROM dbo.MyTable WHERE Id IN ( 1, 2 ,3 )) before going on to to the main query which joins a whole bunch (circa 20) of tables for the final result.

Ideally I meant to better understand the cause, such that this can be avoided in the future, so what should I be doing to stop this from happening?


texts:
if I use

explain (analyze true, verbose true, costs true, buffers true)
select * from mystoredprocedurefunction(arg1, arg2);

I do not get any information about what the server is actually doing inside my sp.  Instead I get 5 rows describing that I invoked a function and that it took a long time.  How do I look into my stored procedure and see what is going on?  I have something very inefficient occurring.


texts: Is this temp table behaviour documented?

texts:
Query Language (SQL) and PL/pgSQL functions treat temp tables differently:

begin;
create table foo(id) as values (1);
select * from foo;
/*
 id
----
  1
*/
savepoint s;
create function f() returns setof integer language sql as $$
  create temporary table foo(id) on commit drop as values (2);
  select id from foo;
$$;
select * from f();
/*
 f
---
 1
*/
rollback to s;
create function f() returns setof integer language plpgsql as $$
begin
  create temporary table foo(id) on commit drop as values (2);
  return query select id from foo;
end;
$$;
select * from f();
/*
 f
---
 2
*/
rollback;

The docs say:

  ...Existing permanent tables with the same name are not visible to the current session while the temporary table exists, unless they are referenced with schema-qualified names...

This does not mention the exception demonstrated above. Is this documented elsewhere?


texts: JDBC local MSSQL Server connection string

texts:
I have installed open source database management tool "dbeaver" and want to use it instead of Sql Server Management Studio. But there is a problem. I searched from the internet but somehow I could not connect to db using dbeaver.

My instance name is: "OMER-HP\OKSQLEXPRESS2014"
I want to connect: "Follower" db
User name: sa
Pwd: Aa123456

Connection String:
jdbc:sqlserver://localhost:1433;databaseName=Follower;instanceName=OMER-HP\OKSQLEXPRESS2014;

Could you help please on this issue

Here is the scrennshot:


texts:
Assuming I have a table with a bytea column called data on a table called t, how can I:

Write a query that returns all rows which have a specific prefix on their data column.
Index the query.

I am using Postgresql 9.5.


texts: How to safely recreate a MS SQL table that has other objects depending on it?

texts:
I have a table in A Microsoft SQL 2012 database, called Courses.  It was "upconverted" from an originally SQL 2000 database, upconverted to SQL 2008 database, and then upconverted again to 2012 (You can't directly upconvert from 2000 to 2012).

A result of this is that we have some tables that have the old text/ntext datatype, and I want to change the datatype to varchar(max) or nvarchar(max).  For reasons I don't quite understand, I can't simply change the datatype of the ntext columns to nvarchar(max), because whenever I try to do that in Table Design view, I get this error:

Additionally, If I try to simply right click the Courses table in Management studio and Delete it, I see that there are quite a few other database objects dependent on Courses, and upon attempted delete, I get an error message saying I can't do so, because Courses is referenced by another Foreign Key constraint from another object.

So, essentially what I want to do is recreate a table, that has other objects depending on it, and the only thing different about the recreation is that I want to change the text/ntext columns in it to varchar(max) and nvarchar(max) datatypes.  Everything currently "depending" on the 'old' Courses table, needs to then "depend" on the 'new' recreated Courses table.  What's the easiest way to go about doing so in SQL 2012?


texts: Subquery returned more than 1 value. My WHERE clause needs to remove SELECT statements

texts:
I have a SQL Stored Procedure that is giving issues. I am aware that it is most likely the SELECT statements in the WHERE clause that are returning multiple values. I commented out the WHERE and results were returned.

My question is how do I modify the statement so that I can still filter on those conditions?

Stored Proc code:

SELECT
    REPORT_SPOOL.ID,
    REPORT_SPOOL.REPORT_SPOOL_TYPE_ID,
    REPORT_SPOOL.FUND_ID,
    REPORT_SPOOL.PERF_ENTITY_ID,
    REPORT_SPOOL.REPORT_GUID,
    REPORT_SPOOL.REPORT_TEMPLATE_GUID,
    PERF_ENTITY.CODE AS PERF_ENTITY_CODE,
    PERF_ENTITY.NAME AS PERF_ENTITY_NAME,
    FUND.CODE AS FUND_CODE,
    FUND.NAME AS FUND_NAME,
    REPORT.CODE AS REPORT_CODE,
    REPORT.NAME AS REPORT_NAME,
    REPORT_TEMPLATE.CODE AS REPORT_TEMPLATE_CODE,
    REPORT_TEMPLATE.NAME AS REPORT_TEMPLATE_NAME,
    FUND.ACCOUNTING_START AS START_DATE,
    FUND.ACCOUNTING_END AS END_DATE
FROM
    PERF_ENTITY
    RIGHT OUTER JOIN
        REPORT
        INNER JOIN
            REPORT_SPOOL
            ON REPORT.GUID = REPORT_SPOOL.REPORT_GUID
        INNER JOIN
            REPORT_TEMPLATE
            ON REPORT_SPOOL.REPORT_TEMPLATE_GUID = REPORT_TEMPLATE.GUID
    ON PERF_ENTITY.ID = REPORT_SPOOL.PERF_ENTITY_ID
    LEFT OUTER JOIN
    FUND
    ON REPORT_SPOOL.FUND_ID = FUND.ID
    WHERE
    (END_DATE IS NULL OR END_DATE > @REPORT_DATE)
    AND
    REPORT_SPOOL.FUND_ID = (SELECT FUND_ID FROM FUND_HLD WHERE [DATE] = @REPORT_DATE)
    AND
    REPORT_SPOOL.FUND_ID = (SELECT FUND_ID FROM FUND_TRD_LINE_VIEW WHERE [DATE] >= (SELECT DATEFROMPARTS(YEAR(@REPORT_DATE),MONTH(@REPORT_DATE),1)) AND [DATE] <= @REPORT_DATE)

Those last 2 SELECTS are the issue


texts:
I'm running my MySQL backups through cron scheduled every night, but the thing is in cron the username and password is given for it run, is there any other way I can securely run the backups? where I can credentials remain secure only to the DBAs and not to anyone else? Btw, I use percona server. But I use mysqldump for backups, just that it has to be secure.

Thanks!


texts: How to create a Hash Computed Column for Many Columns?

texts:
Does anyone know how to create a Hash Computed Column Persisted? I keep receiving an error below. Otherwise, I will have to utilize an update or trigger statement everytime. I know how to conduct for single column, however this includes multiple columns now.

CREATE TABLE [dbo].[CustomerTransactiont]
(
    CustomerTransactionId int primary key identity(1,1),
    CustomerName varchar(255),
    Price decimal(10,2),
    Quantity int,

    RowHash as hashbytes('SHA2_512', (select CustomerName
    ,Price
    ,Quantity for xml raw)) persisted
)

Msg 1046, Level 15, State 1, Line 7
Subqueries are not allowed in this context. Only scalar expressions are allowed.


texts: To find out last activity/access date and date of expiration for all SQL Server 2012 User login

texts:
Apart from enabling the Audit on the SQL server, is there any other way we could find out the last activity/access date and date of expiration for all SQL Server User login ?
Also using DMVs wont work as the information provided by them are limited.


texts: What is the purpose of BACKUP WITH NAME?

texts:
I looked at the Microsoft documentation (http://technet.microsoft.com/en-us/library/ms186865.aspx) and see that there is a WITH NAME option for backups.  But it's not the name of the file (I specify that with TO DISK).  And if I don't specify it, the name is left blank.

So what is the purpose of specifying WITH NAME?  What benefits do I get?


texts: building a view w/ fields that only contain a certain word

texts:
Trying to build a simple SQL query... I have a table filled with "Yes" and "No"

I want a view that only shows me records that contain "No"

SELECT PhysicalBuild.Name AS Name,
  PhysicalBuild.Labels AS Labels,
  PhysicalBuild.DNSDRAC AS DNSDRAC,
  PhysicalBuild.DNSHOST AS DNSHOST,
  PhysicalBuild.PRTGDRAC AS PRTGDRAC,
  PhysicalBuild.PRTGHOST AS PRTGHOST,
  PhysicalBuild.LouKVM AS LouKVM,
  PhysicalBuild.SwitchPorts AS SwitchPorts,
  PhysicalBuild.FiberSwitch AS FiberSwitch
FROM PhysicalBuild

But I want it to be WHERE * = "No" or something to that effect.... my syntax is incorrect. anyone?

EDIT: I came up w/ this. Is there a cleaner/easier way to handle this? How about a way to change the records that contain "Yes" into NULL so they aren't displayed? So only the No's are shown?

SELECT PhysicalBuild.Name AS Name,
  PhysicalBuild.Labels AS Labels,
  PhysicalBuild.DNSDRAC AS DNSDRAC,
  PhysicalBuild.DNSHOST AS DNSHOST,
  PhysicalBuild.PRTGDRAC AS PRTGDRAC,
  PhysicalBuild.PRTGHOST AS PRTGHOST,
  PhysicalBuild.LouKVM AS LouKVM,
  PhysicalBuild.SwitchPorts AS SwitchPorts,
  PhysicalBuild.FiberSwitch AS FiberSwitch
FROM PhysicalBuild
WHERE (PhysicalBuild.Labels = 'No') OR
  (PhysicalBuild.DNSDRAC = 'No') OR
  (PhysicalBuild.DNSHOST = 'No') OR
  (PhysicalBuild.PRTGDRAC = 'No') OR
  (PhysicalBuild.PRTGHOST = 'No') OR
  (PhysicalBuild.LouKVM = 'No') OR
  (PhysicalBuild.SwitchPorts = 'No') OR
  (PhysicalBuild.FiberSwitch = 'No')


texts:
Briefly, I am trying to filter a table using conditions plus a list of items in Oracle. In detail,
I have a reporting table which has millons of records. I would like to filter it using external lists which might include more than 1000 lines of fields from the record table. (Obviously, I especially said 1000, because in clause in Oracle has the limit of 1000 items). So my question is; How can I effectively filter a huge table using another big list.


texts: Return previous running total when value is null in a time series

texts:
This is a continuation of a previous question, where table definition and sample data can be found. (Huge thanks to @Erwin Brandstetter for the help there).

All of this is being done on a PostgreSQL 11.5 DB.

SELECT *
FROM   (
   SELECT the_day::date
   FROM   generate_series(timestamp '2020-01-01', date_trunc('day', localtimestamp), interval '1 day') the_day
   ) d
LEFT   JOIN (
   SELECT customer_id
        , created_at::date AS the_day
        , sum(sum(t.amount) FILTER (WHERE stock_ticker = 'tsla')) OVER w AS tsla_running_amount
        , sum(sum(t.amount) FILTER (WHERE stock_ticker = 'goog')) OVER w AS goog_running_amount
   FROM   transactions t
   WHERE  created_at >= timestamp '2020-01-01'
   GROUP  BY customer_id, created_at::date
   WINDOW w AS (PARTITION BY customer_id ORDER BY created_at::date)
   ) t USING (the_day)
ORDER  BY customer_id, the_day;

When a customer has NO transactions on a day, I need to be able to display the "most recent" running total for that customer for that day.

For example:

2020-01-01: Customer purchases 5 "tsla" for a total balance of 5.
2020-01-02: Customer takes no action. Current total balance display as 5.
2020-01-03: Customer sells 2 "tsla", total balance now 3.
2020-01-04: Customer takes no action. Current balance still display as 3.

Currently, the way that this is set up, the record for a customer on a day where they have no transactions will return as a NULL row for balance totals.

How can I display their "most recent" running balance on days when they have no transactions?

This query will be running for all customers that exist within the transactions table (have had a transaction at least once in the past).


texts: How do I find out details about an error showed iby "brew services list"?

texts:
I want test my postgres13 service on mac by using "brew services list".
I got following:
$ brew services list
Name       Status  User      Plist
postgresql error   gerd      /Users/gerd/Library/LaunchAgents/homebrew.mxcl.postgresql.plist

How can I find out details about this error?
In the /usr/local/var/log/postgres.log logfile (listed in homebrew.mxcl.postgresql.plist) is no error:
2021-01-28 17:36:59.429 CET [41194] LOG:  starting PostgreSQL 13.1 on x86_64-apple-darwin19.6.0, compiled by Apple clang version 12.0.0 (clang-1200.0.32.27), 64-bit
2021-01-28 17:36:59.430 CET [41194] LOG:  listening on IPv6 address "::1", port 5432
2021-01-28 17:36:59.431 CET [41194] LOG:  listening on IPv4 address "127.0.0.1", port 5432
2021-01-28 17:36:59.431 CET [41194] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2021-01-28 17:36:59.436 CET [41195] LOG:  database system was shut down at 2021-01-28 17:35:44 CET
2021-01-28 17:36:59.440 CET [41194] LOG:  database system is ready to accept connections

My database work and I can connect with psql.


texts: Optimize a query with two range conditions

texts:
I have a structure similar to this one:

CREATE TABLE `author` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

CREATE TABLE `book` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `author_id` int(11) unsigned NOT NULL,
  `org` int(11) unsigned NOT NULL,
  `country` char(3) NOT NULL,
  `publish_date` date NOT NULL,
  `price` decimal(6,2) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `author_id` (`author_id`),
  KEY `publish_date` (`publish_date`),
  KEY `i0` (`country`, `org`, `author_id`, `price`, `publish_date`),
  KEY `i1` (`country`, `org`, `author_id`, `publish_date`, `price`)
) ENGINE=InnoDB CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1;

Here is a SQL Fiddle

So I'm trying to execute a query with two range conditions

SELECT
  id as author_id,
  (SELECT COUNT(DISTINCT `book`.`id`)+1
   FROM `book`
   WHERE
      `book`.`org` = 1
      AND `book`.`country` = 'USA'
      AND `book`.`publish_date` BETWEEN '2010-04-30' AND '2011-04-30'
      AND `book`.`author_id` = `author`.`id`
      AND `book`.`price` < 50
  ) AS `books_under_fifty`
FROM `author`
ORDER BY books_under_fifty desc;

but the optimizer uses only part of my index: const,const,db_9_6349e2.author.id from i0

is there a way to optimize it?


texts: Notify/Listen: AccessExclusiveLock on object 0 of class 1262 of database 0

texts:
I am using Notify and Listen on PostgreSQL 9.4 . It's strange to me when getting log as below:

Apr 24 23:53:05 server postgres[6134]: [8-1] user=,db= LOG:  duration: 2450.632 ms  execute <unnamed>: SELECT COUNT(*) FROM mapping_objects
Apr 25 00:00:10 server postgres[6646]: [8-1] user=,db= LOG:  process 6646 still waiting for AccessExclusiveLock on object 0 of class 1262 of database 0 after 1000.076 ms
Apr 25 00:00:10 server postgres[6646]: [8-2] user=,db= DETAIL:  Process holding the lock: 6134. Wait queue: 6875, 6646, 6132, 5642, 5890, 7856, 7863.

On the other hand, I could not find the AccessExclusiveLock mode in the pg_locks at the same time.

Does anyone know why it happen ?

As Tom Lane's message, It can be a lock on Notify queue.


texts: Why would sys.dm_exec_requests show a query in Database A blocking a query in Database B when neither query references the same tables?

texts:
I have a query I use in-house to monitor slow running processes in the data layer. It essentially looks at a few fields from the following system tables and views joined in the following way:

SELECT ER.blocking_session_id, SP.DBID, EST.text, etc...
FROM sys.sysprocesses SP
INNER JOIN sys.dm_exec_requests ER
    ON sp.spid = ER.session_id
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(ER.[sql_handle]) EST

While using it to debug some slow processes I encountered the ER.blocking_session_id was stating Query1 in Database1 was being blocked by Query2 that runs in Database2 and both queries interact with completely different tables. Ironically, both queries also use NOLOCK hints everywhere (not my choice.)

Here's the example 2 queries.

Query1 (being blocked by Query2):

INSERT INTO #TempTable1
SELECT Field1, ISNULL(Field2, Field3) AS NewField
FROM Database1.Table1 WITH (NOLOCK)
INNER JOIN Database1.Table2 WITH (NOLOCK)
    ON Table1.Field4 = Table2.Field4

Query2 (doing the blocking):

INSERT INTO #TempTable2
SELECT Field1, Field2, Field3
FROM Database2.DifferentTable1 WITH (NOLOCK)
INNER JOIN Database2.DifferentTable2 WITH (NOLOCK)
    ON DifferentTable1.Field5 = DifferentTable2.Field5

The last few wait types I'm seeing for Query1 are: PAGELATCH_UP, IO_COMPLETION, CXPACKET.
For Query2 they're: PAGELATCH_UP, and CXPACKET.

I always interpreted that the sys.dm_exec_requests.blocking_session_id literally meant table locks, but is it possible it could refer to other types of blocks due to resource contention?


texts:
User is using the bcp command to load data from a Linux command prompt.

As of now it is loading 1000 rows at a time. There are 9970001000 total rows to send to SQL Server.

If he wants to insert more than 1000 at a time, what do we need to do or enable on the SQL Server end?


texts: Could not find prepared statement with handle -1 SQL Server Profiler

texts:
So I have this issue where I'm running Sql Server Profiler and I'm getting hundreds(at least) of Could not find prepared statement with handle -1.(error 8179) per second.

I can't seem to really correlate the errors with anything specific except some cached prepared statements I think.

I'm using Linq to Sql.

If you need more info just let me know.


texts: Why is turning on Event Scheduler Maxing my CPU?

texts:
I have search the web for the last week trying to figure this out.
I have exported my tables and events, re-installed the lastest MySQL and imported.
I have tried checking individual procedures.
My situation has me at a complete standstill, here it is:
I install the latest MySQL 8.0 from MySQL Installer on Windows 10 x64-bit, here is the slightly modified my.ini file: my.ini
Whenever I enable event_scheduler (with any form of an activated event, even a single one liner "SELECT * FROM [Table_Without_Data];") which reoccurs every second,
I see an indefinite number of "event_worker" threads opening all of which are stuck in the "opening tables" state and sleeping.
My CPU maxes out, and slows down my whole pc.
Additionally I have 8Gb RAM and an i7-3520M CPU @ 2.90GHz
It does not appear when I check full processlist that anything is frozen because of locks which only leaves me to believe it is something in the server configuration.
I also notice that this will not occur if i start the DB and edit the scheduled event, then set event_scheduler=ON, until I send a query anyhow.
Any ideas? Need any further info?

UPDATE

After extenuating efforts to try to crowd source a solution, I've decided this must be a MySQL bug and I've shared it here: BUG#: 100449 Other reference post is here: Stack Overflow - DDeathlonger


texts: Combine - Select Into, with Insert Into Select

texts:
I've read many explainations like this one here that say "Select Into ..." is to create new tables and "Insert Into ..." is to append to existing tables.

I'm automating a coworker's sql scripts. Currently these sql scripts create new tables (assuming they don't exist) using Select Into, and this is causing a dilemma. When the automation fires off the second time, I get an error because the table already exists and, consequently, the second round of data isn't inserted.

I'd prefer not to tell my coworker to rewrite his hundreds of lines of code by specifying all the column names twice in all his scripts. Is there some minimalist way I can combine the idea of the "Select Into" and the "Insert Into" into a single query, without explicitly duplicating all the column names? maybe like "Select Into ... On Error " or something like that?

EDIT: I'm using MS SQL


texts: Ctrl-F1 shortcut stopped working in SSMS 2014

texts:
I've had the Ctrl-F1 query shortcut bound to sp_whoisactive for years (this technique is described in How to assign CTRL+F1 to sp_whoisactive in ssms 2012?).  A few days ago, it stopped working. If I hit Ctrl-F1, SQL Studio now launches Books Online in Internet Explorer.  Has anyone else seen this problem?  Is there a fix?

I confirmed that the shortcut it still set.  Changing the shortcut does not fix the problem, Ctrl-F1 still launches BoL.  Cycling SQL Studio has had no effect.

FWIW, this happened after IT restarted the server while I was logged in, so SSMS did not shut down clean.

I can of course use another keyboard shortcut (though sadly it seems Ctrl-1 is hardcoded to sp_help), it just bothers my OCD to use a different shortcut for the same command on different machines.


texts: Actual Number of Rows Significantly Lower than Estimated

texts:
My Problem
I am having an issue with a update query, where the execution plan has a bad cardinality estimate. It is estimated way more rows (14000%) than there should be when it does an Index seek on one of the tables (ShInvoiceHeader). My understanding of why that's not good is that the plan won't be as optimized and it'll cause more memory to be requested than needed.
Here's a screenshot of what I'm seeing in the execution plan, and a link to the actual execution plan.

Here is the update query. We are finding all the records where a column is False, checking if a matching record exists in a table with records from an activity log and then updating them to True. The log table doesn't have the same uniqueness as the primary table ShInvoiceHeader, so we are using an EXISTS. However I also tested this out with a JOIN and saw the same issue.
UPDATE  HDR
SET PrintedBillTrustFlag = 1
FROM    dbo.ShInvoiceHeader                     HDR
WHERE   EXISTS(SELECT 1
                FROM USESIDW_FastAccess.dbo.LedgerLogEvent  LLE
                WHERE   HDR.LedgerID = LLE.LedgerID
                    AND HDR.GenID    = LLE.GenID
                    AND LLE.EventID IN (25,53,54)
                )
    AND HDR.PrintedBillTrustFlag = 0

What I've done to try and fix it
I right clicked the Analyze Actual Execution Plan and selected the node with the Index Seek on NonClustered ShInvoiceHeader.IX_ShInvoiceHeader_PrintedBillTrustFlag and it listed for findings details:

One of the common reasons for estimation differences is the use of different statistics. Check if the statistics for table [ShInvoiceHeader] are different or stale. Refer to here for more information

So then I updated the stastics for the column using:
UPDATE STATISTICS ShInvoiceHeader [IX_ShInvoiceHeader_PrintedBillTrustFlag] WITH FULLSCAN
and I also tried:
ALTER INDEX [IX_ShInvoiceHeader_PrintedBillTrustFlag] ON ShInvoiceHeader REBUILD;
To no avail, the issue stayed the same. I read posts on StackExchange and most of them mentioned outdated statistics and one mentioned if the update had happened earlier that day it could cause an issue. So I tried to undo the update so the records looked like they would've the first time and then test again and still same issue.
I tried a few query hints for the heck of it like RECOMPILE and MAXDOP 1 and tried joining to an empty table with columnstore to induce Batch mode. I don't know a lot about those topics but tried them.
I also counted the number of rows in each part of the query. There are 5.3M records in ShInvoiceHeader with a PrintedBillTrustFlag = 0, so that number makes sense. There are 4.9M records from the LedgerLogEvent table that meet the criteria. There are 7,604 rows when you combine and do the EXISTS which matches the amount updated. But what doesn't make sense to me is the ACTUAL of 36881 in the Execution Plan.
Update:
I was asked to include the table definition and index definition.

Here's ShInvoiceHeader, with some of the columns, the list goes on and on for a while, it's pretty wide.

Here's LedgerLogEvent. Note uniqueness is different that's why I'm doing the exists instead of a join.

Here is the index being used


texts:
The text below is from the book Database System Concepts by Silberschatz.
Why the values associated with external writes need to be stored in a special relation in the database? Can't we behave with these values like other values?

We must be cautious when dealing with observable external writes, such as writes
to a user’s screen, or sending email. Once such a write has occurred, it cannot be
erased, since it may have been seen external to the database system. Most systems
allow such writes to take place only after the transaction has entered the committed
state. One way to implement such a scheme is for the database system to store any value
associated with such external writes temporarily in a special relation in the database,
and to performthe actual writes only after the transaction enters the committed state. If
the system should fail after the transaction has entered the committed state, but before
it could complete the external writes, the database system will carry out the external
writes (using the data in non-volatile storage) when the system is restarted.


texts:
I encountered an error on PostgresSQL. Here is details for the error which i have seen during the backup.

 pg_dump: SQL command failed
 pg_dump: Error message from server: ERROR: found toasted toast chunk for toast value 16431418 in pg_toast_16431418
 pg_dump: The command was: COPY.XXXXXXXXXXX

So what I did:

 VACUUM ANALYZE <table_name>;

it showed me the same errors, then I ran:

 SET zero_damaged_pages = on;
 VACUUM FULL <table_name>;

it zeroed out the bad page header, then I ran:

 REINDEX table pg_toast.pg_toast_16306418;

After running this command, I checked the other process which could be waiting but nothing was in the waiting except this query taking up 99% of CPU cycles. I waited for an hour and seems nothing was happening, I terminated the query.

Then I ran full vacuum and reindexing of database but vacuum again failed

 VACUUM
 RESET

vacuum did not complete

I thought zero-ing out the invalid page header would allow me to back up the database but it did not do anything. I have checked the memory, RAM, hardware etc. and that all looks pretty good. Can someone suggest something please?

I am fairly new to postgres so please be specific, if you can.


texts: Error message when importing data into SQL (Import & Export Data Wizard)

texts:
I am trying to use the Import & Export Data Wizard to import the contents of a CSV file into a table in MSSQL.

I have no issues if I import all columns as strings however, I need one column to be a float. (In the CSV, column 4 is a currency amount with two decimals e.g. $200.30).

Whenever I set the value of column 4 to a float, I receive the following error during the import:

  Error 0xc02020a1: Data Flow Task 1: Data conversion failed. The data
  conversion for column "Column 4" returned status value 2 and status
  text "The value could not be converted because of a potential loss of
  data.".  (SQL Server Import and Export Wizard)

Here is an example of the data which I am importing:

+----+------+----------+-----------+--------+
|  1 | 2014 | December | 400089323 | 8.03   |
|  2 | 2014 | December | 400319749 | 45.65  |
|  3 | 2014 | December | 400377067 | 38     |
|  4 | 2014 | December | 400524693 | 0      |
|  5 | 2014 | December | 400536070 | 125.31 |
|  6 | 2014 | December | 400663546 | 266.95 |
|  7 | 2014 | December | 400705907 | 218    |
|  8 | 2014 | December | 400763025 | 244.39 |
|  9 | 2014 | December | 400782510 | 12.89  |
| 10 | 2014 | December | 400794040 | 61.04  |
+----+------+----------+-----------+--------+

This is the datatype of column 4:

The rest of the columns are "numeric [DT_NUMERIC]" except column 2 which is "string [DT_STR]".

I have tried different datatypes for column 4 such as numeric & currency. This did not work.

FYI: I am using SQL Server 2012.


texts: Connect to another host from inside mysql stored procedure

texts:
I have two virtual servers on one physical machine.

There are MySQL users and passwords the same for both of them.

I want to write something like this:

SELECT * FROM [host_name].[db_name].[table_name]

Federated / FederatedX is not a solution for me. At least I want to avoid using it if it is possible.

If what I want is not possible I need some solution to do the following:

I have two MySQL servers: server1 and server2.

I want to set a trigger on a few tables in server1 so i.e. when new row appears there a part of that row should go to server2.

I don't want an entire row to be replicated. There might be some values on which I will want to remove some rows form server2 and e.t.c.

I was thinking I should call some procedure on the trigger if it is possible.


texts: Delete rows where matched value is twice as much

texts:
I'm trying to match two tables with two columns, table1 and table2.
Each has id and price columns.

I want the query to match each id and compare the price, then delete the row if the price in the second table is twice as much as the other one.

My query up to now ( does not work ):

include ('go.php');
$sql = "delete
from table1
where table1.id IN
    (select table2.id where table1.price > table2.price*2)"
or die(mysqli_error($sql));

Anyone have any suggestions?


texts: Large number of connections to an SQL server results in read write bottleneck on hard drive?

texts:
I have the following problem which I can't find a solution for.
We have a learning School System for more than 10000 students that upload photos and PDF of Homeworks as binary to an SQL server. During my first trial of the system the hard disk storing the SQL database crashed peaking at 100% of disk usage all the time and was really slow in this scenario.
I was using regular WD Red 4TB and every time after the system went online and a large number of connections started to upload homework the drive kept maxing out at 100% usage all the time and it became the bottleneck.
After the initial fail I changed the drives to XPG NVME SSD and it worked like a charm. But my only problem is that I can't continue like that, because the NVME's capacity is not big enough to handle a large amount of data on it.
So I need a solution for my situation. What can I do in this case to solve the I/O-usage issues?

Windows Server 2016
SQL server 2016


texts: HowTo parsed Stored Procs to get a list of tables and columns referenced in WHERE, GROUP BY, AND JOIN clauses

texts:
Is there a tool, script, utility that can parse sys.sql_modules and return a list of tables and columns referenced in the WHERE, GROUP BY, and JOIN clauses of Stored Procedures?
Example:

CREATE PROC myProc
BEGIN
    SELECT a.col1, b.col2, count(a.col7) as MyCount
    FROM dbo.FirstTable as a
    JOIN dbo.SecondTable as b on (a.col1 = b.col1)
    WHERE b.col5 = 'xxx'
    GROUP BY a.col1,b.col2
END

Return a list such as:

FirstTable.col1
SecondTable.col2
SecondTable.col5
FirstTable.col1
SecondTable.col2

The goal is to use the result list to build some CREATE STATS commands based on the number of times a table.col pair is referenced.


texts:
I want to write a package that will manage mysql/pgsql views, but for that I need to check if the view defined in the code is the same as the view in the database which I get with show create table command.

The problem is that database parse query and might slightly change it with adding aliases, etc. Is there a parser that could normalize views to the same format so I could compare them like strings?

Right now I use approach with creating tmp view, getting the definition and dropping the view. It works, but looks not good.


texts: Why does mySQL automatically add display length to numeric values when adding zerofill property

texts:
I am trying to understand better how the data types are processed by the MySQL system, so I created a table in the following way:
# Field, Type, Null, Key, Default, Extra
test1, tinyint(3) unsigned zerofill, YES, , ,
test2, smallint(5) unsigned zerofill, YES, , ,
test3, int(10) unsigned zerofill, YES, , ,
test4, bigint(20) unsigned zerofill, YES, , ,

I initially added only the columns without autofill:
create table test (
test1 tinyint,
test2 smallint,
test3 int,
test4 bigint
);

And the table looked like this:
# Field Type    Null    Key Default Extra
test1   tinyint  YES
test2   smallint    YES
test3   int YES
test4   bigint  YES

But then I added the autofill property via alter table and the final result was this:
# Field Type    Null    Key Default Extra
test1   tinyint(3) unsigned zerofill    YES
test2   smallint(5) unsigned zerofill   YES
test3   int(10) unsigned zerofill   YES
test4   bigint(20) unsigned zerofill    YES

My question is why does it define automatically the display length for the columns?


texts: sql server: sp_send_dbmail : Can I build the data to send in discrete operation?

texts:
SQL Server 2012 and 2014

I can make sp_send_dbmail work fine when my SQL query is compact.
When the query itself is a big long beast, I can't make sp_send_dbmail work. (The deadly "
Failed to initialize sqlcmd library with error number -2147467259.")

The question is: Within SQL, can I use a different approach to send query results via email, to use in a SQL Agent job??

(IOW: Is there an approach OTHER than sp_send_dbmail + @attach_query_result_as_file)

Is it possible to build the data set to email in Step 1 of a job, and then in Step 2 take the result from Step 1 and attach it to an email?

Ideally, it would be possible to break the process up into discrete steps. The "single shot" sp_send_dbmail is fragile. If I could build the payload in one step, and pass that payload to the email in the next step, things would be much more managable. (And if that attachment could be zipped, oh the joy! And if it could be an xls, we could get some work done!)

(I just spent half a day fighting with sp_send_dbmail. The solution, after hours of obscure errors, was to package the big query in a view, so the sp_send_dbmail  query is just three lines. But this is crazy coupling. sp_send_dbmail  itself is the problem. We need a different path.)


texts: Order questions depending on answer votes

texts:
I'm really curious about how to create an SQL query to order questions depending the votes_up and votes_down. (The votes can be the sum of both, votes_up + votes_down.)

Question has_many answers
Answer belongs_to one question

The answer table is like:

Answer(id, answer, votes_up, votes_down)


texts:
We need to store simple log data in a database, but we also need to get and filter data in realtime, based on 'user', simple 'tag' (ie. redis, memcache, php, mysql, etc.) and timestamp. We need to scale horizontally and real fast data access on billions rows.

In a SQL approach, table can be like this:

ID | timestamp | tag | user       | log text
1  | 19543532  | 1   | root       | { text log }
2  | 19543532  | 3   | redis-user | { text log }

where tag 1 and 3 are different and related to another table (ie. tag_id | tag_name). I think this is a relational approach and we can create three index (timestamp, tag and user) in order to speed up data access.

What is a good practice to reproduce this in a NoSQL database like DynamoDB (AWS) where we can create only HASH or RANGE index? Does a SQL database fit better than a DynamoDB?

My first attempt is:

First table: ID hash index

ID | log text
1  | { text log }
2  | { text log }

Second table: USER, TIMESTAMP range index

user | timestamp | id
root | 123145122 | 1
redis| 123491241 | 2

Third table: TAG index

tag        | id
debug      | 1
production | 2

Thank you in advice!


texts: Information about a column and the data it holds?


texts: Get rows that are referenced in another table

texts:
I have two entities. product and bom.

CREATE TABLE product (
  id integer NOT NULL,
  CONSTRAINT product_pkey PRIMARY KEY (id)
)

/* Bill of materials*/
CREATE TABLE bom (
  id serial NOT NULL,
  parent_item_id integer NOT NULL,
  child_item_id integer NOT NULL,
  quantity integer NOT NULL
)

The entity product register single products and composite products (products made up of other products). The products that are composite, are referenced in the entity bom through parent_item_id and child_item_id.

I am using this structure because I can create composite products made of other composite products.

Now, What I want is to perform a SELECT that return all the products that are composed of another products.

More specifically, the records in the product table that are referenced in the table bom through product.id and bom.parent_item_id.

So far I have the next SQL code:

SELECT product.id
FROM product
INNER JOIN bom ON product.id = bom.parent_item_id;

My problem is that this JOIN give me duplicate product entries, obviously because product.id appears in bom as many product is made of.

I want just one entry per product that is referenced in bom


texts: SQL Server Redundancy without Client Application Changes

texts:
We have a custom .NET build application that has different clients which access the DB residing on SQL Server 2005 Standard. Now we want to add another SQL Server to create redundancy so that in case of failure of the existing SQL Server, the secondary server is able to cater the requests from the clients.

I have gone through Replication (and its different types), Mirroring and Clusters. But i am a bit confused about selecting the right option. My requirement is to have a secondary server without having any major changes to the client applications. Any suggestions ???


texts:
I am using DBdesigner Fork for generating ER diagram.

I have the 'Company' as an entity for which can I place the attribute of 'idShares', despite of having the foreign key of Shares Entity in the Company entity.

As, it can fetch the Share entity attributes automatically from the foreign key.
So, Can I place the attribute in the Entity, when the foreign key is also already present?


texts: Help understanding mongodb explain results

texts:
I have an index on field f1. {f1:1}

Running an explain on the following query

db.coll.find()

says that the winning plan is a CollScan (expected)

However, for db.coll.find().sort({f1:1})

the explain says

"stage" : "FETCH",
            "inputStage" : {
                "stage" : "IXSCAN",

I have trouble understanding this because even though the index is used for sorting , shouldn't it have done a collection scan to fetch the documents that needed to be sorted?

Thank you


texts: Database design - groups chat - seen messages

texts:
I have an application in which users form groups and chat together. One of the features is the well known "this message was seen at {some date here} by user {some user}". My concern is whether I've designed this correctly as with my current implementation database grows really fast in size.

My approach is really simple. Once a user sends a message to a chat group, assuming there are N memebers in that group, N - 1 rows are created in chatMessagesSeen table.

Columns of that table are id, messageId, userId, groupId, date and seen. Last column is a flag used to store whether the message was seen. So that's 4*4+8+1 = 25 bytes per entry. Pretty large chat groups are common, so 100 members in one room is nothing exceptional.

I'd very much appreciate if anyone could share experience with a similiar problem, what should I take into consideration and whether there are any better ways to do this (sure they are, right). I wonder how giant social sites with this feature handle the problem.


texts: Oracle custom week of the year numbering based on first Monday of the year

texts:
I have following sql for generating dates, I wanted to change week of the year numbering based on 'First Monday of the Year'.

Hence numbering would be,
01-05-15 ---> 01
01-12-15 ---> 02
01-19-15 ---> 03
...
...
...

SELECT TO_NUMBER (TO_CHAR (CurrDate, 'YYYYMMDD')) AS date_key,
   CurrDate AS alt_full_date_key,
   TO_NUMBER (TO_CHAR (CurrDate + 1, 'WW')) AS week_num_of_year,
   TO_NUMBER (TO_CHAR (CurrDate + 1, 'IW')) AS week_num_of_year_iso,
   NULL AS week_num_of_year_custom
FROM (    SELECT LEVEL n, TO_DATE ('28/12/2014', 'DD/MM/YYYY') + NUMTODSINTERVAL (LEVEL, 'day') CurrDate
          FROM DUAL
    CONNECT BY LEVEL <= 18000);


texts: Is it useful to split a table into a frequently used and a archive table?

texts:
I have a table with 1.000.000+ entries. Only the newest 100.000 entries are used very frequently. The other 90% are used very seldom.

Is it useful to split this table into a frequently used table with the 100.000 entries and a archive table?

I would have to move approx. 10.000 elements to the archive table every day.

The server logic to find an element would be:

Search in the frequently used table.
If not found there, search in the archive table.

Background

I've done some testing with random data in a small table and a big one (10 times more data). A SELECT query for one specific element took 0.6 times more time in the big table than in the small one. I believe that makes a difference in overall performance with 1000+ queries per second.

@Rick James

The create is

CREATE TABLE IF NOT EXISTS `note` (
  `note_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `title` varchar(100) NOT NULL,
  `content` text NOT NULL,
  `date_added` datetime NOT NULL,
  `date_modified` datetime NOT NULL,
  PRIMARY KEY (`note_id`),
  KEY `FK_note_user` (`user_id`),
  CONSTRAINT `FK_note_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`) ON DELETE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The test query was just:

SELECT *
FROM `note`
WHERE `note_id` = $note_id
LIMIT 1

@Walter Mitty

Your second thought was what i meant. The critical transactions only happen on the newest 10% of data. So would splitting make sense?


texts: MS-SQL-Server 2019 is slower than SQL-Server 2008

texts:
Since we updated from SQL Server 2008 to 2019, the queries have become much slower.
The following query ran for ten minutes on version 2008. On version 2019 it runs for over 30 minutes.
Until yesterday, we always limited the WHERE-clause: where date > '20180101'
To make the script at least a bit faster, I narrowed it down from January 2019: where date > '20190101'
Now it's even slower than before. No joke! Because one year less has to be evaluated, the running time has increased by a few minutes.
I have checked all the indexes in detail and I think we have created the correct indexes.
But I don't know if this is really an index problem, because in version 2008 we had the same indexes.
In version 2019 I even deleted and re-created a few important indexes. But that didn't help at all.
MS-SQL Server 2019 got very slow and I don't know why.


texts: Oracle Change Data Capture: Unsubscribed column results in change set record

texts:
I am using Oracle 11g's Change Data Capture to track changes on a database table. I am using synchronous mode to minimize latency. Here's an example to illustrate my problem:

Published_Table

important_column_a
important_column_b
useless_column_c

Now, both important_column_a and important_column_b are included in the PUBLICATION and SUBSCRIPTION. The third column, useless_column_c, is of no consequence and is not included in either PUBLICATION or SUBSCRIPTION.

My problem is that any time a change is made to the unpublished column (useless_column_c), a record is written to both the CDC table and the resultant CDC View on the subscription. This happens even if no other change is made to the table aside from the unpublished column.

The Published_Table experiences a significant number of transactions, but only changes to the subscribed columns require additional processing. As Oracle is sending every change across to the subscription, the result is an extensive amount of overhead as the subscriber sifts through what amounts to extra "noise."

Is there any way around this?


texts:
login failed for user 'domain\localMachineName$'. Reason: Could not find a login matching the name provided. [CLIENT: ]

This appeared on my new SQL Server right after I added it to an existing Windows cluster. It occurs exactly every 5 minutes. Seems a local resource is trying to log in but I can't see what it could possibly be related to.


texts: include table data in join with group by

texts:
I have the following tables, a short summary to understand how they are related to each other:

cities
+----+----------+
| id |  name    |
+----+----------+
| 1  | new york |
| 2  | dallas   |
| 3  | atlanta  |
+----+----------+

offices
+----+----------+-----------+
| id | address  | city_id   |
+----+----------+------------
| 1  | ...      | 100       |
| 2  | ...      | 101       |
| 3  | ...      | 290       |
+----+----------+-----------+

states
+----+----------+
| id |  name    |
+----+----------+
| 1  | pending  |
| 2  | approved |
+----+----------+

quotes
+----+------------+-----------+----------+
| id |   date     | office_id | state_id |
+----+------------+----------------------+
| 1  | 2020-03-15 |     1     |     1    |
| 2  | 2020-02-23 |     2     |     1    |
| 3  | 2020-01-22 |     2     |     2    |
| 4  | 2020-04-05 |     3     |     2    |
| .. | ...        |    ...    |    ...   |
+----+------------+-----------+----------+

Basically in a city there can be many offices and these can make quotations for various products, and the quotations have basically two states, pending or approved. So I would like to know how many pending (or approved) quotes there may be per city, so I did the following:

select `name`, COUNT(*) AS total
from `cities`
inner join `offices` as `o`
on `o`.`city_id` = `cities`.`id`
inner join `quotes` as `q`
on `q`.`office_id` = `o`.`id`
where `q`.`state_id` = 1
group by `cities`.`name`

This works, but clearly it will only show me the cities that have records in the quotes table, so how can I show all the cities and even if they do not meet the condition, show a total of 0, how should I make my query for this?


texts:
I have a MySQL InnoDb table with approximately 40 million rows and the following columns:

param1 (int),
param2 (int),
param3 (int),
geo    (char2),
value1 (bigint),
value2 (bigint)

I have an index on (param1,param2,param3,geo). Almost all queries against this table are of the form:

WHERE param1=?
AND param2=?
AND param3=?
AND geo=?

In total there are around 200 geo codes. So, we can reduce the number of rows by a factor of 200 by transforming the table structure to:

param1, param2, param3, value1, value2, US, DE, GB... `

This table is recreated and refilled every hour. Inserting data takes too long. I think reducing the number of rows x200 might decrease the insert time. Is it worth doing this denormalization for faster inserts?


texts: Should I use many single field indexes, instead of specific multi column indexes?

texts:
This question is about the effectiveness of a SQL Server indexing technique. I think it is known as "index intersection".

I'm working with an existing SQL Server (2008) application that has a number of performance and stability issues. The developers did some odd things with indexing.  I’ve not been able to get conclusive benchmarks on these issues, nor can I find any really good documentation on the internets.

There are many searchable columns on a table. The developers created a single column index on EACH of the searchable columns.  The theory was that SQL Server would be able to combine (intersect) each of these indexes to efficiently access the table in most circumstances.
Here is a simplified example (real table has more fields):

CREATE TABLE [dbo].[FatTable](
    [id] [bigint] IDENTITY(1,1) NOT NULL,
    [col1] [nchar](12) NOT NULL,
    [col2] [int] NOT NULL,
    [col3] [varchar](2000) NOT NULL, ...

CREATE NONCLUSTERED INDEX [IndexCol1] ON [dbo].[FatTable]  ( [col1] ASC )
CREATE NONCLUSTERED INDEX [IndexCol2] ON [dbo].[FatTable] ( [col2] ASC )

select * from fattable where col1 = '2004IN'
select * from fattable where col1 = '2004IN' and col2 = 4

I think multiple column indexes targeted to search criteria are much better, but I may be wrong. I have seen query plans that show SQL Server doing a hash match on two index seeks. Perhaps this makes sense when you don’t know how the table is searched? Thanks.


texts: How to Resolve deadlock on concurrent MERGE on two tables

texts:
Recently I have been encountering deadlocks occasionally when I try to insert data concurrently on two tables.

Following is the table structure

Create Table TableA
(
  Id Bigint not null primary key ,
  FieldA1 nvarchar(50)
)
go

Create Table TableB
(
  Id Bigint not null primary key,
  TableAId Bigint not null constraint FK_TableA Foreign Key (TableAId) References TableB(Id),
  FieldB1 nvarchar(50)
)
go

CREATE type TableBParam as table
(
    Id Bigint,
    TableAId Bigint not null,
    FieldB1 nvarchar(50)
)

--Deadlock was observed on the save query
go
CREATE PROC SaveTableB
(
 @val [dbo].[TableBParam] READONLY
)
AS
BEGIN
SET NOCOUNT ON;

MERGE [dbo].[TableB] AS T
USING (SELECT * FROM @val) AS S
  ON ( T.Id = S.Id)
WHEN MATCHED THEN
    update set FieldB1 = S.FieldB1
WHEN NOT MATCHED THEN
    insert(TableAId, FieldB1) Values(S.TableAId, S.FieldB1);

END
go

On a single transaction I am trying to complete data insertion on tableA and Table B by calling respective stored procedure (SaveTableA and SaveTableB).
SaveTableA is same as SaveTableB.

.

Database has read committed snapshot isolation enabled.
wondering how deadlock occurs on insertion? From the above graph I infer that the SaveTableB from one thread is victim while other instance of same procedure on different thread was holding lock on primary key of TableA.

From this reference I understand that the lock here is on the Btree of the Index based on the reference https://technet.microsoft.com/en-us/library/ms189849(v=sql.105).aspx and the deadlock graph above. While the locked resource is on the clustered index BTree node itself,

From the above inference I am curious to understand following

Does Insertion operation always holds lock at BTree level as seen the graph?
Trying to understand why there is a lock on the clustered index of TableA when the statements involved in the deadlock are SaveTableB procedure from two different transactions?
Since I am using the Merge statement here, is it the assumption that the btree lock on insert is hold on till the transaction ends or till the Merge statement ends?

Curious to understand that if it is possible to get deadlocks in this kind of scenario mentioned, trying to understand on how to approach it so that I will prepare myself to solve it. Thanks for helping me learn about this.


texts:
I have a table with a "Description" field.  Which contains the titles of a whole lot of phone models.

**Table1**
Id - Int (PK)
Description - nvarchar(200)

**Lookup Table**
LookupId - Int (PK)
SearchString - nvarchar(50)
Result - nvarchar(100)

I then have another lookup table with all the different models of phones I want to search for.

If I was going to do a text search on the description field I could easily write this by doing

SELECT * FROM Listing WHERE Description Like '%SearchTerm%'

However I want to somehow do a search on each record in the Lookup Table.  Im thinking that I probably have to do a loop for each record in the Lookup table and then write some dynamic sql?  Is there an easier way than this?


texts: Table with huge data takes long time to query

texts:
I have a table in Postgres that is having over 50million records in production. It’s a table that I have to query it often for debugging and data extraction because it is heavily used by the application.

However, due to the massive amount of data, the table takes a long time for every query to complete. Even for simple queries that just sort the updated_dt column without any other conditions can take a long time while queries with conditions can take 15-30mins to query! It doesn’t help even when I add a limit 1000 the results and indexed the updated_dt column.

This makes the whole table almost unable to be queried by any kind of statements. My Postgres database is hosted on AWS RDS on an already pretty big instance.

Here's the explain statements:

SELECT "id", "store_id", ... FROM inventory ORDER BY "updated_dt" DESC, "id" DESC  LIMIT 1000;

Limit  (cost=9737754.79..9737757.29 rows=1000 width=1710) (actual time=328054.666..328054.790 rows=1000 loops=1)
  Buffers: shared hit=976936 read=6371799
  ->  Sort  (cost=9737754.79..9829882.64 rows=36851142 width=1710) (actual time=328054.665..328054.711 rows=1000 loops=1)
        Sort Key: updated_dt DESC, id DESC
        Sort Method: top-N heapsort  Memory: 3401kB
        Buffers: shared hit=976936 read=6371799
        ->  Seq Scan on "inventory"  (cost=0.00..7717246.42 rows=36851142 width=1710) (actual time=0.011..291962.272 rows=36813941 loops=1)
              Buffers: shared hit=976936 read=6371799
Planning time: 0.078 ms
Execution time: 328055.138 ms

Here are the indexes applied to the table:

CREATE UNIQUE INDEX "inventory_pkey" ON "inventory"(id text_ops);
CREATE INDEX inventory_store_id ON "inventory"(inventory_store_id text_ops);
CREATE INDEX inventory_item_data ON "inventory" USING GIN (item_data jsonb_path_ops);
CREATE INDEX inventory_created_dt ON "inventory"(created_dt timestamptz_ops);
CREATE INDEX inventory_updated_dt ON "inventory"(updated_dt timestamptz_ops);

What can I do make this table which has a huge number of records run faster when being queried?


texts: MySQL joining two tables and counting based on conditions

texts:
table 1 > cats

cat1
cat2

table 2 > sites

site1 > lang1 > cat2
site2 > lang2 > cat1
site3 > lang3 > cat1
site4 > lang1 > cat1
site5 > lang2 > cat1
site6 > lang3 > cat2

the query should do this :

get cats and for every cat go to sites and count sites that belongs to that cat with lang1 , lang2 , lang3

I want output to be like :

cat1 > lang1 : 41 , lang2 : 11 , lang3 : 24

cat2 > lang1 : 17 , lang2 : 06 , lang3 : 74

thanks in advance


texts: Suggestion for log storage with search by keywords/tag

texts:
I'm trying to find a solution for dealing with a data set of logs of about 500Gb. Ideally what I search for is an index over log message keywords that I parse from the log messages. Like:

Log message
2019-05-06 12:43:32.133 ERROR [ 23][Repository] Updating record 356/23:2,4

Keywords: repository, updating, record, "356/23:2,4"

That search over the keywords like select from logs where keywords contains '356/23:2,4' (not an SQL).

So far I've tried DBMS proof-of-concept with keywords table, logs table and keywords-logs relation table. It works but to put it mildly, it is kind of not ideal. I've also tried influxdb, but it does not support multiple values per tag (otherwise it would be exactly what I looking for).

It seems to be quite basic stuff, so I hope there is just a right solution for that, ideally installed locally (not in cloud) and opensource. Any recommendation?


texts: Recovering from: A transport-level error has occurred when sending the request to the server in SSMS

texts:
Every now and than I loss the connection in SSMS and get the message

Msg 121, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - The semaphore timeout period has expired.)

The annoying thing is, that the button to change the connection ("Verbindung ändern" for me) is inactive, and I have to open a new tab, open the connection and copy the contents of the inactive window to it.

Is there a better way to handle this situation?


texts: Exclusion constraint on recursive foreign key (on the example of a Tree)

texts:
I have the table with recursive foreign key. It's just hierarchical tree structure:

CREATE TABLE tree (
    id              INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    value           INTEGER NOT NULL,
    tree_id         SMALLINT NOT NULL CHECK (tree_id > CAST(0 AS SMALLINT)),
    parent_id       INTEGER REFERENCES tree ON DELETE RESTRICT ON UPDATE RESTRICT,

    EXCLUDE (tree_id WITH =) WHERE (parent_id IS NULL) -- allow only one root within tree
);

How to reject inserting records with non-null parent_id and different tree_id? I.e. foreign key parent_id can have reference only to primary key id within the same tree_id.


texts:
While learning about "data layout", I came across the term "materialize", which does not have a clear definition and explanation. What is materialize and what does materialize do?
This question is not related to materialized views, and is not specific to any particular DBMS.
Context
The source that I am learning from: Youtube Video from Prof. Dr. Jens Dittrich
: Mapping Relations to Devices.
At timepoint 9:24 we have this term materialize. He said that is a process from physical page to storage devices.
And an additional question at this time point: Since physical pages should also belong to main memory, if we have a main-memory database, so the data layout only exists in main memory, will there be a materialize process?


texts: DB2 removing white spaces in field values

texts:
In a table I have a column called column_xml defined as CLOB(1000000). The fields in the column contain XML formatted information. My problem is that there are lots of white spaces in these fields that I need to get remove if there are more than 3 whitespaces in a row.

This is what the output looks like now

column_xml
--------------------------------------------------------------------------------
<values>
 <comment>test test test                               </comment>
 <rights></rights>
 <title>TEST MAN                                            </title>
</values>
--------------------------------------------------------------------------------

How can I remove all the white spaces before </comment> and </title> so it looks like this?

column_xml
--------------------------------------------------------------------------------
<values>
 <comment>test test test</comment>
 <rights></rights>
 <title>TEST MAN</title>
</values>
--------------------------------------------------------------------------------


texts: How to promote an existing index to primary key in PostgreSQL

texts:
I know how to make a primary key within a table, but how do I make an existing index a primary key?  I'm trying to copy an existing table from one database to another. When I show the table, the index at the bottom is in this form:

"my_index" PRIMARY KEY, btree (column1, column2)

I have created the index with:

CREATE INDEX my_index ON my_table (column1, column2)

But I do not know how to make it the primary key ...

UPDATE:  The version of my server is 8.3.3


texts: Is there a better way to handle a multi-level ParentId table structure?

texts:
I work for a publisher, and our products are mainly books and journals. They are most commonly structured as follows:
Book > Chapter

Book Series > Book > Chapter

Book > Volume > Chapter

Book Series > Book > Volume > Chapter

Journal > Volume > Issue > Article

Journal > Volume > Article

We currently have all of these records stored in the same table with Id and ParentId columns. For example, a book with TitleId = 1 that has 3 chapters would have the following rows:
Book: Id = 1, ParentId = 1
Chapter #1: Id = 2, ParentId = 1
Chapter #2: Id = 3, ParentId = 1
Chapter #3: Id = 4, ParentId = 1

All these records, whether they're books, chapters, journals, articles, etc., can have their Id connected to other tables for things like authors, prices, ownership, etc.
The problem this structure creates for us is that the nesting adds tons of overhead in certain situations. For example, if someone tries to access a journal article they purchased, we need to run multiple queries to know if they do, in fact, have access. We have an ownership table containing Id's of owned products, so we not only need to check if the user owns the journal article's Id directly, but we also need to check for ownership of the Id's for the article's parent issue, volume, and journal. (I.e., if the user owns the entire journal, it's implied they own all of the articles within that journal despite not having explicit access.) So our main "ownership" query is rather bulky since we need it to check for ownership at all possible levels of nesting.
There are many other similar situations. For example, if we need to get all the authors associated with a book, we need to run a query for the book directly, then walk down to each chapter and get all the authors for each one, or if the book has volumes, we need to walk down each volume, and get the authors for each volume's chapters.
Another situation is searching, where we need to basically aggregate all these different types to be seen as relatively equal so a search can be performed on them, and the results, whether they're a book, chapter, journal, articles, etc., all need to appear alongside each other in the search results.
What I'm wondering is if there's a way to improve our setup so we can get relational data faster (things like ownership checks, authors, prices, etc.), by either:

Restructuring our main table to get away from the Id/ParentId setup, or,
Building new tables/views off to the side.

I'm interested in any comments/advice/recommendations anyone has about our current setup, or proposing a new setup.


texts: Best way to keep the last N rows by a certain column

texts:
Let's say I have a table of employees and I want to record their monthly salary inside a table and I only want the latest 12 rows to be there.

I know I can do something like this every time I want to insert a salary record:

INSERT INTO salary (employee_id, amount, timestamp) VALUES (id, amount, current timestamp);
DELETE FROM salary WHERE employee_id=id ORDER BY timestamp ASC OFFSET 12;

But is there a better approach?

Akina suggested:

  Update the most ancient record instead of insert/delete. The amount of records will be constant, and you'll store the most recent records.

That seems like it could be really inefficient? As I am developing an application, I need to check if there are 12 rows or not, if there isn't then insert, if there is, I need to update all the rows in the table and move the data on the bottom row and update the first row - that'll take a lot of queries. I need to use automation, I need a better approach to improve performance.

I tried to read from the official reference and search the google to find clear details about INSERT INTO ON DUPLICATE and REPLACE, but unfortunately I can't understand how they could help in my situation.

Can someone give me an example approach please?


texts:
I'm trying to create a trigger that sets an updated flag when a field is changed. The reason I can't do it with a basic UPDATE query is because the change will come from a INSERT ON DUPLICATE UPDATE.

This is the trigger:

CREATE TRIGGER set_update_flag BEFORE UPDATE ON `reputation_email_score`
FOR EACH ROW
    BEGIN
        IF NEW.`score` <> OLD.`score` THEN
          SET NEW.`updated` = "yes";
        END IF;
    END;

When I try to run it, I get these errors:

Error in query (1064): Syntax error near '' at line 5
Error in query (1064): Syntax error near 'END IF' at line 1
Error in query (1064): Syntax error near 'END' at line 1

What is wrong in my SQL query?


texts: Enforce Data Integrity without a direct Foreign Key relationship

texts:
I have a new table that will have a column named County_Codes.  I would like to enforce that data to a list of County_Codes from another table named Institutions.  However, due to this being a legacy, poorly designed table I do not currently have the option of re-writing the database and this is the best source of this data.  The values of County_Codes in the Institutions table can appear multiple times, or be NULL.  I can generate a query to create a unique list of values from this table:

select distinct County_Codes from dbo.Institutions

Is there a way to enforce records in the new table to one of those county codes in the database?

Example of County codes (they're char(5)):

00001
00101
10101
11000
90563
etc...


texts: Custom calendar for calculating workdays per month

texts:
I need to create a custom report for my PMO (Project Management Office) where they want allocation to be calculated by multiplying working days for each month * total hours.

For example, January 2014 should have 21 working days (2 holidays minus Saturday and Sunday) * 8 = 168 hours.

How would I go about creating this logic for the calendar? Should I focus on creating a function that will calculate working days for each month? Also we have various calendars but the server that I'm using doesn't have any connection to them.


texts: MySQL PDO Cannot assign requested address

texts:
Can someone help me with this error?

[08-Apr-2013 17:44:08 Europe/Berlin] PHP Warning:  PDO::__construct(): [2002]
    Cannot assign requested address (trying to connect via tcp://****:3306) in
    /var/www/***
[08-Apr-2013 17:44:08 Europe/Berlin] PHP Fatal error:  Uncaught exception
    'PDOException' with message 'SQLSTATE[HY000] [2002] Cannot assign requested
    address' in /var/www/***

I have a Server with a lot connections per second; out of about 100 Connections, a single one got this error.

I've tried this recommendation from stackoverflow however it does not solve my problem.


texts:
I'm attempting to apply patch 23530402 to Oracle 11.2.0.4 with OPatch 11.2.0.3.15 on a 64-bit Windows 2008 server.  Our Tenable security software indicates this is one of two patches that must be applied to resolve a finding.  Patch 23530402 is the prerequisite for the second patch.
There are two patches already applied:  22839608 and 22839614.
OPatch bombs out with the following error in the log:

Conflicts/Supersets for each patch are:
Patch : 23530402
Conflict with 22839614
Conflict details:
D:\dbApplicationX\Oracle\product\11.2.0\dbhome_1\rdbms\admin\oracle.sym
D:\dbApplicationX\Oracle\product\11.2.0\dbhome_1\bin\oracle.exe
Bug Superset of 22839608 Super set bugs are: 17551261, 18226143, 18689530, etc.

The log then recommends using the MOS Patch Conflict Checker at https://support.oracle.com/epmos/faces/PatchConflictCheck to resolve the conflict.  Unfortunately, the Conflict Checker utility states that "patch(es) on Windows platform are not supported for conflict analysis".  (You have to float your mouse over the button long enough to activate the tool tip containing this information; otherwise it gives no indication of what's wrong with it.)
The log also has this advice:

OUI-67073:UtilSession failed: Please rebuild the superset patch
[23530402] to make sure it supersedes all the relevant patch(es)
[22839608,22839614].
The rebuild patch should contain bug fix
[17551261, 18226143, 18689530, 18904062, etc.].

It wants me to rebuild an Oracle patch?  How?  Is this an appropriate time to use opatch apply -force?  Thanks for any help you can provide.  Definitely not an Oracle master here, so it's appreciated.


texts:
I have an NDB cluster which consists of 4 data nodes. Some of the configuration parameters and system resources as :

CPU: 32
RAM: 128GB
Replication factor: 2
Data memory: 70GB
Index memory: 10GB

I am planning to extend cluster capacity. I have two options to achieve this:

I can add more RAM to data nodes so data memory and index memory will be able to double up
I can add 4 data nodes more to cluster so total data memory and index memory increases too

I am not sure which one is a better solution while extending cluster capacity. If I choose first option data size stored per node will be bigger. On the other hand what about 140GB DataMemory per data node? Are there any recommended maximum value for DataMemory ve IndexMemory parameters?


texts: Is there a way to determine if SQL Server queries are running in memory or going to disk?

texts:
I came across a set of stored procedures in an application today that are called repeatedly within a long running process.  Within each procedure I found multiple different select statements, some within loops; not surprisingly, these routines as currently used take several minutes to run, when intuition would expect them to complete in a few seconds.

It seems fairly obvious that performance wasn't taken into consideration when these procedures were written, there are multiple instances of things that are just "not a good idea".

Processing each row when importing data is taking 300ms per row, so relatively small imports are taking several minutes to process.

However, the tables involved in the procedures are for the most part quite small.  I'm thinking, if all of these tables are fully resident in memory, perhaps there's not all that much that can be gained by rewriting any of this.

I'm trying to determine....for this obviously inefficient code, how much of a real affect is it having?  Is it worth fixing?

So the question is:
 - is there a way to determine what tables are entirely pinned in memory?
 - is there a way to turn on tracing in order to monitor nested stored procedures to find the particularly expensive portions?

Note: This is on SQL Server 2008 R2


texts: Reject Specific Database PostgreSQL User Authentication

texts:
Lets say I have two databases: Sales and Inventory

And two users: A and B

I want user A to be able to do: psql sales B

But user A must not be able to do: psql inventory B

Right now user A can access both of the above psql databases by logging in as user B, as done above.

Here's 3 options I've considered:

Edit the /opt/pgsql/data/pg_hba.conf file

But, configuring that file creates tons of security issues

Create a ~/.pgpass file for user A and B

For some reason I can't even get postgres to acknowledge the file's existence, even when I enter ~/.pgpass's contents as \*:\*:\*:B:wrong_password

Create a PGPASSWORD environment variable

Not database specific


texts:
I feel like this isn't the best question to ask on a programming site like this, but I haven't really found a satisfying answer online and I would really like to get a different perspective on this.

Currently, we're working on a project at school, but instead of relational databases, we are instructed to use XML databases and related technology (XQuery, XPath and the like). I find that writing the code and infrastructure is quite straightforward, but even after working with it for a while, I can't seem to wrap my head around the advantages of choosing XML databases over a relational model.

My questions, then, are:

Could you describe some advantages that XML databases have over relational?
Could you provide an example where it might be better to use an XML database over a relational one?


texts:
I have a custom ssis task that extracts data from google analytics.  Google analytics allows you to request data in up to 10k rows chunks.   The problem im having is that SSIS seams to be unable to handle this amount of data.    I have been forced to set the number of rows returned down to smaller chunks.  But the problem arises when we try and export data for companys that have millions of hits it takes forever to run becouse it has to loop though so much data.

My question:  Is there any way to either fix my custom task to help SSIS handel the large amounts of data, or can i fix ssis itself to increase the amount of data it can handel?

public override void PrimeOutput(int outputs, int[] outputIDs, PipelineBuffer[] buffers)
    {
    PipelineBuffer buffer = buffers[0];
{
 There is a loop for each row. then over each column
 buffer[colIndex] = columnvalue;
}

 buffer.SetEndOfRowset();
}


texts: SQL Server - Cursors - Missing Afterthought - Safeguard against infinite looping in case of missing FETCH NEXT

texts:
It was found that this code mistake caused an infinite loop on PROD:

DECLARE @BatchID INT
DECLARE MyCursor CURSOR FOR
    SELECT BatchID = ...
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @BatchID
WHILE @@FETCH_STATUS = 0
BEGIN
    ...
    IF [condition]
    BEGIN
        ...
        FETCH NEXT FROM MyCursor INTO @BatchID
    END
END
CLOSE MyCursor
DEALLOCATE MyCursor

Assuming the need for a cursor is warranted, is there any way to safeguard against this mistake (besides more testing/code review)?

In other languages, we have FOREACH loops that manage progressing the iteration for us, or FOR loops that have an afterthought, so is there any equivalent in SQL Server that prevents sloppy mistakes like misplacing (or forgetting!) it?

I have never seen custom loops in SQL need to do anything fancy with the cursor, and WHILE loops have the same risk (along with cases like DELETE #WorkData WHERE ID = @BatchID when @BatchID is NULL), so how might this risk be mitigated programmatically/cleanly for the typical use case?

An approach like this is very unappealing:

DECLARE @BatchID INT
DECLARE MyCursor CURSOR FOR
    SELECT BatchID = -1--dummy entry to always be skipped
    UNION ALL SELECT BatchID = ...
OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @BatchID
WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH NEXT FROM MyCursor INTO @BatchID
    IF @@FETCH_STATUS = 0
    BEGIN
        ...
    END
END
CLOSE MyCursor
DEALLOCATE MyCursor

It seems to me that checking @@FETCH_STATUS twice in a row without any cursor position/control statements in-between could be an effective way for SQL Server to guess if there was such a mistake, as my experience with cursors has never seen two checks in a row intentionally (without nested cursors at least, but those still have control statements like OPEN and CLOSE between checks of @@FETCH_STATUS of the outer cursor).

P.S. The [condition] this time was equivalent to "not the DST transition day" (which was this Sunday). More time zone bugs!


texts:
Shall i upgrade Database server from 2005 to 2012 without upgrading SSRS or web Application relying upon the database?
Will the SSRS reports and Web application continue to work?
Would be appreciated if you could respond quickly


texts:
There are 3 machines as follows:

Machine A - Remote Exadata DB machine
Machine B - Remote machine from which only I can connect to port 1521 of Machine A
Machine C - Local Machine (To run SQL Developer and can't connect machineA directly)
Machine D  - Bastion Server through which I connect to Machine B

Machine C can access service running in Machine B only using SSH tunnelling.

Is there any way I can access the Exadata DB from Machine A using SQL Developer running in Machine C?

I am trying this way:

ssh MachineD -L 15219:MachineB:15220 -L 15220:MachineA:1521

which is not working.


texts: BACKUP MASTER KEY failing with cannot find symmetric master key because it does not exist

texts:
I'm trying to backup the master key for a TDE database, but SQL Server says there isn't one. It's a bit weird, but I'm sure I'm just doing something wrong. I'm sysadmin on the server, so I should be able to see everything.

This is the statement that is failing:

USE [my_db];
BACKUP MASTER KEY
TO FILE = 'C:\master_key'
ENCRYPTION BY PASSWORD = 'some_killer_password';

And the error message returned:

  Msg 15151, Level 16, State 1, Line 11
  Cannot find the symmetric key 'master key', because it does not exist or you do not have permission.

I've used the following to see the details about the database encryption key, and the associated certificate, however the certificate details from the sys.certificates table is empty.

USE [my_db];
SELECT DatabaseName = d.name
    , ddek.encryptor_type
    , ddek.opened_date
    , c.name
    , c.cert_serial_number
    , c.pvt_key_encryption_type_desc
    , c.subject
FROM sys.dm_database_encryption_keys ddek
    INNER JOIN sys.databases d ON ddek.database_id = d.database_id
    LEFT JOIN sys.certificates c ON ddek.encryptor_thumbprint = c.thumbprint
WHERE d.name <> 'tempdb' /* tempdb is auto-encrypted by SQL Server */

╔══════════════╦════════════════╦═════════════════════════╦══════╦════════════════════╦══════════════════════════════╦═════════╗
║ DatabaseName ║ encryptor_type ║ opened_date             ║ name ║ cert_serial_number ║ pvt_key_encryption_type_desc ║ subject ║
╠══════════════╬════════════════╬═════════════════════════╬══════╬════════════════════╬══════════════════════════════╬═════════╣
║ my_db        ║ CERTIFICATE    ║ 2017-09-20 11:24:13.590 ║ NULL ║ NULL               ║ NULL                         ║ NULL    ║
╚══════════════╩════════════════╩═════════════════════════╩══════╩════════════════════╩══════════════════════════════╩═════════╝

So, I can see the database encryption key in [my_db], and I can see it's encrypted by a certificate, but the certificate doesn't exist?


texts: OVER and PARTITION BY for db size change

texts:
I have the following column below. I am not too familiar with the OVER function. Question is, will the column usage_change_mb be the daily change (from daily snapshots) or an average change of all the snapshots being evaluated?

Here is the column:

( LAST_VALUE ( reservedpages ) OVER ( PARTITION BY instance_name, database_name ORDER BY instance_name ASC, database_name ASC, snapshot_time ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING )

- /* subtract */

FIRST_VALUE ( reservedpages ) OVER ( PARTITION BY instance_name, database_name ORDER BY instance_name ASC, database_name ASC, snapshot_time ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING ) ) * 8 / 1048576.0
AS usage_change_mb


texts:
I have a question about query decomposition and localization, as in here:

When joining 3 tables (here EMP, ASG and PROJ) is there any specifiy order, when to join which table?
Why ASG and EMP are joined first, then the "result" is joined with PROJ?

Can all the 3 tables not be joined parallelly:

Would the following also be possible (just switching the order of PROJ and EMP):


texts:
I am writing a stored procedure for Firebird 2.5 in which I sometimes need to update a single row in a data table more than once in the same transaction. However, I have noticed that the performance degrades drastically when I do this. (From an execution time measure in ms to sometimes a second or more!)
Is this something intrinsic to Firebird 2.5, or am I mistaken?
Do I just need to ensure that I only update each row once per transaction?


texts: Isolation and atomiticy of SQL Server UPDATE

texts:
Is there any difference in atomicity and isolation between the two following SQL statements?

Batch A:

UPDATE Orders
SET InProgress = 1
WHERE Completed = 0

and

Batch B:

UPDATE Orders
SET InProgress = 1
WHERE OrderID in (SELECT OrderID FROM Orders WHERE Completed = 0)

Under the default isolation level in SQL Server of READ COMMITTED, I believe you could have concurrency issues with the batch B. After the subquery select, another process could set Completed to 1 on some of the selected rows, but batch B wouldn't know it and would still set InProgress = 1 on them.

But what about batch A? Could another process change Completed on any rows before InProgress is set to 1 on them by batch A? Or is it atomic and isolated from any other process?

Another way to ask this is, when is the update (or exclusive) lock placed on the table under READ COMMITTED isolation? In batch A, I'd assume it happens before the WHERE clause is evaluated, making the batch atomic and isolated. What about batch B? Is the update/exclusive lock placed on Orders before or after the subquery is evaluated?


texts: Can MariaDB or MySQL cast as bigint, getting an error: ... right syntax to use near 'bigint)'?

texts:
If I want to take a string and create a table (this fails in strict mode),

CREATE TABLE g
  AS SELECT CAST('2147483699' AS int);

How do I tell it that I want a bigint type? I'll also get an int(10) type. SELECTing from it is humorous too,

+---------------------------+
| CAST('2147483699' AS int) |
+---------------------------+
|                2147483647 |
+---------------------------+

The data is actually wrong, and it silently failed. I know that's a known issue with this database having really poor defaults, but I how can I create the table as a bigint?

CREATE TABLE g
  AS SELECT CAST('2147483699' AS bigint);

Doing that I get this error,

  ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'bigint)' at line 2

How do I cast an interger to a bigint?

PostgreSQL

With PostgreSQL, it's pretty simple. In addition to throwing an exception if you cast to an int, you cast to a bigint using

SELECT CAST('2147483699' AS bigint);
    int8
------------
 2147483699


texts: Modelling limited N-N relationship in DynamoDB

texts:
Having previously never worked much with NoSQL databases, I decided to throw myself in the deep-end with DynamoDB. Mainly because if I get the hang of it, then I have a small personal project that would be great to run off their free-tier.

For the time being, I'm just trying to get to grips with it all, and I'm trying to work out the best way to do N-N modelling. This is based on a previous database schema that is run on MySQL.

Take the below for example. We have categories of "books" or "films". The book or film can have up to 10 tags (limited by the system) so that we can get all similar "books" and "films".

| PK         | SK (group/parent) [GSI PK] | name [GSI SK]      | Attributes... |
|------------|----------------------------|--------------------|---------------|
| Category-1 | Categories                 | Books              | ...           |
| Product-1  | Category-1                 | Harry Potter       | ...           |
| Product-1  | Witchcraft (tag name)      | Books#Harry Potter | ...           |
| Product-1  | Wizardry (tag name)        | Books#Harry Potter | ...           |
| Category-2 | Categories                 | Films              | ...           |
| Product-2  | Category-2                 | Harry Potter       | ...           |
| Product-2  | Witchcraft (tag name)      | Films#Harry Potter | ...           |
| Product-2  | Wizardry (tag name)        | Films#Harry Potter | ...           |

In terms of data access, the most common queries are something along the lines of:

Get all categories
Get all products for a specific category
Get specific product
Get all products by tag

The first three are simple. The fourth one is straight forward enough to find the products (you query on the GSI where GSI PK = tag name. However that only gives you a list of product IDs.

So my question is, is the best method, given the limit of 10 tags, to just put all of the product metadata in each of the tag items as well? It'll be easy to keep updated as all the data is next to each other. Obviously we then have the data duplicated 11 times, but the expected size of all the attributes isn't more than about 512 bytes, so in reality, the increase in storage (and therefore cost) is negligible given its ~200,000 items per GB.

| PK         | SK (group/parent) [GSI PK] | name [GSI SK]      | Attributes 1 | Attribute 2 | ... | Attribute n |
|------------|----------------------------|--------------------|--------------|-------------|-----|-------------|
| Category-1 | Categories                 | Books              |              |             | ... |             |
| Product-1  | Category-1                 | Harry Potter       | Product Name | Image URL   | ... | Price       |
| Product-1  | Witchcraft (tag name)      | Books#Harry Potter | Product Name | Image URL   | ... | Price       |
| Product-1  | Wizardry (tag name)        | Books#Harry Potter | Product Name | Image URL   | ... | Price       |


texts: Selecting Distinct Rows but Counting All Rows

texts:
I have the following table:

----------------------------------------------
| ID | interestingData |      timestamp      |
----------------------------------------------
|  1 |       400       | 2016-01-23 17:01:00 |
----------------------------------------------
|  1 |       400       | 2016-01-24 17:01:00 |
----------------------------------------------
|  1 |       350       | 2016-01-25 17:01:00 |
----------------------------------------------
|  2 |       23        | 2016-01-23 17:01:00 |
----------------------------------------------
|  2 |       34        | 2016-01-24 17:01:00 |
----------------------------------------------
|  2 |       12        | 2016-01-25 17:01:00 |
----------------------------------------------

Where our PK is (ID, timestamp). I'm attempting to determine a query that will give me the unique IDs and the latest interestingData for which interestingData exceeds a threshold. That would, of course, be done with:

SELECT DISTINCT ID
FROM table
WHERE interestingData > threshold
ORDER BY timestamp DESC;

However, I want the count of every occurrence where interestingData exceeded the threshold. My results table would ideally look like

------------------------------------------------------
| ID | interestingData | timestamp           | count |
------------------------------------------------------
|  1 |        350      | 2016-01-25 17:01:00 |   3   |
------------------------------------------------------

Were my threshold 300. I am aware that if you want to pair something distinct with a set of data then a left outer join is going to be in order, but I'm not entirely sure how to go about it. This is the closest I can think of so far.

SELECT DISTINCT ID
FROM table t1
LEFT OUTER JOIN table t2 ON t1.ID = t2.table.ID
WHERE interestingData > 300
ORDER BY timestamp DESC

This gets me the distinct IDs and pairs them with the rest of the data as I need, but no provisions for getting the other parts of the results, let alone the count.


texts: Postgres planner overerstimate index only scan

texts:
\d hits;

    Column    |            Type
--------------+-----------------------------
 id           | integer
 link_id      | integer
 country_code | character varying(2)
 created_at   | timestamp without time zone

Indexes:
    "hits_pkey" PRIMARY KEY, btree (id)
    "hits_id_country_code_idx" btree (id, country_code)

VACUUM;

EXPLAIN ANALYSE SELECT id, country_code
  FROM hits
  WHERE id > 0;

 Seq Scan on hits
    (cost=0.00..2194406.50 rows=73641640 width=7)
    (actual time=63752.432..126357.467 rows=100000000 loops=1)
   Filter: (id > 0)
 Planning time: 183.492 ms
 Execution time: 129469.889 ms

SET enable_seqscan = OFF;

EXPLAIN ANALYSE SELECT id, country_code
  FROM hits
  WHERE id > 0;

 Index Only Scan using hits_id_country_code_idx on hits
     (cost=0.57..2385505.27 rows=73641640 width=7)
     (actual time=67.338..13708.853 rows=100000000 loops=1)
   Index Cond: (id > 0)
   Heap Fetches: 0
 Planning time: 239.017 ms
 Execution time: 16077.119 ms

How can I help the planner to understand how many values in the visibility map are set? Because when I run a VACUUM frequently the index only scan is much faster, but the planner chooses a sequence scan instead.