Postgresql Log "^M" Meaning I am having a bit of trouble trying to figure out why a query is taking long to execute.
When I check log it seems the log may be either incomplete.

My question is, what is ^M? I see an error related with account_invoice before the Select Statement, is it related with the issue?

I am sure the statement is bad, it takes a lot of time, I did some research with Explain and found out this.

This is the first time I had experienced this issue and it seems to consume lot of cpu (cpu load is more than 10). I currently restart the postgresql server to get things back to working and it is really not the solution I want to implement in future. Any suggestion is much appreciated.

[2017-05-31 13:32:26 ICT] app=x db=y user=z command=SELECTSTATEMENT:  SELECT province.name,district.name,subdistrict.name,district.icc_ship_area_code^M
    FROM province,district,subdistrict,account_invoice,contact^M
    WHERE province.id = province_id^M
    AND district.id = subdistrict.district_id^M

database=# EXPLAIN SELECT

province.name,district.name,subdistrict.name,district.icc_ship_area_code FROM province,district,subdistrict,account_invoice,contact WHERE provi
nce.id = province_id AND district.id = subdistrict.district_id;                                                                                QUERY PLAN
----------------------------------------------------------------------------------------------------
 Hash Join  (cost=7771211730.77..272930003461.72 rows=23422097264624 width=74)
   Hash Cond: (district.province_id = province.id)
   ->  Hash Join  (cost=35.61..301.98 rows=7426 width=55)
         Hash Cond: (subdistrict.district_id = district.id)
         ->  Seq Scan on subdistrict  (cost=0.00..164.26 rows=7426 width=27)
         ->  Hash  (cost=23.05..23.05 rows=1005 width=36)
               ->  Seq Scan on district  (cost=0.00..23.05 rows=1005 width=36)
   ->  Hash  (cost=3075225689.05..3075225689.05 rows=242863114648 width=27)
         ->  Nested Loop  (cost=0.00..3075225689.05 rows=242863114648 width=27)
               ->  Nested Loop  (cost=0.00..39436753.99 rows=3154066424 width=0)
                     ->  Seq Scan on contact  (cost=0.00..3646.99 rows=78899 width=0)
                     ->  Materialize  (cost=0.00..7376.64 rows=39976 width=0)
                           ->  Seq Scan on account_invoice  (cost=0.00..7176.76 rows=39976 width=0)
               ->  Materialize  (cost=0.00..2.16 rows=77 width=27)
                     ->  Seq Scan on province  (cost=0.00..1.77 rows=77 width=27)
(15 rows)

Your log file is created with the 'Windows End-of-Line' way. When you check it with Vi or Vim then the ^M (= CR = Carridge Return) is show because it is not part of the 'Linux End-of-Line' way. You can ignore it.


Using group by on several tables with problems I have a poorly-designed table in an Oracle database like this:

sr:
id  an_rap
21  2015

rap:
id      cf
28185   123-123

ch:
id      fk_id_rap   FK_ID_SR
12348   28185       21

dma:
ch_id   FK_AMB_ST
12348   18059

cant:
id      FK_ID_MUN   CANT_VAL    COD_VALORIFICARE
18059   12348       18.56       R12

col:
FK_ID_COL_DMA   CANT_VAL    COD_VALORIFICARE
18059           1134        R10
18059           1234        R3

if I made an:

select
    SR.AN_RAP as AN, RAP.CF,
    max(CANT.CANT_VAL) as CANT_VAL,
    max(CANT.COD_VALORIFICARE) as COD_VALORIFICARE,
    max(COL.CANT_VAL) as kk0,
    max(COL.COD_VALORIFICARE) as kk1
from dma
    inner join ch on ch.id=DMA.CH_ID
    inner join cant on CANT.FK_ID_MUN=ch.id
    full outer join col on cant.id=COL.FK_ID_COL_DMA
    inner join sr on SR.ID=CH.FK_ID_SR
    inner join rap on RAP.ID=CH.FK_ID_RAP
where RAP.CF='123-123'
group by SR.AN_RAP,RAP.CF,CANT.ID,DMA.FK_AMB_ST,
    CANT.COD_VALORIFICARE,COL.COD_VALORIFICARE
order by sr.an_rap desc,rap.cf,cant.id

the result is:

an      cf          CANT_VAL    COD_VALORIFICARE    kk0     kk1
2015    123-123     18.56       R12                 1134    R10
2015    123-123     18.56       R12                 1234    R3

how can I make something like this - without changing the structure of the database???

an      cf          CANT_VAL    COD_VALORIFICARE
2015    123-123     18.56       R12
2015    123-123     1134        R10
2015    123-123     1234        R3

Thanks!

I solved it that way:

  select
        SR.AN_RAP as AN, RAP.CF,
        max(col_cant.CANT_VAL) as CANT_VAL,
        max(col_cant.COD_VALORIFICARE) as COD_VALORIFICARE
    from dma
        inner join ch on ch.id=DMA.CH_ID
    inner join
            (select CANT.ID,CANT.FK_ID_MUN,CANT.CANT_VAL,CANT.COD_VALORIFICARE from cant
            union
            select  COL.FK_ID_COL_DMA,CANT2.FK_ID_MUN,COL.CANT_VAL, COL.COD_VALORIFICARE from col
                inner join cant2 on cant2.id=COL.FK_ID_COL_DMA
            ) col_cant
       on col_cant.FK_ID_MUN=ch.id
    inner join sr on SR.ID=CH.FK_ID_SR
    inner join rap on RAP.ID=CH.FK_ID_RAP
    where RAP.CF='123-123'
    group by SR.AN_RAP,RAP.CF,CANT.ID,DMA.FK_AMB_ST,
            col_cant.COD_VALORIFICARE
    order by sr.an_rap desc,rap.cf,col_cant.id


Strategy for setting up a 60gb MySQL database from a dump? I have been trying to set up a 60gb MySQL database from a dump. I loaded it into MySQL Workbench on my standard-issue dual-core 2.4 Ghz, 4gb RAM, 2011 Macbook Pro. After roughly 24h, it had only been able to load a few percent. During other attempts MySQL Workbench crashed completely. Doing the same thing on the command line generates roughly the same results.

What is a viable strategy for loading a 60gb MySQL database? Do I need to buy a very powerful computer? How powerful? Is there a way of loading it onto an Amazon Web Services instance that is feasible?

To speed up load time of InnoDB you can tune some variables.

innodb_buffer_pool_size. Kind of the most important one. Allocate as much as your system allows, but make sure MySQL doesn't use swap. 4GB RAM is desperately little, try buffer pool around 2G. If you can install more memory that will be more helpful than anything below.
innodb_log_file_size. The larger REDO log the less random IO InnoDB does. It doesn't make to make it larger than the buffer pool. So, 512M - 1G should be OK.
Reduce InnoDB durability with innodb_flush_log_at_trx_commit=0
A bit risky, but you can also disable innodb_doublewrite
Make sure you disable all unnecessary logs (binlog, slow log, general log).

DISCLAIMER:
All recommendations for the load process only. For normal operation configuration must be different.


ALTER TABLE CHECK CONSTRAINT From the Object Explorer in SQL Server, when selecting and scripting a foreign-key constraint, the following code is generated.

     USE [MyTestDatabase]
     GO

     ALTER TABLE [dbo].[T2] WITH NOCHECK ADD CONSTRAINT [FK_T2_T1] FOREIGN KEY([T1ID])
     REFERENCES [dbo].[T1] ([T1ID])
     GO

     ALTER TABLE [dbo].[T2] CHECK CONSTRAINT [FK_T2_T1]
     GO

What is the purpose of the last statement "ALTER TABLE CHECK CONSTRAINT"? It doesn't seem to matter whether or not it is run.  It does not fail on existing bad data, nor does it change that the constraint will be enforced on new data.

Thanks!

It ensures that the constraint is enabled after it is created.  Your ALTER TABLE statement includes WITH NOCHECK which is the piece that says not to check for existing bad data during the creation of the constraint.

As written, the existing data will not be checked against the constraint because of the WITH NOCHECK in the first statement.  Issuing the second statement will enable the check against the constraint for any future changes to the table that are covered by the constraint, up to the point that an ALTER TABLE [dbo].[T2] NOCHECK CONSTRAINT [FK_T2_T1] is issued.

The statements, as written, are basically saying "Create this foreign key constraint but don't check it against existing data.  Make it active for any upcoming changes to the data."


How do I import data into oracle 11g r2 using a `.dmp` file and a `.par` file? How do I import data into a new installation of oracle 11g r2 using a .dmp file and a .par file?

Here is what I have done so far, on a Windows 8.1 devbox:

1.) Open command line as administrator
2.) C:\WINDOWS\system32>sqlplus / as sysdba
This will reveal the SQL prompt as follows:
    SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 25 14:20:46 2016
    Copyright (c) 1982, 2010, Oracle.  All rights reserved.
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options
    SQL> CREATE DIRECTORY DMPDIR AS 'C:\path\to\DumpDir\';
    SQL> exit;
3.) Then back in windows command prompt:
C:\WINDOWS\system32>impdp parfile=C:\path\to\DumpDir\impdp.par;

Then I read this other posting which contains the following answer code:

impdp user/pass schemas=schema1 directory=dumpdir \
  remap_schema=schema1:schema2 \
  dumpfile=schema1.dmp \
  logfile=impdp_schema2.log

Since my question is slightly different, I guessed at the following, which I have not typed yet:

impdp schemas=schema1 directory=dumpdir \
  dumpfile=schema1.dmp \
  logfile=impdp_logfile.log

What is the correct syntax to use?  And what other steps should I take to do this properly?

The PAR file is a parameter file, which contains parameters for data pump that you don't need to enter on the command line.  Without knowing what those are, we can't advise what you need to use in any detail.

Generally speaking, if you have a parameter file, you just put that on the command line:

impdp user/pass parfile=/path/to/parfile

However, there may be other parameters required.

Assuming you have a parameter file, you need the command line to contain at minimum:

Username
Parfile

To perform an import, you need at minimum these parameters defined between the command line and parameter file:

Username
Directory
Dumpfile
Full OR Schemas OR Tables
(and for good practice, Logfile)

For a basic import, those four parameters between the command line and the parameter file will import data.  Without knowing what you're trying to achieve, anything else is guesswork.


Best way to move different desktop local PostgreSQL DB to single amazon RDS I have system which is having java Desktop application with PostgreSQL which is installed on 5 machine each is having individual database and having databases into same database structure.

Now I want to move that all slave data to one master database periodically. Which way will be convenient ?

I found two approach:

Create REST service which will post data each row from each table to master database.
Create slave-master replication.

If there is any other technique then please update me.

Please explain how to apply best approach.

EDITED

Used same schema for all the desktop application and used sequences to generate primary keys.

I want to merge them all into single AMAZON RDS postgreSQL database.

Based on the replies you gave to my comments you must figure out how to resolve the duplicate keys issue. Also I do not think that master-slave replication is appropriate in this case.

