text column compares equal to where clause but does not select matching row We are having trouble when querying a table in our production database. One text column will compare equal to a string we filter on in the where clause, but postgres will not select the row.
(We are on postgres 11.11)
Our table set up like this:
(PROD)=> \d names;
                           Table "public.names"
        Column        |            Type             | Collation | Nullable | Default
----------------------+-----------------------------+-----------+----------+---------
 name                 | text                        |           | not null |
 processed_name       | text                        |           | not null |
 name_index           | integer                     |           | not null |
 when_created         | timestamp without time zone |           | not null |
Indexes:
    "names_pkey" PRIMARY KEY, btree (name, processed_name)
    "names_name_index_key" UNIQUE CONSTRAINT, btree (name_index)
    "ix_names_name" btree (name)
    "ix_names_processed_name" btree (processed_name)

When we process a list of names we check if they are already in the table in order to prevent double adding and violating the primary key constraint.
However on one name, 'Сергей Иванович МЕНЯЙЛО', the query to see if the name is already present returns an empty set
I would expect to get back the row with the same name. However when we attempt to insert the row in the table we get a primary key conflict
Here are some queries which might explain the problem better
(PROD)=> SELECT name_index,
    name,
    name = 'Сергей Иванович МЕНЯЙЛО' names_compare_equal
FROM names where name_index = 75128;
      name_index      |          name           | names_compare_equal
----------------------+-------------------------+---------------------
                75128 | Сергей Иванович МЕНЯЙЛО | t
(1 row)

However filtering instead on the name column selects no rows.
2021-05-24 20:37:41 UTC
(PROD)=> SELECT name_index,
    name,
    name = 'Сергей Иванович МЕНЯЙЛО'
    names_compare_equal
FROM names
WHERE name = 'Сергей Иванович МЕНЯЙЛО';

       name_index     | name | names_compare_equal
----------------------+------+---------------------
(0 rows)

So then if we try and insert the row we get a primary key conflict:
(PROD)>=> INSERT INTO names (name_index, name, processed_name, when_created)
  VALUES (89266, 'Сергей Иванович МЕНЯЙЛО', lower('Сергей Иванович МЕНЯЙЛО'), now());

ERROR:  duplicate key value violates unique constraint "names_pkey"
DETAIL:  Key (name, processed_name)=(Сергей Иванович МЕНЯЙЛО, сергей иванович меняйло) already exists.

What's more, if I query based on the hash of the rows I get the correct result:
(PROD)=> SELECT name_index,
    name,
    name = 'Сергей Иванович МЕНЯЙЛО' names_compare_equal
FROM names
WHERE md5(name) = md5('Сергей Иванович МЕНЯЙЛО');

      name_index      |          name           | names_compare_equal
----------------------+-------------------------+---------------------
                75128 | Сергей Иванович МЕНЯЙЛО | t
(1 row)

This only happens on our production database - which has the following encoding settings
      Name      |     Owner      | Encoding |   Collate   |    Ctype    |
----------------+----------------+----------+-------------+-------------+
 PROD DB        | PROD DB OWNER  | UTF8     | en_US.UTF-8 | en_US.UTF-8 |

This is pretty baffling to me so an ideas about what to check next would be helpful

A corrupted index would be the prime suspect here. Test with:
SELECT * FROM names WHERE name || '' = 'Сергей Иванович МЕНЯЙЛО';

The expression name || '' cannot use any indexes, so you get a sequential scan. If that query finds your entry, you have your diagnosis: corrupted index. Probably the one on just (name), but since multiple indexes qualify, recheck with EXPLAIN. (It's probably not the PK as that one still throws a unique violation in your test, but that one may be corrupted, too ...)
There is a note in the release notes for Postgres 11.11 in particular:

... see the second changelog item below, which describes cases in which reindexing indexes after the upgrade may be advisable.

Or maybe locales have been updated in your underlying OS? Same fix: reindex.
There are other reasons for index corruption, but the only other common one is hardware issues. That should trigger more drastic measures immediately, starting with a backup.
Recreate affected indexes. You can use REINDEX:
REINDEX INDEX ix_names_name;

If you need to allow concurrent access to the table use the non-blocking (but slower) CONCURRENTLY:
REINDEX INDEX ix_names_name CONCURRENTLY;

If there is reason to believe the problem might be systemic, recreate all indexes on the table:
REINDEX TABLE names;

Or the whole the database:
REINDEX DATABASE name_of_current_database;

In case of a bigger cleanup, I would advise a maintenance window without concurrent access. And plenty of maintenance_work_mem.
Aside, you have these indexes for the two columns name and processed_name:
"names_pkey" PRIMARY KEY, btree (name, processed_name)
"ix_names_name" btree (name)
"ix_names_processed_name" btree (processed_name)

The PK index on (name, processed_name) can be used for everything that the additional index on just (name) can be used. That additional index only might be useful if processed_name is a rather large column - in which case I would consider a more efficient PK to begin with. See:

Is a composite index also good for queries on the first field?


nesting MAX for SELECT I have a table, containing TV series episodes:

 series:
    | series_id | season | episode | title
    |         1 |      1 |      18 | xxx
    |         1 |      2 |       3 | xxy
    |         3 |      1 |      13 | yyy
    |         3 |      2 |       5 | yyy

I want to get the latest episode from each show, so 2x03 from series 1, and 2x05 from series 3.

I have 3 ideas, none of them great:

Create a new integer column, calculated as season x 100 + episode, now I can sort easily by it, and quite fast, but I have to recalculate it every time if the season or episode column is updated.
Write a select that first finds the max(season) for each series, and use it in a second select. Easier to maintain, but I was not able to write the right sql statement yet.
Use a calculated aggregare:

  SELECT series_id, max( 100* season + episodes ) as latest FROM series GROUP BY series_id;

Which option is better? How should I write option 2?

It can be done with a query:

SELECT
    s.*
FROM
  ( SELECT DISTINCT series_id
    FROM series
  ) AS ds
  JOIN
    series AS s
      ON  s.series_id = ds.series_id
      AND s.season =
          ( SELECT si.season
            FROM series AS si
            WHERE si.series_id = ds.series_id
            ORDER BY si.season DESC
            LIMIT 1
          )
      AND s.episode =
          ( SELECT si.episode
            FROM series AS si
            WHERE si.series_id = ds.series_id
            ORDER BY si.season DESC, si.episode DESC
            LIMIT 1
          ) ;

Tested at SQL-Fiddle

With an index on (series_id, season, episode), it should be quite efficient, if the number of series_id is small.

CREATE UNIQUE INDEX uix
  ON series
  (series_id, season, episode) ;

There are several ways to do this query but all get rather complicated because the maximum needed is based on two columns (season and episode). Here's another one, which is the query above, simplified a bit:

SELECT
    s.*
FROM
  ( SELECT series_id, MAX(season) AS season
    FROM series
    GROUP BY series_id
  ) AS ds
  JOIN
    series AS s
      ON  s.series_id = ds.series_id
      AND s.season = ds.season
      AND s.episode =
          ( SELECT si.episode
            FROM series AS si
            WHERE si.series_id = ds.series_id
              AND si.season = ds.season
            ORDER BY si.episode DESC
            LIMIT 1
          ) ;

and another using NOT EXISTS:

SELECT s.*
FROM series AS s
WHERE NOT EXISTS
      ( SELECT 1
        FROM series AS sb
        WHERE sb.series_id = s.series_id
          AND ( sb.season > s.season
             OR sb.season = s.season AND sb.episode > s.episode
              )
      ) ;


store text differences I want to store text differences in MySQL. Let's say I have a text, 500 characters or so, and then someone edits the text. I want to be able to reconstruct both the old and new text.

From a database viewpoint, I thought it would be stupid to store both texts if I could just store the difference. That would save a lot of space, especially if the edits are minute and the texts huge. But how do I do that?

I had some ideas which were really complex, such as storing only the old text, and when an edit occurs, replace the changed parts with a certain variable which then refers to another table. That other table could simply store the new and old value of that particular section of the text.

Is there a simple way to do this?

If you store old text and differences, you will have to always apply everything to get current text. So I suggest storing current text and diff (sort of reversed changes) to get previous version. Keep table for those diffs like (text_id, version_number, diff).

For differences you can sure use standard diff tool. And if you want to save space, use some compression (either on mysql side or application side).

Do you need to search in those texts? If yes then in current version only or in all versions? The latter might be a bit bigger challenge to do effectively.


Select table name based on parameter I am trying to get data from table based on parameters,
Declare @Loadtype varchar(10) = 'A'

Select *
from TableA (if  @Loadtype = 'A')

Select *
from TableB (if  @Loadtype = 'B')

Not sure how I can achieve this.
Many Thanks,
Uday

You can do a UNION between the two tables and modify the WHERE clause based on the parameter passed (i.e., SELECT from both tables and let the parameter decide whether to return any rows).
Or you can use dynamic SQL, i.e., build a string and execute that string using sp_executesql. Required reading before using dynamic SQL is http://www.sommarskog.se/dynamic_sql.html. Erland even has a section about this particular requirement.
And, you can of course have an IF and do two separate SELECTS, which one depends on the contents of the variable.
To be honest, above "solutions" sucks. I suggest you take a step back and look at the broader picture.
And to make it clear: You can not have a variable or parameter for the table name in a DML command.


Using Hashes as Unique ID I am creating a database that will hold a number of transaction records.

I was wondering if it is a good idea or good practice to use a hash function that can be replicated easily for verification as the ID column. i.e. SHA1(UserID + TimeOfTransaction)

Generally not a good idea, for the following reasons:

If someone knows UserID and times, they may be able to match that back to transactions (depending on how this ID is exposed - if it's exposed externally via an API, someone knowing UserID could generate candidate TransactionIDs from guessed times)
High amount of entropy in the key means that your indexes on this key are going to fragment rapidly. If your DB has a page cache, the cache efficiency will drop as your data volume grows (this can be a killer).
If you can handle the performance concerns, then why not just generate a GUID?


can not connect to oracle database with ip address I am new to oracle database, I have installed Oracle 11.g on redhat 5.6, I have a database that I can connect to it using the following command:

export ORACLE_SID=test
sqlplus user/passwd

but not connect to database neither remotely nor from localhost:
    sqlplus user/passwd@myhost:1521/test
    error:
    ORA-12541: TNS: no listener

this is my listener.ora file:

# listener.ora Network Configuration File:

/Oracle/oracle_base/product/11.2.0.4/database/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
      (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1522))
    )
  )

ADR_BASE_LISTENER = /Oracle/oracle_base

this inside tnsnames.ora file:

test =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = test)
    )
  )

and this is output of lsnrctl status is:

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 16-JAN-2017

