Does parameter sniffing exist for views? I've heard of and encountered parameter sniffing in stored procedures before.  But today I ran into the first instance of what I can only assume is parameter sniffing in a view.

I have a query that selects from a number of tables and a view.  It's basically:

select *
from view v
inner join table t
on v.id = t.id
where v.date = @EndDate;

With the parameter in place, the query will attempt to execute literally for hours.  I've let it sit for over 2 hours and nothing gets returned.  If I replace the parameter with a specific date where v.date = '2017-11-20' or even a calculated value where v.date = cast(getdate() as date) it will return in 2 seconds.  I can keep the parameter in by adding option (recompile) to the end of the query and it will return results in a second or two.

So I know how to fix this, I am really just asking: Is this something that others have run into before?  Is it possible it's not parameter sniffing but rather something else?

Thanks.

The performance issue that you're seeing is not caused by parameter sniffing. The cause is that your query does not qualify for the parameter embedding optimization without a RECOMPILE hint. There's a lot of confusion about this online, but one could say that it's the exact opposite of parameter sniffing. The query optimizer doesn't know anything about the local variable so your query plan won't change based on the value of the local variable.

If you're still not convinced, try flushing the plan cache or restarting the server in a development environment. You'll still experience the same performance issue even though there aren't any cached plans available.

It is possible to experience parameter sniffing with a view if you execute the query using sp_executesql. There is an example of that in the earlier linked article.


Measuring DB time of application using SQL Server Profiler I have an application residing on the same host as a SQL Server database. There is a scenario in which this application is slow and due to co-existence of IIS and SQL Server, I cannot get which one wastes what fragment of time.

My assumption is that I can save trace to a table and sum completed RPCs time to get microseconds of time that is spent on DB. yet I doubt it very much. I don't know compilation time or any other times is included in this trace time or not.

Question: how can I tell which fragment of time is spent when an application request is sent to the SQL Server database?

Thanks in advance

On server side trace DURATION is the total execution time (in ms) of a query. CPU column will provide you time on CPU (total of one or multiple CPUs). While DURATION - CPU = IO Time

So If RPC-Completed time for single procedure call or total time is according to benchmarks then problem could be on network side.

Where is actual problem. To get this you must keep close eye on Wait Statistics. Which will help you to detect real problem whether its on database side or network.

Note:
1-In case of parallelism (a query resolved by multiple CPUs) DURATION could be less then CPU.
2-Profiler will show you DURATION in milliseconds while inserting same trace data in a table, you will get DURATION  in microseconds.


Customizing nls_sort and nls_comp in Oracle There is different letters for 'KAF' (ک , ك) and 'YA' (ی , ي) in Persian with different unicode codes and same spell.

I am using these commands to set Persian sort and comparing in my project

ALTER SESSION SET nls_sort=persian;

ALTER SESSION SET nls_comp =linguistic;

With setting nls_comp and nls_sort parameter I receieve these output

select first_name from customer
where first_name like '%ك'; -- with result

select first_name from customer
where first_name like '%ک';--with no result

How can receive same output for these queries? Can I customize nls_comp ?