I would, after making sure that there are no duplicate keys, use pg_dump to transfer your data into AmazonRDS one by one. Perform checks after every database that you do. If you have time and space then first do a test. During this test the 5 machines can remain active.

For master-slave you should check the Replication page of PostgreSQL. The problem remains the duplicate keys as mentioned there as:

Your users take a local copy of the database with them on laptops when they leave the office, make changes while they are away, and need to merge those with the main database when they return. Here you'd want an asynchronous, lazy replication approach, and will be forced to consider how to handle conflicts in cases where the same record has been modified both on the master server and on a local copy.

I do not think however that you can have 5 different 'versions' on the local desktops.


Syntax error, MySQL alter column I'm trying to add referential integrity to a column. However, I can't know the proper syntax for an alter statement. I've attempted the below code. Can someone tell me what is wrong with this query?

alter table person_address modify column address_addressID int(11) not null,
on update cascade;

Not null has nothing to do with referential integrity. If you want to require an address ID here, that's separate from adding a foreign key link to the Address table.

alter table person_address modify column address_addressID int(11) not null;

That statement means you MUST have a value in this column. That's all it means; nothing more or less.

To link this table to the Address table (assuming that is the table's name), you need this:

ALTER TABLE person_address
ADD FOREIGN KEY (address_addressID)
REFERENCES address_tbl_name (addressID)
ON UPDATE CASCADE;

I've no idea what the primary key field is named or what that table looks like, so replace address_tbl_name and addressID in line 3 with appropriate field/table name.


How to check if range between two numbers is available in table of ranges I have following table of number ranges

Create table Ranges {
  Id int identity(1,1) primary key,
  RangeFrom int not null,
  RangeTo int not null
}

Insert into Ranges(RangeFrom, RangeTo) VALUES (1,4)
Insert into Ranges(RangeFrom, RangeTo) VALUES (5,9)
Insert into Ranges(RangeFrom, RangeTo) VALUES (10,14)
-- here is free space (15 - 19)
Insert into Ranges(RangeFrom, RangeTo) VALUES (20,24)

And I want to insert a new record for example

Insert into Ranges(RangeFrom, RangeTo) VALUES (15,19) -- Ok
Insert into Ranges(RangeFrom, RangeTo) VALUES (15,30) -- Fail

The question is that before insertion I need to check if the numbers between range in the insert statement are not already used in any other range in the table.

Any Idea ?

select count(*)
from ( select RangeTo, LEAD(RangeFrom, 1, 0) OVER (ORDER BY RangeTo)
                       AS LeadRangFrom
       from table
     ) ss
where @RangeFrom > RangeTo
  and ( @RangeTo < LeadRangFrom or LeadRangFrom is null)

If count(*) is one then you can insert
Is that the question you are asking?


Enforce indirect relationship Given the following structure

There's a way to enforce that Table1.id3 only can seted to values in Table4.id3 where Table4.id2 = Table1.id2?  Initially I thought in a CHECK constraint, but subqueries are not allowed.

There's a way to enforce that Table1.id3 only can seted to values in Table4.id3 where Table4.id2 = Table1.id2?

It seems to me like you could just set a foreign key constraint directly to Table4.

alter table Table1
add constraint your_constraint_name
foreign key (id2, id3) references Table4 (id2, id3);

You might have to jump through a hoop or two to prevent a cycle in the constraints.


Do you have a rough idea of the expected max length?

varchar(X) is limited to 255 character before MySQL 5.0.3 and 65k at most with MySQL 5.0.3 link

Text data type should be big enough.

You should also consider an update of your data model as suggested by @oNare


MySQL Coalesce date missing results, why? Running MySQL 8.0.16, settings left at defaults as far as I know.

I have a table, let's call it the_table, with columns

id: bigint primary key auto_increment

the_time: datetime

I have a record with the_time of 2019-12-19 00:00:00.

When I run either of

SELECT id, the_time
FROM the_table
WHERE the_time BETWEEN '2019-12-19' AND '2019-12-19';

SELECT id, the_time
FROM the_table
WHERE COALESCE(the_time, DATE('9999-1-1')) BETWEEN '2019-12-19' AND '2019-12-19';

I get that record. However, with

SELECT id, the_time
FROM the_table
WHERE COALESCE(the_time, '9999-1-1') BETWEEN '2019-12-19' AND '2019-12-19';

I do not, though changing it to

SELECT id, the_time
FROM the_table
WHERE COALESCE(the_time, '9999-1-1') BETWEEN '2019-12-18' AND '2019-12-20';

I suddenly do again. Why does MySQL behave this way? Why does the extra DATE() make a difference? I would have thought converting between date and string formats would be bijective and shouldn't affect the computation, but it seems it does. And why does just the coalesce make a difference in the not-null record being found or not?

Because it converts the datetimes to strings and does the comparisons with the string values. And '2019-12-19 00:00:00' <= '2019-12-19' is false in text comparison.


Big string in SQLPlus I need to write a Select query in bind-variable and this query has more than 2500 symbols.

Variable sql_txt clob;

Exec :sql_txt := 'Select .....';

But SQL Plus support only <2499 symbols.
How to fix it?

That limitation is for a single line.

You can break your statement into multiple lines to overcome that limitation, such as:

begin
  :sql_txt := 'select ... ' ||
  ' ... ' ||
  ...
  ' ... ';
end;
/


You can use the PIVOT function to get the result but since you have multiple values that you want to convert into columns, you will first want to look at unpivoting the value and wef columns first.

You didn't specify which version of SQL Server you are using but you can use either the UNPIVOT function or you can use CROSS APPLY with VALUES or UNION ALL to convert value/wef into multiple rows.

The syntax will be similar to:

select year, code, col, val
from dbo.yourtable
cross apply
(
  select name, convert(varchar(10), value) union all
  select name+'_wef', convert(varchar(10), wef, 120)
) c (col, val);

See SQL Fiddle with Demo.  This converts your data into the format:

| YEAR | CODE |     COL |        VAL |
|------|------|---------|------------|
| 2014 | a001 |     abe |       2000 |
| 2014 | a001 | abe_wef | 2014-04-01 |
| 2014 | a001 |     def |       3000 |
| 2014 | a001 | def_wef | 2014-05-01 |
| 2014 | a002 |     abe |       2000 |
| 2014 | a002 | abe_wef | 2014-06-01 |

Now you can apply the PIVOT function:

select year, code,
  abe, abe_wef, def, def_wef, mno, mno_wef
from
(
  select year, code, col, val
  from dbo.yourtable
  cross apply
  (
    select name, convert(varchar(10), value) union all
    select name+'_wef', convert(varchar(10), wef, 120)
  ) c (col, val)
) d
pivot
(
  max(val)
  for col in (abe, abe_wef, def, def_wef, mno, mno_wef)
) piv;

See SQL Fiddle with Demo.

The problem that you might run into with the version above is it works great when you have a limited number of values because you can hard-code the query. But if you have an unknown number of values, then the solution won't work. In situations where you have an unknown number of values then you will need to use dynamic sql to get the final result:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT ',' + QUOTENAME(col)
                    from dbo.yourtable
                    cross apply
                    (
                      select name, 1 union all
                      select name+'_wef', 2
                    ) c (col, so)
                    group by col, so
                    order by col, so
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query = N'SELECT year, code,' + @cols +N'
            from
            (
              select year, code, col, val
              from dbo.yourtable
              cross apply
              (
                select name, convert(varchar(10), value) union all
                select name+''_wef'', convert(varchar(10), wef, 120)
              ) c (col, val)
            ) x
            pivot
            (
                max(val)
                for col in (' + @cols + N')
            ) p '

exec sp_executesql @query;

See SQL Fiddle with Demo. Both versions will give a result of:

| YEAR | CODE |    ABE |    ABE_WEF |    DEF |    DEF_WEF |    MNO |    MNO_WEF |
|------|------|--------|------------|--------|------------|--------|------------|
| 2014 | a001 |   2000 | 2014-04-01 |   3000 | 2014-05-01 | (null) |     (null) |
| 2014 | a002 |   2000 | 2014-06-01 | (null) |     (null) | (null) |     (null) |
| 2014 | a003 | (null) |     (null) |   2000 | 2014-04-01 |   5000 | 2014-06-01 |


mysqldump vs mysqlpump I just heard about mysqlpump - it looks like it was released with MySQL 5.7, however MySQL 5.7 still includes mysqldump. They're both backup programs, but could anyone summarize the main differences? Are there scenarios where one of them might work better? Is mysqldump going to be phased out for mysqlpump?

Since I happened to be researching this myself, here's a summary of what I found.

According to a 2015 blog post from the MySQL dev team, the main advantages of mysqlpump are that it can use multiple threads in parallel to speed up the dumping and that it doesn't share mysqldump's backwards compatibility requirements, which should open the door for further improvements.

However, the post cautions that mysqlpump is "currently unsafe as a general purpose backup replacement" because individual threads may generate their sections of the dump from different states of the database. It doesn't mention any plans to deprecate mysqldump.

Giuseppe Maxia played around with mysqlpump in 2015 and found the dumping speed-up to be fairly small. He appreciated the new ability to select precisely which objects to dump, but noted that loading the dump back in can only be done single-threaded and that (unlike mysqldump) dumps can only be loaded back into databases with the same name.


Is it possible to use just one shared folder for log shipping? I wonder if it is possible to use one shared folder(only in source database) instead of two (one in source db and another in destination db). That's Server1 will store its t-log backups there and Server2 will restore those backups from the same folder. Please see images below to understand what I mean:

Yes you can, I remember a shop where we had this kind of setup in that case even the copy job was disabled because there were no need to copy the files.
You just need to be sure your file retention is ok and the Share is available at all the times. You don't want your share folder go offline during a long weekend or something and your transaction log on prod gets full.


SQL Server 2014 Edition Downgrade I have a SQL Server 2014 Standard Edition used by my devs to test their applications pointing to a common instance. Lately I've been informed that for this purpose I can use Developer Edition, that it would keep all the features they need to work.

I've see on Microsoft website that this counts as an Edition Downgrade and won't have a proper "Wizard" to help on this task. Seeing this, I planned on:

Backup all databases, export users, jobs and grants.
Uninstall Standard Edition.
Install Developer Edition.
Restore databases and re-create users, give grants and re-create jobs.

I did use DBA Tools Powershell Library to migrate a previous instance on the exact same case. Is this plan the easiest option to achieve this migration?

Thanks in advance!

Event though it says its a downgrade, I would say its kind of an upgrade since Developer Edition is equivalent to enterprise in-terms of features.

A straight Start-DbaMigration (from dbatools) will copy all stuff.

I would suggest to provision new machines and just use dbatools to migrate off and then decomm the old standard edition.

Just be aware, if in PROD you are using Standard Edition and your devs started using Enterprise features since they are available in Dev edition, then you will be in trouble as those wont work in PROD being in standard edition.


Why OUTER APPLY forces scanning the entire table? I have a query which does not performs well and optimizer scans all 108864 items from table ExternFile uselessly when seeking only 30 rows would be fine. I have simplified the problem to following select from view.

The upper layer query looks like:

SELECT
realtyId,
ServerPath,
OfferState,
CategoryId,
ParentCategoryId
FROM FSTest
WHERE OfferState <= 3 AND (CategoryId = 2 OR ParentCategoryId = 2)
ORDER BY Ranking DESC
OFFSET 2970 ROWS FETCH NEXT 30 ROWS ONLY

The underlaying view this is following:

CREATE View [dbo].[FSTest] AS
SELECT
realty.Id as realtyId,
realty.OfferState,
realty.Ranking,
Category.Id as CategoryId,
Category.ParentCategoryId,
ExternFileOuterApply.ServerPath as ServerPath,
ExternFileOuterApply.Height as ImageHeight,
ExternFileOuterApply.Width as ImageWidth
FROM realty
JOIN Category ON realty.CategoryId = Category.Id
OUTER APPLY (SELECT TOP (1) ServerPath, Height,Width FROM ExternFile WHERE ForeignId =  realty.Id AND IsMain = 1 AND ForeignTable = 5) AS ExternFileOuterApply

The problem is the OUTER APPLY. I would expect that the query skips all 2970 rows and execute the OUTER APPLY only for 30 rows which are contained in the result. But the OUTER APPLY is scanning entire table ExternFile (108864 rows). Why?

Is it somehow connected with temporary index in the index spool (see image bellow)? What is wrong with the query?

Your Scan is running a single time, to populate an Index Spool. This spool is called 3032 times. If you create an index that will suit better, you should be able to avoid the spool, and it probably won't scan any more either.

Edit: But I think what you want is the left-most TOP to be on the right of the Nested Loop. This is a common thing with Key Lookups as well, that they often appear to the right of Top operators when you'd prefer they be on the left. Here, the Spool is quite cheap to query, so there's no much advantage to having it to the side. With a better index, you could probably persuade it not to Spool, and therefore it might see real benefit in pushing the Top further right.

Another edit: Presumably if you left out ServerPath, the Nested Loop disappears completely, as that table is no longer needed at all. But I'm sure you're trying to get it to work without changing the queries much.


Foreign Keys to tables where primary key is not clustered index I'm focusing on SQL Server here, but I think this is a database agnostic question, really.

I have a table whereby a Guid is logically the primary key.  However, uniqueidentifiers apparently make terrible clustered indexes, so I'm planning on creating a dummy key as an identity column and making that the clustered index for the table.

When it comes to foreign keys referencing this table however, should the foreign keys reference the clustered index or the primary key?  I'm thinking it should be the primary key still, right?  And that's because the clustered index doesn't technically HAVE to be unique, whereas the primary key does...?

Also, just to double check, if I make another column the clustered index, that doesn't mean I need to add a non-clustered index to my primary key column, does it?  In other words, does my column automatically have an index on it just by being the primary key?

Taken from the MSDN article on FOREIGN KEY Constraints

  A FOREIGN KEY constraint does not have to be linked only to a PRIMARY
  KEY constraint in another table; it can also be defined to reference
  the columns of a UNIQUE constraint in another table.

I think it is perfectly fine to define a UNIQUE CLUSTERED INDEX on an IDENTITY column and define the PRIMARY KEY on different columns, often with a NONCLUSTERED INDEX but you don't have to specify an index.


How to execute SQL against all DBs on a Server I have some standard SQL that I run against multiple databases on a single server to help me diagnose problems:

select
    so.name,
    so.type,
    MAX(case when sc.text like '%remote%' then '' ELSE 'N' END) AS Relevant,
    @@ServerName as Server,
    DB_Name() as DBName
from
    sysobjects so with (nolock)
    join syscomments sc with (nolock) on so.id = sc.id
where (sc.text like '%emote%')
group by so.name, so.type
order by so.type, so.name

How can I execute this against all databases on a single server? (besides manually connecting to one at a time and executing)

sp_MSForEachDB

One option is sp_MSForEachDB. It's undocumented but useful nonetheless

DECLARE @command varchar(1000)
SELECT @command =
    'USE [?] UPDATE Table1 SET Field1 = ''ninjas'' WHERE Field2 = ''pirates'''
EXEC sp_MSforeachdb @command

A search of the interwebs has many more examples too

Note: Being an unsupported function (which has some known bugs) you may want to write your own version (thanks @Pradeep)

The SQL example above would need to be restructured as:

DECLARE @findKeySQL nvarchar(2000)
DECLARE @searchKey nvarchar(20)

SET @searchKey = lower('%remote%')

SET @findKeySQL = 'IF ''[?]'' NOT IN (''[master]'', ''[model]'',
                                     ''[msdb]'', ''[tempdb]'')
        select
            so.name,
            so.type,
            @@ServerName as Server,
            ''?'' as DBName
        from
            [?].dbo.sysobjects so with (nolock)
            join [?].sys.all_sql_modules sc with (nolock) on so.id = sc.object_id
        where (lower(sc.definition) like ''' + @searchKey + ''')
        group by so.name, so.type
        order by so.type, so.name'