15:57:02

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1522)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                16-JAN-2017 15:45:32
Uptime                    0 days 0 hr. 11 min. 30 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /Oracle/oracle_base/product/11.2.0.4/database/network/admin/listener.ora
Listener Log File         /Oracle/oracle_base/diag/tnslsnr/myhost/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1522)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=myhost)(PORT=1522)))
The listener supports no services
The command completed successfully

and this is output of tnsping test:

oracle> tnsping test

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 16-JAN-2017 16:00:45

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/Oracle/oracle_base/product/11.2.0.4/database/network/admin/sqlnet.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = test)))
TNS-12541: TNS:no listener

Listener listens on port 1522, but you are trying to connect port 1521. Update your tnsnames.ora with the correct port number (1522).


SELECT ro.ROO_Id, ro.ROO_Name, ra.RAT_Price
FROM ___Rooms ro, ___Rates ra, ___Availabilities av
WHERE (FIND_IN_SET(ro.roo_id, ra.RAT_RoomsList) OR ra.RAT_RoomsList='')
  AND av.AVA_Status = 'Open'
  AND av.AVA_RoomId = ro.roo_id
  AND av.AVA_Date BETWEEN GREATEST(ra.RAT_DateStart, '2019-04-02')
                      AND LEAST(ra.RAT_DateEnd, '2019-04-04')
GROUP BY ro.ROO_Id, ro.ROO_Name, ra.RAT_Price
HAVING COUNT(*) = 1 + DATEDIFF('2019-04-04', '2019-04-02')

fiddle


Check attribute table referenced foreign key I have two tables a and b like this:

a: id, type
b: a_id, ...

There is a limitation for which the references of a in b can only be those which have type equal to a limited set of values.

Is it possible to include a CHECK for an attribute of the referenced table or do you have a better design idea?

It's possible to add a_type to b, and have check constraint on b.a_type.  Breaking normalization a bit allows enforcing storing details in proper table . In Oracle you can have 2 constrains supported by the same index, for instance PK on a.id, and UNIQUE on (a.id,a.type) , then some tables that require proper type may have FK to (a.id,a.type), and other tables that don't care about type can refer to a.id .


Which parameters was a stored procedure or command executed with? We are using nHibernate which generates queries from .NET code. Occasionally, some queries get stuck in the sp_whoisactive list and we cannot find out why. I have a feeling that it has to do with a missing or broken parameter. When I run sp_whoisactive, however, I only see the parameter name (in example: @p0), not the actual value. Is there a way to retrieve this information while the process is still running?

Example:

SELECT
    this_.Id as Id6_2_, this_.HRNumber as HRNumber6_2_,
    this_.FirstName as FirstName6_2_, this_.LastName as LastName6_2_,
    this_.StatusTypeID as StatusTy5_6_2_,
    this_.PropertyId as PropertyId6_2_, this_.DepartmentGroupId as Departme7_6_2_,
    property2_.PropertyID as PropertyID10_0_,
    property2_.PropertyCode as Property2_10_0_,
    property2_.LMSCode as LMSCode10_0_, property2_.PropertyName as Property4_10_0_,
    property2_.Report as Report10_0_, department3_.Id as Id4_1_,
    department3_.Name as Name4_1_, department3_.DisplayName as DisplayN3_4_1_
FROM
    [dbo].[EmployeeDepartmentGroupView] this_
LEFT OUTER JOIN
    [dbo].[Property] property2_ ON this_.PropertyId=property2_.PropertyID
LEFT OUTER JOIN
    [dbo].[DepartmentGroup] department3_ ON this_.DepartmentGroupId=department3_.Id
WHERE
    this_.HRNumber LIKE @p0;

Please note, this is automatically generated SQL from nHibernate.

Specs: SQL Server 2008r2

If you have a suspicion about which processes/procedures, etc cause the issue, could you not use SQL Profiler / Trace / extended events to get the data? - it is then fully recorded including the Parameters that were passed. - of course this would need to be running ahead of time - not any use once it's already running. However a well filtered Trace can have relatively low performance impact, so you could leave the Trace running all the time.

Here's basic how to setup a Trace/Profiler: How To Create a Trace with SQL Server Profiler

You can find better sources of info on this once you have your head round the concepts.


SQL Server 2008 user, group, or role already exists in the current database I am trying to migrate an old, problematic Plesk server to a new one but there are some problems with SQL Server 2008.

Plesk couldn't migrate one of the DBs. So, I backed up the DB and restored on the new server. Then I tried to restore 'User Mappings' for the existing user but I got "User, group, or role already exists in the current database" error. I googled for solutions and found a few but they didn't work.

I tried to run the following procedure but no luck.

sp_change_users_login 'Auto_Fix', 'UserName'

I also tried to delete it's scheme as a solution but couldn't do it because - according to SQL Server - it's referenced by an object.

I don't want to change that object's user/scheme because I am afraid of to broke something.

What should I do?

Note : Whenever you move your database from one server to another
  server there is a problem of orphan user usually occur. FileListOnly
  is the new term in sql server that has all the details of the created
  backup that who has access to it.

So there are sequence of task that you have to follow

  Firstly you have to restore the FileListOnly from the created backup to the destination or new server.
  Restore the backup.
  Use the sp_change_users_login  as per the need. For help regarding this procedure you can refer
  http://msdn.microsoft.com/en-us/library/ms174378.aspx.

I put a example here hope it will help:

> RESTORE FILELISTONLY FROM DISK = N'C:\YourDB.bak'
>
> RESTORE DATABASE YourDb FROM DISK = N'C:\YourDB.bak' WITH MOVE
> N'YourDB' TO N'D:\YourDB.mdf', MOVE N'YourDB_log' TO N'D:\YourDB.ldf',
> REPLACE
>
> exec YourDB.dbo.sp_change_users_login 'update_one', 'UserName','UserName'


Where are filtered-index dependencies listed? Given a table of the form...

create table dbo.FilterIDXTest (
    id int not null identity primary key clustered,
    _bigint bigint,
    _varchar10 varchar(10),
    _varchar20 varchar(20),
    _guid uniqueidentifier
);

go

create unique index uq_FilterIDXTest
    on dbo.FilterIDXTest ( _varchar10, _varchar20 )
    include ( _guid )
    where _bigint is not null
        and id > 5;

go

... in what (if any) catalog view (or other well-formed reference object) can the columns names in in the WHERE clause be located and rigorously identified?

sys.indexes can be viewed to remind us that the filter_definition is ([_bigint] IS NOT NULL AND [id]>(5)).

sys.index_columns can be viewed to tell us both the ON and INCLUDE columns but lists only 3 of the 5 columns in the original CREATE INDEX command. The filter_predicate columns are not found here.

I must assume that the filter_predicate dependencies are rigorously checked at some stage because sp_rename 'dbo.FilterIDXTest._bigint', 'foo_bigint', 'column' fails with the the error...

Caution: Changing any part of an object name could break scripts and stored procedures.
Msg 5074, Level 16, State 1, Procedure sp_rename, Line 611
The index 'uq_FilterIDXTest' is dependent on column '_bigint'.
Msg 4922, Level 16, State 9, Procedure sp_rename, Line 611
RENAME COLUMN _bigint failed because one or more objects access this column.

...and I can't bring myself to believe that the only reason this fails is because of some text matching at sys.indexes.filter_definition.

For reference, the task at hand is to extract the source code from a number of undocumented legacy servers. My request to use RedGate/some other paid helper app for this purpose has already been rejected since "You can just script out the objects from the UI, right?". Perhaps I'm been overly rigorous, but I'd like to have a very high level of confidence that the index I'm checking in to source control is the index that actually exists. I'm testing this script on sql server 2012, but the target servers on which it will ultimately be executed range from 2005 SP1 to 2008 R2 SP3.

I think this information is stored in sys.sql_expression_dependencies. See the table in MSDN, sys.sql_expression_dependencies:

THIS TOPIC APPLIES TO:
yes: SQL Server (starting with 2008)
no: Azure SQL Database
yes: Azure SQL Data Warehouse
yes: Parallel Data Warehouse
Remarks
The following table lists the types of entities for which dependency information is created and maintained. Dependency information is not created or maintained for rules, defaults, temporary tables, temporary stored procedures, or system objects.

Entity type          Referencing entity    Referenced entity
Table                Yes*                  Yes
View                 Yes                   Yes
Filtered index       Yes**                 No
Filtered statistics  Yes**                 No
...

** Each column used in the filter predicate is tracked as a referencing entity.

Also note that filtered indexes were added as a feature in version 2008 and this system view does not exist in 2005 (thnx to @Michael Eriksson for noting this).


Query for all the Postgres configuration parameters‘ current values? You can set various configuration parameters for Postgres by either editing the postgresql.conf file manually or by calling ALTER SYSTEM commands. These are two avenues for writing the settings, but how about reading?

➥ Is there a way to query for all the current settings?

I know the settings in the client authentication configuration file pg_hba.conf can be read with the pg_hba_file_rules view, like this: table pg_hba_file_rules ;. Is there something similar for postgresql.conf?

SHOW ALL

The SHOW ALL command displays the current setting of run-time parameters in 3 columns.

SHOW ALL ;

pg_settings

The pg_settings view shows the same items as SHOW ALL but with additional details, across 17 columns versus 3 columns.

TABLE pg_settings ;

pg_file_settings

To read what is stored in the postgresql.conf file itself, use the view pg_file_settings.

If freshly written, this file will hold values that may not yet be in effect within the Postgres server. After writing, the settings must be loaded in one of various ways including a server restart.

TABLE pg_file_settings ;

Admin tool

Your databased-administration tool may display these settings.

For example, in pgAdmin 4, choose the cluster name (the Postgres installation) in the navigation panel, click the Dashboard tab, and in a list bottom panel titled Server activity, click the Configuration tab to see a list of your settings.

Writing

By the way… For info about writing these settings with ALTER SYSTEM commands, see my Answer on another Question, How to edit postgresql.conf with pgAdmin 4?.


Cannot connect to local db I am in a Supply Chain Masters program and need to install the Excel Data Mining add-in for Excel. I downloaded and installed Microsoft® SQL Server® 2012 SP1 Data Mining Add-ins for Microsoft® Office®.

This package includes two add-ins for Microsoft Office Excel (Table Analysis Tools and Data Mining Client) and one add-in for Microsoft Office Visio 2010 (Data Mining Templates). The add-ins are supported on Office 2010 and Office 2013.

Once installed, I cannot connect to localhost. I run two separate accounts: an admin account and a regular account. I've tried starting the service and it doesn't work.

I've tried to uninstall and reinstall several times. Any help would be greatly appreciated.

Note that this download SQL Server® 2012 SP1 Data Mining is not a SQL Server database service. Its just a tool/add-on to work with Office and connect to a database.