I think the Oracle Locale Builder utility is the tool that you want. The documention on the 10.2 version (since you've tagged with oracle-10g) of this utility is here: http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch13custlocale.htm


Rebuild Index Causing Data File Growth Issue I have a database which is 200 GB in size and I have scheduled a Rebuild Index maintenance plan for all databases every Saturday at 2:00 AM.

The job ran for 8 hours and then it failed. I knew while the rebuild operation was in progress, that the database data file size increases.

In my case, the database size got stuck at 655 GB and the rebuild job failed. I have verified the database data file free space which is currently showing 1.5 GB free space.

Can you please help me to reduce the data file size to the previous state? (200+ GB)

I think you mean that your TRANSACTION LOG is growing (not your DATA files)? Especially when it's a REBUILD of your indexes it would seem strange that only your DATA files are growing.

You should perform a log backup of your TRANSACTION LOG (when in full recovery mode) and then you can do a shrink files of your LOG files to a desired size (with a minimal size taken into consideration; but don't make it too small, or it will start growing again. Key is to determine a good size of your LOG and then leave it that way).

Do a right-click on your database > Reports > Disk Usage.
There you can see how the total size is divided between DATA and LOG.

You should perform scheduled LOG backups (every 15 mins e.g.) to avoid this large growth of LOG files. After every LOG backup, the transaction log frees up space for the next transactions.

I would recommend Ola Hallengren backup maintenance solution for FULL, DIFF and LOG backups as it also cleans up old files)


How can I create a recursive query over a column that has gaps? I've adapted the recursive query from here in order to calculate ELO ratings (like in chess) from a list of game outcomes on-the-fly.

Given a table of games like:

id | home_player | away_player | home_score | away_score |
|--|-------------|-------------|------------|------------|
1  | 1           | 2           | 21         | 18         |
2  | 2           | 3           | 14         | 21         |
3  | 1           | 3           | 12         | 21         |
5  | 3           | 2           | 21         | 8          |

I want to construct a table of ELO ratings for each player after each game has been played, like this:

| current_game_number | player_id | previous_elo | new_elo |
|---------------------|-----------|--------------|---------|
| 0                   | 1         | 1000         | 1000    |
| 0                   | 2         | 1000         | 1000    |
| 0                   | 3         | 1000         | 1000    |
| 1                   | 3         | 1000         | 1000    |
| 1                   | 2         | 1000         | 984     |
| 1                   | 1         | 1000         | 1016    |
| 2                   | 1         | 1016         | 1016    |
| 2                   | 2         | 984          | 969     |
| 2                   | 3         | 1000         | 1015    |
| 3                   | 3         | 1015         | 1031    |
| 3                   | 2         | 969          | 969     |
| 3                   | 1         | 1016         | 1000    |

I can get most of the way there with this recursive query:

WITH RECURSIVE p(current_game_number) AS (
  SELECT
    0               AS id,
    id as player_id,
    1000.0 :: FLOAT AS previous_elo,
    1000.0 :: FLOAT AS new_elo
  FROM players
  UNION ALL
  (
    WITH previous_elos AS (
        SELECT *
        FROM p
    )
    SELECT
      games.id,
      player_id,
      previous_elos.new_elo AS previous_elo,
      round(CASE WHEN player_id NOT IN (home_player, away_player)
        THEN previous_elos.new_elo
            WHEN player_id = home_player
              THEN previous_elos.new_elo + 32.0 * ((CASE WHEN home_score > away_score THEN 1 ELSE 0 END) - (r1 / (r1 + r2)))
            ELSE previous_elos.new_elo + 32.0 * ((CASE WHEN away_score > home_score THEN 1 ELSE 0 END) - (r2 / (r1 + r2))) END)
    FROM games
      JOIN previous_elos
        ON current_game_number = games.id - 1
      JOIN LATERAL (
           SELECT
             pow(10.0, (SELECT new_elo
                        FROM previous_elos
                        WHERE current_game_number = games.id - 1 AND player_id = home_player) / 400.0) AS r1,
             pow(10.0, (SELECT new_elo
                        FROM previous_elos
                        WHERE current_game_number = games.id - 1 AND player_id = away_player) / 400.0) AS r2
           ) r
        ON TRUE
  )
) SELECT * FROM p;

However, If there is a gap in the game ID sequence, as in the example table above, the query stops at the gap and doesn't continue to calculate ELOs. I would like to support gaps so that games can be deleted and concurrent inserts are supported.

I'm lost as to how to extend the query to "skip gaps" in the id sequence. My hunch is it would require changing the recursive join to skip the gaps:

FROM games
  JOIN previous_elos
    ON current_game_number = games.id - 1

Does anyone have a suggestion to fix this? Any help is very appreciated!

Please see this SQLFiddle which is populated with the schema and sample data.

To avoid gaps use additional CTE:

WITH re_enumerated_games AS
( SELECT ROW_NUMBER() OVER (ORDER BY id) id,
         id real_game_id,
         home_player,
         away_player,
         home_score,
         away_score
   FROM games ),


Move Large number of rows I am trying to move close 88101840 rows from one table to another. To handle log growth on the server looking at doing 10000 rows at time. Please have a look at the query below. Just want to find if this way is good or would there be a better way to do it.

DECLARE @Archive_Rows INT;

SET @Archive_Rows = 1;

WHILE (@Archive_Rows > 0)
  BEGIN
    DELETE TOP (10000) Audit
    OUTPUT deleted.*
    INTO AuditArchive
    WHERE EVENTYEAR = '2016';

    SET @Archive_Rows = @@ROWCOUNT;
  END

Method 1,

DECLARE @TopSize INT = 10000
DECLARE @BatchSize INT = 10000
DECLARE @MaxLimit INT = 1
DECLARE @RowCount INT = 0

BEGIN TRY
    WHILE (@TopSize <= @MaxLimit)
    BEGIN
        DELETE TOP ((@TopSize) Audit
    OUTPUT deleted.*
    INTO AuditArchive
    WHERE EVENTYEAR = '2016';

        SET @RowCount = @@RowCount

        --PRINT @TopSize
        IF (
                @RowCount = 0
                OR @RowCount IS NULL
                )
            BREAK;
        ELSE
            SET @TopSize = @TopSize + @BatchSize
    END
END TRY

BEGIN CATCH
    --catch error
END CATCH

where EVENTYEAR  is index column


Oracle: dbms_repair outputs zero errors about known corrupted object In an Oracle 9i instance we have a datafile with corrupted blocks as evidenced by the log generated by the exp utility:

. . exporting table                  IMPORTANT_TABLE
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 120, block # 249555)
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-01110: data file 120: '/ora/data/important_datafile.dbs'

We tried to move all good rows to another table by means of ifentifiying the corrupted row rowid using dbms_rowid.rowid_create(1,a,b,c,0) where a is the DATA_OBJECT_ID of the table, b is the datafile number and c is the block number. Then trying to copy all rows but the damaged one to another table everything fails because most probably there are other corrupted blocks and there are other corrupted rows.

exp doesn't write to the log a list of all corrupted blocks, it just stops exporting that table an continues with the next one.

We tried to use dbv to identify all corrupted blocks with the idea of further filtering out bad rows and be able to copy the good ones, but the output of dbv doesn't include datafile number, and what seems to be a block number doesn't make sense since 249555 is nowhere to be found. Also trying to use dbv to connect to the database providing a SEGMENT_ID for it to report all corrupted blocks seems buggy since it insists in asking for the dmp file instead, even when the documentation states they are mutually exclusive.

Out last attempt was to use dbms_repair.check_object(...) and then dbms_repair.fix_corrupt_blocks(...). We followed this procedure by Burleson Consulting, we created the repair table and the orphans table .

But dbms_repair.check_object(...) yields zero corrupted errors, even when it's demonstrated that there are corrupted blocks in the table.

This is how we ran it:

set serveroutput on
declare corr_count binary_integer;
begin
corr_count := 0;
dbms_repair.CHECK_OBJECT('IMPORTANT_OWNER','IMPORTANT_TABLE',null,dbms_repair.table_object,'MY_REPAIR_TABLE',null,null,null,null,corr_count);
dbms_output.put_line(to_char(corr_count));
end;
/

Obviuosly running dbms_repair.fix_corrupt_blocks(...) does nothing because the package thinks there are no corrupted blocks to begin with.

The datafile is corrupted in all backups and all exports, because of a disk array failure than happened sometime ago.

I know Oracle 9i is end-of-life but the client uses it and I have to support it.

The question is, having attempted all the above:

What's left to attempt?
What are we doing wrong?

What's left to attempt?

You can just instruct the database to skip corrupt blocks on a table scan:

begin
dbms_repair.skip_corrupt_blocks(
  'USER',
  'TABLE_NAME',
  dbms_repair.table_object,
  dbms_repair.SKIP_FLAG);
end;
/

Then scan and move the whole table (except corrupt parts):

alter table user.table_name move;

Finally disable skipping corrupt blocks:

begin
dbms_repair.skip_corrupt_blocks(
  'USER',
  'TABLE_NAME',
  dbms_repair.table_object,
  dbms_repair.NOSKIP_FLAG);
end;
/

Alternatively, you can set event 10231 and run the export:

alter system set events '10231 trace name context forever, level 10';

Then run your export. Finally, you can disable the event for future sessions by:

alter system set events '10231 trace name context forever, off'

and

dbms_system.set_ev for existing sessions. Or just simply restart the instance.


MysqlDump with ignore errors I'm helping a friend to backup his database, but I can't get it right doing standard mysqldump process, when I try to use mysqlcheck, I sort the result: http://pastie.org/9748430

'___') It's seriously scary..

Ok, now the question, how to make mysqldump ignore the error and continue backup till it's complete?

last error:

mysqldump: Couldn't execute 'show create table `xxx`': Table '.\mydb1\xxx' is marked as crashed and should be repaired (145)

Found it, using this flag:

--skip-lock-tables -f

The schema still can't be exported correctly, except by using phpmyadmin


How does Oracle know who I am? Slightly unimportant but it's piqued my curiousity.

I've just logged into an Oracle 10g database for the first time using the Oracle SQL Developer Tools.

I used a generic user login which isn't my name (nottstest2), from a machine that is called something that isn't my name (courgette). My name is nowhere in the database and isn't associated with the login.

Yet, the server logs show a connection from "Jon Hopkins".

How does it know who I am? Is it being pulled from my Windows login in some way (though I'm not using single sign in)?

How do you log onto courgette?  Would that username identify you?

You can check that by running

select sys_context('userenv', 'os_user')
from dual;

The USERENV namespace can retrieve a lot of different information about the user and their environment.  Find out more.


Issue With EXPDP Full Tablespace I am trying to export schema with below command using expdp in Oracle 18c XE , the schema uses tablespace  named "testtablespace"
expdp myschema/*******@//localhost:1521/XEPDB1 schemas=myschema directory=DATA_PUMP_DIR dumpfile=myschema.dmp logfile=myschema.log

the tablespace "testtablespace" has maximum size of 100MB and currently 70MB of it is utilized , upon running above command I get error
ORA-01654: unable to extend index TESTTABLESPACE.SYS_MTABLE_0000357BA_IND_2 by 8 in tablespace testtablespace

When I increase size of tablespace size the issue is fixed , it seems like during export the tablespace of schema is being used for some temporary data storage
Is there a way that I can specify a temporary tablespace to be used instead of my schema tablespace so that even if my schema tablespace is completely full it can still export schema using expdp without any issue?

Tracking Progress Within a Job

While the data and metadata are being transferred, a master table is
used to track the progress within a job.
...
The master table is created in the schema of the current user
performing the export or import operation. Therefore, that user must
have the CREATE TABLE system privilege and a sufficient tablespace
quota for creation of the master table.

So alter the user performing the export to have another default tablespace. That way the master table will be created in another tablespace.


Database function SELECT with optional argument I need to perform a SELECT in postgres that filters records only when a certain argument value is not null. If the argument is null then the argument is ignored. I am experimenting with a function...
create or replace function my_func(argument INT)
returns table
        (
            field1   VARCHAR(50),
            field2 VARCHAR(2),
            field3 BIGINT
        )
language plpgsql
AS
$$
begin
select d.field1, d.field2, COUNT(*) as record_count
             from my_table d
             group by d.field1, d.field2;

if argument IS NOT NULL
    -- TODO: How to filter the first recordset here?
end;
$$

How do I perform a second, optional SELECT in a function please? Or perhaps there's a slick way to do an option where clause?

select d.field1, d.field2, COUNT(*) as record_count

WHERE d.fieldX = argument OR argument IS NULL

from my_table d
group by d.field1, d.field2;

Be aware that this may not always produce an efficient access plan. If you experience performance issues, you'll likely be better off executing one of the two query variants conditionally, something like
IF argument IS NULL THEN
   SELECT ... -- without conditions
ELSE
   SELECT ... WHERE fieldX = argument
END IF


Select from multiple rows without duplicate values, with all random data I have a table that gets results from three different sources. Each column represents a source, and each row a result of an outcome. There are over 50k rows for a total of 150k results.

I need to run a report that within these results, I want to remove duplicates leaving the unique values behind, in their respective columns. The majority of the results will all be duplicates, and I would assume around ~500 are unique.

The other 'remove duplicate from multiple columns' posts haven't worked for me; any combo of distinct, groups, and unions I have not been able to get to work.

Example of data below. Thanks.

Raw Data:

Expected Results:

Squiggles:

I broke this down using pivot and not exists. I really would handle this in the presentation layer though.

--load test data
declare @table table (c1 int, c2 int, c3 int)
insert into @table
values
(1,1,1)
,(1,1,1)
,(2,3,2)
,(4,2,4)
,(5,4,6)
,(7,5,8)
,(9,7,11)
,(11,9,13)
,(14,16,15)

--get our unique values in a cte to pivot later
;with cte as(
select
    --here we add a RN so that we can use pivot without losing values
    r = row_number() over (partition by Col order by (select 1))
    ,i.*
from
    (
    --for each column, we get the unique values where they don't exist in the other two columns
    --we union them together, but give them 1 /2 / 3 column identifier
    select
        1 as Col, c1.c1 as val
    from
        (select distinct t1.c1 from @table t1
         where  not exists (select 1 from @table t2 where t2.c2 = t1.c1)
            and not exists (select 1 from @table t3 where t3.c3 = t1.c1)) c1
    union
    select
        2 as col, c2.c2
    from
        (select distinct t1.c2 from @table t1
         where  not exists (select 1 from @table t2 where t2.c1 = t1.c2)
            and not exists (select 1 from @table t3 where t3.c3 = t1.c2)) c2
    union
    select
        3 as col, c3.c3
    from
        (select distinct t1.c3 from @table t1
         where  not exists (select 1 from @table t2 where t2.c1 = t1.c3)
            and not exists (select 1 from @table t3 where t3.c2 = t1.c3)) c3
    ) i
)

--simple pivot
select
    [1], [2], [3]
from cte
pivot
(max(Val) for Col in ([1],[2],[3]))
p

RETURNS

+------+------+----+
|  1   |  2   | 3  |
+------+------+----+
| 14   | 3    |  6 |
| NULL | 16   |  8 |
| NULL | NULL | 13 |
| NULL | NULL | 15 |
+------+------+----+


can I exclude or delete .pem files in backup data myqdl? I am making a backup of the mysql data folder. In this folder are the databases. The method I am using is described in this post which uses ROBOCOPY

During installation, mysql (tested in 5.7.30) generates the following .pem files by default (at least in the wampserver version. I'm not sure if in the other versions):

private_key.pem
public_key.pem
server-cert.pem
server-key.pem
ca-key.pem
client-cert.pem
client-key.pem

the question is: What happens if I don't backup these files or delete them?

tests performed:
I have stopped the mysql service and I have removed all the .pem files and when I start mysql again only these .pem files appear

private_key.pem
public_key.pem

What about the rest?

thanks

They are needed  for the mysql to generate nbew ssl keys

So to answer your question

  You have to decide if you want ssl or not, so if you use ssl and have user created some  client certificates with it you need it, if not you don't need them.

When you active ssl in my.ini

like

ssl=1
ssl-ca="\sslfolder\ca-cert.pem"
ssl-cert="\sslfolder\server-cert.pem"
ssl-key="\sslfolder\server-key.pem"

you can  allow a user only to access via ssl

First you generate a new ssl certificate for a user exampleuser

and add like this the use of ssl

CREATE USER 'exampleuser'@'localhost' IDENTIFIED BY 'secretsparrworf' REQUIRE X509;
GRANT ALL PRIVILEGES ON *.* TO 'exampleuser'@'localhost';

fionaööy you can access mysql via ssl like this

mysql -u exampleuser-p --ssl-ca=ca-cert.pem --ssl-cert=client-cert.pem --ssl-key=client-key.pem


Can I copy a collection in mongoDB from one database to another by copying the db files directly? I tried using cloneCollection http://docs.mongodb.org/manual/reference/command/cloneCollection/ , but clonecollection takes a lot of time in case of big collection because it starts indexing the data.

My use case :

Machine 1 : only one collection with huge data (lets say collection x) Machine 2 : more than one collection. (lets say collection y,z)

I need to copy collection x from machine 1 to machine 2 , so that machine 2 has all the collections (x,y,z) . It should also have all the indexes intact.

You can copy an entire database across in this manner, by copying the files to a new instance, but not collections.  You also cannot simply copy the database files from database foo and alter the filenames at the filesystem level (to bar.0, bar.1 etc.) to avoid namespace collisions, the name must remain the same.

If you wish to move a single collection (or any subset of collections) rather than a whole database then you will have to use the command you mentioned or use mongodump/mongorestore to do the migration.  However, any per-collection method will require rebuilding the indexes from scratch - you only get them in place if you can copy the data files (i.e. move the entire database).

If the other collections are relatively small, you could look at copying them elsewhere,  out of the current database, leaving just the large collection you wish to move.  You can then copy the entire database, and move the database files as mentioned.


Oracle - MUST_RENAME_THIS_LOGFILE in logfile name I'm creating a data guard using hot backup from the primary server.
In my restore I've used the following commands to restore:

run{
set newname for database to '+DADOS';
restore database;
switch datafile all;
switch tempfile all;
recover database;
}

After successful restore and recovery my redo logfile names has the name below:

select member from v$logfile;

+DADOS/MUST_RENAME_THIS_LOGFILE_1.4294967295.4294967295
+FRA/MOOTDB/ONLINELOG/group_1.287.1040583755
+DADOS/MUST_RENAME_THIS_LOGFILE_2.4294967295.4294967295
+DADOS/MUST_RENAME_THIS_LOGFILE_3.4294967295.4294967295
+FRA/MOOTDB/ONLINELOG/group_3.259.1036639239
+DADOS/MUST_RENAME_THIS_LOGFILE_4.4294967295.4294967295
+FRA/MOOTDB/ONLINELOG/group_2.288.1040583761
+DADOS/MUST_RENAME_THIS_LOGFILE_5.4294967295.4294967295
+FRA/MOOTDB/ONLINELOG/group_5.295.1042634101
+DADOS/MUST_RENAME_THIS_LOGFILE_6.4294967295.4294967295
+FRA/MOOTDB/ONLINELOG/group_6.297.1042634113

Why they have the "MUST_RENAME_THIS_LOGFILE_"? isn't automatic?

In my standby I'm using the db_log_file_name_convert and log_file_name_convert

log_file_name_convert = +DATA, +DADOS
db_file_name_convert = +DATA, +DADOS

Other issue is that some tempfiles come just with the DG name (see last line below).

select name from v$datafile union all select name from v$tempfile;

+DADOS/MOOTSTB/DATAFILE/system.266.1042731967
+DADOS/MOOTSTB/DATAFILE/sysaux.292.1042731967
+DADOS/MOOTSTB/A249914E10CA3494E0532900A8C0EAA4/DATAFILE/system.278.1042732081
+DADOS/MOOTSTB/A249914E10CA3494E0532900A8C0EAA4/DATAFILE/sysaux.296.1042732081
+DADOS/MOOTSTB/DATAFILE/users.309.1042731967
+DADOS/MOOTSTB/A249914E10CA3494E0532900A8C0EAA4/DATAFILE/undotbs1.320.1042732081
+DADOS/MOOTSTB/DATAFILE/undotbs2.283.1042731967
+DADOS/MOOTSTB/A249E748BB047773E0532900A8C02088/DATAFILE/system.257.1042731811
+DADOS/MOOTSTB/A249E748BB047773E0532900A8C02088/DATAFILE/sysaux.284.1042731809
+DADOS/MOOTSTB/A249E748BB047773E0532900A8C02088/DATAFILE/undotbs1.289.1042731811
+DADOS/MOOTSTB/A249E748BB047773E0532900A8C02088/DATAFILE/users.267.1042731811
+DADOS/MOOTSTB/A249E748BB047773E0532900A8C02088/DATAFILE/soe.311.1042731809
+DADOS/MOOTSTB/A249E748BB047773E0532900A8C02088/DATAFILE/teste.308.1042731811
+DADOS/MOOTSTB/A249E748BB047773E0532900A8C02088/DATAFILE/undo_2.304.1042731811
+DADOS/MOOTSTB/DATAFILE/undotbs1.302.1042731967
+DADOS/MOOTSTB/TEMPFILE/temp.319.1042736311
+DADOS/MOOTSTB/A249914E10CA3494E0532900A8C0EAA4/TEMPFILE/temp.318.1042736323
+DADOS  <<<<<< is a tempfile

Restore + recovery from backup does not use online redo logs. Your redo logs were just named something, but those files do not exist yet. A physical standby database also does not use online redo logs (it uses standby redo logs).

The redo log groups will be automatically cleared (rename according to OMF + create the file) whenever they are needed the first time. Like when you actually open the database in READ WRITE mode after converting the physical standby into a primary database or snapshot standby or logical standby.

+FRA/MOOTDB/ONLINELOG/group_1.287.1040583755 is also incorrect. At this point that file does not exist and the location of that file, once cleared, will be +FRA/MOOTSTB/ONLINELOG/... as the db_unique_name on the standby site is MOOTSTB. (Assuming you have an FRA diskgroup there.)

With the db_create_file_dest, db_create_online_log_dest_N, db_recovery_file_dest parameters set properly, you can simply clear the logfile groups manually (what would be done automatically anyway when required), so they will be named and placed correctly:

alter database clear logfile group 1;
alter database clear logfile group 2;
alter database clear logfile group 3;
alter database clear logfile group 4;
alter database clear logfile group 5;
alter database clear logfile group 6;

The tempfile named +DADOS is also not a problem. A physical standby in mount stage does not require a tempfile. The tempfile will be automatically renamed and created once the database is opened.


SQL Server backup practice with limited disk space I have a SQL Server 2012 Std Edition installed, contain 1 database in full recovery mode, so the log grown up quickly, but I just need a PIT recovery in past 7 days.

Can I schedule a full database backup every day (rolling 7 days) and truncate the t-log right after the backup job ? or what is the best way to keep the file size as small as possible ?

If you need to be able to restore to any point in time then you need to be in full recovery mode (which you are). When in full recovery mode, you need to do transaction log backups in addition to your full backups.

Doing a Full Database backup does not make any difference to your log file, only a transaction log backup will clear it out and allow you to reuse the space.

When you're in full recovery mode NEVER truncate your transaction log - this will break the backup chain and you won't be able to do a point in time restore.

Transaction Log backups can run as frequently as you want or as often as you need to keep the file from growing bigger than your disk space or are required to by the business. I've seen some companies run them daily and some run them every minute and a lot in between. The advantage of running them more frequently is that if your disks die then you only lose data since your last log backup (assuming they're on different drives/hardware). It also keeps your log backup files smaller and the backup operation is usually faster because it has less to backup.

You can schedule a job to clean up old log backup files that you no longer need (e.g. in your case more than 7 days old).

If you need to restore from backups then you need to do the following:

Restore last Full Backup
Restore last differential backup (if any)
Restore all log backups up to the point you want to restore to

Can I strongly suggest using Ola Hallengren's scripts if you're not using a 3rd party tool to do backups (https://ola.hallengren.com/)


How to recover an overwritten SQL Server database We have SQL Server Standard Edition with several databases. We backup via a Windows Task that executes the sp_BackupDatabases procedure.

We have a case where a database got replaced by a backup from 2 days ago by mistake.

Unfortunately the connection to the NAS crashed, so the last 4 days, there where no backups made.

Since a backup was restored over the current database, there is no transaction log we could use to manually undo the changes.

Does anyone have an idea how I can get a version of the database before the database was restored? For example, does sp_BackupDatabases  make a local temp backup and moves it then, so that this temp backup could be still available?

As far as SQL native backups are concerned, you can only restore to the last good backup that you have which is 4 days ago.  So, unfortunately, from a SQL server backup perspective you may have lost 4 days worth of data.

That being said, if this is a VM (HyperV, VMWare, etc) and there are snapshots, you may be in luck and your systems guys may be able to get the information back.


Mysql InnoDB Full-Text Search IN BOOLEAN MODE Sort Rows In Order of Decreasing Relevance In the official documentation of Boolean Full-Text Searches:

Boolean full-text searches have these characteristics:

They do not automatically sort rows in order of decreasing relevance.

However, when running full-text searches IN BOOLEAN MODE (mysql v8.0.23), they are sorted by relevance in order of decreasing relevance without having to use ORDER BY score
SELECT product_category_id,
MATCH(product.barcode, product.brand_name, product.generic_name) AGAINST('paracetamol*' IN BOOLEAN MODE) as score
FROM product
WHERE
MATCH(product.barcode, product.brand_name, product.generic_name) AGAINST('paracetamol*' IN BOOLEAN MODE);

Result:
+---------------------+--------------------+
| product_category_id | score              |
+---------------------+--------------------+
|                  97 | 3.4546611309051514 |
|                  91 | 3.4546611309051514 |
|                  91 | 3.4546611309051514 |
|                  91 | 3.4546611309051514 |
|                 123 | 3.4546611309051514 |
|                 123 | 3.4546611309051514 |
|                  91 | 3.4546611309051514 |
|                  91 | 1.7273305654525757 |
|                  97 | 1.7273305654525757 |
|                  97 | 1.7273305654525757 |

What have I missed here?

(A guess)
There are many queries where the results are in a predictable order even without an ORDER BY.  However, this is often an artifact of the implementation and not a guaranteed ordering.
It used to be that GROUP BY automatically ordered the results as if there were a matching ORDER BY.  However, the existence of that as a guarantee hobbled the Optimizer.  With an appropriate INDEX, such a group by will continue to be in order.  But for other cases, the Optimizer would rather use an in-memory hash table; this essentially guarantees a somewhat random order; furthermore, it makes getting the order be an extra effort.
So, I guess, the manual is leaving the door open for some future optimization.
Rule:  If you want a particular order, specify ORDER BY.  If the Optimizer sees that the code can (or could) guarantee the order without doing any extra effort, it will do so.
PS, in the past, you could nullify the GROUP BY's implicit ordering by saying ORDER BY NULL.  (Kludgy?)


Install last CU for SP2 without installing SP1 in sql server A client asked me to update their SQL Server.

It's an 2016 base release (13.00.1601.00)

Microsoft already released the SP2 CU10. I know that cumulative updates are... like... cumulative, but even between service packs?

I can jump and install directly SP2 CU10 or do I need to install the last CU for SP1 before?

You can just install SP2 and then CU10. No need to install CU for SP1.

You can have a list of all SP + CU here

More relevant info :

If you have a large estate that needs to be patched, you can use dbatools - Update-DbaInstance

Also, dbatools has a really nice - build reference page  that will tell you what the latest SP/CU is and End of support dates as well.


AWE to allocate memory in SQL Server 2008 Express with Advanced Services I am running SQL Server Express Edition with Advanced Services on Windows Server 2008 Enterprise 32 bit OS.

I have enabled "Use AWE to allocate memory" on the SQL Server, but I am not sure if this will help at all as in the Microsoft Documentation it says that AWE is not supported for SQL Server 2008 Express.

Why then there is check box for a option that supposedly is not supported?

I would go with the Standard edition of SQL Server if I know for sure that AWE is not supported in SQL Server Express.

There is no point in checking AWE on express edition when SQL Server can see 'just' more than 1G memory. Although it says SQl server can take maximum up till 1G on express edition it takes bit more than that.

AWE is a windows feature in 32 bit which allow SQL Server to use memory greater than its VAS limit. VAS limit for SQL server 32 bit running on 32 bit windows machine is 2G and on WOW system is 4 G. Using AWE SQL Server data and index pages can use memory beyond its VAS limit of 2 G. But since in your case max server memory is 1G there is no point in enabling it. And this is the reason I guess why documentation says so.

As to why it is there because MS does not wants to make change. AWE on 64 bit server has also no meaning but this option is there as well.

  I would go with standard edition of SQL if I know for sure that AWE is not supported in SQL Express?

Go with standard edition 64 bit and yes you would also need 64 bit OS for that. 32 bit server these days have lots of performance restriction especially in terms of memory and VAS. 64 bit will have 8 TB VAS and enough memory to support. 32 bit server is kind of outdated now ( unless you have grave requirement)


Avoid Vacuum (to prevent wraparound) on read-only databases We use a Postgresql 10 database server to import nearly 1 million user files every day.
To keep the databases easier to handle (backup, maintenance) we create a new database every week.
The database from the previous weeks are vacuumed, backuped and no further write operations are performed on them.
In total we have 17 databases with > 7 million imported  files (500GB) in each.
That are 120 million files which leads to 240 million transactions in total.
(To import one file we need two transactions.)

Some days ago, we detected that Postgresql starts the autovacuum process with the ‘prevent wraparound’ option on the oldest databases.
Postgresql starts only those vacuums and not the occasional vacuums (clean-up and maintenance tasks) to analyze the current week's database.
Due to the additional IO load, we have performance problems with select statements on the current database.

Is there a way to prevent this behavior on the old databases?
We tried a vacuum freeze to reset the relevant counters but they start growing immediately when our import is running.

We fetch these counters with the following query:

SELECT
       oid::regclass::text AS table, --relfrozenxid,
       age(relfrozenxid) AS xid_age,
       mxid_age(relminmxid) AS mxid_age,
       least(
           (SELECT setting::int
            FROM    pg_settings
            WHERE   name = 'autovacuum_freeze_max_age') - age(relfrozenxid),
           (SELECT setting::int
            FROM    pg_settings
            WHERE   name = 'autovacuum_multixact_freeze_max_age') - mxid_age(relminmxid)
        ) AS tx_before_wraparound_vacuum,
pg_size_pretty(pg_total_relation_size(oid)) AS size,
pg_stat_get_last_autovacuum_time(oid) AS last_autovacuum
FROM    pg_class
WHERE   relfrozenxid != 0
AND oid > 16384
ORDER BY tx_before_wraparound_vacuum;

Edit

After further investigation, we have read about visibility maps in the 10.x documentation.
A freeze run does not touch the XID but set a flag in this map for indication.
So we hope that any old database, where the wraparound or freeze vacuum was running, is never touched again by any autovacuum in the future.

from your description , you actually need this process or else your old data will effectively become future data that you can’t access (wraparound)

You have 2 options here:

Run the vacuum yourself in non-peak hours to better control load
Reduce the number of transactions
(E.g. if you load data hourly you’ll have 24 transactions, each one loading just 41k persons )


Business Periods/Weeks to date conversion Our current date system divides the business year into 'Periods' which last for 4 weeks. Period one begins on a fixed date, lets say 1st March.

I have a simple table with Weeks and which period they come under. I would like to add another column [Date] with each day in it, effectively multiplying my number of rows by 7. How can I go about calculating these values using an inbuilt calender in SSMS?

There isn't a built-in date table for SQL Server, but it's easy to add or expand your own. If you want to add weeks to your existing table, the code might look like this:

UPDATE tblDates
SET WeekOfYear = DATEPART(WEEK, [date])

If you're just starting out and only have dates so far, it may be easier to find someone else's table script and use that. That way, you don't have to write the code to populate all those other columns.

A good example of a simple table using calendar years is here: https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server/

This is important: Weeks are goofy -- you'll notice in the script linked above that there's a [week] and there's an [ISO_week] in the DATEPART() functions. Note that the [week] column won't always have seven days (e.g, the first week of January 2013 is only five days long -- Tuesday through Saturday). This is what you'll find with the DATEPART(WEEK, [date]) function. If you need each week in your 4-week period to be exactly 28 days, it's easier to use the ISO_week than to string weeks together in code.


Mysql - get sum() result in another column I get user_point column's sum like

select sum(user_point) as user_total_point from users;

but I also want to use that sum result for new generated column like

select sum(user_point) as user_total_point, (user_total_point * 100) as hundred_times from users;

-

Error: Query 1 ERROR: Unknown column 'user_total_point' in 'field list'

How can i use user_total_point column's value for another colums?

If you want both values in the result, you can nest them as in:

select user_total_point, (user_total_point * 100) as hundred_times
from (
    select sum(user_point) as user_total_point
    from users
) as tmp;

Renaming ( as user_total_point ) happens last, so you can not reference that at the same level as you are defining it. The name does not exist yet when you reference it.


Database Design for Employee Information I would like to ask if I'm doing this right since it's my first time creating a database model.

Basically what I'm trying do build is an Employee Information model where:

An employee can have multiple addresses but only one can be tagged as the permanent address
An employee can have multiple positions but only one can be tagged as the current position. Same for the employee department

The reason I choose one-to-many relationships on the position and department is to track its histories

An employee can have multiple dependents.

Here's an example of my initial schema

I would appreciate your feedback if I'm doing it right or if you can help me design it in a much better way.

The most common pattern for handling historical data in an OLTP schema is to have a current table and a separate history table.  So any time an employee changes department, the employee_department table is updated, and a new row is inserted into the employee_department_history table.

You can also use a single table with begin/end dates.  This example of this pattern in SQL Server's older AdventureWorks sample database.  Which tracks both department and position (shift) in a single table:

CREATE TABLE [HumanResources].[EmployeeDepartmentHistory](
    [BusinessEntityID] [int] NOT NULL,
    [DepartmentID] [smallint] NOT NULL,
    [ShiftID] [tinyint] NOT NULL,
    [StartDate] [date] NOT NULL,
    [EndDate] [date] NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID] PRIMARY KEY CLUSTERED
(
    [BusinessEntityID] ASC,
    [StartDate] ASC,
    [DepartmentID] ASC,
    [ShiftID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

SQL Server eventually added a feature to implement the current/history pattern automatically called Temporal Tables, and the newer WideWorldImporters sample database uses those.


Financial database design I am working on an application to store financial data and need to model the tables where the data for several financial statements will be stored. I came up with the following two designs but I can't decide on which one to use. Appreciate your input.

Diagram 1

Diagram 2

Update:

A 3rd diagram based on Daniel's answer below

Diagram 3

IMO, the balance sheet, income statement and cash flow tables can be the same. Here's my take on it:

-- Companies
CREATE TABLE company (
    [id]      int NOT NULL PRIMARY KEY,
    [name]    varchar(255) NOT NULL
);

-- "Balance sheet", "IFRS Income statement", etc
CREATE TABLE statement (
    [id]      int NOT NULL PRIMARY KEY,
    [name]    varchar(255) NOT NULL
);

--- "Tangible assets", "Outstanding stock", etc
CREATE TABLE statementRow (
    [id]      int NOT NULL PRIMARY KEY,
    statementId int NOT NULL,
    rowOrder  int NOT NULL,
    rowTitle  varchar(255) NOT NULL,
    rowDescription varchar(max) NULL,
    rowProperties varchar(max) NULL,
    FOREIGN KEY (statementId) REFERENCES statement ([id])
);

--- The facts
CREATE TABLE statementFact (
    companyId      int NOT NULL,
    statementRowId int NOT NULL,
    [date]         date NOT NULL,
    amount         numeric NULL,
    PRIMARY KEY ([date], statementRow),
    FOREIGN KEY (companyId) REFERENCES company ([id]),
    FOREIGN KEY (statementRowId) REFERENCES statementRow ([id])
);

Advantages of this model:

You can have different types of balance sheets, income statements, etc, in order to cover future reporting needs
The model defines the ordering of the rows for each statement (don't rely in an identity column for that, because you won't be able to insert rows later on)
Using "date" instead of "year" allows you to publish statements more than once per year, i.e. per quarter or month, or even ad-hoc.
The rowProperties field allows you to add information like if the row should be boldface, italics or other formatting properties.
Optionally, you may want to move "companyId" from statementFact to "statement" if certain reports only apply to certain companies.


MongoDB 3.6 - Journal files are not getting cleaned up and they keep building everyday and use up a lot of disk space We have recently upgraded to MongoDB 3.6 and since then we have noticed that the journal files keep building up and are not getting cleaned. Due to this, the disk usage keeps growing until there is no space left on the disk.

This issue does not occur with MongoDB 3.4. Is there anyway to enforce cleanup of journal files in MongoDB 3.6?

Update on data volumes:
We process around 148 GB of data every day. The disk space that the MongoDB data files use is around 60 GB. However, the journal files use up additional 70 GB of space everyday. Since the journal files are not getting cleaned up, the total disk space used increases by 70 GB everyday.

This is a known issue recently discovered in the MongoDB 3.6.3 release when using WiredTiger on Windows: SERVER-33982: Unbounded growth of pre-allocated log files.

This issue will be corrected in the 3.6.4 production release, so upgrading to MongoDB 3.6.4 (or newer) when available should resolve the issue.

In the interim, possible workarounds are:

Disable WiredTiger preallocation in 3.6.3

In your MongoDB config file:

storage:
  wiredTiger:
    engineConfig:
      configString: log=(prealloc=false)

Or, via command-line parameter:

--wiredTigerEngineConfigString=log=(prealloc=false)

Downgrade to MongoDB 3.6.2
Restart your mongod to free up the preallocated space


InnoDB: Getting rid of a phantom table I was playing around on a test server and i actually removed both "frm" and "innodb" files of particular table. Ok - I tried creating that table again but got information that table already exists and that i should discard it's table space. I wasn't able to discard table space because there were no underlying files, i've got error that table doesn't exist. Creating new table (or simply doing copy from other server) won't work too.

In despair i tried removing any mention of that table in "information_schema", it seemed obvious - removing any trace that table ever existed but "information_schema" is read only :(

What should i do to actually fix this issue?

The table still exists in the InnoDB dictionary. It's similar to how MySQL left temporary tables after a failed ALTER.

I found that the easiest way to remove an entry from the dictionary tables is to drop whole database. Obviously you want to keep the data, so moving it to a temporary database will work. Anyways, the steps are:

Create an empty temporary database. Let it be tmp1234.
Move all tables from the original database to tmp1234.
Drop the original database (it’s empty by now, all tables are in tmp1234).
Create the original database again.
Move all tables from the temporary database to the original one.
Drop the empty temporary database.

Check out https://twindb.com/resolving-error-1050-42s01-at-line-1-table-already-exists/ for some background.


What units is Page Life Expectancy in? I'm currently using the following query to get the Page Life Expectancy from SQL Server:

declare @PLE int

select @PLE=cntr_value
from psql1.master.sys.dm_os_performance_counters
where counter_name='Page life expectancy'
and instance_name='';

The issue I'm having is I don't know if this is in milliseconds, seconds, or what.  The value I am currently getting is 88936.  This could be in seconds or milliseconds.

It would make more sense if it was in miliseconds, but this server isn't used that much currently so something staying in memory for 24 hours (88936/3600=24.7) isn't impossible.

I believe that it's in seconds.  I've got a script running a loop that's capturing the value once every minute and it's going up steadily by ~60 every 1 minute interval.


SSDT - Deprecated table trigger preventing publish There is a table trigger that was removed in the past that is blocking SSDT publish preview:

This deployment may encounter errors during execution because changes to [table] are blocked by [trigger]'s dependency in the target database.

but in order to remove it from the database it must either be manually dropped (since the publish preview is failing so not even predeploy scripts get run), or the option "drop objects in target not in source" must be enabled.

([trigger] is on a different table than [table]. The table containing [trigger] is having a column removed and [table] is having a column added. Neither column is even referenced by [trigger]!)

We cannot enable this advanced publish setting in general because of other migration scripts that exist in the postdeploy section that rely on deprecated objects, and we cannot have our integration team use this option either because it risks data loss on PROD. But we like to use this option periodically to (manually) test for leftover references to objects that were removed, because the build does not throw errors about them due to deferred name resolution (which presumably exists to enable what I consider a pungent code smell to create non-temporary objects at runtime), as the error can go unnoticed otherwise until a newer system that never had the object introduced is finally tested.

How is this situation fully resolved without pre-model scripting?

There is a setting on the "drop" tab of the advanced publish settings called "Drop DML Triggers not in source."  This is checked by default, and thus triggers should be dropped by the publish process.  This is true even if "drop object in target but not in source" is unchecked.

It sounds like this has been unchecked in your publish process.  You can confirm that by looking for this element in your publish XML file (if that's how you're publishing):

<DropDmlTriggersNotInSource>False</DropDmlTriggersNotInSource>

Removing that element, or setting it to true, should resolve your issue without setting the undesirable "drop object in target but not in source" or having to use a pre-model script.

Regarding the inciting issue:

  There is a table trigger that was removed in the past that is blocking SSDT publish preview (due to a dependency)...

If the setting I mentioned above doesn't resolve the problem, it would be helpful if you could provide a repro steps for this scenario.


How to run pg_dump so that it doesn't slow down other connections? I am running a daily pg_dump on PostgreSQL 9.3.14 database of approx. 50 GB of size:

pg_dump -Fc dbname > file

The problem I am having is that while pg_dump runs, which takes several hours, all the other database connections are extremely slow.

Is there any way to make the pg_dump consume less resources or is there a more proper way of creating periodic backups that doesn't slow down the db server so much?

E.g. would running pg_dump from a different server help the database I/O on its working server?

Little hard to say, because we don't know your database setup.

does your database use schemas with views in it. you can use the -n option to dump only the data schemas and rebuild you views after the restore.

maybe you can do it on a quit moment on your server, with -j you can parallelize the dump. It will take less time, but uses more memory.

Otherwise the slave option is a good practice


Multiplication Using Values From Various Tables I have created three table using with the following code:

CREATE TABLE Invoices (
    invoiceID nvarchar(5) NOT NULL PRIMARY KEY,
    invoiceDate date,
);

CREATE TABLE ContainsItems (
    invoiceID nvarchar(5) REFERENCES Invoices(invoiceID),
    itemID nvarchar(5) REFERENCES Items(itemID),
    itemQuantity numeric(2, 0)
);

CREATE TABLE Items (
    itemID nvarchar(5) NOT NULL PRIMARY KEY,
    itemName nvarchar(20),
    unit nvarchar(20),
    pricePerUnit float
);

How would I be able to query a list of all invoices showing the invoiceID, invoiceDate, and the total cost of all items for each invoice?

Many Thanks.

Edit:

So far I have written something like:

SELECT Invoices.invoiceID, Invoices.invoiceDate, _______________________
FROM Invoices
INNER JOIN ContainsItems ON Invoices.invoiceID = ContainsItems.invoiceID
INNER JOIN Items ON ContainsItems.itemID = Items.itemID;

...but I have no clue how to generate the total of each invoice.

I am using MySQL Workbench.

First, excellent that you provided create table statement. For additional bonus you can provide insert statements and expected output of the query ;-)

Second, some things are missing in your model. What is the primary key of ContainsItems, I guess it should be invoiceID,itemID and for that they need to be declared as NOT NULL. What does it mean if itemQuantity is null? I assume it can't be that (may be incorrect):

CREATE TABLE ContainsItems (
    invoiceID nvarchar(5) NOT NULL REFERENCES Invoices(invoiceID),
    itemID nvarchar(5) NOT NULL REFERENCES Items(itemID),
    itemQuantity numeric(2, 0),

    primary key (invoiceID, itemID)
);

In the same spirit I assume that an Item has a name, unit and PricePerUnit:

CREATE TABLE Items (
    itemID nvarchar(5) NOT NULL PRIMARY KEY,
    itemName nvarchar(20) NOT NULL,
    unit nvarchar(20) NOT NULL,
    pricePerUnit float NOT NULL
);

If my assumptions are wrong you will have to adjust the query to handle this.

What you want is the SUM of the price of items per invoice. I assume this to be:

ContainsItems.itemQuantity * Items.pricePerUnit

Your query then only needs some slight modification. First we need to add information on what to apply the ? later on:

SELECT Invoices.invoiceID, Invoices.invoiceDate, ?
FROM Invoices
JOIN ContainsItems
    ON Invoices.invoiceID = ContainsItems.invoiceID
JOIN Items
    ON ContainsItems.itemID = Items.itemID

GROUP BY Invoices.invoiceID, Invoices.invoiceDate

Now per such group we can sum the ContainsItems.itemQuantity * Items.pricePerUnit

SELECT Invoices.invoiceID, Invoices.invoiceDate

     , SUM( ContainsItems.itemQuantity * Items.pricePerUnit )

FROM Invoices
JOIN ContainsItems
    ON Invoices.invoiceID = ContainsItems.invoiceID
JOIN Items
    ON ContainsItems.itemID = Items.itemID

GROUP BY Invoices.invoiceID, Invoices.invoiceDate


Joining WordPress Comments with Post Titles from MySQL DB I'm working with a wordpress database and need to get some data on the comments.

The goal is to get comments from a decommissioned site.  I have the database running on my local system and can generate a report from the wp_comments table easy enough.  That table has a column named 'comment_post_id' which is a direct correlation to the ID column in the wp_posts table.

I've dumped both from mysql into excel and done a vlookup from the comments table into the posts table to get the desired report.

While the dump->excel process works, I know this could probably be done with a query in mysql.

My first crack at it fails:

SELECT * FROM 'wp_posts' WHERE ('wp_comments.comment_post_id' = 'wp_posts.id');

and doesn't even include a join yet.

Can you provide any pointers or tips on getting a correct query?

Thank you.

SELECT * FROM wp_posts WHERE id IN (SELECT comment_post_id FROM wp_comments);

or

SELECT A.* FROM wp_posts A INNER JOIN
(SELECT comment_post_id FROM wp_comments) B
ON A.id = B.comment_post_id;


Odd SQL Server hang when querying exec DMVs The other day I ran into a very odd issue that when I execute this query:

select * from sys.dm_exec_cached_plans cp
inner join sys.dm_exec_procedure_stats ps
    on ps.plan_handle = cp.plan_handle

It would execute for a long time (10 minutes before I gave up which is much longer than I expected).  At the time I ran it I had recently cleared the procedure cache so there were likely no more than 20-30 cached plans.

When I cancel the query it continues to run and never cancels.

I then tried to use the KILL command on the spid. When I look at the query in sys.dm_exec_requests the last wait type was SOS_SCHEDULER_YEILD and the command is KILL/ROLLBACK.  Once again I waited for a long time (10 minutes or so)

In order to kill the query I tried to stop the SQL Server service through SQL Configuration Manager.  This hung.  The only way I could find to stop the query or the service was by using sysinterals tool pskill.  Not ideal but it a test installation so no big deal.

I tried to search to see if anyone else has seen this but can't find a search criteria that filters out the noise.

Ive seen this on two boxes in my environment, one SQL Server 2008 R2 SP1 and another SQL Server 2008.

I executed this again just now and it returned quickly (milliseconds).

Can anyone shed any light about this?  Am I missing something obvious or is this a bug.

[Edit: adding based on an answer from luvizuvi]
I could find no obvious blocking processes or other bottlenecks related to memory, disk, cpu, other processes, etc.  When looking at sys.dm_exec_requests this was the only command in a running state.

Thanks.

I ran into the problem and found the bug article on MSDN. It has been fixed with cumulative updates for SQL 2008 and 2012. http://support.microsoft.com/kb/2803799


Cassandra - Query a column with collection type I am pretty new to cassandra, so pardon me if this turns out to be a silly question.

I have a table structure as below

CREATE TABLE data_points (
  id text PRIMARY KEY,
  created_at timestamp,
  previous_event_id varchar,
  properties map<text,text>
);

I wanted to know, whether I can execute a query which gives matching records from the map type fields.

For instance, if I insert values in the table as below

INSERT INTO datapoints (id, properties) VALUES ('1', { 'fruit' : 'apple', 'band' : 'Beatles' });

Will I be able to fetch it as

SELECT * from data_points WHERE properties.band='Beatles';

Please Help.

You can index collection types in cassandra 2.1 and later. You are after:
SELECT * FROM <table> WHERE <field> CONTAINS <value_in_list/map/set>

Detailed example:

cqlsh> USE ks;
cqlsh:ks> CREATE TABLE data_points (
            id text PRIMARY KEY,
            created_at timestamp,
            previous_event_id varchar,
            properties map<text,text>
         );
cqlsh:ks> create index on data_points (properties);
cqlsh:ks> INSERT INTO data_points (id, properties) VALUES ('1', { 'fruit' : 'apple', 'band' : 'Beatles' });
cqlsh:ks> INSERT INTO data_points (id, properties) VALUES ('2', { 'fruit' : 'cherry', 'band' : 'Beatles' });
cqlsh:ks> SELECT * FROM data_points WHERE properties CONTAINS 'Beatles';

 id | created_at | previous_event_id | properties
----+------------+-------------------+----------------------------------------
  2 |       null |              null | {'band': 'Beatles', 'fruit': 'cherry'}
  1 |       null |              null |  {'band': 'Beatles', 'fruit': 'apple'}

(2 rows)

Word of warning, secondary indexes don't scale out well as they use a scatter/gather algorithm to find what you need, if you plan to use them for heavy tagging it might be better to denormalize the properties field int a separate table and carry out multiple queries.

Further reading:

Is it possible to use cql to query collections in a row?


Why is Postgres not respecting my log file name convention dictated by "log_filename"? I’m using Postgres 9.5 on Ubuntu 14.04.  I have these settings set in my /etc/postgresql/9.5/main/postgresql.conf file

log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # log file name pattern,
                                        # can include strftime() escapes
#log_file_mode = 0600                   # creation mode for log files,
                                        # begin with 0 to use octal notation
log_truncate_on_rotation = on           # If on, an existing log file with the
                                        # same name as the new log file will be
                                        # truncated rather than appended to.
                                        # But such truncation only occurs on
                                        # time-driven rotation, not on restarts
                                        # or size-driven rotation.  Default is
                                        # off, meaning append to existing files
                                        # in all cases.
#log_rotation_age = 1d                  # Automatic rotation of logfiles will
                                        # happen after that time.  0 disables.
log_rotation_size = 50MB                # Automatic rotation of logfiles will
                                        # happen after that much log output.
                                        # 0 disables.

Although I have restarted my server using the

sudo /etc/init.d/postgresql restart

command, when I run some transaction and check my logs, the log files are not respecting my files names …

myuser@mymachine:~$ ls -al /var/log/postgresql/
total 1912144
drwxrwxr-t  2 root     postgres       4096 Dec 31 14:27 .
drwxrwxr-x 14 root     syslog         4096 Dec 31 06:55 ..
-rw-r-----  1 postgres postgres 1957826560 Dec 31 14:30 postgresql-9.5-main.log
-rw-r-----  1 postgres postgres     194785 Dec 26 06:46 postgresql-9.5-main.log.1

Why is Postgres not respecting the filename convention I specified in the configuration file and how do I get it to?

You need to edit the values for logging_collector and log_directory so that it knows you want to manage the logs and where they should go.

https://www.postgresql.org/docs/9.5/static/runtime-config-logging.html

Look at the section for log_filename.


Order subquery with order by I need order my query for my subquery field.

Attempt:

 SELECT * from
 (
    SELECT *
    FROM (`multicategoria`)
    INNER JOIN `modelo` ON `modelo`.`id_MOD` = `multicategoria`.`id_MOD`
    INNER JOIN `categoria` ON `categoria`.`id_CAT` = `multicategoria`.`id_CAT`
    LEFT JOIN `media` ON `media`.`id_MED` = `modelo`.`id_MED`
    WHERE `categoria`.`id_CAT` =  '154'
    LIMIT 20, 4 )
order by x.id_CAT

But mysql output:

  (1248): Every derived table must have its own alias

What is x?  The missing alias on the subquery?

Get rid of the subquery:

SELECT *
    FROM ...
    JOIN ...  ON ...
    JOIN ...  ON ...
    WHERE ...
    ORDER BY ...   -- Belongs after WHERE and before LIMIT
    LIMIT ...;     -- Useless without ORDER BY

Problem:  Since you are specifying a single value for id_CAT, it does not make sense to ORDER BY id_CAT.  Perhaps you want to ORDER BY id_CAT, something_else?  Or simply  ORDER BY something_else?


Update MySQL slave when changing the hostname/IP of master I have one master and one slave replicating great, but the master is about to have both it's hostname and IP changed. In master.info on the slave server I see the current hostname for the master, if I shutdown the slave server, change the hostname in that file and then restart MySQL will that be all I need? No usernames/passwords etc are changing.

If I used CHANGE MASTER queries instead it looks like it will require re-setting the binlog position etc?

Once you change the MASTER_HOST, everything else needs to change with it.

The "dark back-alley" approach you just suggested is exactly what to do.

Warning: Some systems use "mysqld" versus "mysql".
The --skip-slave-restart "option" may not available without editing your
/etc/init.d/mysqld script to include this as a new option.
If used, it will still start the slave upon startup.</p>

On the Slave, run these four lines:

cd /var/lib/mysql
service mysql stop
cp master.info master.info.bak
wc -l < master.info

The last line echoes the number of lines in master.info

Next, vi master.info and change the IP address.

Next, run wc -l < master.info and make sure the linecount is still the same

Next, start mysql without starting replication

service mysql start --skip-slave-start

Login to MySQL, and run

SHOW SLAVE STATUS\G

If the IP address you added appears, then run

START SLAVE; SELECT SLEEP(5);
SHOW SLAVE STATUS\G

If you see Slave_IO_Running : Yes and Slave_SQL_Running : Yes, Congratulations !!!

Give it a Try !!!


How to set foreign key on two tables in MySQL? I am struggling in resolving a db design for MySQL 5.7 to solve the following scenario:
There are category dimensions that change each day, I store them in category_entities. They belong to a category which relates to a merchant and his products:
merchants
merchant_id | merchant
1           | amazon
2           | ebay

PK: merchant_id

products
prod_id    | merchant_id | product
1          | 1           | jumper big
2          | 1           | hat red
3          | 2           | shoe black
3          | 1           | shoe black2

PK: merchant_id, prod_id

categories
cat_id     | merchant_id | category
1          | 1           | jumpers in green
2          | 1           | hats for woman
3          | 1           | shoes
4          | 2           | jumpers
5          | 2           | hats for children
6          | 2           | shoes

PK: cat_id

category_entities (30M rows)
cat_id | prod_id | characteristica | date
1      | 1       | 23              | 2021-01-07
2      | 2       | 22              | 2021-01-07
1      | 2       | 22              | 2021-01-08
2      | 3       | 01              | 2021-01-08
3      | 1       | 22              | 2021-01-08
4      | 2       | 01              | 2021-01-08

PK: cat_id, prod_id, date

So the category table defines what categories exists and the category_entities talbe shows the value for each day
How can I set a foreign key on the tables (categories, category_entities) on table products using prod_id, merchant_id. Those fields are the primary key of product.
I as reading about polymorphic assosiations but I am not sure this is one and how to solve this.

I'll add a partial answer since it is too much information to stuff in comments. I'm not saying that it is the best solution, but unless you want to remodel, it is a possability. Given the facts you presented you can extend category_entities as:
CREATE TABLE category_entities
( cat_id int not null primary key
, prod_id int not null
, merchant_id int not null
, ...
    constraint ... foreign key (prod_id, merchant_id)
                   references products (prod_id, merchant_id)
, ...
);

Now, you may have inconsistent merchant_id information between categories and category_entities. You can prevent this by adding a unique constraint in categories:
ALTER TABLE categories ADD CONSTRAINT ak1_categories
    UNIQUE (cat_id, merchant_id);

Now you can reference this constraint in category_entities as:
ALTER TABLE category_entities ADD CONSTRAINT fk1_categories
    FOREIGN KEY (cat_id, merchant_id)
    REFERENCES categories (cat_id, merchant_id);

This will guarantee consistency between category and category_entities, but is a bit ugly since ak1_categories is a reducible key.
I've heard rumours that there exists DBMS that allow CHECK constraints with sub-queries, but I never used one. Beside MySQL 5.7 doesn't care about check constraints so that is not an option for you.
Another alternative is to add before triggers for validation of merchant_id. They are however procedural by nature so they do not tell you anything about the current situation. All you know is that no invalid data passed them as long as they were active.
As mentioned this is not a complete answer but presents some ideas that do not fit in a comment.


How to export Oracle database without data I want export my Oracle database with its structure such as tables, schema, users, tablespaces, datafiles and everything but without the contained data.

Judging by the answer you gave in response to @Ozzie, I'm not sure you can do what you want.

You can export the metadata of an 11gR2 database using expdp (Data Pump) and the METADATA_ONLY flag.  See http://docs.oracle.com/cd/E11882_01/server.112/e22490/dp_export.htm

This will export the structure of your data.  This won't include redologs, controlfiles, the supporting files required for the instance.

To recover using this export, you would need to create a new database instance using dbca which has all the logs and supporting structure set up, and then import the metadata using impdp.

If you had a copy of the data files, you could also use transportable tablespaces to move the data to the new instance.

The normal method for backing up the entire database is RMAN.  This will include everything that you need to restore the database.  I don't believe that RMAN provides the option to exclude the data, though.

It may help if you can explain what you are trying to achieve, then better options may be available.


sql server cache clock hands I'm doing some research about how to troubleshoot local memory pressure. Correct me if I'm wrong but as I understand it is that SQL Server uses a CLOCK algorithm for the cache- and userstores. Each clock has two hands, an external and an internal.

My interest is for the internal clock hand. If a worker that accesses a cache notices that a cache is greater than a certain percentage of memory, the internal clock hand starts to free memory for that cache.

Using the dmv dm_os_memory_cache_clock_hands I can see information about the clock hands. The column rounds_count represents how many times the clock hands scanned the entire cache. last_tick_time is the time the hand last moved. round_start_time is time of the last sweep.

What is the difference between last_tick_time and round_start_time? Is the cache not cleaned when the hand is moved?

How many entries get removed with each tick of the clock?

Can somebody helps me understanding the working of this clock algorithm in SQL Server?

Correct me if I'm wrong but as I understand it is that SQL Server uses a CLOCK algorithm for the cache- and userstores. Each clock has two hands, an external and an internal.

Yep, almost like what you have said but let me add something more. Cache and Userstores shares common costing and algorithm called as Least Recently Used(LRU) algorithm. This LRU mechanism has what is called as "clock algorithm". This algorithm has what is called as "hands". There are various eviction policies which work inside SQl Server to remove "old" entries from SQL Server caches and to help this SQL Server has 2 clock hands:

External: Implements Global policy
Internal: Implements local policy

Policy is something which you can consider as rule when dealing with removing old entries from caches.

  If a worker that accesses a cache notices that a cache is greater than a certain percentage of memory, the internal clock hand starts to free memory for that cache.

There is lot more that happens it is not like that internal clock will "only" free memory by removing old entries after total memory utilized is  greater than certain percentage, LRU and internal memory pressure is also taken into the account.

  What is the difference between last_tick_time and round_start_time? Is the cache not cleaned when the hand is moved?

last_tick_time - time when a clock hand moved last.

round_start_time - time when a clock hand started current round. The round which it did to sweep the caches.

  How many entries get removed with each tick of the clock?

There is no predefined value, after LRU algorithm has done its task other thread would see how many entries in cache are "old" and then would remove it. If there is memory pressure the value may increase and the sweeping may happen too often.

  Can somebody helps me understanding the working of this clock algorithm in SQL Server

Slava Oaks from Microsoft has already written a lot about it. I will quote from his blog

  In SQL Server 2005 we introduced new common caching frameworkAll caches except for Buffer Pool leverage it. The framework consist
  of set of stores and ResourceMonitor. There are three types of stores:
  cache store, user store  (the name here is related to internal usage
  of the store - it does not have to do anything with a user)  and
  object store. Both cache and user stores share common LRU mechanism
  and leverage common costing. Procedure cache is an example of a cache
  store and metadata cache is an example of a user store. Object stores
  are just pools of memory blocks and don't require either LRU nor
  costing. Network library leverage object store for pooling network
  buffers.

  Store's LRU mechanism is a rather simple - we use variation of
  thoroughly discussed in operating system's literature clock algorithm.
  As you remember clock algorithm has a hand. When hand moves it
  decreases a cost of an entry. Once cost of an entry reaches 0, the
  entry can be discarded from the cache. Cost gets reset whenever an
  entry is reused. When dealing with multiple caches a designer has to
  take into account global and local eviction policies. Global policies
  consider global picture and enable running of LRU, clock algorithm,
  across all the caches. In its turn, local policies look at the local
  resource consumption only and run LRU for a given cache only.

  In order to satisfy global and local policies SQL Server's stores
  implement two hands: external and internal (please don't confuse a
  clock hand names with types  of memory pressure. There is no
  connection!). External clock hand implements global policy and
  internal clock hand implements local policy. Even though every cache
  has its own external hand, external hands move "synchronously" - one
  at a time. Their task is to simulate global hand.

Additional Reading: The Clock Hands of Buffer cache


Why is our 1000 MB database file not uploading completely via phpMyAdmin? We have an e-commerce Magento site and a large database of 1000 MB for that site.

When we try to import the database through phpMyAdmin, it's not uploading the full databse, a lot of tables will be missing after uploading.

We use a GoDadddy cPanel and phpMyAdmin.

How can we upload the full 1000 MB database completely?

Also, is there any way to replace the old domain name to new domain name in the database?

thanks in advance

Try this

For Export:

mysqldump -u [user] -p [db_name] | gzip > [filename_to_compress.sql.gz]

For Import:

gunzip < [compressed_filename.sql.gz]  | mysql -u [user] -p[password] [databasename]

Note: There is no space between the keyword '-p' and your password.


Deprecating SQL Server, need to redirect clients to new server I am in the process of decommissioning an old SQL Server server and replacing it with a new server.

My old SQL Server is a named instance called: SQL\Development
My new SQL Server is a default instance called Sandbox

Is there a way on the server side to redirect clients from SQL\Development to Sandbox?

There are hundreds of clients and I am looking for the easiest way to accomplish this task.

We are doing exactly this at the moment in our company - decommisioning servers and moving the databases to a new infrastructure. We use a DNS alias to point to the SQL instance - the only downside is that the SQL instance must be configured to use static ports. Once this is done you can connect to a SQL instance using the following address: servername,port - this way you do not need to know the instance name. Once you have configured your application with to connect with the DNS alias moving your databases is easy - just create a copy of the database and change the IP Address that the DNS Alias points to.

There are a couple of downsides to this approach; firstly, configuring SQL Server to use static ports requires a restart of the SQL Server instance; secondly, all servers that are to take part in the moving of databases must be configured to use the same port (otherwise you would need to reconfigure the client each time).

We primarily use this for DR to be fair (although it is making the migration to new infrastructure easier) and the approach is pretty robust. When failover occurs an automated job kicks off to redirect all of the DNS Aliases to point to the new server. There will be a slight window of failure within the application while the new addresses are replicated across the domain controllers but this tends to be fairly small.

I hope this helps you.


The (2nd form of the) crosstab() function expects these columns as input:

1 row_name column
(0-n) extra columns
1 category column
1 value column

See:

Pivot on Multiple Columns using Tablefunc

Your specific difficulty is that you are trying to process 3 value columns at once (param1, param2, param3). Your input table is already "half pivoted". There are various ways to solve this. Joining three crosstab queries is probably cleanest. Demonstrating for 5 weeks:

SELECT *
FROM   crosstab(
     'SELECT id, week, param1
      FROM   tbl
      ORDER  BY 1,2'
   , 'SELECT generate_series(1,5)'
   ) ct1 (id int, w1p1 int, w1p2 int, w1p3 int, w1p4 int, w1p5 int)
JOIN   crosstab(
     'SELECT id, week, param2
      FROM   tbl
      ORDER  BY 1,2'
   , 'SELECT generate_series(1,5)'
   ) ct2 (id int, w2p1 int, w2p2 int, w2p3 int, w2p4 int, w2p5 int) USING (id)
JOIN   crosstab(
     'SELECT id, week, param3
      FROM   tbl
      ORDER  BY 1,2'
   , 'SELECT generate_series(1,5)'
   ) ct3 (id int, w3p1 int, w3p2 int, w3p3 int, w3p4 int, w3p5 int) USING (id)

dbfiddle here

[INNER] JOIN is safe, since all instances are guaranteed to return the same week ids. Else we'd use FULL JOIN.

With over 50 weeks, you get over 150 columns. Is that really what you want?


Calculate balance for every row by sequentially subtracting each row's amount from an initial sum I'll explain the issue with an example.
A query that will select and show the Withdrawal Process I made and show the status of my deposit balance.

A table Deposit is created with columns TotalAmount, DepositDate.
Another table Withdrawal is created with columns WithdrawAmount, WithdrawDate.

So I use a SELECT query to select from both tables with formulae:
SELECT WithdrawAmount,
CASE WHEN ( TotalAmount - WithdrawAmount) = 0 THEN 'ZeroBalanceOops'
ELSE 'StillAvailableYAY' as 'Status'
FROM Deposit Inner Join Withdraw WHERE [WithdrawDate] between this month beginning and ending

So for this query, let's assume I have a deposit of 500 and will only deposit once. It works well if I only withdraw once this month and withdraw the deposit completely. The result will be showing like this:
| WithdrawAmount | Status          |
| 500            | ZeroBalanceOops |

However, it doesn't work if I withdraw more than once in a month and these withdrawals would make the balance = 0. Assuming the deposit is 500 again, the result would show like this :
| WithdrawAmount  | Status            |
| 250             | StillAvailableYAY |
| 250             | StillAvailableYAY |

The expected result is to have 'StillAvailableYAY' at first withdrawal then 'ZeroBalanceOops' at second withdrawal status but as it only compares 500-250 not 500-250-250 thus it will be always the 'StillAvailableYAY' status. Using SUM(WithdrawalAmount) does not give the result I want as it would cause the two withdrawals to have status 'ZeroBalanceOops'.
Any idea on how can I retrieve the previously selected query and include it into calculation?
Or is there any better way to do so?

This is a "running total" type of problem: each row's total is calculated based on that row's value added to, or subtracted from, the previous row's total.

Since you are using SQL Server 2014, Transact-SQL has built-in syntax available to you to help you with getting the results.

Using the simplified model of "One deposit per month, many withdrawals per month", the SQL statement could go like this:

SELECT
  d.TotalAmount,
  w.WithdrawAmount,
  Balance = d.TotalAmount - SUM(w.WithdrawAmount) OVER (ORDER BY w.WithdrawDate ASC)
FROM
  dbo.Deposit AS d,
  dbo.Withdrawal AS w
WHERE
  d.DepositDate  >= start_of_this_month AND d.DepositDate  < start_of_next_month
  AND
  w.WithdrawDate >= start_of_this_month AND w.WithdrawDate < start_of_next_month
;

The WHERE clause is supposed to filter the tables down to one deposit row from Deposit and the however many corresponding withdrawals from Withdrawal. If the tables support multiple accounts and the amounts need to be further related by account, you may want to replace the FROM clause with something like this:

FROM
  dbo.Deposit AS d
  INNER JOIN dbo.Withdrawal AS w ON d.AccountNumber = w.AccountNumber

The SUM(w.WithdrawAmount) OVER (ORDER BY w.WithdrawDate ASC) expression calculates a running WithdrawAmount total for each row. So, the total increases with each row (sorted in the ascending order of WithdrawDate) – thus with each row an ever increasing amount is subtracted from TotalAmount, drawing Balance nearer to 0.

The above query will give you each row's balance but not the status. To get the status, you will need to reference the Balance value to compare it 0 and select a corresponding status string to return. Balance is a calculated column and in order to be able to reference it you need to nest the above query and reference Balance at the outer level. Nesting could be done with either a derived table or a common table expression (CTE). This query uses a CTE:

WITH balances AS
  (
    SELECT
      d.TotalAmount,
      w.WithdrawAmount,
      Balance = d.TotalAmount - SUM(w.WithdrawAmount) OVER (ORDER BY w.WithdrawDate ASC)
    FROM
      dbo.Deposit    AS d,
      dbo.Withdrawal AS w
    WHERE
      d.DepositDate  >= start_of_this_month AND d.DepositDate  < start_of_next_month
      AND
      w.WithdrawDate >= start_of_this_month AND w.WithdrawDate < start_of_next_month
  )
SELECT
  TotalAmount,
  WithdrawAmount,
  Balance,
  Status = CASE WHEN Balance > 0 THEN 'StillAvailableYAY' ELSE 'ZeroBalanceOops' END
FROM
  balances
;

Of course, if you do not really need to return the balance – only to compare it to 0, then nesting is not needed and you can put the d.TotalAmount - SUM(w.WithdrawAmount) OVER (ORDER BY w.WithdrawDate ASC) expression directly into the CASE, replacing Balance.


Multiple select for single row insert I need to insert a row into a table by selecting value from multiple tables. I want a row to be inserted from multiple tables.

insert into tab(id,name,date) values
((select max(id)+1 from tab),(select name from tab1),now());

Can you tell me how to do achieve this?

Whether this can be achieved by simple query itself or stored procedure is the only option?

This is typically achieved by writing a SELECT statement that outputs the fields in the same order as the INSERT statement and using that instead of a VALUES clause. For example, one might:

INSERT INTO my_table (field1, field2)
SELECT t1.field1, max(t2.field2)
FROM t1 INNER JOIN t2 ON (...)
GROUP BY t1.field1
WHERE something_else = 'whatever';

The point is that you can just do an

INSERT INTO ... SELECT ...

to insert rows. You do not have to create a VALUES clause and mess with subqueries.


SQLServer AlwaysOn Availability Groups in 2014 and 2016 We're designing a new, HA database cluster to support two of our vendor software applications; both applications are mission critical so we are trying to make sure we get this right.

One vendor will support SQLServer 2016 but the other is only validated against 2014. The second vendor is not preventing us from using 2016 but, I know customer support will be an issue down the road (it'll be the thing they blame any time anything goes wrong...)

Both applications are pretty vanilla in terms of how they use the database.

I'd like to know whether there are compelling enough reasons to use 2016 that I would risk the support issues with my second vendor. Stability and operational support issues vs. 2014 are my biggest concerns.

There are plenty of reasons to upgrade to 2016 and even possibly 2017, however if a vendor will not support you on those versions, then the benefits of 2016 and 2017 are moot. It is less risky to stay with a supported version than to take on the benefits of what 2016 and 2017 will bring you.


How to update a column in a table with data from another, unrelated tables columns data jus in order it selected? There are two tables that unrelated. I want to update a field in first table with data that selected from second table in some order. There is no relations between tables, fields and data.
I have just errors from my try outs.
PS: Using MySQL.

Create a column in each table that you can update with a sequence. Then you have something to join on.


How is my software conecting on my SQL Server if it failover via availability groups? I'm a Little confused about this topic.

I know with a default instance, we can create a cluster with let's say, with the ip  x.x.x.10. then automatically this IP will connect to the x.x.x.9 that is the principal. if the principal is down, it will connect to x.x.x.8 that is the secondary.

How does this work if I have a named instance? I'm having a problem that I create both instances X.X.X.36\SQLPRIMARY and X.X.X.37\SQLBACKUP. I'm trying to connect via SSMS to the X.X.X.38 That is the CLUSTERs Ip but It shows the error that the instance cannot be found. Normally with a non named instance, using that cluster IP, it points to the primary SLQ instance on that cluster.

Does the listener have this job here? I think I quiet not understand it's purpose.

I'm trying to connect via SSMS to the X.X.X.38 That is the CLUSTERs Ip but It shows the error that the instance cannot be found.

Named instances, by default, don't listen on port 1433, which is required to connect without a specific port or instance name.

However Failover Cluster Instances always listen on dedicated IPs, so you can (and should) reconfigure your FCIs to listen on port 1433 using SQL Server Configuration Manager.

You can do the same thing if your instances are standalone (non-FCI), but you have to configure them to listen on specific IPs in the case you have multiple instances installed on a server.  If you have a single named instance installed on each server you can simply configure it to listen on port 1433 on all IPs.


Incorrect execution plan I'm running a select query. On certain days it's using an incorrect execution plan. Other days it's using the correct execution plan. I have verified with (nolock) option, index stats, blocking, CPU, and memory load; everything is normal. Are there any other ways to check why it is using the wrong execution plan?

The query:

DECLARE @ProjectName as nvarchar
DECLARE @vehicleName as nvarchar
DECLARE @fromDate as datetime2
DECLARE @toDate as datetime2

select
    d.DDSEventId, d.PrjName, d.VehicleName, d.StartTime, d.EndTime,
    e.Name, s.Value, es.Name, s.DDSSignalId, e.ProcessId, e.EventId,
    d.VehiclePos, e.DefaultDescription, es.DefaultDescription,
    d.CarName, d.ExclusionState
from
    DDSEvents d
inner join
    Events e on d.DiagnosticConfigurationId = e.DiagnosticConfigurationId
             and d.EventId = e.EventId
             and d.ProcessId = e.ProcessId
left join
    DDSSignals s on d.DDSEventId = s.DDSEventId
                 and s.StartTime = d.StartTime
left join
    EnvironmentSignals es on s.EnvironmentSignalId = es.EnvironmentSignalId
where
    d.PrjName = @projectName and
    d.VehicleName = @vehicleName and
    d.StartTime >= @fromDate and
    d.StartTime < @toDate

Correct plan

Wrong plan

I have declared the same variables for both execution plans.

An execution plan could change for the same parameters for numerous reasons. Different set options such as ANSI_NULLS etc., statistics updates or data variance between executions.

As you are using SQL Server 2008 R2, you can control the plan using query hints. However, these should be avoided where possible as they are hard-coded and will remain in place when data volumes change in the future. Far better to let the optimiser choose the best plan after you maintain statistics and indexes appropriately.

If you really need to fix a query plan, a plan guide is another option as you don't have to modify any code.

If you ever upgrade to SQL Server 2016, the Query Store also allows you to fix the plan for a query with a single click.


How to sort category by path and sort order? first of all I am sory for my english.

My category structure is shown below. My purpose is to get category structure with single query.
I sort my table by path and I get category tree.

sort_order : Indicates sort order between categories with same level

When I run query

SELECT * FROM category order by path;

RESULT:

But result must be as shown below  because sort_order of Electronic is 1 and this is less than sort_order value of Computer category 2

 - ELectronic
 - - TV
 - - - LCD
 - - - - LED LCD
 - Computer
 - - Laptop

EDIT :

For @adopilot

Query : SELECT * FROM category order by level, path;

RESULT:

The problem is you need to extract the sort order for the root items and make that same order apply to the sub-items in the tree without re-ordering the sub-items.  In Oracle you can do this with a windowing function as follows:

SELECT RPAD('- ',Length(Path)-1,'- ') || label, levelx, path, sort_order
   , min(sort_order) OVER (PARTITION BY FirstLevel) Sort_Order2
FROM (
   SELECT id, parent_id, label, levelx, path, sort_order
      , substr(path,2,1) FirstLevel
   FROM t1 WHERE Label IS NOT NULL
)
ORDER BY Sort_Order2, Path;

I don't know if you can do something like that in MYSQL, so here is a version using a GROUP BY and self join that works in MySQL 5.5.28.

SELECT Label
FROM T1 a
JOIN
   (SELECT SUBSTR(Path,2,1) FirstLevel, MIN(Sort_Order) FirstSort FROM T1
      WHERE Label IS NOT NULL GROUP BY SUBSTR(Path,2,1)) b
   ON substr(a.Path,2,1) = b.FirstLevel
ORDER BY FirstSort, Path;

(SQL Fiddle)


Optimize MySQL Query with multiple subqueries I have the following Query:

SELECT
      p.id AS post_id,
      p.status,
      p.user_id AS post_user_id,
      ( SELECT  pl.id
            FROM  postlocks AS pl
            WHERE  pl.post_id = p.id ) AS postlock_id,
      ( SELECT  COUNT(rn.id)
            FROM  renews AS rn
            WHERE  rn.post_id = p.id ) AS renew_count,
      ( SELECT  rn.status
            FROM  renews AS rn
            WHERE  rn.post_id = p.id
            ORDER BY  rn.id DESC
            LIMIT  1                 ) AS last_renew_status,
      ( SELECT  TIMESTAMPDIFF(SECOND, rn.timestamp, CURDATE())
            FROM  renews AS rn
            WHERE  rn.post_id = p.id
            ORDER BY  rn.id DESC
            LIMIT  1                 ) AS date_diff_from_renew,
      TIMESTAMPDIFF(SECOND, p.timestamp, CURDATE() ) AS date_diff_from_post
    FROM  posts AS p
    HAVING  (postlock_id IS NULL)
      AND  p.status='success'
      AND  ((date_diff_from_renew IS NOT NULL
                      AND  date_diff_from_renew > 172800
                      AND  last_renew_status='success')
              OR  (date_diff_from_renew IS NULL
                      AND  date_diff_from_post > 172800)
           )

It takes about 3 seconds to execute this query.

Here is the EXPLAIN:

+----+--------------------+-------+-------+---------------+---------+--    -------+----------+------+-------------+
| id | select_type        | table | type  | possible_keys | key     | key_len | ref      | rows | Extra       |
+----+--------------------+-------+-------+---------------+---------+---------+----------+------+-------------+
|  1 | PRIMARY            | p     | ALL   | NULL          | NULL    |     NULL    | NULL     | 2988 |             |
|  5 | DEPENDENT SUBQUERY | rn    | index | post_id       | PRIMARY | 4           | NULL     |    1 | Using where |
|  4 | DEPENDENT SUBQUERY | rn    | index | post_id       | PRIMARY | 4           | NULL     |    1 | Using where |
|  3 | DEPENDENT SUBQUERY | rn    | ref   | post_id       | post_id | 4           | rto.p.id |    2 |             |
|  2 | DEPENDENT SUBQUERY | pl    | ALL   | NULL          | NULL    |     NULL    | NULL     |    1 | Using where |
+----+--------------------+-------+-------+---------------+---------+---------+----------+------+-------------+

Is there any way to optimize this query, or do I need to do a total rewrite another way?

Thanks.

Add an index to (post_id) in postlocks, remove the subquery against that table and the reference to that column in HAVING, and add WHERE NOT EXISTS (SELECT * FROM postlocks pl WHERE pl.post_id = p.id).

...for a start.

You want that condition to be evaluated early since it will eliminate some of the other lookups, and this should help ensure that the optimizer does that.  And the column needs to be indexed, otherwise you're scanning the table, as indicated by type = ALL in dependent subquery 2.


Can we downgrade from 19c to 12.1.0 if the compatibility is set to 12.2.0 Team:
Recently i upgraded a 12.1.0 database to 19c (non CDB architecture). Current compatibility is set to 12.2.0. Can I downgrade it to the earlier version - 12.1.0? Documentation states the downgrade is possible but am not sure about the compatibility parameter.
https://docs.oracle.com/en/database/oracle/oracle-database/19/upgrd/downgrading-oracle-db-after-upgrade.html#GUID-59F92E2A-DC4D-46A3-9AC9-63F47BE4A86F

Check for Incompatibilities When Downgrading Oracle Database

If the compatibility level of your Oracle Database 12c database is set
to 12.2.0, then you are not able to downgrade.

You can downgrade to 12.2, but not to 12.1.


How can I better model the relationships in this project? I'm developing an internal tool (as an intern). We deal with a lot of hardware, which is all custom and managers have a hard time knowing who is developing on what machine.
The three main things to track for this project will be Hardware, Projects and the Employees. I'm struggling with how to deal with the managers.

I was struggling with databases and of course I have to design one in my first internship. Any and all suggestions are greatly appreciated.

Here is my current ERD:

Here is an excerpt from the design document for context:

  Tool Features

  Track gaming hardware:
  Sort or search by serial no, vendor, platform, type.
  Track project pool:
  See employees and VLT’s assigned by project. Emails are directly in the web >page.
  Track lab hardware:
  Sort or search by serial no, vendor platform and type.

I am still learning myself so you might not agree with everything I say.

In my business we have 'projects' that last for a certain amount of time and have staff assigned to them.

I would give your Employees a 'default role' eg Manager, Technician whatever. Then when they are added onto a project this role will be used by default in a drop-down menu unless you choose otherwise. This role may affect their pay rate for each project also.

If you really wanted to impress them you could make an application in Visual Basic for creating and updating the tables, you can do this for free I believe.


Creating a database link from Oracle Cloud server to remote access database I work for a small company that currently use Apex to run reports on our accounts system. Oracle is installed on our local server and part of the data is gained via a database link to an access database.

We are changing our server and I was interested in trying out the Always Free cloud service to recreate the above.

Can anyone give me any pointers on if/how you could connect the cloud service to an access database held on of local server?

Please let me know what crucial information is missing from my question - I am sure there is some!

Any advice would be gratefully received.

Cheers

Andrew

In theory yes, you could set up such a link, but I'm not sure I'd advise it. You would need an Oracle Gateway server on your local network to bridge the connection. You will also need direct network connectivity from the cloud to your Gateway server, which means opening your local firewall to allow the inbound connection from the internet. It would imply that you have a pretty robust network/secure infrastructure to support something like that.

See here: https://blogs.oracle.com/datawarehousing/making-database-links-from-adw-to-other-databases

or here: https://conferences.heanet.ie/assets/files/Thursday_B_11.00_Dick_Doyle_UCD.pdf


add column without metadata lock in mysql 5.6 I am using MySQL Server version: 5.6.15 Homebrew. I need to add new null column. alter table requires no metadata lock. In order to avoid this MySQL 5.6 introduced algorithm=inplace. Here is the reference articles http://www.mysqlperformanceblog.com/2013/07/05/schema-changes-whats-new-in-mysql-5-6/.

Executing the following sql throws

mysql> alter table candidate_candidate add column responsible_user_id int, algorithm=INPLACE, lock=NONE;
ERROR 1845 (0A000): ALGORITHM=INPLACE is not supported for this operation.
Try ALGORITHM=COPY.

Is there a way to add column without affecting app read/write ?

I have executed the very same query on 5.6.14 and got a positive result:

alter table candidate_candidate add column responsible_user_id int, algorithm=INPLACE, lock=NONE;
Query OK, 0 rows affected (0.48 sec)
Records: 0  Duplicates: 0  Warnings: 0

So I am sure that this kind of operation can be done online. You are probably experiencing some of the exceptions shown at: http://dev.mysql.com/doc/refman/5.6/en/innodb-create-index-limitations.html

My bet would be on having outdated temporal datatypes, foreign keys or fulltext indexes. Any of those on the table? Recently migrated from 5.5? If I am wrong, can you show the full CREATE TABLE?