EXEC sp_MSForEachDB @findKeySQL

NOTES:

? is replaced in the query as the
database name, so structure the
query to explicitly define which DB
it is to query against
modified to use sys.all_sql_modules as holds complete module text (syscomments could split the keyword when reach the spanning over rows)


How can I find out who is trying to connect to my MongoDB? I have a MongoDB server that I recently configured to allow only SSL connections. Now my mongo.log is filled with this:

2017-05-30T17:39:24.528+0000 I NETWORK  [conn7881820] AssertionException handling request, closing client connection: 17189 The server is configured to only allow SSL connections
2017-05-30T17:39:25.053+0000 I NETWORK  [conn7881821] AssertionException handling request, closing client connection: 17189 The server is configured to only allow SSL connections
2017-05-30T17:39:25.592+0000 I NETWORK  [conn7881822] AssertionException handling request, closing client connection: 17189 The server is configured to only allow SSL connections
2017-05-30T17:39:25.593+0000 I NETWORK  [conn7881823] AssertionException handling request, closing client connection: 17189 The server is configured to only allow SSL connections
2017-05-30T17:39:26.369+0000 I NETWORK  [conn7881824] AssertionException handling request, closing client connection: 17189 The server is configured to only allow SSL connections

Unfortunately the IP address of the offending process isn't logged.

How can I find out what IP is trying to connect to my MongoDB?

The IP address will be logged by default.

For example, the following log entry you shared shows activity for the connection with an ID of conn7881820:

2017-05-30T17:39:24.528+0000 I NETWORK  [conn7881820] AssertionException handling request, closing client connection: 17189 The server is configured to only allow SSL connections

Prior to this log line, there will be a log line that shows the establishment of this connection. The log line should look something similar to the following:

2017-05-30T17:39:24.528+0000 I NETWORK [initandlisten] connection accepted from SOME_IP_ADDRESS:PORT #7881820 (SOME_# connections now open)