You must install SQL Server to connect to the database.

Microsoft offers Express Edition to use locally with no licence need.


SQL Schema for XBRL Document storage Does anybody know of a relational database schema that can be used to store XBRL data? I am looking to store financial report data (balance sheet, income statement and profit/loss statement data) along with other company data (Name, Stock Ticker, etc.) in MySQL.

Wouldn't any db supporting the XML sections of SQL2003 work?  If you are trying to store the document as reported, I think you'd want to store as an XML file, not breaking it apart.

So I think if you are storing these as records of documents, your schema is not going to be based on XBRL but rather based on simple document management.


How can I achieve conditional page breaks in SSRS 2016 I am using SSRS 2016 and Report Builder. I need the page breakinging with a group to be conditional on the value of a parameter. Does anyone know if this is even possible?

Another workaround, which seems to work quite nicely - place a parent group of the group you want to selectively break on, then in Group Properties for that, group on a derived expression of either the group field, or a fixed value. And then tick the box for that grouping as per your screenshot.

e.g. Assuming a parameter called "BreakPages", and a grouping on a field called "MyGroupField", you group on:

=iif(Parameters!BreakPages.Value="Y", Fields!MyGroupField.Value, "None")

You don't need to display any fields in this special group, it'll simply use the page break setting as per your requirements..


Amazon RDS Promote Db Read Replica to RDS Db Instance When I promote a read replica to a db instance, does it replace the main db instance from which it was a read replica or does it create an entirely new Db Instance. I do not want to replace the Db Instance that the read replica is part of. I want to promote the read replica to a new Db Instance for testing.
thanks for replies and other suggestions

When I promote a read replica to a db instance, does it replace the main db instance from which it was a read replica or does it create an entirely new Db Instance?

It does neither of those things.¹

Promoting an RDS replica converts the replica, in place, into a new and independent master.  The action leaves both the master and the replica instances running, exactly as they were, except that the replication connection between the two servers is permanently severed, and the former replica becomes an independent, writable master.

It cannot be rejoined to its former master as an RDS replica again after promotion, and it contains a copy of the data as it existed when it was promoted.

¹ This isn't true for Aurora, but the phrasing of the question implies that you aren't using Aurora.


How to export some rows of multiple tables linked between them according to one column value? I'm not sure my question is clear so let's me rephrase it. I have three tables organized like that:

I want to make a dump of these tables but I'm not interested by all the rows, I just want those who were last updated during the last month.
Is there a way with Data Pump Export to do it? I thought to the QUERY parameter but I can't use it because there is no last_updated_date in the SON and DAUGHTER tables.

For instance, if, in my MOTHER table, I have 3 rows:

id_mother = 1 ; last_updated_date = 01/01/17 ;
id_mother = 2 ; last_updated_date = 01/02/17 ;
id_mother = 3 ; last_updated_date = 01/03/17.

And, in my SON table, I have 2 rows:

id_son = 1 ; id_mother = 2 ;
id_son = 2 ; id_mother = 3.

If I want to export the rows of month of March, the process will return one row for the MOTHER table (id_mother = 3) and one row for the SON table (id_son = 2).

How can I handle it?

you can, if you use a correlated subquery. Check the manual.

QUERY=son:"where exists (
    select *
    from mother
    where
        mother.id_mother = ku$.id_mother and
        mother.last_updated > trunc(sysdate,'month')"

ku$represents the table you are actually exporting, so here it is the table son.

Starting with Oracle 12c you have the VIEWS_AS_TABLES parameter. Then you can export  the data of a view and store it in a table, e.g. with sqlplus do

create view new_son
as
select son.*
from son, mother
where
    mother.id_mother = son-id_mother and
    mother.last_updated > trunc(sysdate,'month')"

Now export this using

VIEWS_AS_TABLES='new_son'

If you import this it will create a table new_son.


How to create a database that is a randomized subset of another db (mysql)? I have a MySQL-DB containing 2 tables "records" and "features" linked by one foreign key in features referring to the primary key "id" in records.

My goal is to create a randomized but fixed subset of the data in the DB for machinelearning that can be dumped and hashed for reproducability.

So I thought it would be handy if I could draw a randomized subset of the data and move it to a seperate DB.

Is that possible and if yes: how?

Add another column to the table.  It could be DOUBLE or INT or BIGINT, depending on size, etc.  Populate it with some function of RAND().  Also, index the column.

Now you can fetch rows that are consecutive in this new column; they will be "random" from the point of view of the original data.  And it will be reproducible.

SELECT * FROM tbl
    WHERE myrand > $x
    ORDER BY myrand  LIMIT 200000;

In this case I am allowing $x to give you different subsets of the table -- each reproducible.  You would need to adjust $x to avoid overlapping between one subset and another (if that matters).

Note:  I did not copy the data over, merely fetched directly from the original data.  This may be an added benefit.  Or it simply gives you the way to 'copy' the rows.


Add running count of specific state changes I have a table in PostgreSQL with below fields, where timestamp is simplified as integers:

ts  status
1     m
2     m
3     i
4     s
5     s
6     i
7     i
8     m
9     s
10    m

I want to break then aggregate rows based on the presence of the 's' status, to spit out an additional column which I can use to easily group rows:

ts  status  flag
1     m      1
2     m      1
3     i      1
4     s      1
5     s      1
6     i      2
7     i      2
8     m      2
9     s      2
10    m      3

The dynamic column flag shall increment whenever status changes from 's' to something else.

I don't see a clear pattern to partition by. How to do this?

Two levels of window functions.
To keep the code short, you can combine the aggregate FILTER clause with the outer window function:

SELECT ts, status
     , 1 + count(*) FILTER (WHERE previous_status = 's' AND status <> 's')
                    OVER (ORDER BY ts) AS flag
FROM (
   SELECT *, lag(status) OVER (ORDER BY ts) AS previous_status
   FROM   tbl
   ) sub;

db<>fiddle here

Assuming current Postgres and all columns NOT NULL.
Related:

Return counts for multiple ranges in a single SELECT statement
Grouping or Window


Split a very large SQL table to multiple smaller tables Is it possible to search a column within a table for a numerical value range of 1-2000 and then copy each full row into a different table? By doing so technically splitting the table into smaller parts.

Addressing the question behind the question:

  Maybe you can answer this. Is it possible to search a column within a table for a numerical value range of 1-2000 and then copy each full row into a different table?

If you already have a column by which you can "partition" the table, then you can do something like this:

SELECT *
 INTO dbo.newtable_0001_to_2000
 FROM dbo.existingtable
 WHERE column >= 1 AND column <= 2000;

SELECT *
 INTO dbo.newtable_2001_to_4000
 FROM dbo.existingtable
 WHERE column >= 2001 AND column <= 4000;

...

Just note that this will give you a predictable number of rows, but won't control the size of the rows, so depending on the data in each "partition" these tables will not likely be of equal sizes.


DELETE command not completing on 30,000,000 row table I have inherited a database and am looking to clean and speed it up. I have a table that contains 30,000,000 rows, many of which are junk data inserted due to an error on behalf of our programmer. Before I add any new, more optimized indexes, I converted the table from MyISAM to InnoDB and am looking to delete a lot of the rows that contain junk data.

The database is MySQL 5.0 and I have root access to the server. I was first running these commands through Adminer and then phpMyAdmin, both with the same results.

The command I am running is,

DELETE
FROM `tablename`
WHERE `columnname` LIKE '-%'

Essentially, delete anything in this column that begins with a dash -.

It runs for about 3-5 minutes and then when I view the process list, it's gone.

I then run,

SELECT *
FROM `tablename`
WHERE `columnname` LIKE '-%'

and it returns millions of rows.

Why is my delete statement not completing?

PS, I am aware of how out-of-date MySQL 5.0 is. I am working on moving the DB to MySQL 5.6 w InnoDB (maybe MariaDB 10 w XtraDB) but until that happens, I am looking to answer this with the DB as is.

--

Edit removed, see my answer.

Please look at the Architecture of InnoDB (picture from Percona CTO Vadim Tkachenko)

The rows you are deleting is being written into the undo logs. The file ibdata1 should be growing right now for the duration of the delete. According to mysqlperformanceblog.com's Reasons for run-away main Innodb Tablespace:

Lots of Transactional Changes
Very Long Transactions
Lagging Purge Thread

In your case, reason #1 would occupy one rollback segment along with some of the undo space since you are deleting rows. Those rows must sit in ibdata1 until the delete is finished. That space is logically discarded but the diskspace does not shrink back.

You need to kill that delete right now. Once you kill the delete query, it will rollback the deleted rows.

You do this instead:

CREATE TABLE tablename_new LIKE tablename;
INSERT INTO tablename_new SELECT * FROM tablename WHERE `columnname` NOT LIKE '-%';
RENAME TABLE
    tablename TO tablename_old,
    tablename_new TO tablename
;
DROP TABLE tablename_old;

You could have done this against the MyISAM version of the table first. Then, convert it to InnoDB.


design database with 2 simillar tables I have a database which has product_sell and product_buy tables, these two tables are different in a few fields and each one has own comment table

product_sell >---< product_sell_cmt
product_buy >---< product_buy_cmt

now , I want to ask which modelling approach is better ?

design four tables as showed above
design product_sell and product_buy separately with common comment table
merge two product tables in one table with unused columns in rows or even using 1 to 1 relations

since, I will use product_sell and product_buy and their comments lonely in some cases , is it useful for indexing and performance to have 4 separated tables ?

Read a plain language description of your data. A Product is a noun. A noun can become an entity and an entity becomes a table (or several tables). The words buy and sell are verbs, actions that take place with entities. Verbs do not become tables. A suggestion was to have a transaction table. Fine, "transaction" is a noun and the actions buy and sell would be one of its attributes.

There are many ways to design such a schema based on your specific needs, but in general you would have one Products table with a complement of supporting tables. One such supporting table could be Comments. A comment is an attribute of the Product entity but you could normalize it to a separate table if you really wanted to. You should have a valid reason for doing so, however. For example, if you want to allow a comment on every state change, that will be several comments possible for each product. First normal form. That's a valid reason. But beware. Such comments would be attributes of the state change (transaction) rather than the entity itself.

Other supporting tables would help you track state changes to your entities. One such would be Transactions which could track "Ordered", "Received/Entered to Inventory", "Sold", "Shipped" and any other state you want to track. While a state is an attribute of the Product entity, there will be several of them (even if only one is current at any given time) so that would require normalization to a separate table.

Many books have been written on database design. But for your example the biggest no-no right now is creating a table to represent actions: buy and sell in your instance. That is going to be really difficult to work with.