The number following the IP address will match the connection ID (without the conn portion) of the log lines in question, (i.e. #7881820)

Note: The log line I fabricated above is using the same timestamp of the log messages you provided; in reality it should be earlier in time.


XML parsing failed in the sqlplus I get following error when executing the query

  Invalid token in '[count(../[name(../)=name(.)])=count(../) and count(../*)>1'

However when installing the Itstar_xml_util package, it asks for two initial variables one quot and another call gt, which form the initial structure of the XML, I believe that is causing the error, someone knows how to configure correctly?

SQL> @C:\instantclient_11_2\itstar_xml_util.pxk
     Packge created.
     Enter value for quot: ?
     Enter value for gt: ?

Also follow the query...

SQL > declare
      l_sql_string varchar2(32000);
      l_xml xmltype;
      l_json xmltype;
      begin
      l_sql_string := 'select DESCRICAO from VEI_TIPO_VEICULO';
      l_xml := 'itstar_xml_util.sql2xml(l_sql_string);
      dbms_output.put_line(l_xml.getclobval());
      l_json := itstar_xml_util.xml2json(l_xml);
      dbms_output.put_line(l_json.getclobval());
      end;
      /

You have to call

SET DEFINE OFF

before installing the package to ensure that &quot and &gt are not recognized as substitution variables. See also docs.


Saving SSIS package from server to local computer Right now I'm able to upload an SSIS package from my local computer to SQL Server with my C# program:

Microsoft.SqlServer.Dts.Runtime.Application app;
app = new Microsoft.SqlServer.Dts.Runtime.Application();

string pkg = @"C:\Temp\myPackage.dtsx";
Package loadedPkg = app.LoadPackage(pkg, null);

app.SaveToSqlServerAs(loadedPkg, null, "myNewPackage", "myServer", null, null);

This works great. Now I want to do the opposite of this, save an SSIS package from the SQL Server to my local computer. I'm using the Microsoft documentation here: https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.dts.runtime.application?view=sqlserver-2017

It doesn't seem to have any methods to save to a computer. I see the "load" methods, but I haven't been able to get those to work. I think they just prep a package to be uploaded to the server, like in my previous example. Is this possible through the same "Application" class?

TECHNICALLY, it can be done through the SaveToXML method. However, for this to work, the process on the SQL Server has to have write access to a share on your destination machine, and you have to specify the share when you give the filename.

Other than that, I can't see a way to do what you are looking for. I was hoping that there was some way to get the package into an object in memory that you could manipulate - which, again technically, I guess you could do by saving the package to XML, then reading it from disk into an object, then saving the object locally. Neither of these options are really what you are looking for, but I believe that they are your only real options available.


Structure database(s) dealing with different time points I have few data sets that give a score for a specific account at a specific point in time. So, account A might have a different score each quarter going back 5 years so. There aren't a ton of data points per customer and we're only dealing with tens of thousands of accounts at the most.

I'm new to tying databases together, so I'm curious about the best way to structure this do I need multiple database or can this all be in one that I can easily query to get all the values over time for a specific account? Seems like this would be difficult though.

Any expertise you can lend would be helpful.

Sounds like a single table in a single database.  It sounds like you need three columns: account, date, score.  The PRIMARY KEY might be (account, date).

10K rows is "small" for a table.


How much overhead is involved with querying a DB on the same server? History

The company I work for had two databases, DB A and DB B, on two different servers. A contains information pulled from a COTS application database in addition to information from our own developed applications. This was done to ensure the COTS application database was never disrupted as it is required 24/7/365.

Some of our users required raw data from A, thus B was born. B pulls data from A into it's own local tables and provides views for users. (I am not a fan of this design at all)

Now

Upgrades and migrations happened. B eventually found it's way onto the same server as A. We are considering cleaning up our database structure and have come up with several options. Keep in mind that access to data is a top priority for the users, but we want to cut down on costs. For simplicity, lets call the COTS database Z

Each option begins with: A pulls data from Z via OpenQuery.

The data is stored in 2 different tables on A, one for applications, and one for the views. The views are moved to A. This method benefits from never having data unavailable to users if an application lock the first table and being able to remove B all together.
The data is stored in 1 table in A. A copy is also stored in B at either the same time or later via scheduled procedure (I'm thinking the former). The views stored in B will continue to access the data in B
The data is stored in 2 different tables on A, similar to option 1. Database B is maintained in order to strictly provide views to the users from A. This would require the least amount of work for us and our tech illiterate users. Though, it seems silly to maintain a DB just because we are too lazy to set up some accounts and permissions.

I would really love to be able to have an option where data from Z is stored in 1 table in A, and everyone plays nicely, but that's not the case. Although we are the owners of this data, there are other groups that utilize it too. They've managed to lock our database on several occasions, so we're being safe.

I am the type of person who likes to try to make things as efficient as possible, so I am really leaning toward option 1, but many of my coworkers are liking option 3 and some even option 2. 1 and 2 are pretty darn similar, so I was wondering what kind of overhead is involved with accessing another database within the same server.

Perhaps there is an option we have not considered?

EDIT

Note: The tables in question are read only.

If you just want a readable copy of the data in table A, then there are ways to make this efficient without worrying about whether the copy is in the same database or a different database. Or a different server, for that matter, as long as you can reliably point your application differently depending on whether it is reading or writing, and as long as there isn't a bunch of referential integrity tied to A.

Let's keep things simple and have everything in the same database. Create two new schemas:

CREATE SCHEMA fake   AUTHORIZATION dbo;
CREATE SCHEMA shadow AUTHORIZATION dbo;

Now create a copy of A in the dbo and shadow schemas:

SELECT * INTO shadow.B FROM dbo.A;
SELECT * INTO dbo.B    FROM dbo.A;

(Then add all of the appropriate indexes, etc. to both tables.)

Now, create a stored procedure that does the following:

TRUNCATE TABLE shadow.B;

INSERT shadow.B SELECT * FROM dbo.A;

-- optionally add an UPDATE STATISTICS command here

BEGIN TRANSACTION;

  ALTER SCHEMA fake TRANSFER     dbo.B;
  ALTER SCHEMA dbo  TRANSFER  shadow.B;

COMMIT TRANSACTION;

ALTER SCHEMA shadow TRANSFER fake.B;

-- optionally truncate the now stale copy, though I keep
-- this around for debugging / comparisons:
TRUNCATE TABLE shadow.B;

Then schedule it to run in a job (frequency will depend on your tolerance for how long this process takes and how fresh the copy of the data needs to be). You can always call it manually if you want it to be refreshed in between runs.

As I mentioned above, unless you use the Auto-Close feature, the overhead of accessing a table in a different database on the same instance is negligible compared to accessing the same table in the same database. So the process or any related efficiency doesn't really change that much if you put B in a different database (just an extra prefix on the INSERT/SELECT, and the pointing of the application as mentioned above). The process also doesn't change that much if you move the copy to a different server (though linked servers, distributed transactions etc. make this a little more cumbersome). You will see the process might be a little slower across servers, but users shouldn't notice unless they also now have to join data between the two servers - if they can query the other server independently to pull data from B they won't be able to tell the difference, unless the other server is severely underpowered.

If you have referential integrity you may find that you will need to drop/re-create and/or disable/re-enable constraints in order to schema transfer (I've almost always used this trick for relatively static lookup data and, as a copy primarily used for reporting, did not have DRI). Also both copies of A will have their own statistics, so half the time you'll have one set of statistics, and half the time you'll have the other set. I haven't done extensive testing of what happens with query plans etc. after the schema transfer.

Of course if you store B in a different database then perhaps the DRI issues etc. are not all that important anyway, but I thought I would mention them.

I have, however, blogged a bit more about how I arrived at this technique:

http://www.sqlperformance.com/2012/08/t-sql-queries/t-sql-tuesday-schema-switch-a-roo


Does MariaDB Support Materialized views? Before I even try to experiment with MariaDB, could anyone tell me if MariaDB supports Materialized views? I can't seem to find it in their documentation.

This is one of my major issues with MySQL (the fact that it does not have materialized views out of the box). This is a major feature for me in a database, and I would like to know if MariaDB supports it.

Not natively, no, but see Flexviews: https://mariadb.com/kb/en/mariadb/documentation/clients-and-utilities/flexviews/


Hierarchial, tree-like data model, which DB type? I have the need for a database, I know what to store, however the question is what database to use and why. I have used relation SQL databases a lot in the past (up to a few years ago) and I know I could do this in SQL. My question is mostly if there is a better alternative. I will sketch the scenario.

I have objects, and those objects can also contain other objects. There is a hierarchial relationship between those objects, bigger objects can contain smaller objects, not the other way around, but outside of this fact, the smaller and bigger objects are to be treated the same, have similar features and can and will be queried just the same. Important to note is that smaller objects can be part of multiple bigger objects (shared by the bigger objects)

Most of the queries will be on an identifier, to just retrieve the main object information plus the objects that it contain (and the objects those objects contain). Most of the information on these objects will remain the same, with a few exceptions, information that will be updated frequently. The important update is adding new objects (and their children which are also objects)

The two options that I see are:

SQL relational database, with a table that has two keys (parent and child)
MongoDB style document database, faster query times (I think, since we can use object ID as index) but slower and more chances at messing up

Is there something I'm missing? What is likely the best approach?

A graph database would be a good candidate. They specialise at retrieving interconnected data and navigating the relationships between objects. The ones I'm familiar with allow dynamic schema so different objects can have different values. Some allow classes of objects to be constructed so some consistency can be enforced. The links between objects are integral to query processing.

A document database has no explicit concept of a connection between one record / row / object / node and another. Your queries would reduce to step-by-step retrieval of an object, then following the "foreign key" pointers to other objects in the application. At least in an RDBMS the referential integrity could be enforced.


Renaming SQL Server Agent operator I want to rename SQL Server Agent operator, that is change it's name.

Is this a "valid" thing to do? I noticed that trying to change name in SSMS via Operator/Properties dialog doesn't actually do anything. However, clicking on selected operator in Object Explorer also allows you to rename it and produces desired result.

My other concern is existing jobs that were setup to send notifications using old operator's name. I wouldn't want them to fail to send notifications but on the other hand would like to avoid changing all of them manually. I dug around a bit and found that table dbo.sysjobs in msdb uses operator ID, so it seems it shouldn't be a problem. I also tried it on a test job and after renaming operator everything still worked as expected plus job properties displayed new operator name without me having to do anything. So it looks like it should not cause any problems at all but I would like some confirmation on this.

In order to change the name (or any other property of an operator), you'd make a call to msdb.dbo.sp_update_operator.  For instance:

exec msdb.dbo.sp_update_operator
    @name = 'OldOperatorName',
    @new_name = 'NewOperatorName';
go

As for SSMS:  most things you do in SSMS you have to option to script out the changes you are making in the GUI.  So, without hitting Ok, you can select the Script button at the top to see what T-SQL SSMS will be executing.  It seems as though even with a changed name (i.e. altered text in the Name text box), the generated T-SQL doesn't contain a specified @new_name parameter for the stored procedure call.

Moral of the story?  Don't rely on a UI.  If you want to have full control over what's happening then you should be using T-SQL.  There will be no question in your mind if you're writing out the code to do what you want.  No assumptions, nothing.

Regarding changing the name, SQL Server stores the ID of the operator to create the correlation between notification -> operator, and not the operator name.  So no, you are not bound to the operator name and I don't believe changing the name of the operator will break anything.


Improve delete speed for SQL Server We have huge production database, its size is around 300GB. Is there any approach to improve performance of a delete query? Right now deletion speed is between 1-10k per minute, it is very slow for us.

If you are trying to delete a large number of rows in a single statement, then it is likely you are waiting on log activity. So you can:

Make sure your log is adequately sized so that growth events don't slow you down. With the defaults your log is probably starting at 1MB with 10% growth. Growth events are expensive, and if you are logging even 10 GB of deletes, this will destroy performance not just now but also in the future (due to what this does to VLFs).
If you are deleting the whole table, use TRUNCATE or DROP/CREATE.
If you are deleting most of the table, use SELECT INTO to put the data you want to keep into another table, then TRUNCATE, then move the small portion back. (Or just drop the old table, rename the new, and re-apply constraints / permissions etc.)
Minimize the impact of logging in the first place by deleting the data in chunks instead of all at once. See this article. You can also consider switching to simple recovery temporarily, so that you only have to CHECKPOINT to clear the log instead of take log backups, but you need to be sure to set it back and to take a new full backup to re-initiate the log chain.


How can I restore a MySQL database after formatting my hard drive I recently formatted my PC which had MySQL running on it.  As a result, I lost the databases on my hard drive.

I tried to restore the databases by re-installing the MySQL Server and putting the databases back into the MySQL datafiles folder.  When I started the MySQL service, I got error 1067: Windows could not start the MySQL Service on local computer.

If I just install MySQL and do not put my databases into the datafiles folder, I don't get the error.

How can I successfully restore my databases?

I'm using Windows Server 2008 R2 enterprise.

After putting the data files, remove ib_logfile0 and ib_logfile1. Dont delete ibdata1 file. And then restart MySQL service.

Before deleting take the backup of ib_logfiles and then delete.

Just give a try in this way.


Restoring a postgres8.3 backup with postgres8.4 I'm trying to restore a backup dumped from a postgres 8.3 server in a server with postgres 8.4 with this command:

pg_restore --host host --port 5432 --username username --dbname my_db /route/to/backup/b.backup --ignore-version --format=c

But I got this error:

  pg_restore: [custom archiver] could not read from input file: end of
  file

I have done a restore with another Postgres 8.3 server and it worked just fine. My box is a Linux Mint Julia.

Anyone else with this same problem? Any idea?

PostgreSQL uses various formats for backups. What's the format of yours?

pg_restore is only needed for backups using one of the archive file formats. The default format of backups produced with pg_dump is an SQL script that you can restore via psql:

psql dbname < /path/to/backup

Start by reading the chapter "Backup and Restore" in the manual.


SQLAnywhere: Savepoint for ROLLBACK not found What is the correct way on setting and rolling back to savepoint on SQLAnywhere. Having this snippet:

begin transaction;
SAVEPOINT spt_abc;
insert into eins (pl1) values (5);
SAVEPOINT spt_123;
UPDATE eins SET pl1 = 10 where pl1 = 5;
ROLLBACK TO SAVEPOINT spt_abc;
commit;

Getting this error message while running it in dbisql:

  Could not execute statement. Savepoint 'spt_abc' not found SQLCODE=-220, ODBC 3 State="HY000" Line 6, column 1 You can continue
  executing or stop.

  ROLLBACK TO SAVEPOINT spt_abc

the very same happens when trying to rollback to spt_123.

This behaviour seems to be caused by the option to auto commit after each command in dbisql configurable via Tools->Options->SQLAnywhere->Execution->"Commit after every statement". If this option is set, I can reproduce the error described at the question. Unchecked it's working as expected.


What is a database with ".res" in its name? I'm not that experienced with MySQL and I'm trying to fix some databases.

In mysql, a "SHOW DATABASES" shows (among others) the main database "MainDB" and another database "MainDB.res". If I'm trying to DUMP or USE that second database (with `` around the name), I get the error
ERROR 1102 (42000): Incorrect database name 'MainDB.res'.
Dumping the MainDB works without problems. Now I'm wondering if MainDB.res is some special or maybe auto-generated database that the MySQL admin has no rights for? How can I look into that database?

Detailed information:
Originally I wanted to see all ibfk connections between all tables.
Now I'm searching for a bug in the databases because I can't look into information_schema, as a
mysql> SELECT * FROM information_schema.KEY_COLUMN_USAGE;
results in the error message
ERROR 1103 (42000): Incorrect table name 'accessgroup'
and I read some forum posts suggesting that the error happens because more than one table was found with that name, and renaming it may solve the problem. That is the reason I wanted to take a look at "MainDB.res" in the first place...

EDIT:

MainDB.res/ is indeed a direcory (in /var/lib/mysql/) containing some, but not all files in the directory MainDB/. Some missing files are of type .frm, some of .MYD and .MYI. 'accessgroup.frm' is in both directories. (I can't anonymize the output of ls -l enough to paste it here.) I'll try what happens if I move the directoy MainDB.res somewhere else.

The MainDB.res is not the Actual database.Someone by mistake have created it in your data directory.

Have a look at the content of my datadir

-rwx------ 1 mysql mysql     0 2012-05-23 14:48 debian-5.1.flag
drwx------ 2 mysql mysql  4096 2012-05-28 16:09 mysql
-rwx------ 1 mysql mysql     6 2012-05-23 14:49 mysql_upgrade_info
-rw-rw---- 1 mysql mysql     5 2012-05-29 09:15 OSS-240.pid
drwx------ 2 mysql mysql 36864 2012-05-28 16:58 test
drwx------ 2 mysql mysql  4096 2012-05-29 10:39 world
drwxr-xr-x 2 mysql mysql   4096 2012-05-29 14:29 world.res

where world.res is the directory which i have created as

mkdir world.res

and now when i want to acess it in the MySQL Shell see the result(The world.res is created as #mysql50#world.res by itself.
)

mysql> use #mysql50#world.res
ERROR 1102 (42000): Incorrect database name '#mysql50#world.res'

So in my opinion the MainDB.res is not the database.

UPDATE

In MySQL you can't create the Database's that contains the word "."

mysql> create database test.res;
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 '.res' at line 1

so it is sure that that was not your database.


DB2 Trigger Error : An unexpected token "OR INSERT" was found following SQLCODE=-104, SQLSTATE=42601, DRIVER=3.68.61 I am getting an error while executing the following queries. As per the DB2 documentation, we can have both update and insert event in the same trigger, but when I am trying to do so I am getting an error.

CREATE TABLE NULL_LOOKUP (
    ID INT NOT NULL,
    TABLE_NAME VARCHAR(100) NOT NULL
);

CREATE TABLE PARENT (
    PARENT_ID INT NOT NULL,
    PARENT_NAME VARCHAR(100) NOT NULL
);

CREATE TABLE CHILD_ONE (
    CHILD_ONE_ID INT NOT NULL,
    CHILD_NAME VARCHAR(100) NOT NULL,
    PARENT_ID INT
);

--#SET TERMINATOR @
CREATE OR REPLACE TRIGGER RESET_NULL_TRIGGER
BEFORE UPDATE OF PARENT_ID OR INSERT ON CHILD_ONE
REFERENCING NEW AS N
FOR EACH ROW WHEN (N.PARENT_ID IS NULL)
    BEGIN ATOMIC
    SET N.PARENT_ID = (SELECT ID FROM NULL_LOOKUP WHERE TABLE_NAME = 'PARENT');
    END
@
--#SET TERMINATOR ;

You need to replace BEGIN ATOMIC with just BEGIN in the trigger body.

BEGIN ATOMIC indicates an inlined compound SQL statement, BEGIN -- a compiled compound statement. Documentation states:

  If multiple events are specified, the triggered action must be a compound SQL (compiled) statement.


Extract and combine multiple values from a jsonb column I am looking to extract multiple values from a jsonb column in Postgres, and am running into an issue where some values are coming back null.

fiddle

Setting up schema:

create table jsonb_test (test jsonb);
insert into jsonb_test values('{
"title": "test",
"tags": [
    {"tag": 1},
    {"tag": 2}
]
}'::jsonb);

Query that I am running:

select jsonb_path_query(test, '$.title'::jsonpath) as title,
       jsonb_path_query(test, '$.tags.tag'::jsonpath) as tag
from jsonb_test

The result that I am looking for should be:

| title | tag |
|-------|-----|
| test  | 1   |
| test  | 2   |

and what I am getting is:

| title | tag |
|-------|-----|
| test  | 1   |
| null  | 2   |

For the query, I have tried everything I can think of to make the null go away, selecting the second set of values from a cross join as opposed to the same table, aggregating it into a regular array and then using unnest, etc, but I can't seem to get it to work, and more importantly, I don't understand what is causing that second null.

What is the easiest way (performance is not expected to be an issue here) to get the result that I am looking for?

jsonb_path_query() is a set-returning function. When putting more than one of those in a SELECT list, this is the expected behavior. See:

What is the expected behavior for multiple set-returning functions in SELECT clause?

You seem to be looking for a CROSS JOIN instead:

SELECT *
FROM       (SELECT jsonb_path_query(test, '$.title'::jsonpath) AS title FROM jsonb_test) a
CROSS JOIN (SELECT jsonb_path_query(test, '$.tags.tag'::jsonpath) AS tag FROM jsonb_test) b

Alternatively, and probably more elegantly, use two LATERAL joins:

SELECT a.title, b.tag
FROM   jsonb_test j
     , jsonb_path_query(j.test, '$.title'::jsonpath) a(title)
     , jsonb_path_query(j.test, '$.tags.tag'::jsonpath) b(tag);

db<>fiddle here

What is the difference between LATERAL and a subquery in PostgreSQL?

Effectively, a "proxy cross join". See:

Understanding multiple table join with aggregation


Oracle Data Guard:When would data rows exist in the view v$archive_gap? I am trying to learn how to resolve the gap between primary and standby manually, the version of my oracle database is 11gr2.

What I did as follow:

At the beginning, primary and standby is synchronized with same log sequence
Disable the parameter log_archive_dest_2,which is used by primary to send redo to standby
Create the archive log by alter system switch logfile at primary
perform a query against v$archive_gap at standby to find the gap between primary and standby

At step4, there is no rows return from v$archive_gap, so I came out with confuses with this situation:

Q1: I has the parameter fal_client and fal_server set at standby, is this factor responsible for “no data return from v$archive_gap”

Q2:If everything goes find, some data rows will be returned from v$archive_gap.In fact, I have log_archive_dest_2 disable at primary ,so standby and primary didn’t not realize each other, how standby know there is a gap?

An archivelog gap simply means that the apply service on the standby site cannot continue because there is a missing sequence between the sequence that was applied and the next sequence that is available (either shipped from the primary or copied manually).

In other words, if sequence 57 was applied at the standby site, but the next sequence available is 59, then the apply service cannot continue until sequence 58 is available (and has been applied).

Resolving gaps largely depends on why there is a gap.  This can happen if an archivelog backup is taken on the primary site with the "delete all input" option and those archivelogs have not been sent to the standby site yet.  Since archivelog backups are not automatically sent to the standby site when the shipping process to re-enabled, the gap is detected on the standby site and the gap is then viewable in v$archive_gap.

In the above example, resolving the gap involves (assumes a Windows environment):

Manually copying the archivelog backup to the standby site via OS commands (in this example, I copied the archivelog backups into l:\oracle\archivegaps):

Register the archivelog backups with the standby site:

catalog start with 'l:\oracle\archivegaps';

Unpacking the archive files from the archivelog backup:

RUN
{
  SET ARCHIVELOG DESTINATION TO 'l:\oracle\archivegaps';
  RESTORE ARCHIVELOG FROM sequence 58 UNTIL sequence 58;
}

Register the archivelogs with the standby site (this step may be optional, as unpacking the archivelogs from the backup may register the archivelogs automatically, but I honestly don't remember).

catalog start with 'l:\oracle\archivegaps';

You may also need to restart the apply service before the gap is resolved:

alter database recover managed standby database cancel;
alter database recover managed standby database using current logfile disconnect from session;

As for your specific questions:

A1: fal_client and fal_server should have no bearing on whether rows are in v$archive_gap.

A2: A gap is detected on the apply side, not technically the shipping side, so a gap can be detected even if shipping is turned off.  Additionally, if a gap is detected while shipping is on, then shipping is turned off, the gap will still be listed in v$archive_gap until the gap has been resolved.


output psql results and errors to a file I want to display the results of psql queries on a file.In the log just the name DO for the anonymous Bloc is being displayed.

The following is being executed from a shell script file :

psql --host=${HOST} --port=${PORT_IP} --dbname=${DATABASE_NAME} --username=${USERNAME} -f $scriptSQL -v v1=$fichierLog -v v2=$annee

and here is the content of scriptSQL :

\o :v1
\timing on
DO $$
declare
    -- programme de lancement de la purge des redevances

    -- date creation : 20/02/09

    annee       varchar(4); --  annee a purger

begin

    annee := ':v2';

  -- lancement de la fonction de purge
    SELECT pckg_activ.purgeRedevance(annee);

end $$;

\timing off
\o
\q

nothing is being displayed apart from DO.

Is the DO Anonymous Bloc the reason why nothing is displayed on v1 (the output file) ?

Any solutions ?

Thanks

RAISE NOTICE must be used to display values in a DO block,
as in

DO $$ begin raise notice 'hello'; end $$;

Result:

NOTICE:  hello

If you do just a SELECT, here's what happens:

DO $$ begin select 'hello'; end $$;
ERROR:  query has no destination for result data
HINT : If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT : PL/pgSQL function inline_code_block line 1 at SQL statement

Since it's an error, it doesn't go into the file designated by \o :v1

The output of \timing also won't go into that file.

The documentation says about \o:

  \o or \out [ filename ] \o or \out [ |command ]
     Arranges to save future query results to the file filename or pipe
     future results to the shell command command. If no argument is
     specified, the query output is reset to the standard output.

  ...

  “Query results” includes all tables, command responses, and notices
     obtained from the database server, as well as output of various
     backslash commands that query the database (such as \d); but not
     error messages.

To capture all the output, use redirection at the shell:

$ psql [options] >file 2>&1

Another problem is that the :v2 variable cannot be expanded in the dollar-dollar-quoted string that constitutes the DO block (and it should be written as :'v2', not ':v2', but anyway it doesn't matter here because there's no variable expansion in $$-quoted strings )

Both input parameters and output results are sadly difficult to manage with DO blocks.

For the input side, custom session parameters can be used, as shown below:

test=> \set var 'foo'
test=> set params.v1 to :'var';
SET
test=> show params.v1;
 params.v1
-----------
 foo
(1 row)

test=> do $$ begin raise notice '%', current_setting('params.v1'); end $$;
NOTICE:  foo
DO


adding and removing a node from a mongo replica set I have a new backup server I want to add to our mongo replica sets. As far as I can tell its as easy as logging on to the replica sets primary and adding the following command:

rs.add( { host: "mongobackup:10003", priority: 0, votes: 0, hidden: true } )

My understanding is that after doing so the primary should sync all its configuration and data with the new node.

Additionally I have an old backup server I want to remove from the replica set which I looks like all i have to do is type this command into the replica sets primary:

rs.remove( "oldmongobackup:10003" )

Is there anything else I am missing?

rs2:PRIMARY> rs.conf()
{
    "_id" : "rs2",
    "version" : 26,
    "members" : [
        {
            "_id" : 7,
            "host" : "mongo03:10001",
            "arbiterOnly" : false,
            "buildIndexes" : true,
            "hidden" : false,
            "priority" : 20,
            "tags" : {
                "dc" : "maid"
            },
            "slaveDelay" : 0,
            "votes" : 1
        },
        {
            "_id" : 8,
            "host" : "mongo01:10002",
            "arbiterOnly" : false,
            "buildIndexes" : true,
            "hidden" : false,
            "priority" : 10,
            "tags" : {
                "dc" : "maid"
            },
            "slaveDelay" : 0,
            "votes" : 1
        },
        {
            "_id" : 9,
            "host" : "mongo02:10003",
            "arbiterOnly" : true,
            "buildIndexes" : true,
            "hidden" : false,
            "priority" : 1,
            "tags" : {

            },
            "slaveDelay" : 0,
            "votes" : 1
        },
        {
            "_id" : 10,
            "host" : "oldmongobackup:10003",
            "arbiterOnly" : false,
            "buildIndexes" : true,
            "hidden" : true,
            "priority" : 0,
            "tags" : {

            },
            "slaveDelay" : 0,
            "votes" : 1
        }
    ],
    "settings" : {
        "chainingAllowed" : true,
        "heartbeatTimeoutSecs" : 10,
        "getLastErrorModes" : {

        },
        "getLastErrorDefaults" : {
            "w" : 1,
            "wtimeout" : 0
        }
    }
}

The command should be fine to run. Why ???

Each rs.add() and rs.remove() should trigger an election. Since the priorities of the remaining nodes have unique values:

_id : 7 as priority : 20
_id : 8 as priority : 10
_id : 9 as priority : 1

you can say the election is rigged. The primary node should remain the primary.


How can I execute multiple statements at once in MySQL Workbench? I'm trying to execute multiple LOAD DATA INFILE statements at once using MySQL Workbench. However, they execute one at a time. Anyway of forcing them to run in parallel?

Thanks for the help!

You can open a separate SQL editor for each LOAD command and run it there. While one command is running you can switch to the next editor and run the next command and so on.


MySQL Database Schema for Train Timetables I am interested in what a MySQL database schema would look like for a train timetable.

Generally this is presented in table format as the final result.

Train No.    11111  22222  11111
Train Day    Mo-Fr  Sat    Sun
Station A  d 06.00  07.00  07.00
Station B  d 06.10         07.10
Station C  d 06.20  07.15
Station D  a 06.30         07.40
Station D  d 06.35  07.25
Station E  d 06.45  07.45

So each train has a train number, but that isn't unique as the number will be duplicated for a Monday-Friday to a Saturday and Sunday.  Then a train will stop at a number of station, but not all, and sometimes the station needs an arrival (a) and departure (d) time, or one or the other.

Stations can be ordered by their distance.

So far I am thinking I need tables for:

Stations (ID, name, distance, line)
Train (ID, train no, Day Operation, details)
Train run (Train.ID, Station.ID, arr/dep, time, notes)

Is this normalised enough?

Should say train 11111 for Mon-Fri be just one entry with lots of combinations of Day Operation, ie 7 columns (M, T, W, Th, F, S, Su) ?
Shouldn't it also handle a one off train that runs on say just one date and doesn't repeat weekly?

I would strongly consider explicitly storing all the days a particular schedule actually runs on. This would give a structure looking something like this:

By doing so, you'll make it much easier to answer questions such as "What are all the trains going to station X on 1 Oct?". It'll also makes "temporary gaps" when trains aren't running (e.g. Christmas day) possible to identify. A one-off train is now simply one with only one entry in SCHEDULE_DAYS.

As the schedule can be different on weekends to weekdays, I think it's better to have separate rows for each day. This allows linking different schedules for every day of the week, should you ever need to do this.


Can see other user's databases in shared hosting This looks like a security concern but I do not know enough about SQL Server to know.

Using a shared hosting service, if I go to restore a database I'm able to see other user's databases.

I'm not able to restore their database to mine so I cannot see any of their data, just their database names.

Is this a security concern?

I'd talk to your hosting provider's technical support people about this and get them to explain to you the security configuration they have on the msdb database, which is where those details are stored.

By itself, the list of database names likely does not indicate a security risk.  I'd ensure I wasn't giving away any details in my database names.

If I had SQL Server Agent Jobs running where the output was logged, I'd probably be somewhat concerned about others being able to see that output, so I'd confirm that (a) I don't have SQL Server Agent jobs with output, and (b) no one else can configure SQL Server Agent jobs to run against my databases.


SQL Server-Table has many AFTER INSERT triggers that insert into the table itself I purposely created this example to represent my problem.
I created table like this:
CREATE TABLE a
(
  id INT
)

I then created 2 AFTER TRIGGER like this:
CREATE TRIGGER insert_a
ON a AFTER INSERT
AS
BEGIN
  INSERT INTO a VALUES (1)
END
GO

CREATE TRIGGER insert_a2
ON a AFTER INSERT
AS
BEGIN
INSERT INTO a VALUES(2)
END
GO

After that I inserted into table:
INSERT INTO a VALUES (0)

I got the result :

Msg 217, Level 16, State 1, Procedure insert_a2, Line 5 [Batch Start Line 0].
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

I think there is cyclical situation here.The insert, inside the trigger, fires the trigger, and again and again. Will that happen?
I want to know what happens inside ?
Is there the way to solve this problem but still keep intact 2 AFTER INSERT triggers?
Crazy situation.

Your issue is related to the nested triggers notion in SQL Server. This concept is explained as like below in MSDN

  Both DML and DDL triggers are nested when a trigger performs an action that initiates another trigger. These actions can initiate other triggers, and so on. DML and DDL triggers can be nested up to 32 levels.

Particularly your issue you can limit the nested_triggers config as 0 so that the triggers will be only performed one time.

At first, we will set the nested_triggers value as 0. However, this option is on the server level to take account of this point.

sp_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO

And then we will re-execute your query and the resultset of the a will be liked as below.

+----+
| id |
+----+
|  0 |
|  2 |
|  1 |
+----+


Is mysqlcheck output buffered I have 15 databases running MariaDB with roughly 1000 tables each. Nightly I run mysqlcheck to repair and optimize the tables which takes around an hour. I've tail -F'd the log before and it looks like it runs through around 900 tables at once, outputs it's finding, then does a bunch more. I decided to run the output through a timestamper (ts from moreutils) so I can verify this and see which tables take the longest and potentially clean up unnecessary data. The output from the command shows the first table takes 13 minutes, then it does 900 tables at 0 seconds each, then it stops for 13 minutes on another table. None of the tables it stops on are particularly big nor slammed.
So my question is, is mysqlcheck buffering it's output and is there a way to prevent it from doing so, or am I looking at writing a manual script?

Command: /usr/bin/mysqlcheck -p<PASSWORD> -A --auto-repair -o

Server version: 10.0.29-MariaDB SLE 12 SP1

(This "Answer" does not answer the Question; it eliminates the Question.)

Don't use MyISAM; switch to InnoDB.  That gets rid of the need to repair tables.

Don't use MyISAM; switch to InnoDB.  That gets rid of the need to OPTIMIZE tables.


Extended Event doesn't capture every rpc_completed I have a long running Extended Event session that captures rpc_completed events filtered for a few procs. It has found some that have run. But others it doesn't capture them when I know they have been executed. The reason I know this is because I look in the cache and find them called there.

I'm using SQL Server 2016. I'm filtering on object_name as the field name, equal_i_sql_unicode_string as the operator and the value minus the schema name. I've also modified the session to add procs that I know are used regularly and it captures those fine.

Why does it overlook and not capture some?

Things may not be in Extended Events data when you look for a few reasons

Something in your XE session filtering excluded the data. This would be hard to diagnose without your XE session code, and the code that didn't make it in there. I'm not asking for those details, mind you -- that's a tip of the iceberg thing that isn't a good fit for Q&A here.
When you set up XE sessions, you have the choice to lose single, multiple or no events. The GUI even warns you against choosing to not lose any.
Events may not have been dispatched to the file in the time specified. For instance, if you run a query to see if it goes in there, you may have to wait at least 30 seconds from it finishing until it's in the XE session data.

Depending on the target, and how much data is allowed to go into the target, the data you're looking for may have expired from the XE data.

For rpc_completed, it's possible that the rpc did not complete-d. To tackle that, you'd need to get rpc_starting, and then track causality in the XE session settings.

I'd probably want to cover sp_statement_starting and sql_batch_starting as well. As David Browne mentioned, catching things in the right place can be tricky:

  An RPC is a query sent from the client, not embedded in a TSQL batch. A stored procedure can be run from a TSQL batch, or from another stored procedure and not appear as an RPC.

This isn't stuff that's easy to troubleshoot without looking at a server directly, but it should be enough for you to start your own investigation.


Every person has a contact info table and every contact has an address so i'm still in the design phase of this database. Anyways, every person, whose PK is their name, has a FK to a contact table then I'm very confused as to what's next as every contact table contains a reference to the address table. Person -> Contact -> address
Tables contain as follows:
Person:
PK | name
contact info references contact table (?)

contact:
PK | address(?)
email

Address:
PK | street name (?)

First Question: What would be the PK for my contact table?
Second question: What would be the PK for my address table? Would it even have a PK or is it just referenced?
Essentially one person would have one entry into the contact table, then in the contact table it references the address table and I'm just trying to figure out the best way to do that.

Name - can't be considered a good candidate for primary key. Since, it can be duplicate/modified later. In your case, I don't see any natural key which can be used as primary key. Better, you can add/use a surrogate key(Serial No. -autoincrement) as primary key.
For address table: If you are also storing email address. You can create  separate columns for email and postal address and make email address as primary key.
To reference Contact Info from Person table - you can use email address as foreign key into person table.
So, schema can be like below:
    Person:
    S.N. : (Auto Increment) - Primary Key
    name :
    email : Foreign key

    Contact:
    email : Primary key
    Postal address


How to find column which causing "Conversion failed" error I've run into quite a lot of issues specially when it comes to conversion errors:
convert(datetime, [fieldname]) or SELECT CAST([field2] AS int).
Imagine a large select statement with 30+ conversion or cast functions, or a complex view with conversions, or even an insert statement on a field with wrong data type. I get so much headache trying to exclude fields one by one to find the troublemaker.
Is there a way to find out which field and/or value is causing the issue? How do people troubleshoot these without having to go through an arduous process of going field by field? Is there a log or information message that can help?

How do people troubleshoot these without having to go through an arduous process of going field by field?

Exclude half of them.  If the error persists exclude half the remaining.  Repeat.


PowerShell 'get-ClusterQuorum' output is not showing 'QuorumType' column PowerShell cmdlet 'get-ClusterQuorum' output is not showing "QuorumType" column. Has anybody seen this before? Thanks

Perhaps the default formatting for the output of that command has been modified. You can override the default formatting with the Select-Object cmdlet.

Get-ClusterQuorum | Select-Object Cluster, QuorumResource, QuorumType

Using that command will ensure that those three columns are outputted to the console.


Auto rollback Explicit transactions after X amount of time Is it possible to have SQL Server auto rollback your transactions after a certain amount of time to avoid the blocking/locking that is caused? I have been guilty of this in the past and I hear it from many others that it is a common mistake.

For example:
I always use a block such as this When doing deletes/updates.

BEGIN TRAN
--SQL Code Here
ROLLBACK TRAN
COMMIT TRAN

This allows me to see how many rows are affected or perform a select on the data and then I can comment out the ROLLBACK which will automatically COMMIT the TRANSACTION.

However, there is a possibility of the of not having the COMMIT highlighted/selected which causes the transaction to hang. What I am looking for is a setting such as:

BEGIN TRAN 15 Seconds
--SQL Code Here
ROLLBACK TRAN
COMMIT TRAN

This will only keep the transaction open a max of 15 seconds.

If this is not possible, does anyone have a better workflow for this?

You could create a job that checks for open transactions and kills them if they've been open for a period of time and are causing blocking. But you'd have to be careful with this as you could have a large, necessary transaction that takes a while that you don't want to get killed. Imagine rolling back an index rebuild that's been running for an hour on Standard Edition.

You can use sp_WhoIsActive to find open transactions that are causing blocking, though it'll take a bit of work.

But I would recommend that you don't do this and instead are diligent with your transactions in Management Studio. Any window that I ever started a transaction in gets closed when I am done. And if there's still an open transaction in it, SSMS will warn me about it so that I can commit/rollback.


MySQL memory usage keeps growing. 30GB growth in 3 weeks We have a server with 64GB memory.

After restarting MySQL, in 3 weeks memory is almost full.

MySQL was installed using Debian Percona packages.

Version: Percona Server Release 17 5.7.19-17-log

In daytime, average selects per second are 2500.
Average writes per second are around 100.

Here is the mysqld config.
I'm not a DBA but I have access to server and MySQL root.

Are there any obvious problems? Thanks.

[mysqld]
skip-slave-start
log_error = /var/log/mysql/error.log
server-id               = 2
relay-log               = /var/log/mysql/mysql-relay-bin.log
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 1
max_binlog_size         = 1G
binlog_do_db            = gsa

user   = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket   = /var/run/mysqld/mysqld.sock
port   = 3306
basedir    = /usr
datadir    = /var/lib/mysql
tmpdir   = /tmp
lc-messages-dir  = /usr/share/mysql
explicit_defaults_for_timestamp
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES
symbolic-links=0

# CACHES AND LIMITS #
tmp-table-size                 = 512M
max-heap-table-size            = 512M
query-cache-type               = 1
query-cache-size               = 200M
query-cache-limit              = 10M
max-connections                = 500
thread-cache-size              = 50
open-files-limit               = 102400
table-definition-cache         = 16384
table-open-cache               = 400

# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 32M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 20G
innodb-buffer-pool-instances   = 8

innodb_write_io_threads = 8
innodb_read_io_threads = 8

Well, I did not spot a likely cause of the 30GB, but there are some issues to address:

Observations:

Version: 5.7.19-17-log
64 GB of RAM
Uptime = 23d 17:12:41
You are not running on Windows.
Running 64-bit version
You appear to be running entirely (or mostly) InnoDB.

The More Important Issues:

Normally I would suggest increasing innodb_buffer_pool_size, but since the goal here is to figure out why so much RAM is being used; I will put that off.

Tables are being opened at a large rate.  Perhaps because of lots of short connections?  Perhaps queries with lots of tables?  The OS apparently has a limit of 1024 files per process.  This is unreasonably small for MySQL; try to change the OS limit (to, say, 4096) so that open_file_limit can go up, and then table_open_cache can go up -- to, say, 1000.

'open_files_limit', '1024' -- this disagrees with the unreasonable setting (102400) in my.cnf.  Probably the OS overrode it.

innodb_log_file_size is unreasonably low at only 32M; raise it to 200M.  (See the 5.7 manual for specifics on how to do that.)

Do you have essentially no "transactions"?  That is, are you happy with autocommit=ON?

Some of the details below indicate inefficient queries -- find them so we can discuss them and hopefully make them run more efficiently.

Are you using SSD drives?  (Some of the numbers hint that you are.)

Hundreds of SHOW commands are being run per minute.  (Production systems rarely need to run any SHOW commands.)  Are you using some 3rd party software that can't remember what the schema is?

Details and other observations:

( Innodb_buffer_pool_pages_flushed ) = 220,833,222 / 2049161 = 107 /sec -- Writes (flushes)
-- check innodb_buffer_pool_size

( innodb_buffer_pool_size / _ram ) = 20480M / 65536M = 31.2% -- % of RAM used for InnoDB buffer_pool

( Opened_tables ) = 84,342,523 / 2049161 = 41 /sec -- Frequency of opening Tables
-- increase table_open_cache

( Opened_table_definitions ) = 3,568,448 / 2049161 = 1.7 /sec -- Frequency of opening .frm files
-- Increase table_definition_cache and/or table_open_cache.

( open_files_limit ) = 1,024 -- ulimit -n
-- To allow more files, change ulimit or /etc/security/limits.conf or in sysctl.conf (kern.maxfiles & kern.maxfilesperproc) or something else (OS dependent)

( Table_open_cache_overflows ) = 84,342,104 / 2049161 = 41 /sec
-- May need to increase table_open_cache

( Table_open_cache_misses ) = 84,342,523 / 2049161 = 41 /sec
-- May need to increase table_open_cache

( (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed) ) = ((42793206 + 220833222) ) / 2049161 = 128 /sec -- InnoDB I/O
-- Increase innodb_buffer_pool_size?

( Innodb_os_log_written / (Uptime / 3600) / innodb_log_files_in_group / innodb_log_file_size ) = 234,594,526,208 / (2049161 / 3600) / 2 / 32M = 6.14 -- Ratio
-- (see minutes)

( Uptime / 60 * innodb_log_file_size / Innodb_os_log_written ) = 2,049,161 / 60 * 32M / 234594526208 = 4.88 -- Minutes between InnoDB log rotations Beginning with 5.6.8, this can be changed dynamically; be sure to also change my.cnf.
-- (The recommendation of 60 minutes between rotations is somewhat arbitrary.) Adjust innodb_log_file_size. (Cannot change in AWS.)

( Innodb_rows_deleted / Innodb_rows_inserted ) = 94,976,602 / 102716752 = 0.925 -- Churn
-- "Don't queue it, just do it." (If MySQL is being used as a queue.)

( Innodb_row_lock_waits ) = 398,208 / 2049161 = 0.19 /sec -- How often there is a delay in getting a row lock.
-- May be caused by complex queries that could be optimized.

( Innodb_dblwr_writes ) = 16,335,687 / 2049161 = 8 /sec -- "Doublewrite buffer" writes to disk. "Doublewrites" are a reliability feature. Some newer versions / configurations don't need them.
-- (Symptom of other issues)

( expand_fast_index_creation ) = OFF -- ALTER and OPTIMIZE may be greatly sped up by using ON.
-- Probably better to be ON.

( innodb_thread_concurrency ) = 0 -- 0 = Let InnoDB decide the best for concurrency_tickets.
-- Set to 0 or 64. This may cut back on CPU.

( innodb_print_all_deadlocks ) = OFF -- Whether to log all Deadlocks.
-- If you are plagued with Deadlocks, turn this on. Caution: If you have lots of deadlocks, this may write a lot to disk.

( local_infile ) = ON
-- local_infile = ON is a potential security issue

( bulk_insert_buffer_size / _ram ) = 8M / 65536M = 0.01% -- Buffer for multi-row INSERTs and LOAD DATA
-- Too big could threaten RAM size. Too small could hinder such operations.

( Created_tmp_disk_tables ) = 2,577,361 / 2049161 = 1.3 /sec -- Frequency of creating disk "temp" tables as part of complex SELECTs
-- increase tmp_table_size and max_heap_table_size.
Check the rules for temp tables on when MEMORY is used instead of MyISAM. Perhaps minor schema or query changes can avoid MyISAM.
Better indexes and reformulation of queries are more likely to help.

( tmp_table_size ) = 512M -- Limit on size of MEMORY temp tables used to support a SELECT
-- Decrease tmp_table_size to avoid running out of RAM. Perhaps no more than 64M.

( (Com_insert + Com_update + Com_delete + Com_replace) / Com_commit ) = (88969136 + 202120925 + 856731 + 0) / 7 = 4.17e+7 -- Statements per Commit (assuming all InnoDB)
-- Low: Might help to group queries together in transactions; High: long transactions strain various things.

( Select_scan ) = 19,298,109 / 2049161 = 9.4 /sec -- full table scans
-- Add indexes / optimize queries (unless they are tiny tables)

( Com_insert + Com_delete + Com_delete_multi + Com_replace + Com_update + Com_update_multi ) = (88969136 + 856731 + 0 + 0 + 202120925 + 0) / 2049161 = 142 /sec -- writes/sec
-- 50 writes/sec + log flushes will probably max out I/O write capacity of normal drives

( expire_logs_days ) = 1 -- How soon to automatically purge binlog (after this many days)
-- Too large (or zero) = consumes disk space; too small = need to respond quickly to network/machine crash.
(Not relevant if log_bin = OFF)

( innodb_autoinc_lock_mode ) = 1 -- Galera: desires 2 -- 2 = "interleaved"; 1 = "consecutive" is typical; 0 = "traditional".

( slave_pending_jobs_size_max / max_allowed_packet ) = 16M / 4M = 4 -- For parallel slave threads
-- slave_pending_jobs_size_max must not be less than max_allowed_packet

( slow_query_log ) = OFF -- Whether to log slow queries. (5.1.12)

( long_query_time ) = 10.000000 = 10 -- Cutoff (Seconds) for defining a "slow" query.
-- Suggest 2

( back_log / max_connections ) = 92 / 214 = 43.0%

( Connections ) = 170,473,414 / 2049161 = 83 /sec -- Connections
-- Increase wait_timeout; use pooling?

Abnormally large (not necessarily "bad"):

Binlog_cache_use = 130 /sec
Bytes_sent = 15636572 /sec
Com_release_savepoint = 0.042 /HR
Com_rollback_to_savepoint = 0.39 /sec
Com_savepoint = 0.042 /HR
Com_show_create_table = 0.39 /sec
Com_show_fields = 0.83 /sec
Com_show_table_status = 0.39 /sec
Com_show_triggers = 0.43 /sec
Com_update = 99 /sec
Created_tmp_files = 0.26 /sec
Handler_commit = 1423 /sec
Handler_savepoint = 0.084 /HR
Handler_savepoint_rollback = 0.79 /sec
Innodb_buffer_pool_pages_made_not_young = 4570 /sec
Innodb_buffer_pool_read_ahead = 9.24e+7
Innodb_master_thread_active_loops = 2.02e+6
Innodb_rows_deleted = 46 /sec
Open_table_definitions = 16,384
Performance_schema_digest_lost = 4.1e+7
Qcache_hits = 543 /sec       -- This seems strange since the QC is OFF
Qcache_inserts = 198 /sec
Sort_rows = 10139 /sec
Ssl_default_timeout = 7,200
Ssl_finished_accepts = 53
Ssl_session_cache_size = 0.0MB
eq_range_index_dive_limit = 200
innodb_purge_threads = 4
table_definition_cache = 16,384

Note

The buffer_pool will grow until it gets to 20GB.  So, I would expect MySQL to use some amount more than that, but not 30GB.  Did it keep growing after 30?


neo4J - sandbox doesn't work? I've been fooling with neo4j:

however, it appears the sandbox feature is just completely broken?

simply click here

https://auth.neo4j.com/index-sandbox-v2.html#

or perhaps https://auth.neo4j.com/index-sandbox.html#

create an act with your email (or Google/etc);

it just doesn't work - it spins forever:

Am I making some basic mistake?

It would appear neo4J (well, the web site) was just totally broken for some days there (around Aug 7 2017).

As reported by wBob, it does seem to be working now!

It's worth noting that the basic problem (reported in the question above) seems to be resolved.  However, after you "create a sandbox" the system appears to fail when you "go to the data browser" in that sandbox ... but it would appear that it simply works after, say, a few minutes (there's no warning while it is getting going).

Once you get past all that it does seem to work, eg the "movie" sandbox:


Non-Clustered Index key columns to cover a variable where clause To whoever reads this, sorry for the long question and thanks so much for your time.

I'm needing some help on understanding what columns should be key/non key in the following situation, and what potential impacts it might have on performance either way.

We're using SQL Server 2008 but must support 2005 and in our application, we have a query which is hit constantly but the where clause changes depending on how the query is hit.

It ALWAYS includes 3 columns in the where (lets call them a1, a2, a3), and varaibly includes some of the others (lets call them v1,v2,v3), and always selects all of the columns.

so what we're looking at is a query that always looks like;

select id, a1, a2, a3, v1, v2, v3, data1, data2, data3, table_order
from example_table
where a1 = ?
and a2 = ?
and a3 = ?
...
order by table_order

where sometimes (but not too often) the where clause contains one of or combinations of;

and v1 = ?
and v2 = ?
and v3 = ?

Is there a best practice for this sort of situation? From what I understand about indexing, I should be make a non-clustered index that has key columns for stuff in the where clause and everything else selected as a non key column;

CREATE NONCLUSTERED INDEX
ON example_table(a1, a2, a3, table_order)
INCLUDE (id, v1, v2, v3, data1, data2, data3)

My Question here is should I be including v1, v2, v3 in as key columns given that sometimes they are there? IE;

CREATE NONCLUSTERED INDEX
ON example_table(a1, a2, a3, table_order, v1, v2, v3)
INCLUDE (id, data1, data2, data3)

Would that help with performance given that this table is queried, inserted to, updated to, etc. all the time? Or should I have different indexes for each possible iteration (IE an index with a1-3, table_order & v1 as key columns for example)? IE;

CREATE NONCLUSTERED INDEX
ON example_table(a1, a2, a3, table_order, v1)
INCLUDE (id, v2, v3, data1, data2, data3)

CREATE NONCLUSTERED INDEX
ON example_table(a1, a2, a3, table_order, v2)
INCLUDE (id, v1, v3, data1, data2, data3)

etc.

I should mention here that DB size is of concern so I don't really want to do the multiple indexes if I can help it.

I guess what this really boils down to is that I'm wondering if the query optimizer will use the one index that has all of the key columns listed in all of these different situations?

select id, a1, a2, a3, v1, v2, v3, data1, data2, data3, table_order
from example_table
where a1 = ?
and a2 = ?
and a3 = ?
order by table_order

select id, a1, a2, a3, v1, v2, v3, data1, data2, data3, table_order
from example_table
where a1 = ?
and a2 = ?
and a3 = ?
and v1 = ?
order by table_order

select id, a1, a2, a3, v1, v2, v3, data1, data2, data3, table_order
from example_table
where a1 = ?
and a2 = ?
and a3 = ?
and v1 = ?
and v2 = ?
order by table_order

etc.

Or am I forced into having to do multiple indexes to satisfy each condition?

Given that the where clause 90% of the time only have a1, a2, a3, should I just include the v columns in as non key columns because they're selected? Does the inclusion of the extra key columns hurt performance for the 90% use case?

Could specifying so many key columns in the order I'm looking to specify cause performance concerns on simple joins like;

inner join example_table
on a1 = ?

or would the query optimizer just not use this index whatsoever for that kind of simple join? If so, I'm going to need a separate index like the follow, right?

CREATE NONCLUSTERED INDEX
ON example_table(a1)

That's a great question.
And there are good answers.

The engine definitely will use the index even if you don't use every key column.
That's especially so if they are in order, as you are talking about.
(can anyone else speak to different orders of key columns?)

You will benefit just fine from selecting just on the first column alone as a key, or multiple columns.
What will make a difference - for any index - is staying inside the INCLUDEd columns.
No matter how many key columns you use in your Where, the performance hit for having to go back to the primary key for additional columns can be huge as it doubles the "operations".

When it comes to dealing with performance vs. size, you have the same problem as with any index.
Since you know you want the same columns returned in all cases, if you are READ focused, you will probably want to the index with all 6, if you INCLUDE everything.
It will certainly save you db size compared to making both indexes.

On WRITE, you obviously have a bigger burden with a larger index.  That is a significant additional amount of sorting.
If you do just one row inserted at a time, maybe it won't hardly matter at all.
If you do bulk inserts, you'll definitely want to test the two indexes to see the write performance for your actual inserts.


Forums about getting SQL Server 2000 Administration help? What I'm trying to accomplish is that I need to migrate the SQL Server 2000 installation into a  SQL Server 2008 installation, and I would like to say that I am afraid that after this operation I will be unable to use some of my database objects.

What is the best (in your opinion) forum about MS SQL Server 2000 administration?
Is it possible to ask hard questions and get professional answers at those forums you would recommend?

This forum or ServerFault.

In the 13 years i've been working with SQL Server since 6.5 I've frequented several (SQL Server Central, SQL Server Performance etc) but it's hard to discern quality answers on these.

The reputation system on the Stack Exchange sites is a powerful tool to ensure higher quality answers as well weeding out the poor ones.

I can't understand your worries about upgrading: unless you have some code that (ab)uses sp_OA% or such you should be OK. The Upgrade Advisor will assist you here


Should every table have a single-field surrogate/artificial primary key? I understand one benefit of surrogate/artificial keys in general - they do not change and that can be very convenient. This is true whether they are single or multiple field - as long as they are 'artificial'.

However, it sometimes seems to be a matter of policy to have an auto-incrementing integer field as the primary key of each table. Is this always the best idea to have such a single-field key and why (or why not)?

To be clear, this question is not about artificial vs natural - but about whether all artificial keys should be single-field

I'm going to say no, not always, but most of the time yes..

These are some circumstances in which you don't need a surrogate or artificial key:

Pure intersection tables. If there is no risk of the
intersection being the target of a foreign key and if there is little
or no risk of the intersection attracting independent attributes
(i.e. something other than FK's to the two parent tables) then you
can get away with using the combination of FKs as the PK with fair
confidence.
Lookup tables with static business keys. If you have a lookup
table with a unique business key which is fixed externally to your
business and which has zero chance of ever changing for any
practical purpose, then using the business key directly can make
things simpler.  An example might be a list of state or province
codes or a list of ANSI standard numbers, etc.
Tables containing data consolidated from multiple, independent
sources.  If your system has many sources of data that must be
shoehorned together into a single table, say at head office, then
sometimes you need a compound key that includes the source system key
value and a code indicating what the source system was.

There are also some situations where the old-faithful monotonically increasing integer surrogate key is not ideal.  You can have keys that are alphanumeric surrogates.  These could include:

Situations where you need to merge data from multiple, independent
sources.  To avoid key collisions you might use GUIDs instead of
IDENTITY keys.
Situations where you are forced to use non-numeric key
representations.  Let's say you've got a license plate database.
Your key could be the alphanumeric value instead of a pure number.
Situations where some external requirement forces you to apply
compression to your key value.  Instead of using 10 digits for an
int32 you can use six base 36 digits.

Why most of the time yes?  The most fundamental answer to that question is that it is pure hell if you ever need to modify a primary key value on any table.  Since almost anything a user can see or touch is conceivably subject to an update at some point, using a visible key value is inviting pure hell.  Using a surrogate key will keep you from falling into this trap.

Having said that, remember that there is room for YAGNI in applying this concept.  You don't need to go forcing code tables with IDENTITY keys into every nook and cranny of your schema, just in case someone decides that the symbol for male gender in your employee table needs to change from M to X or something silly.


Change 'root' Password forMySQL but Change Lost after Reboot One of my people was asked to install a standalone MySQL on his laptop. After doing this with the Synaptic Package Manager he was unable to login to MySQL. He then claimed he was not offered the option to set a root password during the install (not sure whether to believe this).

After I reset the password everything worked fine until he rebooted. It then seemed to reverted to the original(unknown) password or something else. I have been through this loop three times and cannot understand why this is. After resetting the password I have executed 'flush privileges;' before stopping and starting the server. Everything then works fine until it is rebooted! I am at a loss what to try next bar a complete reinstall...

The laptop is running Mint 17.3 64-bit.

There appeared to be no way out of this! Eventually after backing up Mint 17.3 was re-installed and MySQL added. MySQL did ask for a password to be set and everything has worked fine since. I doubt the reason for the problem will ever be known!