SQL Server error "Missing family sequence number 2" I'm trying to restore a local database on my server, but I'm having problems with the backup. I did a file .bak but every time I try to load it on the server it doesn't let me.

Here's a screenshot of the error message I receive:

I read that I can use RESTORE LABELONLY to restore my file but still not working, every time I run it I get something like this:

  Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.JOSEMA\MSSQL\Backu\INNOVA-T.bak'. Operating system error 3(failed to retrieve text for this error. Reason: 15105).
  Msg 3013, Level 16, State 1, Line 1
  RESTORE LABELONLY is terminating abnormally.

Any help would be great. Thank you.

The error indicates that your .bak file is only a part of the backup. When the backup was written it was written across three files. If you can write a backup to multiple files on multiple drives, it will often complete faster.

According to this StackOverflow Q & A, RESTORE LABELONLY should help you confirm that the backup spans multiple files, but it won't let you actually restore the backup - you'd need all three files for that.

If you can locate all three files (and are sure they're from the same run of the backup), you should be able to restore the backup - you'll need to specify all three files in the RESTORE command (or through the SSMS restore dialog).

IF you can't, but you still have access to the original server, then you can take a new backup.

NOTE: If that original server has a DBA, work with him. You want to make sure that you taking your backup won't cause issues with any replication, log shipping, or ability to restore to a point in time.

If you're effectively the DBA, then:

Get into SQL Server Management Studio (SSMS), connect to the server, and expand Databases.
Right-click on the original database, and choose Tasks -> Backup... A dialog window will open.
On the first page of the dialog:

Make sure the database you want to back up is selected as the Source database.
Make sure the Backup Type is "Full".
Check on "Copy Only Backup".
Make sure backup component is set to "Database"
At the bottom of the window: if there are any existing entries in the "Destination" list, remove them.
Click "Add" to add a new backup file. Make sure that you know where the file will be (generally, you've only got the option to write to the DB server's local disk using this interface; if you need to write to a folder on a shared server, you can try pasting in the full UNC path to the shared server and entering the new file name in the file selection dialog. If that doesn't work, specify a local drive and a unique file name). Also, make sure the file name is unique - you don't want to overwrite any existing files on the server.
When you get back to the original Back Up dialog window, change to the "Options" page (left side of the window).
Under "Overwrite Media", select "Back up to a new media set, and erase all existing backup sets".

Yes, this sounds scary - however, this is primarily used to reinitialize a backup tape so any old data is erased; as long as you're writing to a new file name, everything should be fine.
Enter a new for the media set - something like " - one time backup" is sufficient.

You can set some of the options under "Reliability"; I'd just leave them all off.
You can accept the default for "Set backup compression" as well; I'd probably set tit to "Compress backup", but that's not critical. (If you're not running SQL Server 2008 or later, then this is either not there at all, or not relevant).
DO NOT click "OK", instead, click the little arrow by "Script" at the top of the dialog window, and choose "Script Action to New Query Window". Once you can see the script, click "Cancel".

Double-check the script - there should only be one file included in it.
If you wanted to write the file to a different location, you can change the file path in the script. Again, the server needs to be able to access the location you specify, so the account used to run the SQL Server service (and/or the SQL Server Agent service) needs to have access to the path where you want the file written.
Now, run the script.

Assuming all goes well, you can now use your new backup file to perform the restore to the other location. Again, the SQL Server you're restoring to will need to have access to the backup file, so you may want to copy it to that server.


Is it possible to backup and restore a materialized view with it's data? I have some hard to compute materialized views, which I want to be able to backup and restore using the actual stored data, and not by recomputing.

Is it possible in PostgreSQL 9.4?

(the alternative is to create actual tables, but they  are harder to "refresh")

Not really. You have to remember that pg_dump command creates simple CREATE TABLE and INSERT statements etc. So effectively when running pg_restore you're just running CREATE and INSERT statements on the server and inserting the data would require a "INSERT INTO MATERIALIZED VIEW"-command. That wouldn't make sense as getting the data by a shortcut would also danger the "integrity" of the view - you could insert invalid data on the manual restore process that the view wouldn't return otherwise. So I doubt that PostgreSQL would ever support manual restoring of materialized views data straight into database.

If you need to backup the actual data I would recommend using a table instead of materialized view and running a scheduled DELETE FROM/INSERT INTO statements to the table.


Export all tables in database using SQLCMD I'm looking to export all tables from a SQL Server Standard Edition database with data into CSV files onto a remote linux server. I setup FreeTDS and am able to connect, do SQLCMD or bcp successfully.

I know we can use SQLCMD but I was wondering if there is a way that we can write each table into its individual CSV file using a single connection instead of looping through every table in the database and writing to a CSV file.

Any thoughts if its possible?

Thanks in advance,
simak

I looked into doing this years ago and looked into both BCP and SSIS. Neither one really provided for great flexibility. BCP needed format files to get the text-qualifiers, etc. But this was for a dynamic backup solution for a subset of tables to get archived nightly. SSIS seemed a bit more dynamic (though not fully), but couldn't do a few of the things that BCP could. I needed something that could dynamically accept changes to tables (include new tables, don't require removed tables) AND changes to columns (as I needed to do "SELECT * FROM {SchemaName}.{TableName};") AND dynamic text-qualification AND column headers, etc.

If you have a static set of tables, then SSIS, using a ForEach container, would be one way to do this (again, assuming the desire is to stay away from BCP due to requiring multiple calls).

Using PowerShell would probably also be an option that would allow for more flexibility than SSIS.

However, if you need something fully dynamic and/or need to stick with a single connection  per each DB (all tables in that DB) or even a single connection per server (all tables for all databases), then the only way I have ever found to handle this is the one that I created. It is a SQLCLR stored procedure called DB_BulkExport which has parameters for @Query and @FilePath that allow for easily cycling through a list of all databases, then per each one cycling through a list of tables, and building both the query and filename from those pieces. You could write a fairly simple stored proc that uses a cursor to get the Database and Table names and call this proc, hence being a single call to SQLCMD.EXE to call that one proc. Or it can be scheduled via SQL Agent without needing to enable xp_cmdshell.

This procedure -- DB_BulkExport -- is part of the SQL# library (which I am the creator of), though it is only available in the Full (i.e. paid for) version, not in the Free version. I generally do not mention stuff here that is not available in the Free version, but as I said, I am not aware of any other existing means of automating this type of process. Of course, anyone is free to write this themselves, either as a SQLCLR proc as I have done, or as a stand-alone .NET app, or using PowerShell. It is more than a few lines of code in C# and I suspect it is also not overly simplistic in PowerShell either (but I haven't tried and don't have enough experience with PowerShell to really know).

EDIT:
Regarding how I have used the above mentioned stored proc in at least one instance: It was used to export data nightly, for long-term archival, the oldest day from a large table that was itself a common, short-term archival of data from 20 source servers.

It would extract maybe 10 million rows for a given date
The table held 150 - 450 million rows (fluctuation due to several factors)
It would grab all records for the oldest date and export them to a delimited file
1 day was 5 million rows (avg)
Each row was 1240 bytes (avg; 2 URLs plus a few other text fields, etc)
The file was 3 GB - 6 GB depending on the day
It exported to a local temp folder so that it didn't need to go across the network.
The archival process then:

used the File_GZip function -- which can handle zip64 (i.e. source files over 4 GB) -- to compress the text file down to 1 GB - 2 GB (no, it didn't take much memory; buffer is 8k)
used File_Copy to copy the .gz file to a networked archive folder
used File_Delete to remove the local file(s)
If the above steps completed successfully, the date of the data that was just archived was placed into a queue table for another SQL Agent job to pick up and incrementally (in small batches as the table was constantly being filled from the 20 source servers) delete the records from the archive table.

So to amend the description above of the archival process: it would extract the oldest date that was not in this queue table.
Once all of the records had been deleted the date being deleted would be removed from the queue table.

Ideally there would be a local drive that would not have pagefile, mdf, or ldf files on it that could be used for the extraction and compression. For the long-term archival process noted above, that server was a dedicated archive that just collected data for a few tables from the 20 source servers. Hence the only querying of the data 99% of the time was the SELECT * FROM ArchiveTable to do the extract; the other 1% of the time it was research.


Compare Indexes/Constraints between two SQL Server databases I have two databases on one SQL Server that I would like to compare Index/Constraints. The two databases should be identical but I have a suspicion that one database is missing a index or constraints. Is there a way to script this out to compare the two?

Thank you!

DECLARE @Sourcedb sysname
DECLARE @Destdb sysname
DECLARE @SQL varchar(max)

SELECT @Sourcedb = '<<Database1>>'
SELECT @Destdb = '<<Database2>>'

SELECT @SQL = ' SELECT ISNULL(SoSource.name,SoDestination.name) ''Object Name''
                     , CASE
                       WHEN SoSource.object_id IS NULL      THEN SoDestination.type_desc +  '' missing in the source -- '
                                                                                + @Sourcedb + ''' COLLATE database_default
                       WHEN SoDestination.object_id IS NULL THEN SoSource.type_desc      +  '' missing in the Destination -- ' + @Destdb
                                                                                + ''' COLLATE database_default
                       ELSE SoDestination.type_desc + '' available in both Source and Destination'' COLLATE database_default
                       END ''Status''
                 FROM (SELECT * FROM ' + @Sourcedb + '.SYS.objects
                        WHERE Type_desc not in (''INTERNAL_TABLE'',''SYSTEM_TABLE'',''SERVICE_QUEUE'')) SoSource
      FULL OUTER JOIN (SELECT * FROM ' + @Destdb + '.SYS.objects
                        WHERE Type_desc not in (''INTERNAL_TABLE'',''SYSTEM_TABLE'',''SERVICE_QUEUE'')) SoDestination
                   ON SoSource.name = SoDestination.name COLLATE database_default
                  AND SoSource.type = SoDestination.type COLLATE database_default
                  ORDER BY isnull(SoSource.type,SoDestination.type)'

EXEC (@Sql)


Can you rename or name Reporting Services jobs? In Report Manager you can create schedules, either shared or one-of's.  When you create these schedules a job is created in SQL Server with names that look like guids.  IS it safe to rename these jobs to something readable and understandable, or will renaming them break something?  Or, is there to give these schedules a name in Report Mnager that will be reflected in the job?

The GUID identifies the job in the ReportServer database too. So don't change it.

That is, the schedule uses uniqueidentifier datatype in the table: the value matches the job


Converting a relation into BCNF I understand that to convert to BCNF, we need to take into account all violations and decompose as necessary with each FD.

For example, if we had R(A,B,C,D) with FD's AB->C, B->D, C->A. We can compute the closures of each, {AB}+ = ABCD, {B}+ = BD, {C}+ = CA.

And after going through the algorithm, a valid decomposition into BCNF would be R1(B,D), R2(C,A), R3(B,C).

Where I'm confused is that while this decomposition seems correct following the algorithm, how can it be correct if the first functional dependency AB->C does not seem to be satisfied?

The decomposition that you have produced is in effect correct, in the sense that the decomposed schemas are in BCNF.

However, as you have already noted, it does not preserve the dependencies, in particular the dependency AB → C is lost.

So you have re-discovered an important point about the decomposition in BCNF: one can always decompose a relation in BCNF, but at the price of sometimes losing one or more dependencies.

What does this mean in practice? We can lose (possibly important) constraints. In this case, for instance, the constraint that for each couple of values AB there is always a single value of C cannot be enforced on the resulting schema.

Can we do something for this? Well, we could decompose instead in 3NF, because the synthesis algorithm used for 3NF is guaranteed to produce always lossless and functional dependency preserving decompositions. In this case, for instance, it will produce the decomposition R1(A B C) and R2(B D), that maintains all the dependencies.

But, wait a moment! we have now a decomposition that does not eliminates all the redundancies eliminated with the BCNF algorithm, since, because of the dependency C → A, we will have the same value of A each time we have a certain value for C.

So, we have now a dilemma: should we prefer the 3NF that preserves the dependencies at the expense of maintaining some redundancy, or should we prefer the BCNF that reduces the redundancies at the expense of losing some “meaning” of the data?

The opinion of many is that we should choose the 3NF, since data meaning is considered more important that data redundancy (and not only this, but because the 3NF algorithm is a polynomial algorithm, while the BCNF algorithm is an exponential one).


How do I reduce SharePoint 2010 SQL server log files? I have a work place portal built on SharePoint 2010 and the SQL Server drive space is full. I am using SQL Server 2008 R2. Of the 800 GB disk space, the log files have taken approximately 730 GB. There are many huge log files and what I want to know is:

How do I reduce the sizes of the log files?
Which log files can I reduce?
Can anybody help with a step by step guide on how to do this? This is a production server.

If your databases are in full recovery mode you will need to backup the transaction logs, when that is done you can shrink the log files and setup a regular log backup to minimize growth and keep good backups. I would reccomend that you read this: https://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx but first things first.

The first step is to find the largest log files which you can shrink without backups - So you have to find databases which are in simple recovery mode:

select d.name, d.recovery_model_desc as recovery,d.log_reuse_wait_desc as wait_desc
,f.name as [LogicalName]
,CAST((f.size/128.0) AS DECIMAL(15,2)) AS [Total Size in MB]
from sys.databases d
 inner join sys.master_files f on d.database_id = f.database_id
 where f.type = 1
 order by [Total Size in MB] desc

This will give you an output similar to this:

name    recovery    wait_desc   LogicalName Total Size in MB
dba     FULL        NOTHING     dba_log     4693.50
dbb     SIMPLE      NOTHING     dbb_Log     1024.00

For each file which is in SIMPLE recovery and not having wait_desc = "active transaction" you can simply do

use database [NAMEFromQuery];
GO
DBCC SHRINKFILE (N'LogicalNameFromQuery' , Truncateonly);

And then figure out why the log is growing.

Now for the databases in FULL Recovery mode you can do

  ALTER DATABASE [NAMEFromQuery] SET RECOVERY SIMPLE WITH NO_WAIT

and then truncate the log as above to get out of trouble but remember which databases were in full recovery model.

Now read the article linked above and start making good backups.


Postgres: What happens if your slave DB went down temporarily? I have a master DB that is replicated to a slave DB through binary replication. What happens if this slave DB were to go offline for a period of time? Would it eventually catch up with the master as soon as it goes back online (without much intervention)?

The answer is yes.  What is actually happening is that the slave receives binary patch files and applies those.  It is slightly complicated but in general your slave can be offline temporarily and then later catch up.  I would imagine that there are limits in the amount of time that can pass but in the event that it falls too far behind you can use WAL segments to catch up (as I understand it, unarchived wal segments are the primary key here, so if you pass the point where you need archived segments to catch up, you will need those segments accessible via log shipping or the like).


Galera cluster slightly slower than single database? I recently set up a MariaDB galera cluster for our production. I used sysbench to benchmark the cluster against the old database which is on a single server.
On my PRD Galera Cluster I got the following results:
SQL statistics:
    queries performed:
        read:                            3914980
        write:                           0
        other:                           782996
        total:                           4697976
    transactions:                        391498 (1304.77 per sec.)
    queries:                             4697976 (15657.22 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          300.0492s
    total number of events:              391498

Latency (ms):
         min:                                    5.37
         avg:                                   12.26
         max:                                   66.20
         95th percentile:                       15.83
         sum:                              4798745.23

Threads fairness:
    events (avg/stddev):           24468.6250/414.77
    execution time (avg/stddev):   299.9216/0.01

Meanwhile our old single database production got this results:
SQL statistics:
    queries performed:
        read:                            5306060
        write:                           0
        other:                           1061212
        total:                           6367272
    transactions:                        530606 (1768.51 per sec.)
    queries:                             6367272 (21222.18 per sec.)
    ignored errors:                      0      (0.00 per sec.)
    reconnects:                          0      (0.00 per sec.)

General statistics:
    total time:                          300.0266s
    total number of events:              530606

Latency (ms):
         min:                                    3.87
         avg:                                    9.04
         max:                                   59.99
         95th percentile:                       12.08
         sum:                              4798278.00

Threads fairness:
    events (avg/stddev):           33162.8750/440.14
    execution time (avg/stddev):   299.8924/0.01

Now I'm wondering why does the cluster operate a bit slower than the single database? They have the same specs: Quadcore CPU, 32GB RAM and vm.swappiness=1. Here's my cluster configuration (same across 3 servers) and is using HAProxy to load balance between 3 servers:
max_connections = 3000

wsrep_slave_threads=4
innodb_lock_wait_timeout=8000
innodb_io_capacity=2000
innodb_buffer_pool_size=25G
innodb_buffer_pool_instances=25
innodb_log_buffer_size=256M
innodb_log_file_size=1G
innodb_flush_log_at_trx_commit=2
innodb_flush_method = O_DIRECT_NO_FSYNC

innodb_read_io_threads=8
innodb_write_io_threads=4

thread_handling = pool-of-threads
thread_stack = 192K
thread_cache_size = 4
thread_pool_size = 8
thread_pool_oversubscribe = 3

wsrep_provider_options="gcache.size=10G; gcache.page_size=10G"

I used sysbench on a spare server, does the latency between servers also affect the outputs? I would appreciate any inputs, thank you.

Galera being slower for that benchmark is not surprising.  But is it useful info?
At the end of a transaction, in the COMMIT, the node being written communicates with each other node, asking "will you guarantee that this transaction will succeed?"  (This is slightly weaker, but faster, than actually waiting for the writes to finish on the other nodes.)
What's in the 12 queries that make up a "transaction"?  Does that reflect your usage?
If I munch the numbers correctly, the stand-alone test has 200 transactions running at any moment; 100 for Galera.  Is that realistic?  I hope not -- your quadcore machine will begin to stumble over itself after about 4 transactions.  I would expect it to hit a ceiling long before 200/100 -- This ceiling often characterizes itself by transaction-per-second improving no further, while latency goes through the roof.
Are you hitting all nodes, or just one?
Does a "transaction" have SELECTs in support of the transaction.  I ask this because that is a situation where running transactions on multiple nodes can actually increase throughput.  (On a solo machine, all the selects would have to be done on the Primary.)
Here's an analogy that makes me question benchmarks.  Benchmarks are like testing a hot car -- Drive it for an hour with the accelerator on the floor.  Not realistic.
If you need anywhere near 1768 TPS, neither topology will survive long.


How to sync two MySQL high load tables with 10M rows? I have two identical MySQL innodb table with 10M rows each. One is used at backend while other at application end. Thus, either of them loaded. I need to synchronize them in a way which is fast and non blocking. How can I do that?

Is there any particular LINUX software for this?

And how about syncing two dbs?

EDIT

Both tables exist on different dbs hosted on different machines. And both of them are updated separately before the sync process.

pt-table-sync from Percona Toolkit will do this sort of thing.


SQL statement sequential execution for multiple statments If a single SQL file contains 2 SQL statements below each other (as below) for millions of records

Query 1 : select Id into MyTempTable from TableB b join abc a on a.Id = t.Id

Query 2 :  Update TableB set Completed =1

Is there any chance that query 2 will execute first for some of the records or do we have a guarantee of sequential execution of those statements?

In another words, will Query 2 execution be started only after Query1 finishes?

If there are multiple queries in a batch then these queries will be executed in the order they appear in the batch.

After the execution of Query 1 is finished then the update query will be executed.
You can run debugger to see the sequential execution.


PostgreSQL Select value row depending on another value row How to select only the value of the column phase that corresponds to the value of the column type?, that way I want to show only the TAXI_SMALL values if the value of type is small_airport, or TAXI_LARGE If the value of type is large_airport as in a sample of my table:

ident
type
aircraft
phase
co2_lto_kg

LFPG
large_airport
AC300
APPROACH
2033.64

LFPG
large_airport
AC300
CLIMB OUT
3279.83

LFPG
large_airport
AC300
TAKE OFF
1281.1932

LFPG
large_airport
AC300
TAXI_LARGE
3675.672

LFPG
large_airport
AC300
TAXI_MEDIUM
2261.952

LFPG
large_airport
AC300
TAXI_SMALL
1413.72

The desired result:

ident
type
aircraft
phase
co2_lto_kg

LFPG
large_airport
AC300
APPROACH
2033.64

LFPG
large_airport
AC300
CLIMB OUT
3279.83

LFPG
large_airport
AC300
TAKE OFF
1281.1932

LFPG
large_airport
AC300
TAXI_LARGE
3675.672

This is the code I've so far and I know that the CASE instance is not at all right...
select
ident, type, aircraft, phase,
avg(co2_by_phase_lto) as co2_lto_kg,
case(
    WHEN t.type ilike 'large'||'%' THEN phase('TAXI_LARGE')
)
from aviation.emissionsco2 as t
where t.aircraft ilike '%'||'300' and
t.rep_airp ilike '%'||'lfpg'
group by ident,phase

You can use NOT LIKE to include all the non taxi related data -- check that the phase doesn't begin with TAXI_. Then add for each size a comparison that couples the airport size to the taxi route with OR.
SELECT *
       FROM aviation.emissionsco2
       WHERE phase NOT LIKE 'TAXI$_%' ESCAPE '$'
              OR (phase,
                  type) IN (('TAXI_LARGE',
                             'large_airport'),
                            ('TAXI_MEDIUM',
                             'medium_airport'),
                            ('TAXI_SMALL',
                             'small_airport'));


PostgreSQL Query Refactoring I have a simple table called events

It has a date, start time, end time and status

I have multiple events for the same date, start and end.

To know if someone can make an appointment, I have to check if for that date and start, end I have a line that the status is false (not occupied).

The table definition with some example code:

CREATE SEQUENCE events_id_seq;

CREATE TABLE events(
  id int4 NOT NULL DEFAULT nextval('events_id_seq'::regclass),
  event_date date NULL,
  status bool NULL DEFAULT false,
  event_start time NULL,
  event_end   time NULL
) ;

INSERT INTO events(event_date,status,event_start,event_end)
VALUES
('2016-10-10', TRUE, '08:00:00','08:30:00'),
('2016-10-10', TRUE, '08:00:00','08:30:00'),
('2016-10-10', FALSE, '08:00:00','08:30:00'),
('2016-10-10', TRUE, '08:00:00','08:30:00'),
('2016-10-10', TRUE, '08:30:00','09:00:00'),
('2016-10-10', FALSE, '08:30:00','09:00:00'),
('2016-10-10', FALSE, '08:30:00','09:00:00'),
('2016-10-10', FALSE, '08:30:00','09:00:00');

select distinct event_date, event_start, event_end, (
    select count(*) from events e1
    where
        e1.status = false and
        e1.event_date = e.event_date and
        e1.event_start = e.event_start and
        e1.event_end = e.event_end
) as free_slots from
events e
order by event_date, event_start;

http://sqlfiddle.com/#!15/698b7

It works, but since I'm not a SQL expert, I am just seeking advice on other forms to solve this, just to learn a little more.

If you just want to see the number of free slots, you can simplify your query using conditional aggregation:

select event_date,
       event_start,
       event_end,
       count(*) filter (where not status) as free_slots
from events e
group by event_date, event_start, event_end
order by event_date, event_start;

The filter() clause was introduced in Postgres 9.4. If you are still using an older version you can use a CASE statement instead:

select event_date,
       event_start,
       event_end,
       count(case when not status then 1 end) as free_slots
from events e
group by event_date, event_start, event_end
order by event_date, event_start;

Incidentally, status is not a very good name for the column. Something like is_taken or is_full would be better and likely cause less confusion.


Best approach for populating date dimension table I am looking to populate a date dimension table in a SQL Server 2008 database.
The fields in the table are as follows:

[DateId]                    INT IDENTITY(1,1) PRIMARY KEY
[DateTime]                  DATETIME
[Date]                      DATE
[DayOfWeek_Number]          TINYINT
[DayOfWeek_Name]            VARCHAR(9)
[DayOfWeek_ShortName]       VARCHAR(3)
[Week_Number]               TINYINT
[Fiscal_DayOfMonth]         TINYINT
[Fiscal_Month_Number]       TINYINT
[Fiscal_Month_Name]         VARCHAR(12)
[Fiscal_Month_ShortName]    VARCHAR(3)
[Fiscal_Quarter]            TINYINT
[Fiscal_Year]               INT
[Calendar_DayOfMonth]       TINYINT
[Calendar_Month Number]     TINYINT
[Calendar_Month_Name]       VARCHAR(9)
[Calendar_Month_ShortName]  VARCHAR(3)
[Calendar_Quarter]          TINYINT
[Calendar_Year]             INT
[IsLeapYear]                BIT
[IsWeekDay]                 BIT
[IsWeekend]                 BIT
[IsWorkday]                 BIT
[IsHoliday]                 BIT
[HolidayName]               VARCHAR(255)

I have written a function DateListInRange(D1,D2) that returns all the dates between two parameter dates D1 and D2 inclusive.

ie. parameters '2014-01-01' and '2014-01-03' would return:

2014-01-01
2014-01-02
2014-01-03

I want to populate the DATE_DIM table for all dates within a range, i.e. 2010-01-01 to 2020-01-01. Most of the fields can be populated with the SQL 2008 DATEPART, DATENAME, and YEAR functions.

The fiscal data contains slightly more logic, some of which is dependant on each other. For example:
Fiscal quarter 1 -> Fiscal month must be 1, 2 or 3
Fiscal quarter 2 -> Fiscal month must be 4, 5 or 6

I can easily write a table valued function that accepts a specific date, and then outputs all of the fiscal data, or ALL of the fields even. Then I would just need this function to run on each row of the DateListInRange function.

I am not highly concerned with speed as this will only need to be populated a few times a year when the table of holidays is altered.

What is the best way to write this in SQL?

Currently its like this:

SELECT
    [Date],
    CAST([Date] AS DATE)                AS [Date],
    DATEPART(W,[Date])                  AS [DayOfWeek_Number], -- First day of week is sunday
    DATENAME(W,[Date])                  AS [DayOfWeek_Name],
    SUBSTRING(DATENAME(DW,[Date]),1,3)  AS [DayOfWeek_ShortName],
    DATEPART(WK, [Date])                AS [WeekNumber],
    DATEPART(M, [Date])                 AS [Calendar_Month_Number],
    DATENAME(M, [Date])                 AS [Calendar_Month_Name],
    SUBSTRING(DATENAME(M, [Date]),1,3)  AS [Calendar_Month_ShortName],
    DATEPART(QQ, [Date])                AS [Calendar_Quarter],
    YEAR([Date])                        AS [Calendar_Year],

    CASE WHEN
    (
        (YEAR([Date]) % 4 = 0) AND (YEAR([Date]) % 100 != 0)
        OR
        (YEAR([Date]) % 400 = 0)
    )
    THEN 1 ELSE 0
    END                                     AS [IsLeapYear],

    CASE WHEN
    (
        DATEPART(W,[Date]) = 1 OR DATEPART(W,[Date]) = 7
    )
    THEN 0 ELSE 1
    END                                     AS [IsWeekDay]
FROM [DateListForRange]
('2014-01-01','2014-01-31')

If I do the same for the fiscal data there will be quite a bit of repetition in each case statement would could be avoided using a function and maybe cross applying the TVF over the list of dates.

Please note I am using SQL Server 2008 so a lot of newer date functionality is minimal.

UPDATE: for a more generic example of creating and populating a calendar or dimension table, see this tip:

Creating a date dimension or calendar table in SQL Server

For the specific question at hand, here's my attempt. I will update this with the magic you use to determine things like Fiscal_MonthNumber and Fiscal_MonthName, because right now they're the only non-intuitive part of your question, and it's the only tangible information you actually didn't include.

The "best" (read:most efficient) way to populate a calendar table, IMHO, is to use a set, rather than a loop. And you can generate this set without burying logic into user-defined functions, which really don't gain you anything but encapsulation - otherwise it's just another object to maintain. I talk about this in a lot more detail in this blog series:

http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1
http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-2
http://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-3

If you want to keep using your function, make sure it's not a multi-statement table-valued function; that's not going to be efficient at all. You want to make sure that it is inline (e.g. has a single RETURN statement and no explicit @table declaration), has WITH SCHEMABINDING, and doesn't use recursive CTEs. Outside of a function, here is how I would do it:

CREATE TABLE dbo.DateDimension
(
  [Date]                      DATE PRIMARY KEY,
  [DayOfWeek_Number]          TINYINT,
  [DayOfWeek_Name]            VARCHAR(9),
  [DayOfWeek_ShortName]       VARCHAR(3),
  [Week_Number]               TINYINT,
  [Fiscal_DayOfMonth]         TINYINT,
  [Fiscal_Month_Number]       TINYINT,
  [Fiscal_Month_Name]         VARCHAR(12),
  [Fiscal_Month_ShortName]    VARCHAR(3),
  [Fiscal_Quarter]            TINYINT,
  [Fiscal_Year]               SMALLINT,
  [Calendar_DayOfMonth]       TINYINT,
  [Calendar_Month Number]     TINYINT,
  [Calendar_Month_Name]       VARCHAR(9),
  [Calendar_Month_ShortName]  VARCHAR(3),
  [Calendar_Quarter]          TINYINT,
  [Calendar_Year]             SMALLINT,
  [IsLeapYear]                BIT,
  [IsWeekDay]                 BIT,
  [IsWeekend]                 BIT,
  [IsWorkday]                 BIT,
  [IsHoliday]                 BIT,
  [HolidayName]               VARCHAR(255)
);
-- add indexes, constraints, etc.

With the table in place, you can perform a single, set-based insert of as many years of data as you want from whatever start date you choose. Just specify the start date and the number of years. I use a "stacked CTE" technique to avoid redundancy and only perform a whole slew of calculations once; the output columns from the earlier CTEs are then subsequently used in further calculations later on.

-- these are important:
SET LANGUAGE US_ENGLISH;
SET DATEFIRST 7;

DECLARE @start DATE = '20100101', @years TINYINT = 20;

;WITH src AS
(
  -- you don't need a function for this...
  SELECT TOP (DATEDIFF(DAY, @start, DATEADD(YEAR, @years, @start)))
    d = DATEADD(DAY, ROW_NUMBER() OVER (ORDER BY s1.number)-1, @start)
   FROM master.dbo.spt_values AS s1
   CROSS JOIN master.dbo.spt_values AS s2
   -- your own numbers table works much better here, but this'll do
),
w AS
(
  SELECT d,
    wd      = DATEPART(WEEKDAY,d),
    wdname  = DATENAME(WEEKDAY,d),
    wnum    = DATEPART(ISO_WEEK,d),
    qnum    = DATEPART(QUARTER, d),
    y       = YEAR(d),
    m       = MONTH(d),
    mname   = DATENAME(MONTH,d),
    md      = DAY(d)
  FROM src
),
q AS
(
  SELECT *,
    wdsname   = LEFT(wdname,3),
    msname    = LEFT(mname,3),
    IsWeekday = CASE WHEN wd IN (1,7) THEN 0 ELSE 1 END,
    fq1 = DATEADD(DAY,25,DATEADD(MONTH,2,DATEADD(YEAR,YEAR(d)-1900,0)))
  FROM w
),
q1 AS
(
  SELECT *,
    -- useless, just inverse of IsWeekday, but okay:
    IsWeekend = CASE WHEN IsWeekday = 1 THEN 0 ELSE 1 END,
    fq = COALESCE(NULLIF(DATEDIFF(QUARTER,DATEADD(DAY,6,fq1),d)
         + CASE WHEN md >= 26 AND m%3 = 0 THEN 2 ELSE 1 END,0),4)
    FROM q
)
--INSERT dbo.DimWithDateAllPersisted(Date)
SELECT
  DateKey = d,
  DayOfWeek_Number = wd,
  DayOfWeek_Name = wdname,
  DayOfWeek_ShortName = wdsname,
  Week_Number = wnum,
  -- I'll update these four lines when I have usable info
  Fiscal_DayOfMonth      = 0,--'?magic?',
  Fiscal_Month_Number    = 0,--'?magic?',
  Fiscal_Month_Name      = 0,--'?magic?',
  Fiscal_Month_ShortName = 0,--'?magic?',
  Fiscal_Quarter = fq,
  Fiscal_Year = CASE WHEN fq = 4 AND m < 3 THEN y-1 ELSE y END,
  Calendar_DayOfMonth = md,
  Calendar_Month_Number = m,
  Calendar_Month_Name = mname,
  Calendar_Month_ShortName = msname,
  Calendar_Quarter = qnum,
  Calendar_Year = y,
  IsLeapYear = CASE
    WHEN (y%4 = 0 AND y%100 != 0) OR (y%400 = 0) THEN 1 ELSE 0 END,
  IsWeekday,
  IsWeekend,
  IsWorkday = CASE WHEN IsWeekday = 1 THEN 1 ELSE 0 END,
  IsHoliday = 0,
  HolidayName = ''
FROM q1;

Now, you still have these "holiday" and "workday" columns left to deal with - this gets a little more cumbersome, but you need to update those three columns with any holidays that appear in your date range. Things like Christmas Day are really easy:

UPDATE dbo.DateDimension
  SET IsWorkday = 0, IsHoliday = 1, HolidayName = 'Christmas'
  WHERE Calendar_Month_Number = 12 AND Calendar_DayOfMonth = 25;

Things like Easter get a lot trickier - I have blogged some ideas here many years ago.

And of course your company non-workdays that have absolutely nothing to do with public holidays etc. must be updated directly by you - SQL Server isn't going to have some built-in way to know your company's calendar.

Now, I purposely stayed away from computing any of these columns, because you said something like the end users have previously preferred fields they can drag and drop - I'm not sure if the end users really know or care if the source of a column is a real column, a computed column, or comes from a view, query or function...

Assuming you do want to look into computing some of these columns to ease on your maintenance (and persist them to pay storage for query speed), you can look into that. However, just as a warning, some of these columns can't be defined as computed and persisted because they are non-deterministic. Here's one example, and how to get around it.

CREATE TABLE dbo.Test
(
  [date] DATE PRIMARY KEY,
  DayOfWeek_Number AS DATEPART(WEEKDAY, [date]) PERSISTED
);

Results:

  Msg 4936, Level 16, State 1, Line 130
  Computed column 'DayOfWeek_Number' in table 'Test' cannot be persisted because the column is non-deterministic.

The reason this can't be persisted is because many date-related functions rely on the session settings of the user, like DATEFIRST. SQL Server can't persist the above column because DATEPART(WEEKDAY should give different results - given the same data - for two different users who happen to have different DATEFIRST settings.

Then you might get clever, and say, well, I can set it to be the number of days, modulo 7, offset from some day I know to be a Saturday (say, '2000-01-01'). So you try:

CREATE TABLE dbo.Test
(
  [date] DATE PRIMARY KEY,
  DayOfWeek_Number AS
    COALESCE(NULLIF(DATEDIFF(DAY,'20000101',[date])%7,0),7) PERSISTED
);

But, same error.

Instead of using an implicit convert from a string literal that represents a date time in an unambiguous (to us, but not SQL Server) format, we can use the number of days between the "zero date" (1900-01-01) and that date we know is a Saturday (2000-01-01). If we use an integer here to represent the difference in days, SQL Server can't complain, because there is no way to misinterpret that number. So this works:

-- SELECT DATEDIFF(DAY, 0, '20000101');  -- 36524

CREATE TABLE dbo.Test
(
  [date] DATE PRIMARY KEY,
  DayOfWeek_Number AS
    COALESCE(NULLIF(DATEDIFF(DAY,36524,[date])%7,0),7) PERSISTED
    -----------------------------^^^^^  only change
);

Success!

If you're interested in pursuing computed columns for some of these calculations, let me know.

Oh, and one last thing: I don't know why you would ever scrub down this table and re-populate it from scratch. How many of these things are going to change? Are you going to alter your fiscal year constantly? Change how you want to spell March? Set your week to start on Monday one week and Thursday the next? This really should be a build-it-up-once table, and then you make minor tweaks (like updating individual rows with new/changed holiday information).


how to verify mysql backup file Is There any tool to verify the backup taken using mysqldump without manual restoration ?

I have to take full backup daily so it is difficult for me to verify the backups daily by restoring them. As it will take time and also temporary disk space.

Please suggest me how to verify my backup online

Start a dedicated Mysql server with as default engine blackhole (and maybe the only one) and engine_substitution enabled.
Then you can restore your mysqldump and monitor warning(s) and error(s) generated.

Maximum execution speed and no disk space taken ...

Hope this will help you


Can a Table Lock eliminate a deadlock or make it worse Oracle We have a situation in which an Oracle Database is having deadlocks issues.

I said that perhaps we should lock into some table locks, and a guys response to me was

  "Unless I am misunderstanding what you mean by a table lock, I don’t think a table lock will eliminate deadlock … it could potentially exacerbate it."

Is what he is saying true?

Error we get: "Deadlock detected while waiting for resource Stack Trace"

Here is most of email on the error that we get:

System Error: Error for Step Type:REMOVE PRED OPEN MAN User Error:
ORA-00060: deadlock detected while waiting for resource Stack Trace:

Timestamp 2015/08/21 14:02:39:130363000130363000
dbms_utility.format_error_backtrace:
ORA-06512: at "OM.FOLDER_PKG", line 2661
ORA-06512: at "OM.DOC_LOG_STEP_PKG", line 2436
dbms_utility.format_error_stack:
ORA-00060: deadlock detected while waiting for resource

UPDATE:

This is PROBABLY useful, this is from a developer that is familiar with this happening every few months of which NO DBA at the company seems to be able to be able to figure it out.

This is what the main developer on this project said:

Unless I am misunderstanding what you mean by a table lock, I don’t think a table lock will eliminate deadlock … it could potentially exacerbate it.

e.g.

Transaction 1

Locks table X at 10:00:00 (all times are just examples)
Attempts to lock table Y at 10:00:02. DEADLOCK – transaction 2 is waiting on transaction 1 to unlock table X and transaction 1 is waiting on transaction 2 to unlock table Y

Transaction 2

Locks table Y at 10:00:00
Attempts to lock table X at 10:00:01. Blocks waiting for transaction 1 to release the lock

Can replace the word ‘table’ with ‘record’. Problem remains the same. Think lower granularity of table instead of record could make the problem worse.

We can handle the issue by:

Locking tables in a consistent order  - this is difficult to do because we have many thousands of lines of code we’d need to make sure that we’d be calling things in the same order. It would also lead to uglier code where natural processing flow would have been changed simply to avoid deadlock.
Locking all top level transactions on a single object at the very start (they can then lock sub-tables/ objects to their hearts content). The natural object on the folder side to do this one would be a WORK_FOLDER.
Rolling back on deadlock and retrying – we have already implemented this but it only does one retry and sometimes Oracle is taking too long to detect that the deadlock occurs. And occasionally that one retry is not enough.

In addition to someone doing full table locks, there are two other things that I can think of. If you have a table where there are several columns that have a parent child relationship to the same column in a child table and if the child column is not indexed, then indexing it could help. I had a case with an online content management system that had unindexed foreign keys that caused cascading locks. I was able to resolve that by using the Tom Kyte query to find the missing indexes and add them in.

http://tkyte.blogspot.com/2009/10/httpasktomoraclecomtkyteunindex.html

Another possibility is that you have cases where two triggers are firing on different tables and they are each trying to update the other table. You can tell this by looking at the trace log that gets produced from a dead lock and figuring out what objects and rows are locked, then check your triggers to see if any of your triggers are running that SQL.

It would be helpful to know how long this has been happening and what changed around the time that this became an issue.


Move table from InnoDB to Memory I am using mariadb 10.4 with nignx  with joomla 4 on Debian 8 system.

Is it possible for a table that is already created with records in a working database to be moved on memory?

I find only tutorial to create the table from scratch not for changing it after it is created.

Is it possible to have some records on the disk and the rest on the memory?

Yes:

ALTER TABLE the_table ENGINE=MEMORY;

That said, I must strongly advice against doing this because the contents of memory tables are lost once the MariaDB server is shut down.

InnoDB is perfect for having some rows in memory and some on disk: It will put as much into memory as it can, and if there isn't enough memory for all of them, then it will only keep those that are accessed the most frequently. Also, it will persist the data to disk so that it's not lost when the MariaDB server is shut down.


Stream every row coming from a Postgres function This is the how the function definition looks:
CREATE OR REPLACE FUNCTION my_function(foo NUMERIC)
RETURNS SETOF my_type AS $$
DECLARE rec record;
BEGIN
    FOR rec IN SELECT * FROM my_table WHERE my_table.bar > foo
    LOOP
        RETURN QUERY SELECT
            my_scalar_function(rec.bar1),
            my_scalar_function(rec.bar2),
            my_scalar_function(rec.bar3);
    END LOOP;
END;
$$ LANGUAGE plpgsql;

On the client i am trying to use a QueryStream from the pg-query-stream package:
pool.connect((err, client, done) => {
  if (err) throw err
  const query = new QueryStream('SELECT * FROM my_function($1);', [123])
  const stream = client.query(query)
  stream.on('end', ()=>{
    console.log('finished')
    done()
    process.exit(0)
  })
  stream.pipe(JSONStream.stringify()).pipe(process.stdout)
});

But still i am getting all rows at the end of the function and no intermediate rows. I would like to stream every row when it is calculated in the my_function.

Q: But still i am getting all rows at the end of the function and no
intermediate rows

Unfortunately these results are accumulated (as opposed to streamed) by the server-side implementation of RETURN QUERY. This is explicitly mentioned in the documentation:

Note
The current implementation of RETURN NEXT and RETURN QUERY stores the
entire result set before returning from the function, as discussed
above. That means that if a PL/pgSQL function produces a very large
result set, performance might be poor: data will be written to disk to
avoid memory exhaustion, but the function itself will not return until
the entire result set has been generated. A future version of PL/pgSQL
might allow users to define set-returning functions that do not have
this limitation.


Retrieving orders that only contain certain product IDS I am trying to retrieve a list of transaction IDS that only contain at least 5 product IDS, the baskets could have many more product IDS but it must contain these 5 also.

My table looks something like this:

| transaction_id | trans_date | product_id | store_id |

The table is all at product_id line level detail, nothing is rolled up.

select * from Transactions
where trans_date in ('2019-03-07','2019-03-06','2019-03-08','2019-03-09','2019-03-10')
and product_id in ('000000000021003503','000000000060280873','000000000060281480','000000000060281642','000000000020059281')

This is recovering all the transactions that contain these ids but I need it to be rolled up to a transaction level, so if the transaction_id contained these ids (at least) then its included.

Hopefully this makes sense

If Impala supports GROUP BY, COUNT and HAVING, then you can use the following.

Here we select the records for matching dates and products, then we count how many product_ids we actually found for each transaction on each day, and we take only those results for which we found all 5 product_ids.

SELECT transaction_id, trans_date, COUNT(product_id) as counter
FROM   Transactions
WHERE  trans_date IN ('2019-03-07','2019-03-06','2019-03-08','2019-03-09','2019-03-10')
AND    product_id IN ('000000000021003503', '000000000060280873', '000000000060281480', '000000000060281642', '000000000020059281')
GROUP BY transaction_id, trans_date
HAVING count(product_id) = 5

If a single transaction is allowed to span multiple dates, then you can just leave out the trans_date from the SELECT and GROUP BY parts.


Problem creating Oracle database manually I am using Windows 7 and Oracle 11. When I want to create a database manually, it is giving me two problems:

I set my oracle_home to c:\app\best\product\11.2.0\dbhome_1. Under home I have a directory as database, in this directory I want to create the parameter file by edit command it is giving me an error: edit is not recognized as internal or external command. Can any body explain why?
Anyhow, I have created the initfinance.ora file by edit command in c:\ directory (here edit command is working) so I created it here and copy it to the database directory under oracle home. Then I set oracle home and set the sid as finance then I want to create the instance so I gave the command oradim -new -sid finance -startmode manual
here also it has given me the errors (cannot open the windows NT service control manager
access denied.. and it is not creating my instance please help me out in this situation and one more thing I am running this on my laptop machine only.

edit was a 16-bit application and 16-bit applications don't run on 64-bit Windows, therefore it is not included in 64-bit Windows.

https://support.microsoft.com/kb/896458

Just use notepad.

You should run this with elevated privileges, for example in a command prompt started with "Run as Administrator".


Postgresql behavior at end of sequence As per subject when a sequence hits its max val what happens?

What is the behavior? I have a big table with a sequence as a primary key and would like to know.

Thanks.

If you try it, you'll get:

ERROR: nextval: reached maximum value of sequence "testseq" (9223372036854775807): select nextval('testseq')

This is the case when the sequence is created automatically for a serial or bigserial column.  You can create a sequence with specifying CYCLE, in which case it will restart from the MINVALUE specified (or left at the default 1).


Calculating percentage of count and adding it to the count in MySQL table with the query I have following basic MySQL query and I my aim is to artificially inflate the amount of today's orders by 75%.

Is there a more efficient way of doing this without using COUNT twice?

SELECT COUNT(order_id) + (COUNT(order_id) * 0.75) AS total
FROM orders
WHERE order_date BETWEEN BETWEEN CURDATE() + ' 00:00:00' AND NOW()

EDIT: I spotted an issue with my query (using + for concatenation when I should have used CONCAT)

For general case use your original query as derived table:

SELECT cnt + (cnt * 0.75) AS total
FROM (
    SELECT COUNT(order_id) AS cnt
    FROM orders
    WHERE order_date BETWEEN CURDATE() + ' 00:00:00' AND NOW()
) AS tmp

But in your case it is much simpler:

SELECT (COUNT(order_id) * 1.75) AS total
FROM orders
WHERE order_date BETWEEN CURDATE() + ' 00:00:00' AND NOW()


What is a Clustered Index? I need a short explaining of clustered index.

What is a clustered index?
What are best practices for using the clustered index?

In simple terms...

A telephone directory: the data is the index/the index is the data. To look you up, I'd start with Rezaei, Amir for example. No external lookup is needed.

In database terms:

The table data and clustered index are one and the same (in SQL Server, also InnoDB, Oracle IOT)

Best practice is narrow, numeric, strictly increasing (think IDENTITY or Autonumber column). Data types like GUIDs are not a good idea

Links to read (to avoid bloating this answer)

"Effective Clustered Indexes" (Simple Talk)
Wikipedia
MSDN (SQL Server 2000)
MSDN (SQL Server 2005+)

Edit, to fully explain my best practice suggestions, follow the link in  Eric Humphrey's comment: Queen of Indexing: Kimberly Tripp


Using Trigger creates duplicates I have created a trigger to inert into table2 if a new row is inserted into table1 (at least I thought).
The queries is like this:
CREATE TRIGGER project_insert
ON TABLE1
AFTER INSERT
AS
BEGIN
Insert into TABLE2 (ProjectID, Status, Period)
SELECT
    ProjectID
  , Status
  , Period = concat(FORMAT(GETDATE(), 'yyy'), FORMAT(GETDATE(), 'MM'))
FROM TABLE1
WHERE Status in (1, 2)

This seems to work as it is inserting the project after I add it to table1, however everytime I add a new project it seems that the trigger inserts a new row for each project, I have in table1. I would like it to only add the new project.
Is this doable?
Thanks!

You need to use the special virtual table INSERTED which represents the rows that have been inserted causing the trigger to fire and JOIN it back to your Table2 using the PK
(ProjectID in this case I think)
Insert into TABLE2 (ProjectID, Status, Period)
SELECT
    ProjectID
  , Status
  , Period = concat(FORMAT(GETDATE(), 'yyy'), FORMAT(GETDATE(), 'MM'))
FROM TABLE1 T
INNER JOIN INSERTED I ON
  T.ProjectID = i.ProjectID
WHERE Status in (1, 2)


In-Memory Optimized Error: The code generation directory cannot be created or set up correctly I ran into a problem yesterday trying to create the first memory_optimized table on a SQL Server 2016 SP1 Enterprise Edition.

I created the database and filegroup as so

CREATE DATABASE imoltp   --  Transact-SQL
 CONTAINMENT = NONE
 ON  PRIMARY
(
    NAME = N'imoltp',
    FILENAME = N'F:\UNREFRESHED_DB\imoltp.mdf' ,
    SIZE = 5120KB ,
    FILEGROWTH = 1024KB
)
 LOG ON
    (
        NAME = N'imoltp_log',
        FILENAME = N'F:\UNREFRESHED_DB\imoltp_log.ldf' ,
        SIZE = 2048KB , FILEGROWTH = 10%
    )
GO

ALTER DATABASE imoltp ADD FILEGROUP [imoltp_mod]
    CONTAINS MEMORY_OPTIMIZED_DATA;

ALTER DATABASE imoltp ADD FILE
    (name = [imoltp_dir], filename= 'F:\UNREFRESHED_DB\imoltp_dir')
    TO FILEGROUP imoltp_mod;
go

Then I created the table

USE imoltp
GO

CREATE TABLE imoltp.[dbo].[T1] (
  [TempID] INT NOT NULL PRIMARY KEY NONCLUSTERED HASH WITH (BUCKET_COUNT=1000000),
  [object_ids] NVARCHAR(255) NOT NULL,
  [names] NVARCHAR(255) NOT NULL,
  [comment] NVARCHAR(50)
) WITH (MEMORY_OPTIMIZED=ON, DURABILITY = SCHEMA_ONLY);

GO

Which results in this error:

  Msg 41334, Level 16, State 0, Line 8
  The code generation directory cannot be created or set up correctly.

I finally figured out that SQL was trying to create a directory (XTP) at the default database location defined in the server properties which no longer existed in this situation. This directory is where it wants to store dll files that in-memory needs.

https://blogs.msdn.microsoft.com/bobsql/2016/08/23/create-table-disk-vs-in-memory-optimized/

So my questions is this.

Is there a way to define where the XTP directory will be created in the CREATE TABLE syntax or elsewhere?

Thanks,
Craig

Is there a way to define where the XTP directory will be created in the CREATE TABLE syntax or elsewhere?

No, there is not. This always points to the default database path. If the path is removed, it will attempt to be created and have security applied to it a few times before returning an error.

This is true as of build: 13.0.4422.0 and older

If you feel that this should be changed to a configurable value - I'd invite you to create a request in Connect.


When Mysql chose to kill a Process(automatically) I faced some real issues regarding the execution of some stored procedures using Mysql server. Sometimes, one of my procedures is not executed (or maybe stopped) during an external call (from app) so I'm wondering :

Is it possible that Mysql could stop automatically the and kill the
process?
if is it true, what are the indicators and factors that make Mysql
chose its victim?
Is there an alternative to set up a list of priority of running
queries?

Thanks.

MySQL can kill a connection if it exceeds wait_timeout.  (Caution, there are various wait timeout settings.)
InnoDB can kill a transaction in the case of a "deadlock".  One of the competing transactions is killed, the other(s) are allowed to continue.
InnoDB can kill a query if it is waiting too long for a lock.  See innodb_lock_wait_timeout, which defaults to 50 seconds.
A user can "kill" a mysql process via the commandline tool 'mysql' and it's kill command.
I don't think there is anything special about Stored Procedures, Functions, Events, Triggers -- they are essentially collections of queries that are, in turn, subject to the comments above.
UDFs contain code written by users and specially installed.  These can do anything nasty that you like.
There are essentially no "priorities" that you can control.  It is better to let MySQL decide what to do when.
Do you mean something else by "process"?


Is 'desc' a valid name for a column in mysql? If I try to create a table called test with one column, it works:
mysql> create table test(surname VARCHAR(300));
Query OK, 0 rows affected (0.02 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)

But if I try now to create the same table, but this time the column is called 'desc' instead of 'surname', I get an error:
mysql> create table test(desc VARCHAR(300));
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'desc VARCHAR(300))' at line 1
mysql>

What am I getting wrong here? Is 'desc' a reserved keyword in mysql?

"ASC" or "DESC" are the reserved keyword in MySQL. you can't use it for column name. "ASC/DESC" is the keyword used to sort result sets in either ascending or descending order. "ASC" is used as the default.


Backup files not found in ASM I have an Oracle database 11gr2 and I have installed Oracle Grid on Oracle Linux 5.9. I have two disk groups.

/etc/init.d/oracleasm listdisks :

flash_recovery_area

DATA

The first disk group is mounted on /u01 and the second is mounted on /flash_recovery_area.

The value of fra parameters are:

db_recovery_file_dest = /+flash_recovery_area

db_recovery_file_dest_size = 30G

When I want to connect to rman and get backup from current database, I can but I can not find anything in /flash_recovery_area destination.

Can any one help me?

Oracle will write all the datafiles and such to your DATA directory. You actually have to create a backup in the +flash_recovery_area you have created. What sort of backup are you trying to create?

What command are you using to create your backup?

For instance, if you wanted to execute a Level 0 IMAGE COPY, you could issue the following command:

backup incremental level 0 as copy database tag 'L0_COPY';