texts: Oracle PK index extremely low performance

texts:
There's a table MYHIST containing 42M rows.

The table has a primary key column MYHIST_ID NUMBER(22) with a valid index PK_MYHIST on it, and 50 more columns (of types VARCHAR2, NUMBER and DATE), which seems to be not interesting regarding this topic.

Suppose, that statistics for the table (and it's PK index) has been gathered 5 minutes ago.

select index_name, table_name, index_type, num_rows, distinct_keys, sample_size, status
  from user_indexes
 where table_name = 'MYHIST';

 INDEX_NAME | TABLE_NAME | INDEX_TYPE | NUM_ROWS | DISTINCT_KEYS | SAMPLE_SIZE | STATUS
----------------------------------------------------------------------------------------
  PK_MYHIST |    MYHIST  |    NORMAL  | 42297450 |    42297450   |    42297450 | VALID

Unexpectedly, the simplest query with a filter by MYHIST_ID ends with full scan. It's guaranteed that no more than 1000 rows in the table having MYHIST_ID > 4615200.

    select *
      from MYHIST t
     where MYHIST_ID > 4615200
     order by MYHIST_ID desc;

    Plan:

    --------------------------------------------------------------------------------------
    | Id  | Operation            | Name    | Rows     | Bytes       | Cost    | Time     |
    --------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT     |         | 38067431 | 14655960935 | 3872743 | 12:54:33 |
    |   1 |   SORT ORDER BY      |         | 38067431 | 14655960935 | 3872743 | 12:54:33 |
    | * 2 |    TABLE ACCESS FULL | MYHIST  | 38067431 | 14655960935 |  746842 | 02:29:23 |
    --------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ------------------------------------------
    * 2 - filter("MYHIST_ID">4615200)

With an index hint the plan appears to be even worse.

    select /*+ index(t PK_MYHIST) */ *
      from MYHIST t
     where MYHIST_ID > 4615200
     order by MYHIST_ID desc;

    Plan:
    ---------------------------------------------------------------------------------------------------
    | Id  | Operation                      | Name       | Rows     | Bytes       | Cost    | Time     |
    ---------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT               |            | 38067431 | 14655960935 | 9274232 | 30:54:51 |
    |   1 |   SORT ORDER BY                |            | 38067431 | 14655960935 | 9274232 | 30:54:51 |
    |   2 |    TABLE ACCESS BY INDEX ROWID | MYHIST     | 38067431 | 14655960935 | 6148331 | 20:29:40 |
    | * 3 |     INDEX RANGE SCAN           | PK_MYHIST  | 38067431 |             |   85356 | 00:17:05 |
    ---------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ------------------------------------------
    * 3 - access("MYHIST_ID">4615200)

Could you explain what causes the situation, when the index doesn't give an expected performance?


texts: Merging database entries and keeping history

texts:
I have a postgres database backend to a webapp, with a table of users, and those users have an id which is used in other database tables (things like orders, tasks etc).

I have a situation where a user has changed name, and at the moment has two seperate user accounts, both now with seperate history (orders entered, tasks created), and i'd like to combine both user accounts into 1, keeping the history from both accounts.

What would be the best way to go about doing this?


texts: Mysql innoDB write operations are extremely slow

texts:
I'm having serious performance problems with MySQL and the InnoDB engine. Even the simplest table makes writing operations (creating the table, inserting, updating and deleting) horribly slow, as you can see in the following snippet.

mysql> CREATE TABLE `test` (`id` int(11) not null auto_increment,
   -> PRIMARY KEY(`id`)) ENGINE=InnoDB;
Query OK, 0 rows affected (4.61 sec)

mysql> insert into test values ();
Query OK, 1 row affected (1.92 sec)

mysql> insert into test values ();
Query OK, 1 row affected (0.88 sec)

mysql> insert into test values ();
Query OK, 1 row affected (1.10 sec)

mysql> insert into test values ();
Query OK, 1 row affected (6.27 sec)

mysql> select * from test;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)

mysql> delete from test where id = 2;
Query OK, 1 row affected (0.28 sec)

mysql> delete from test where id = 3;
Query OK, 1 row affected (6.37 sec)

I have been looking at htop and the long waiting times are not because of abnormal CPU load. It's almost zero, and memory usage is also normal. If I create the same table using the MyISAM engine, then it works normally. My my.cnf file contains this (if I remember right I haven't changed anything from the default Debian configuration):

[client]
port        = 3306
socket      = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice        = 0

[mysqld]
user        = mysql
pid-file    = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port        = 3306
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
language    = /usr/share/mysql/english
skip-external-locking
bind-address        = 127.0.0.1
key_buffer      = 40M
max_allowed_packet  = 16M
thread_stack        = 128K
thread_cache_size       = 8
myisam-recover         = BACKUP
max_connections        = 100
table_cache            = 64
thread_concurrency     = 10
query_cache_limit   = 1M
query_cache_size        = 40M
log_slow_queries    = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes
expire_logs_days    = 10
max_binlog_size         = 100M

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[isamchk]
key_buffer      = 16M
!includedir /etc/mysql/conf.d/

I have also tried to restart the server, but it doesn't solve anything.

The slow queries log doesn't give any extra information.


texts: Can you separate schemas and objects with spaces and line feeds in T-SQL?

texts: When writing a query, can you separate schemas and objects with spaces and line feeds?


texts: Select rows with line breaks: Unexpected result

texts:
I'm trying to find rows in a table where there are line breaks.

I found some sample code that lets me do this:

where dump(f1) like '%,10%' and  Not (dump(f1) like '%100%')

However, I'm getting an unexpected result.

create table test1 (f1 varchar(25));

insert into test1(f1) values ('Note');
insert into test1(f1) values ('Nota');   --<-- Slight spelling difference

select * from test1 where dump(f1) like '%,10%' and  Not (dump(f1) like '%100%')

F1
-------------------------
Note

Why does the query return a record, if there isn't a carriage return? And why does it only return a single record?

Prediction: I'm having a brain fart and/or I have no idea what I'm doing. It is Monday morning, after all.


texts: mongodb compact does nothing - no entry in mongodb.log and nothing in db.currentOp()

texts:
We have space problems. The actual data size is only 5 GB, but disk size is 15 GB because of deleted documents. MongoDB (3.0.6) runs in docker container where we can't extend volume.

# tail -f mongod.log
2016-04-26T14:43:59.631+0000 I STORAGE  [FileAllocator] allocating new datafile /data/work/mongodb/data/opWNFbyNlGQ6xShT.11, filling with zeroes...
2016-04-26T14:43:59.632+0000 I STORAGE  [FileAllocator] FileAllocator: posix_fallocate failed: errno:28 No space left on device falling back
2016-04-26T14:43:59.632+0000 I STORAGE  [FileAllocator] error: failed to allocate new file: /data/work/mongodb/data/opWNFbyNlGQ6xShT.11 size: 2146435072 failure creating new datafile; lseek failed for fd 40 with errno: errno:2 No s^C

disk

/dev/vdh                            16G   15G  553M  97% /data/a6a186a3-dc98-4f47-83f3-96e953c18b69

repairDatabase doesn't work because no scratch space

> db.runCommand( { repairDatabase: 1 } );
{
        "ok" : 0,
        "errmsg" : "Cannot repair database opWNFbyNlGQ6xShT having size: 14975762432 (bytes) because free disk space is: 578928640 (bytes)",
        "code" : 14031
}

So I tried

> db.runCommand ( { compact: 'accounts', paddingFactor: 1.1 } )
{ "ok" : 1 }

I don't see nothing in progress

> db.currentOp()
{ "inprog" : [ ] }
> db.currentOp()
{ "inprog" : [ ] }
> db.currentOp()
{ "inprog" : [ ] }

There is no entry in mongodb.log about the compact operation

  You may view the intermediate progress either by viewing the mongod
  log file or by running the db.currentOp() in another shell instance.

compact

Why compact fails?

is our only solution to do dump/drop/restore?


texts: Instant File Initialisation on Windows Oracle Server

texts:
I require to add additional datafiles to a SmallFile tablespace. The process took several minutes on my system for 1 datafile.

Is it possible to allow Oracle the same capability as SQL Server using instant file initialisation? my googling was unproductive.

Thanks,

Chris


texts: Nullable smallint column usage in a CASE Statement nested in a STUFF function

texts:
I'm having issues writing a Stored Procedure that uses a CASE statement inside of a STUFF function. I thought my issue may have to do with Restricted being a nullable smallint. I tried converting it (can you even convert a smallint to an string?) and I'm still not getting results as expected. I'd like the WHEN and the ELSE to combine when they meet the specific conditions in my query, but instead it is completely eliminating this row from my results.

In the STUFF, if the row is Restricted, I’d like to tack on “(Restricted)” to the ItemStatus. If not Restricted (0 or NULL), I'd like to just tack on the ItemStatus.

I tried replacing the WHEN in the CASE statement with something like:

WHEN CONVERT(varchar(100),  ISNULL(Statuses2.Restricted, 0) ) = 0 THEN ', ' + dmStatuses2.ItemStatus + ' (Restricted)'

If I change the WHEN to:

WHEN Statuses2.Restricted IS NOT NULL THEN ', ' + dmStatuses2.ItemStatus + ' (Restricted)'

...I get the row to show up (but only with the ItemStatus. This column should have 2 strings concatenated as there is at least one Restricted and non-Restricted value)…but once I add an additional condition to try and check the value of Restricted (Restricted = 0 or Restricted = 1), I start not getting any results at all.

Any ideas of what I'm doing wrong?

SELECT
    StatusList
    --etc...
FROM
(
    SELECT
        StatusList
        --etc...
    FROM
    (
        SELECT
            StatusList,
            [Count] = ISNULL(COUNT(*), 0)
            --etc...
        FROM
            Items
        INNER JOIN
        (
            SELECT ROW_NUMBER() OVER (PARTITION BY /* some stuff */ ) as RowNum, Statuses.*,
                STUFF((
                    SELECT
                        CASE
                            WHEN Statuses2.Restricted = 1 THEN ', ' + dmStatuses2.ItemStatus + ' (Restricted)'
                            ELSE ', ' + dmStatuses2.ItemStatus
                        END
                    FROM Statuses Statuses2
                    WHERE
                        Statuses2.StatusId = Statuses.StatusId
                     FOR XML PATH('')
                ), 1, 2, '')
                     AS StatusList
            FROM Statuses
            INNER JOIN dmStatuses ON
                dmStatuses.StatusId = Statuses.StatusId
            WHERE
                Statuses.TableId = @TableId
        ) AS Statuses2 ON
            Statuses2.TableId = Items.TableId
            AND Statuses2.EndDt IS NULL
            AND RowNum = 1
        INNER JOIN dmStatuses dmStatuses2 ON
            dmStatuses2.StatusId = Statuses2.StatusId
        LEFT JOIN dmItems dmItems2 ON
            dmItems2.ItemId = Items.ItemId
        WHERE
            Items.TableId = @TableId
        GROUP BY
            Items.TableId,
            dmItems2.ItemId,
            Statuses2.StatusList
    ) AS ItemCounts
    LEFT JOIN dmItems ON
        dmItems.ItemId = ItemCounts.ItemId
) AS ItemCountsAndLimits
WHERE
    ItemCountsAndLimits.[Count] > ItemCountsAndLimits.Limit


texts:
I am having to Cast() a Text datatype to a TIME datatype.  THe issue I'm running into is when the results display i'm loosing the hh:mm:ss format.  The syntax I am using is Select SUM(CAST(salestime As Time)) From testData; How do I display my query results in hh:mm:ss format?  I also tried to use this syntax SUM(Cast(Time_Format(salesTime, "%h, %i, %s") As Time)) but that is returning a whole number
EDIT
A few sample of the times in the TEXT column are:
0:05:58
0:00:00
0:31:54
0:23:26


texts:
Reference manual for MySQL 5.6 states that " Some changes, however, still use the statement-based format. Examples include all DDL (data definition language) statements such as CREATE TABLE, ALTER TABLE, or DROP TABLE. "

Does this statement means that even if we have ROW format for binary logs all DDLs will be logged in binary log as statement based? How does this affect replication?

Kindly help me to understand this.


texts: MySQL Authentication Error After Re-applying the mysqldump after an upgrade

texts:
After upgrading from a lower version of MySQL 5.5.* to MySQL 5.5.52, after reapplying the mysqldump that I took before the upgrade, I am receiving the following error:

Authentication to host 'localhost' for user '[user]' using method 'mysql_native_password' failed with message: Access denied for user '[user]'@'localhost' (using password: YES) ---> MySql.Data.MySqlClient.MySqlException (0x80004005): Access denied for user '[user]'@'localhost' (using password: YES)


texts: Any way of optimizing joins between 2 databases with different collations?

texts:
I'm creating a query for a report where we must do a cross database join. Unfortunately the fields we have to join on have two separate collations - "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS". The join is very slow between these and is really impacting the speed of the report - is there any ways of optimizing this? Is changing the collation to match wise and/or difficult?

The join portion of the query is below

INNER JOIN EDE.dbo.DeliveryDetail D
              ON D.DeliveryNoteNo COLLATE database_default = Consignments.CustomerReference COLLATE database_default


texts: sql server Full text different than like results

texts:
I am working on query which seraches for 4.01 using CONTAINSTABLE as " 4.01 ".
I am expecting it would return all results similar to LIKE %4.01%. But the results are less in contains query.

Here are the values missing when using the full text query, but displayed by when using the LIKE operator:

4.01.02.01.01.01
4.01.02.01.01.01
4.01.02.01.01.02
4.01.02.01.01.02
4.01.02.01.01.03
4.01.02.01.01.03
4.02.04.01.01.01
4.02.04.01.01.01
4.02.04.01.01.02
4.02.04.01.01.02
4.04.01.02.0L.01
4.04.01.02.0L.01
4.04.01.02.0N.01
SUB-4.01.01.01

How do I get the complete results using Full Text Search? I tried not using a stoplist and also rebuilt the index, but it did not help.


texts: MySQL Reusing calculation in SELECT clause in a VIEW

texts:
I have an SQL query that has a simple date difference between NOW() and the creation date (is in the table). I need to reuse this again within the SELECT clause in a VIEW. Is this possible at all?

The query (note substitutions have been made from the original):

CREATE VIEW upcoming_events_view AS
SELECT *, TIMESTAMPDIFF(WEEK, `create_date`, NOW()) AS week_diff,
       (week_diff % week = 0) AS do_current_week
FROM event_repeats

Needless to say, this didn't work due to the week_diff being reused in the SELECT clause. As a normal SELECT query, I found variables worked; however, I can't use that in a VIEW:

SELECT *, @week_diff := TIMESTAMPDIFF(WEEK, `create_date`, NOW()) AS week_diff,
       (@week_diff % week = 0) AS do_current_week
FROM event_repeats

The table:

CREATE TABLE IF NOT EXISTS `event_repeats` (
  `event_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `event_name` varchar(150) NOT NULL,
  `start_date` date NOT NULL,
  `week` int(11) NOT NULL,
  PRIMARY KEY (`event_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=51 ;

What's the best way to do this (if any)?

Thanks in advance.


texts: Manually increase PostgreSQL's table size limit of 32 TB?

texts:
I need a free database software and thought of PostgreSQL. But then I read it has a table size limit of 32 terabytes, but I need more (and don't want to spend thousand/millions on high end database software like Oracle).

Is there a way to manually increase that limit? And if so, what is the penalty for doing so?


texts:
I currently have an important TSQL job that hangs probably once a week.

It moves data from one database to another.

It is on a schedule running every 5 minutes indefinitely throughout the day.

Sporadically, it will hang usually around 1 am in the morning.  When I say hang I mean it just gets caught at 1am never finishes its current scheduled run in turn it never moves on to the next scheduled run.

In order to get it to stop I have have to stop the job.  Disable the job.  Kill the associated SPID.  Re-enable and restart the job.  Sometimes this gets it running but other times I have to restart the SQL Agent itself.

There is really no rhythm or reason as to why this happens datawise.  Sometimes it will go weeks without issue other times it occurs once or twice a week.

I am just wondering if there is possibly a way to have this job restart it self regularly in the morning so it doesn't hang.  I am assuming someone else has had an issue like this before.  Any direction here is greatly appreciated.

There is no blocking in the database.  I am not looking to fix a blocking issue.  Looking for a way to determine if the job is hung and restart it or something along those lines.

I have reviewed sp_who2 regularly and never seen any blocking with this particular job.


texts: What process(s) writes to ALERT log file in Oracle?

texts:
Team:
I have been searching for the process(s) which write to alert log file but couldn't find any reference to the documentation. I believe the background processes write to alert log. Not sure if it's correct. Also, does a server process write to alert log as well? We see a lot of entries in the alert log which states some info is written to trace file abc.trc etc, I was wondering if the particular background process involved with that error write inti that particular trace file.


texts:
We are using SQL Server 2016 and want to use the Force Encryption option.  From what I read, is it just installing the certificate on the Server itself and then setting the ForceEncyption to "Yes"?  Is there anything needed on the client side like updating the connection string that it uses to connect?  Or does setting the ForceEncryption take care of it?

Sorry, I'm new to encryption and we want to implement that on our SQL servers and it seems straight forward but I'm getting conflicting instructions as to whether the data connection string needs to be modified or not.
We are also using AAG as well so will implementation be different as well?


texts: Azure SQL Warehouse - Data Ingestion - Convert a huge fixed width (with commas) file to delimited

texts:
I am not even sure if I am framing this question right, but I will try - I have a bunch of huge text files generated from an Oracle export on a Linux system. Each file is about 30 GB in size, and I have about 50 of them.

The goal is to export this data to Azure SQL Data Warehouse. BCP in this case is not the right approach keeping in mind the size of the data, so I had to use Polybase.

After converting from ASCII to UTF8 encoding, I ran into an issue while querying the external tables. Polybase doesn't play well with the fixed width text file with a line break in each line.

The text file looks something like this:

101,102,103,104,105,106,107
108,108,109,110,111,112,113
114,115,116,117,118,119,120
121,122,123

--nothing here, just a blank line

201,202,203,204,205,206,207
208,209,210,211,212,213,214
215,216,217

Polybase tries to process from 101 through 107 and errors out complaining there were not enough columns in this file to process.

Here is what I think is happening: The fixed width and line breaks are making it treat the line break as a row delimiter.

How do I convert this file to look like below:

101,102,103,104,105,106,107,108,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123{CR}{LF}
201,202,203,204,205,206,207,208,209,210,211,212,213,214,215,216,217{CR}{LF}

EDIT: Here is the sample data from a file. I opened it in git bash on a windows VM.

These files are supposed have 167 columns with , as the column separator. The problem is, since each row spawns multiple lines, its difficult to process them from Polybase external tables.


texts: Interview Question:- I have one table with following data in sql server

texts:
CREATE TABLE [dbo].[T_COL](
    [COL1] [varchar](10) NULL,
    [COL2] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[T_COL] ([COL1], [COL2]) VALUES (N'A', 1)
INSERT [dbo].[T_COL] ([COL1], [COL2]) VALUES (N'A', 2)
INSERT [dbo].[T_COL] ([COL1], [COL2]) VALUES (N'A', 3)
INSERT [dbo].[T_COL] ([COL1], [COL2]) VALUES (N'B', 1)
INSERT [dbo].[T_COL] ([COL1], [COL2]) VALUES (N'B', 2)
INSERT [dbo].[T_COL] ([COL1], [COL2]) VALUES (N'B', 3)

The desired output should look like this:-

col1     col2
A         1
A         1,2
A         1,2,3
B         1
B         1,2
B         1,2,3


texts:
I'm trying to build a conditional check constraint for my table TBL_AFIL, which has two fields:

Fec_Renun DATE
flg_afil INTEGER

Constraint: IF flg_afil=1 THEN Fec_Renun can not be NULL.

ALTER TABLE TBL_AFIL
ADD CONSTRAINT FecRenun_Not_Null CHECK (Fec_Renun IS not NULL and flg_afil =1) ;

But it throws this error:

ORA-02293: cannot validate (FecRenun_Not_Null) - check constraint violated

But when I run this query, it returns 0 rows.

SELECT * FROM TBL_AFIL
WHERE FEC_RENUN IS NULL
AND FLG_AFIL =1;

I know that to this constraint I could add ENABLE NOVALIDATE, but I'm intrigued why can't build this constraint.


texts:
I know that those letters mean Extract, Transform, and Load.

But, when I used it at first, I thought that during the Transform phase I could do plenty of different joins on data that I've extracted from data sources, later on I realized that doing a join on a different ETL is not that handy.

So what do we do in Transform phase ?
Calculate and output the result ?
String transformation?
Should input data sources only be csv, xml or plain file?
If joins are not that handy, should we only do high level transformation within an ETL ?

Thank you


texts: Two primary keys but not a composite, what is this sort of relationship called?

texts:
I am integrating employee/position data with another system.

The first system is HR. It has an Employee table and a Positions table. The second system tracks office space and tracks who the office belongs to. It uses one table [em] to store both employees and position data.

If an employee changes position then the position id changes. If the position becomes vacant and a new employee fills the position then employee id is changed.

Both position id and employee ID act like primary keys for this table. I am trying to figure out if there is a name for this type of relationship in a database terminology?


texts:
I have a multi-threaded application that does an insert into a table using a stored procedure, and it is causing deadlocks in the database. I ran a trace on my database, and the deadlock processes are calling the same stored procedure.

CREATE PROC dbo.MyTable_Insert
@parm1 INT,
@parm2 INT, ..... and so on for the parms (around 20 of them)
AS
INSERT INTO MyTable
(Column1, Column2, .....)
VALUES
(@parm1, @parm2, ....)

The stored procedure doesn't reference any other tables. It just takes the parms passed in, and inserts into the table.

Here is a sample of the deadlock log.

<deadlock victim="process3f0868">
<process-list>
    <process id="process34f978" taskpriority="0" logused="476" waitresource="OBJECT: 16:2089826557:0 " waittime="1421" ownerId="194136667" transactionname="user_transaction" lasttranstarted="2017-08-13T21:09:16.750" XDES="0x275dd57d0" lockMode="X" schedulerid="1" kpid="4352" status="suspended" spid="93" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2017-08-13T21:09:16.977" lastbatchcompleted="2017-08-13T21:09:16.910" clientapp=".Net SqlClient Data Provider" hostname="MYDB" hostpid="7272" loginname="myUser" isolationlevel="read committed (2)" xactid="194136667" currentdb="16" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
        <executionStack>
            <frame procname="MYDB.dbo.MyTable" line="51" stmtstart="3448" sqlhandle="0x03001000a31b0d7bdca06a0185a700000100000000000000">
                INSERT  INTO MyTable
                ( Column1,
                Column2 , </frame>
        </executionStack>
        <inputbuf>
            Proc [Database Id = 16 Object Id = 2064456611]    </inputbuf>
    </process>
    <process id="process3f0868" taskpriority="0" logused="476" waitresource="OBJECT: 16:2089826557:0 " waittime="1421" ownerId="194136678" transactionname="user_transaction" lasttranstarted="2017-08-13T21:09:16.750" XDES="0x25af8d670" lockMode="X" schedulerid="4" kpid="4660" status="suspended" spid="86" sbid="0" ecid="0" priority="0" transcount="2" lastbatchstarted="2017-08-13T21:09:16.977" lastbatchcompleted="2017-08-13T21:09:16.910" clientapp=".Net SqlClient Data Provider" hostname="MYDB" hostpid="7272" loginname="myUser" isolationlevel="read committed (2)" xactid="194136678" currentdb="16" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128056">
        <executionStack>
            <frame procname="MYDB.dbo.MyTable" line="51" stmtstart="3448" sqlhandle="0x03001000a31b0d7bdca06a0185a700000100000000000000">
                INSERT  INTO MyTable
                ( Column1,
                  Column2 , </frame>
        </executionStack>
        <inputbuf>
            Proc [Database Id = 16 Object Id = 2064456611]    </inputbuf>
    </process>
</process-list>
<resource-list>
    <objectlock lockPartition="0" objid="2089826557" subresource="FULL" dbid="16" objectname="MYDB.dbo.MyTable" id="lock224663700" mode="IX" associatedObjectId="2089826557">
        <owner-list>
            <owner id="process34f978" mode="IX"/>
            <owner id="process3f0868" mode="IX"/>
        </owner-list>
        <waiter-list>
            <waiter id="process34f978" mode="X" requestType="convert"/>
            <waiter id="process3f0868" mode="X" requestType="convert"/>
        </waiter-list>
    </objectlock>
</resource-list>

NOTE: Sensitive information was redacted.


texts: WHERE clause is not filtering on LESS THAN and GREATER THAN or BETWEEN

texts:
I am trying to select MTR values that are between the values of 0 and 4. What am I doing wrong?

SELECT
DATEDIFF(M, TheDate, TheOtherDate) AS MTR FROM MyTable
WHERE MyID = 2074163
AND MTR >= 0  OR MTR <= 4

Returns results that contain values inside and outside of the bounds of MTR being between 1 and 4. (from -3 to 75)

Alternately

SELECT
DATEDIFF(M, TheDate, TheOtherDate) AS MTR FROM MyTable
WHERE MyID = 2074163
and MTR BETWEEN 0 AND 4

Is also returning results that are 0, -2, -3, and -4 - but nothing higher than 0 and less than 4 (except for the entries that are 0)


texts: What advantages does in-memory OLAP have over traditional systems with significant memory?

texts:
Do in-memory OLAP engines have advantages over the traditional OLAP engines backed by enough RAM to contain the entire cube(s)?

For example, if I use a MOLAP engine (SSAS) and GB / TB of RAM where the entire cube (or even star-schema) is RAM resident, what is the difference compared to something like TM1 / SAP HANA?


texts:
I'm a bcp newbie. I want to insert a large file. I'm having a hard time uploading datetime format.

Table SQL:

"Create Table dbo.my_test ( " + \
    "transaction_id BIGINT ," + \
    "transaction_no INT ," + \
    "store_no INT ," + \
    "register_no INT ," + \
    "transaction_date DATETIME," + \
    ");"

My csv file:

"771666690","346138","400","1","2015-06-18 00:00:00"

My format file:

13.0
6
1       SQLBIGINT           0       8       "\""      0     test_                                                       ""
2       SQLBIGINT           0       8       "\",\""   1     transaction_id                                                       ""
3       SQLINT              0       4       "\",\""   2     transaction_no                                                       ""
4       SQLINT              0       4       "\",\""   3     store_no                                                             ""
5       SQLINT              0       4       "\",""   4     register_no                                                          ""
6       SQLDATETIME         0       8       "\r\n"   5     transaction_date                                                     ""

My bcp command:

>bcp data_base.dbo.my_test in "test.csv" -f "test.fmt" -S localhost -U *** -P *****

Output

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid field size for datatype

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1

I am using SQL Server 2016. Default language is csv.

I slimmed down the type just to get a toy example up, here is the new scripts:

Table SQL:

"Create Table dbo.my_test ( " + \
    "transaction_date DATETIME," + \
    ");"

My csv file:

2015-06-18

My bcp command:

>bcp data_base.dbo.my_test in "test.csv" -f "test.fmt" -S localhost -U *** -P *****

Output:

Starting copy...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]Invalid field size for datatype

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 1


texts: Which Oracle directory takes up the most space and why?

texts: Does any directory file in Oracle occupy the most space? Why does it takes up the most? e.g. system


texts: Clustered indexed fields hash matching. Does it matter?

texts:
I'm trying to performance tune some lengthy queries and I'm slicing bits out and running them in isolation.

The query below features three tables D500M, D550M and D580M, all joined on person_ref. All of these tables have clustered indexes on the person_ref fields. They also have non clustered indexes which include the fields in the select list.

These tables are not large - just under 10k rows in each.

select p.PERSON_REF,p.surname,p.first_forname,e.employee_number,ph.MAIN_FLAG
from D500M p
INNER join D550M e on e.PERSON_REF=p.PERSON_REF
INNER join D580M ph on ph.PERSON_REF=p.PERSON_REF

I'm studying the Actual Execution Plan and seeing Index scans on the three tables (nonclustered), and then two Hash Matches on the joins, with costs of 39% and 41%.

The question is this; Is having these hash matches good, bad or irrelevant?


texts: SQL query to return a columns with given range

texts:
I have a table tblItems with records having the same UniqueID and Description but with different dates and amount.
Now I want to show the records by grouping them together using the UniqueIDs and then display the Amount under a dynamically created Date column using the same record.
Date range will be provided as parameter like start date and end date and based on the date range there might be N number of columns.
Described with the example....
tblItems table
╔════════════╦════════════════════╦══════════╦═════════╗
║ UNIQUECODE ║    DESCRIPTION     ║   DATE   ║ AMOUNT  ║
╠════════════╬════════════════════╬══════════╬═════════╣
║ ABCD       ║ Item Decsription 1 ║ 1/3/2014 ║ 2068    ║
║ PQRS       ║ Item Decsription 2 ║ 1/5/2014 ║ 412.104 ║
║ PQRS       ║ Item Decsription 2 ║ 1/5/2014 ║ 635.448 ║
║ UVWX       ║ Item Decsription 3 ║ 1/5/2014 ║ 214.52  ║
║ UVWX       ║ Item Decsription 3 ║ 1/7/2014 ║ 827.2   ║
╚════════════╩════════════════════╩══════════╩═════════╝

I want to display this result as:
╔════════════╦════════════════════╦══════════╦══════════╦══════════╦══════════╗
║ UNIQUECODE ║    DESCRIPTION     ║ 1/3/2014 ║ 1/5/2014 ║ 1/7/2014 ║  Total   ║
╠════════════╬════════════════════╬══════════╬══════════╬══════════╬══════════╣
║ ABCD       ║ Item Decsription 1 ║     2068 ║ 0        ║ 0        ║ 2068     ║
║ PQRS       ║ Item Decsription 2 ║        0 ║ 1047.552 ║ 0        ║ 1047.552 ║
║ UVWX       ║ Item Decsription 3 ║        0 ║ 214.52   ║ 827.2    ║ 1041.72  ║
╚════════════╩════════════════════╩══════════╩══════════╩══════════╩══════════╝


texts: Move database to another drive in same server

texts:
I have installed MSSQL server 2008 in my server in C: drive and I want to move it to the D: drive completely. That means, database locations, installed location to move another drive with data. Is it possible to done this?

Note: Detach method is only for move database. but I want to move whole installation and DB files to another drive.


texts: Domain group as single login, but still many different database users?

texts:
We want to use Windows Authentification on SQL Server 2008 within Active Directory.

Is it possible to use an AD Group as a LOGIN to the server, but then to map the actual AD USER to a database user?

The idea is to have one AD group which has the basic login access, but then to fine-granulate roles and access rights to a list of single database users.

We also are using row-level-security such that every single user will have access to one single country.


texts:
My postgres was working previously, but for some reason does not work anymore. I have tried several different searches in stack overflow and other sites, and after coming up with the same results and reading the same pages over and over again, without any success, it was time to post a new question.

Trying to run my rails project on the local host raises the following error message: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

The server is not running at all, as indicated by the following output from ps -fu postgres:

postgres 23969 23968  0 09:54 pts/25   00:00:00 -bash

Error messages that I'm getting:

When running as the "postgres" user, executing psql results in:

psql: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/tmp/.s.PGSQL.5432"?

When trying to start postgres:

Other things I have tried:

Executing postgres -D /usr/local/pgsql/data >logfile 2>&1 & (as indicated by the official documentation) outputs: [1] 24670. But the server still doesn't run.
I also tried starting the  server via service postgres start. This prompts me to my password to authenticate me as the user (it says Authentication is required to start 'postgres.service'.) My login succeeds (==== AUTHENTICATION COMPLETE ===)but then it says Failed to start postgres.service: Unit postgres.service not found.
I tried restarting the server (following these instructions using sudo /etc/init.d/postgresql restart), which in the past did fix postgres, but now simply prompts Restarting postgresql (via systemctl): postgresql.service. However, now I try to start the server again, and nothing has changed.
The problem does not seem to be any postmaster.pid file that I need to remove. After going through the same process that this user did, nothing was found when running sudo find / -name .s.PGSQL.5432 -ls
I have tried a clean install, and it still doesn't work. Nothing has changed, and the same error messages are appearing.

After removing postgres (following these instructions), I went through the digital ocean tutorial up to the point where it says  "You can now access a Postgres prompt immediately by typing: psql".

Other possible factors: (I am at my wit's end, so I'm listing any possible factor that comes to mind)

I am running Ubuntu Xenial (16.04), and postgresql 9.5
I recently moved and am using a new Internet provider. I'm not sure why this would be an issue, but it did require me to change other settings (specifically, to deploy my rails project locally, instead of localhost:3000 I was using my inet addr. In other words, "http://:3000" (where  is the number provided for inet by ifconfig). But after moving to a new Internet provider, my inet addr changed.
[Update] Log files:

LOG: could not bind IPv4 socket: Cannot assign requested address
HINT:  Is another postmaster already running on port 5432? If not, wait a few seconds and retry.
WARNING:  could not create listen socket for "localhost"
FATAL:  could not create any TCP/IP sockets

At this point I spent several hours to no avail... Please help!!


texts:
I need to protect a Microsoft SQL Server 2008 data file (.mdf) against restoring/attaching on another machine. I'm aware of TDE or Transparent Data Encryption but unfortunately this option is not available in Express version. Any other way to protect the database?

Thanks


texts: Move data from mysql 4.0 to 5.6 without corrupting encoding

texts:
I have a old mysql database that I would like to move to a recent version of mysql and optimize for SELECT ... WHERE ... statements. It is going to be a SELECT only database.

Original database uses latin 1 encoding but the actually data uses Turkish chracters. It is used by a desktop application. Probably application interprets data as latin 5 to display correctly.

I may ditch the original program and implement my own UI for queries.

My problem is that when I mysqldump -> mysql import, some chracters get corrupted. For example, YAŞAR becomes YAÞAR. I didnt add any encoding information to mysql dump and import. When I use phpmyadmin, it seems like my rows use latin1_swedish_ci collation with latin1 encoding.

How can I move my data and don't mess up with encoding.


texts:
I need to display user-specific tables for users logged into a CMS. These tables are stored in another database with tables for the different user groups. For example User 1 to 4 should get data from Table1, User 5-12 from Table2 etc. Unfortunately I can only run one query because of the CMS plugin creating charts only supports one. I can pass the UserID into that query though.

Tables:

UserTable matches User IDs to Group IDs (User1 = Group99).
GroupTable matches Group IDs to table names (Group 99 = Table3).
These User specific tables (Table3) contain Datetimes and Values.

This query returns the values I need from Table3 for example:

SELECT DATE(timestamp) As Date, AVG(Value) AS Value
FROM *DB.TableX*
WHERE timestamp BETWEEN CURDATE()-INTERVAL 4 WEEK AND CURDATE()
GROUP BY DATE(timestamp)

DB.TableX should now be replaced with the result from the following query, which basically returns "Table3":

SELECT grouptable.tablename FROM usertable
LEFT JOIN grouptable ON grouptable.ID = usertable.groupID
WHERE User = %USERID%

When I try to nest the second query into the FROM statement of the first, I get an error "Unknown column timestamp in field list":

SELECT DATE(timestamp) As Date, AVG(Value) AS Value
FROM
  (SELECT grouptable.tablename FROM cmsdb.usertable
  LEFT JOIN cmsdb.grouptable ON cmsdb.grouptable.ID = cmsdb.usertable.groupID
  WHERE User = 2) AS SUBQUERY
WHERE timestamp BETWEEN CURDATE()-INTERVAL 4 WEEK AND CURDATE()
GROUP BY DATE(timestamp)

I thought that was possible but I can't figure out what's wrong. I don't want to build a seperate page for each user :-/


texts: Getting Msg 15151, Cannot alter the user 'xxxx', because it does not exist or you do not have permission

texts:
I have the following script and I keep getting errors when it is executed.

USE [master]
GO

If Not EXISTS (Select loginname from [master].[dbo].[syslogins]
    Where name = 'xxxx' and dbname = 'xxxx-xxxx')
BEGIN
    CREATE LOGIN [xxxx] WITH PASSWORD=N'xxxxx', DEFAULT_DATABASE=[xxxx-xxxx], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
    ALTER LOGIN [xxxx] ENABLE
END
GO

USE [xxxx-xxxx]
ALTER USER [xxxx] WITH DEFAULT_SCHEMA=[db_datareader]
ALTER ROLE [db_datareader] ADD MEMBER [xxxx]
ALTER ROLE [db_datawriter] ADD MEMBER [xxxx]
ALTER ROLE [db_owner] ADD MEMBER [xxxx]
GO

I have tried several permutations of this script with no success. It does create the login but won't let me alter the "user" or "role" properties. When I can get it to work with no errors, it does not make the changes to the user profile.


texts: What is the Server name for SQL Server2008?

texts:
I have sucessfully installed the Microsoft® SQL Server® 2008 Management Studio Express
from the following link:
http://www.microsoft.com/download/en/details.aspx?id=7593
file name: SQLManagementStudio_x86_ENU.exe

When I opend the MS SQL Server Management Studio, it asks for the Server Name which I am not sure what it is nor the login name and password.

Can someone please explain?

Thanks much!


texts: Concatenate two columns into one column in a view in MySQL

texts:
I am trying to create a view and would like to display the firstName and lastName columns from a table as one column (User) in a view.

The view is using a left join. What I would like to do is join on userID from tableA with concat of firstName and lastName from tableB.

Is this possible in MySql? I have seen some proposed solutions for SQL Server.

Instead of displaying emailAddress column as per below view, I want to display a concat of firstName and lastName columns.

create or replace view document.Documents
as
select
    d.documentID
    ,cu.emailAddress as createdByUser
from document.Document as d
        left join customer.User as cu
            on cu.userID = d.recCreatedByUserID


texts: Is forcing T-SQL query to use parallelism OK?

texts:
It can be done by calling the trace flag 8649 in a query option.

OPTION (QUERYTRACEON 8649)

This trace flag causes the cost of the query to be 0, which will always be below the cost threshold for parallelism and therefore the query will be considered for a parallel plan.

Does attempting to force a T-SQL query to use parallelism in this way have any downsides?

sample tutorial how to force parallelism


texts: Is there much difference between having a key (intA, intB), or having key (longAB) where longAB is the concatenation of bytes of intA and intB?

texts:
This is a bit of an odd question.  Let's go with MySQL for example.  I don't know a ton about db internals.

Compare two scenarios, in one, we have a table with a composite key of the from (intA, intB).  Queries filter by range intA, and the intB portion of the key is to enforce uniqueness.  So we might do WHERE intA > 0x123 AND intA < 0xabc

The alternate scenario is where we have key (longAB), where longAB is the byte concatenation of intA and intB, in other words intA << 32 | intB.  Now our queries look like WHERE longAB > 0x12300000000 AND longAB < 0xabc00000000

So we'd get back the equivalent rows.

My question is, would these have substantially different impact on space usage, IO intensity, and why?

Thanks


texts: I am asked to prepare a Database Strategy for my Company? My company is using MSSQL Server 2016 with 1 instance and 10 databases. What should I include in the Database Strategy? Any format or template?


texts: How to find existing instances "instance root" (MS SQL)?

texts:
I'm installing a new instance of MS SQL. There is a step asking to specify instance root. The current DBA is off but I want to follow his logic / way of doing things.
The installer lists the existing instances but does not specify the configured paths. A quick google search doesn't reveal anything useful either.
The question is in the title.


texts: Filtered index only used when the filtered part is in the JOIN not the WHERE

texts:
I've created the filtered index below however when I run the 2 queries further down this index only gets used for a seek in the first example that has the END_DTTM in the JOIN rather than the where clause (that's the only difference in the queries). Can anybody explain why this happens?

Index Creation

CREATE NONCLUSTERED INDEX [ix_PATIENT_LIST_BESPOKE_LIST_ID_includes] ON [dbo].[PATIENT_LIST_BESPOKE]
(
    [LIST_ID] ASC,
    [END_DTTM] ASC
)
WHERE ([END_DTTM] IS NULL)

Queries

DECLARE @LIST_ID INT = 3655

--This one seeks on the index

SELECT
    PATIENT_LISTS.LIST_ID
FROM
    DBO.PATIENT_LISTS
    LEFT JOIN DBO.PATIENT_LIST_BESPOKE ON PATIENT_LISTS.LIST_ID = PATIENT_LIST_BESPOKE.LIST_ID
                                      AND PATIENT_LIST_BESPOKE.END_DTTM IS NULL
WHERE
    PATIENT_LISTS.LIST_ID = @LIST_ID

--This one scans on the index

SELECT
    PATIENT_LISTS.LIST_ID
FROM
    DBO.PATIENT_LISTS
    LEFT JOIN DBO.PATIENT_LIST_BESPOKE ON PATIENT_LISTS.LIST_ID = PATIENT_LIST_BESPOKE.LIST_ID
WHERE
    PATIENT_LISTS.LIST_ID = @LIST_ID AND
    PATIENT_LIST_BESPOKE.END_DTTM IS NULL


texts: Can having a LIMIT on DELETE degrade performance dramatically?

texts:
I have a database project in php and mysql.  For, hopefully redundant, data security I added a LIMIT 1 to the delete($uniqueID) php function we use.

Nobody cared because records really aren't supposed to be deleted often from this database.  However we recently got a massive jump in the response time for this server.  By massive I mean approximately four times worse than our previous worst heavy load performance.

The culprit seems to be a series of 100 individual DELETE statements from a small table, about 7 columns and 150 records.  This doesn't seem right.  Is it possible that this newly added LIMIT 1 is obliterating performance?

I don't think so, but the data logging I have suggest that it is.


texts: SQL Server - Can I surgically remove a bad cached query plan or am I chasing the wrong idea?

texts:
Let me set the stage:

SQL Server > 2016
Several databases with the same schemas
Data is similar but not identical
The same indexes exist etc., all systems but one work as expected
Examining the execution plan recommends the creation of an index with ALL of the columns as INCLUDE. The same base column already exists without the includes.

The problem:

On ONE of the databases, the query engine does not utilize an existing index and uses a full table scan
Manually executing the query (which uses a parameter) - full table scan!
Manually executing the query without a parameter (used fixed date) - uses the index.
Manually executing the query with a parameter and adding WITH (INDEX(update_ts_INDEX)) - uses the index. The query goes from 2 minutes to less than a second.

Hypothesis:

I believe that the query cache might be corrupt - but I don't know if that is possible. By trade I am a java developer who has spent the last 3 years digging into SQL server performance tuning, etc.
The fact that manually executing the query without a parameter - the query engine picks the right index - blows my mind. Ideas?

Notes:

On the other servers, which run the same code and queries, I can find NO evidence of this same query taking any time. They must be using the index.
I just executed the command manually, in another region with a parameter, and it used the index so the issue is indeed in ONLY one region - one database.

Example of query:
DECLARE @P1 DATETIME = GETDATE() - .1;

SELECT value_1, update_ts, value_2 FROM PRODUCTION_TABLE
WHERE (PRODUCTION_TABLE.update_ts > @P1);

If I remove the variable and handcode a date, it uses the index.
Question:
Can I surgically remove a bad cached query plan for this particular query?
--- UPDATE ---
Using OPTION (RECOMPILE) causes the query engine to pick the correct index.


texts:
I'm trying to upgrade from 9.2 to 9.5 using the pg_upgrade tool.

I'm using CentOS 7.0

current postgres bin folder: /opt/rh/postgresql92/root/usr/bin
new postgres bin folder: /opt/rh/rh-postgresql95/root/usr/bin
current data folder: /mnt/postgres/data
new data folder: /mnt/postgres/data95

I've set the LD_LIBRARY_PATH=/opt/rh/rh-postgresql95/root/usr/lib64

When I run

/opt/rh/rh-postgresql95/root/usr/bin/pg_upgrade --link --check --old-bindir=/opt/rh/postgresql92/root/usr/bin --new-bindir=/opt/rh/rh-postgresql95/root/usr/bin --old-datadir=/mnt/postgres/data --new-datadir=/mnt/postgres/data95

I get this message :

Performing Consistency Checks
-----------------------------
Checking cluster versions                                   /opt/rh/postgresql92/root/usr/bin/pg_ctl: error while loading shared libraries: libpq.so.postgresql92-5: cannot open shared object file: No such file or directory

Could not get pg_ctl version data using "/opt/rh/postgresql92/root/usr/bin/pg_ctl" --version: No such file or directory
Failure, exiting

I'm not sure why it is trying to use the 9.2 version of pg_ctl

which pg_ctl

returns

/opt/rh/rh-postgresql95/root/usr/bin/pg_ctl

What have I done wrong?


texts: Linked Server stops responding for particular login

texts:
We have an issue where the Linked Server does not work for the app team login but tests successfully when the DBA checks the connectivity. On investigating we found that the app login had some Killed/Rollback sessions which are causing this issue. The only resolution we have now is to Restart the instance.

Multiple sessions from the app login which are in Killed/Rollback and would not do anything (0 CPU Time and 0 Disk IO). This is a report login which extracts reports all day and sometimes this happens.

Is there any way to fix it without an instance restart?


texts: How to add a volume to current in use MySQL database

texts:
related to the problem I faced here No space left on device, I want to adopt a long term solution about the point no space left on device. One of the action includes to add a volume to the machine in which the database runs (MySQL 8, Ubuntu 20.04 and Digital Ocean provider).
I would like to know which one is safest way and rules to follow in order to avoid loosing reference and/or data when I will add a volume on the machine.
Considering that this database is running in production without any replication.
Thank you in advance for the support


texts: Allocate each value, considering what would be inserted

texts:
I'm stuck in a challenge where maybe you can help me.
This is what my data sets for the tables look like:
entities
Object
Payment
Invoice
InvoiceItem
Payoff

Object has N payments
Invoice has N invoice items
Payment has many payoffs
Invoice items have many payoffs
Payoffs is the new N-N break entity between invoice item and payments

Object ->payments -> payoff <- invoiceItem <- invoice
object
id.  amount.    name
1.   500.           Iphone
2.   20.             Additional cover for iPhone
3.   100.           Fuel

payment
id       amount         object.     desc
1        400            1        first collect
2        40             1         second payment
3        20             2        Paid with cc
4        100            3.       Transaction Kk2b

invoice
Id         total.       paid.      name
1          400.        true       Jonny
2          19.          true       Jonny
3          100.        true       Manuel
4          ...

invoice_item
Id        Invoice         object          amount
1         1               1               400
2         2               1               9
3         2               2               10
4         3               3               100

I created a table invoice_payoff like this:
create table invoice_payoff (
 id (int 11) primary key auto increment,
 payment int(11),
 invoice int(11)
 Invoice_item int(11)
 amount decimal(10,2)
)

How can I assign, using a query (no stored procedure here), the value of the table payment to cover each invoice?
So as to fill invoice_payoff.
As suggested from comments I try to rappresent the final result of invoice_payoff
Example payoffs
Id.       Payment.        Invoice.         I.Item  amount
1.      1     1       1     400
2.      2      2       2     9
3.      3      2       3     10
4.      4      3       3     100

How the amount comes up

The first payment(relative to obj1) of 400 can cover the invoice of 400.
The second payment (relative to obj1 too) can cover the invoice item of 9, so just 9 are used and 31 leaved alone.
The third payment can cover the invoice item of 10 relative to object 2.
4th payment covers the only invoice item of the 4th invoice, 3rd element

What I've tried:

Before this issue, I've already joined all tables and generated the payments for all invoices that was registered as paid. For this payments is easy to generate the payoff because one payment, one payoff.

I tried to retrieve the sub query of available payments for each invoice, sort them by decreasing price, and selecting the first one that can cover the invoice. I was near to create a valid script, but I think it wasn't enough because theorically I should have to execute the script many times.


texts: pg_ctl says "no server running" when I'm connected to postgres and running queries

texts:
EDIT: the original question can be found below. Basically, this is a misconception on my part of how pg_ctl works and if you're getting this message too (from pg_ctl), then the TLDR; is, as @jjanes explains in the accepted answer:

pg_ctl doesn't track your database. It interacts with a specific directory, either specified by -D, or through the PGDATA env variable.

So the postgres you're connected to must have been started somehow, maybe not by you, but was given a PGDATA directory to work with.
pg_ctl is not your interface to multiple running postgres instances.

(original question):
I created a directory in postgres user's home dir called mydb: /var/lib/postgresql/mydb
I did this using initdb -D mydb.
Now, I want to start postgres and use mydb as the location where all this db's data is stored; so I run:
pg_ctl -D /var/lib/postgresql/mydb start

It starts successfully, but if I try:
psql (12.6 (Ubuntu 12.6-0ubuntu0.20.04.1))
Type "help" for help.

postgres=# show data_directory;
       data_directory
-----------------------------
 /var/lib/postgresql/12/main
(1 row)

i.e. it's using the "main" or default data directory, completely ignoring the -D argument.
So now it occurs to me that I might be connecting to a different db... even though running pg_ctl status before pg_ctl start gives me:
pg_ctl: no server running

However, this has to be the case because... with "no server running", I am able to psql and query the main database.
So then, how do I get the main database "tracked" by pg_ctl? Also, how do I use psql to connect to the right database?
Thank you
Edit: was able to connect to the postgres started with pg_ctl using:
psql -h /var/run/postgresql

(even though I don't see any changes to that directory's contents before / after I pg_ctl start i.e. it remains: 12-main.pg_stat_tmp  12-main.pid).
Edit: Also I can see that the postgres process not being tracked by pg_ctl was started using postgres executable (from running pstree -a):
postgres -D /var/lib/postgresql/12/main -c config_file=/etc/postgresql/12/main/postgresql.conf

Probably from a startup file. I would imagine I need to find this startup file and replace use of postgres with pg_ctl...


texts: Extract Archived Log Files from RMAN Backupset

texts: I have an RMAN backup that is taken on 19th january with: BACKUP DATABASE PLUS ARCHIVELOG command. Now I need to restore Archived logs from that backupset to some other OS directory. Is there any way to extract Archived Log files from that backup?


texts: How does one identify if Oracle Data Guard is enabled in Oracle database?

texts:
How does one identify if the Oracle Data Guard feature is enabled in Oracle database?

If this can be determined by a query, what is the query for finding the same?


texts: CXPACKET Waits performance tune for SQL Server 2008

texts:
I have a SQL Server 2008 query that is working on millions of records.  The query is w/in a proc that is run nightly by a job.  The query can take a full day to run when I first put it on the server, but within a week or so it will drop down to less than one hour--without any intervention from me.  It FIXES ITSELF somehow.

The query runs in tempdb and, before it fixes itself, when I examine performance stats on it I find the following:
CXPACKET: 20,700sec or 66% of wait time.
PAGEIOLATCH:_SH 2,500 or 8% of wait time.
LOGBUFFER: 1500sec or 5% of wait time
IO_COMPLETION: 1500sec or 5% of wait time

I tried to tune indexes, etc. and the stats above are somewhat of an improvement over my first run when CXPACKET was 77% of wait time.  I read trouble shooting tips that said I should split my tempdb into one file for each CPU.  I have a dual cpu 32 bit W2K8 system and so I split tempdb into 2 files and greatly increased the size of each to 150 GB each 10% autogrow, but they aren't growing so I think the size is sufficient.

When I looked at the server while the query was running I could see that the CPUs were NOT pinned and were hovering down around <10% of their capacity.  What was pinned was DISK IO.  The machine has a single disk.

Without further ado, here are the two queries causing the trouble (the first query used to be a subquery of the latter-see explanation below):

insert into #ttcbm(tradeId1, tradeId2)
select distinct tp.tradeid tradeId1, tp1.tradeid tradeId2
from #tradeP tp
join #tradeP tp1
on tp.cmbId = tp1.cmbId
and tp.qs_plyrid = tp1.qs_plyrid
and tp.tradeId > tp1.tradeId
OPTION (MAXDOP 1)

insert into #mergeT(tradeId1, tradeId2)
select distinct tp.tradeid tradeId1, tp1.tradeid tradeId2
from #tradep tp
join #tradep tp1
on tp.cmbId = tp1.cmbId
and tp.tradeid > tp1.tradeId
left join #ttcbm x
on tp.tradeId = x.tradeId1
and tp1.tradeId = x.tradeId2
where 1 = 1
and x.tradeId1 is null
and x.tradeId2 is null
OPTION (MAXDOP 1);

I added MAXDOP 1 per a troubleshooting tip I read that said CXPACKET was caused by parallelism, and perhaps it did help drive down my waits a bit, but not like the improvement that happens when the query fixes itself, i.e., from 24 hours to  lest than 1 hr.

the #ttcbm table has a PK of tradeid1, tradeid2 and #tradep has a pk of (cmbId, qs_plyrid, tradeid) and both tables have record counts on the order of 100K to 500k.  #ttcbm used to be a subquery of the latter 'insert into #mergeT' query, but I separated it out when I read that separating out complicated queries can improve performance when parallelism is a problem.


texts: PostgreSQL function parameters JSON to TYPE

texts:
I try to connect my NodeJS API to my database.

I have created some type of object for the transport.
I use LANGUAGE plpgsql.

One example :

API_CAR: this type exists on my database

I receive an object API_CAR in JSON

{
"car_id": 18,
"car_brand": "AUDI",
"car_model": "A4",
"car_nb_places": 5,
"car_picture_url": "http://...",
"horse_power": 3
}

And I would like to pass the data directly to my function :

SELECT * FROM carPATCH( ... );

I know that I can CAST it like this:

SELECT * FROM carPATCH
(
   (18,'et','vel',5,'Donec dignsque',3):: API_CAR
);

But it's complicated to do this trick.

My question is what you would advise:

Is it better to do this transformation outside the database in NodeJS or can I pass this JSON directly inside and transform it after?

I think it should be possible to transform a JSON to another type but I don't know how to do it.

This is a simple example, but I have some big structure to forward to the database.


texts: MYSQL Get column group with different values on other column


texts: Is sp_stoppublication_snapshot considered a undocumented procedure or is my searching just bad?

texts:
I am trying to start and stop the replication snapshot via t-sql. I found sp_startpublication_snapshot, and doing a trace while hitting the stop on the snapshot from inside SSMS I see it calls sp_stoppublication_snapshot however I don't see any documentation at all on the procedure.

Is this considered a "Undocumented procedure", I thought they all started with sp_MS...?

If this is a undocumented procedure is there a better way to stop the snapshot agent?

I did look in to sp_stop_job, the only thing that makes me hesitant to just use it is the remark in the documentation of

  sp_stop_job should be used only in extreme circumstances if the job contains steps of type CmdExec or PowerShell.

I assume sense the job only has two "Transact-SQL script" type steps and a "Replication Snapshot" type step it would be safe to call, is that correct? Does sp_stoppublication_snapshot do anything extra that calling sp_stop_job on the job would not do?

If I want to write scripts that is safe to use on Sql2008 through Sql2016+ and up what would be the best approach to do?


texts: Possible to rewrite this query as a join?

texts:
I'm fairly certain it's possible to rewrite this as a join query but I'm just not sure how to approach it:

SELECT
   events.*,
   (SELECT MAX(updated_at)
    FROM   events e
    WHERE  e.user_id = events.user_id
           AND type = 'follow'
           AND e.updated_at < events.updated_at
           AND events.type = 'unfollow'
   ) AS last_follow_date
FROM   events
WHERE  user_id = 1;

Database structure:

CREATE TABLE `events` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    `user_id` int(10) unsigned NOT NULL,
    `type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
    `follower` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `follower_pk` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    PRIMARY KEY (`id`),
    KEY `events_user_id_foreign` (`user_id`),
    CONSTRAINT `events_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `users` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `users_email_unique` (`email`)
 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Some data:

INSERT INTO `users` (`id`, `name`, `email`)
VALUES (1, 'Aydin Hassan', 'email@website.com');

INSERT INTO `events` (`id`, `created_at`, `updated_at`, `user_id`, `type`, `follower`, `follower_pk`)
VALUES
(1, '2015-01-01 00:00:00', '2015-01-01 00:00:00', 1, 'follow', 'annie.leibovitz', '10'),
(2, '2015-04-04 00:00:00', '2015-04-04 00:00:00', 1, 'unfollow', 'annie.leibovitz', '10'),
(3, '2015-04-04 00:00:00', '2015-04-04 00:00:00', 1, 'follow', 'edward.weston', '11'),
(5, '2015-10-10 00:00:00', '2015-10-10 00:00:00', 1, 'follow', 'annie.leibovitz', '10'),
(4, '2017-01-01 10:10:00', '2017-01-01 10:10:00', 1, 'unfollow', 'annie.leibovitz', '10');

The table stores events regarding followers of a user on a makeshift social platform. The query is adding (if the event is a unfollow type) the last date that the follower started following the user.

The correct results should be:

+----+---------------------+---------------------+---------+----------+-----------------+-------------+---------------------+
| id |     created_at      |     updated_at      | user_id |   type   |    follower     | follower_pk |  last_follow_date   |
+----+---------------------+---------------------+---------+----------+-----------------+-------------+---------------------+
|  1 | 2015-01-01 00:00:00 | 2015-01-01 00:00:00 |       1 | follow   | annie.leibovitz |          10 | NULL                |
|  2 | 2015-04-04 00:00:00 | 2015-04-04 00:00:00 |       1 | unfollow | annie.leibovitz |          10 | 2015-01-01 00:00:00 |
|  3 | 2015-04-04 00:00:00 | 2015-04-04 00:00:00 |       1 | follow   | edward.weston   |          11 | NULL                |
|  4 | 2017-01-01 10:10:00 | 2017-01-01 10:10:00 |       1 | unfollow | annie.leibovitz |          10 | 2015-10-10 00:00:00 |
|  5 | 2015-10-10 00:00:00 | 2015-10-10 00:00:00 |       1 | follow   | annie.leibovitz |          10 | NULL                |
+----+---------------------+---------------------+---------+----------+-----------------+-------------+---------------------+

As a bonus question: Is there maybe a better way to store and query this data?


texts: Convert clustered primary key to non-clustered and use another column for the clustered index

texts:
I have a large table with a primary key (GUID) that is also the clustered index. There is already a integer sequence based field. So I want to leave the GUID as the PK and make the integer column the clustered index.

I can't figure any way to do this except drop the original constraint and create new PK and and new clustered index. But this takes a long time and from what I gather, rebuilds the table twice, once to go from clustered index to heap, and then heap back to clustered index.

I can't do a table rebuild (create new, migrate data, swap names) since I can't have an outage.

Any ideas?

Version: SQL Server 2008 Service Pack 2, Developer/Enterprise.


texts: How does SQL Server generate a query execution plan that adds up to 6,000%?

texts:
Today I was on The Heap and was looking at a query plan for something I thought could be improved. However, it created something that shook my belief in the SQL Server query optimizer. Can I still trust sql-server if it cannot even count to 100%?

Characteristics of the table:

clustered on a non-identity column
12 indexes, one of them being the date_entered column in question
60,000 records
26 columns of various types and length
PAGE compressed table

Has anyone seen this before and what causes the plan to look so skewed?

And below from SQL Sentry Plan Explorer


texts: Scheduled stored procedure on all databases in my server

texts:
On SQL Server 2012 (not express edition) I'd like to schedule the execution of a stored procedure every hour.

On the same server I have lot of databases with the same structure, and I'm constantly adding new db.

How can I schedule the execution of the same stored procedure on all the databases (if this exists in the database)?


texts: How to test encryption on Amazon RDS MySQL?

texts:
Our company is creating a MySQL RDS with encryption enabled. Now, as we are new to RDS, we have couple of questions related to RDS.

Once we create a MySQL RDS with encryption enabled, how will we able to confirm that the RDS is actually encrypted?
Whether encrypted RDs is disk/file level encrypted or db level encrypted? I have done some searching, but could bot able to get a clear answer.


texts: Using Oracle Datapump when target table has additional column

texts:
Table 1 has 5 columns. Table 2 has the same 5 columns, plus an additional non-nullable column. I would like to use datapump to copy the data from Table 1 to Table 2. In Sql Server, I can use bcp and specify a query to be used on export, in which I can "SELECT *, 'New Data'" from Table 1. This has the effect of putting the string 'New Data' into my new column in Table 2 when I import.

How would I accomplish this using Oracle's Datapump? From what I've read, I can only specify a WHERE clause, which means I can't add 'New Data' to my new column.

So far the only idea I've come up with is to disable the not null constraint on the new column in Table 2, datapump the data over from Table 1, and then update all rows to add 'New Data' into the new column. Is that the only option I have?


texts: SQL Operating system error 5(Access is denied.)

texts:
Having trouble restoring .bak file located on desktop.

My query

RESTORE DATABASE MyDatabase
FROM DISK='C:\Users\Mason\Desktop\48WSDB2_backup_2014_10_01_010027_0576172 - Copy.bak'

results in

  Msg 3201, Level 16, State 2, Line 1 Cannot open backup device
  'C:\Users\Mason\Desktop\48WSDB2_backup_2014_10_01_010027_0576172 -
  Copy.bak'. Operating system error 5(Access is denied.). Msg 3013,
  Level 16, State 1, Line 1 RESTORE DATABASE is terminating abnormally.

I am an administrator
I ran SQL Server as an administrator.
I have allowed myself full rights to myself for the desktop.

I think it might have something to do with service accounts?


texts: Alternative to removing ONLY_FULL_GROUP_BY

texts:
After upgrading from MySQL 5.5 to MySQL 5.7 I'm getting an error with some of my queries:

  ERROR 1055 (42000):

  Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'grocery.Product_Category.category_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I did my research and found the cause of the problem and how to solve it, basically I just need to remove ONLY_FULL_GROUP_BY from @@sql_mode and  everything will work again.

However I was wondering if that is the right course of action. Is there an alternative for this, maybe a better way to build the query?

This is my case (http://sqlfiddle.com/#!9/6f1bd):

I have two tables (I simplified their structure here but is basically the same): Product and Category and a many to many relationship table to allow products to belong to more than one category:

SELECT * FROM Product;

+------------+---------+
| product_id | name    |
+------------+---------+
|          1 | Tomato  |
|          2 | Orange  |
|          3 | Banana  |
|          4 | Lettuce |
|          5 | Carrot  |
+------------+---------+
5 rows in set (0,00 sec)

SELECT * FROM Category;

+-------------+------------+
| category_id | name       |
+-------------+------------+
|           1 | Fruits     |
|           2 | Vegetables |
+-------------+------------+
2 rows in set (0,00 sec)

I want to get the products from both categories so the simplest query wold be:

SELECT * FROM Product JOIN Product_Category USING(product_id)
  JOIN Category USING(category_id);

+-------------+------------+---------+------------+
| category_id | product_id | name    | name       |
+-------------+------------+---------+------------+
|           1 |          1 | Tomato  | Fruits     |
|           1 |          2 | Orange  | Fruits     |
|           1 |          3 | Banana  | Fruits     |
|           2 |          1 | Tomato  | Vegetables |
|           2 |          4 | Lettuce | Vegetables |
|           2 |          5 | Carrot  | Vegetables |
+-------------+------------+---------+------------+
6 rows in set (0,00 sec

But if a product exists in both categories I just want it once, doing a DISTINCT select won't help since the category_id differs:

SELECT DISTINCT * FROM Product JOIN Product_Category USING(product_id) JOIN Category USING(category_id);

+-------------+------------+---------+------------+
| category_id | product_id | name    | name       |
+-------------+------------+---------+------------+
|           1 |          1 | Tomato  | Fruits     |
|           1 |          2 | Orange  | Fruits     |
|           1 |          3 | Banana  | Fruits     |
|           2 |          1 | Tomato  | Vegetables |
|           2 |          4 | Lettuce | Vegetables |
|           2 |          5 | Carrot  | Vegetables |
+-------------+------------+---------+------------+
6 rows in set (0,00 sec)

So with MySQL 5.5 I used a GROUP BY clause on the product_id field:

SELECT * FROM Product JOIN Product_Category USING(product_id)
  JOIN Category USING(category_id) GROUP BY product_id;

+-------------+------------+---------+------------+
| category_id | product_id | name    | name       |
+-------------+------------+---------+------------+
|           1 |          1 | Tomato  | Fruits     |
|           1 |          2 | Orange  | Fruits     |
|           1 |          3 | Banana  | Fruits     |
|           2 |          4 | Lettuce | Vegetables |
|           2 |          5 | Carrot  | Vegetables |
+-------------+------------+---------+------------+
5 rows in set (0,00 sec)

Which effectively removed the duplicate, I know the result is not deterministic but I don't care if Tomato appears listed in Fruits or Vegetables category, all I care about is getting it only once in the result set.

But this query with MySQL 5.7 causes the error mentioned above.

So, my question is: Is there another (perhaps better) way to get the same result without having to remove ONLY_FULL_GROUP_BY from @@sql_mode?


texts: adrci not purge trace files and alert logs

texts:
I try to purge some files in oracle. I use adrci. I want to purge trace files in listener file. But it's not work. I don't know why.

[grid@orcl1 app]$ adrci

adrci> set homepath diag/tnslsnr/orcl1/listener
adrci> purge -age 360 -type alert
adrci> purge -age 60 -type trace
adrci> purge -age 1 -type trace
adrci> exit

Tracefile still full with 4.9 gb.

[root@orcl1 trace]# ls -alh
total 4.9G
drwxr-xr-x  2 grid oinstall 4.0K Nov  2 12:43 .
drwxr-xr-x 14 grid oinstall 4.0K May  7 14:48 ..
-rw-r-----  1 grid oinstall  43M Aug 28  2014 listener_backup_20140828.log.bz2
-rw-r-----  1 grid oinstall 4.8G Nov  2 12:43 listener.log
[root@orcl1 trace]# pwd
/u01/app/grid/diag/tnslsnr/orcl1/listener/trace


texts: Optimal design for 90mm row clickstream MySQL

texts:
Question
Which optimizations should I employ to make this DB manageable?  I've read a lot of posts about optimizing big dbs, but I'm pretty new so it's difficult to determine which techniques are suitable for my setup.
DB Info
I loaded about 90 mil rows from flatfile csvs into a mysql database (aws RDS, if that matters).  There are 60 columns and each row is a click on a website.
Datatypes: All datatypes are text because of limitations with the mysqlimport utility (specifically, its treatment of null vs. 0 values in numerical fields, which i could be wrong about)
Each column falls into one of these categories:

Visit info: time, referrer, etc. Many pages log millions of visits (front page, for ex.), some log only a few.

User info:  city/state, etc.  Users log anywhere from 1 to thousands of visits, with the distribution skewing right.

Page info: url, content flags, etc.

IO - The database only has 4 users, so will not have a lot of i/o, mostly just queries to populate dataframes in our python data analysis environment.
indexes - none besides the default generated index.  Candidates for indexing are user ID and page view timestamp (potentially a multi column index?)
Options
I welcome any other ideas, but this is what I've come up w/ with some searching.  Interested to hear which steps are worthwhile and which aren't.

right-size the datatype of each field -- does this offer performance boost? most fields are text or boolean flags.

break table into at least 2 others, e.g., visit info and page info.

Keep it in one table and partition it. From what I've read, I think partitioning on the most frequently queried field is the optimal choice.


texts: Counting using window function with partition over an empty table

texts:
If i have an empty table (created but without rows in it), and i would like to get the number 0 from the below query, how i could achieve that?

select count(*) over (partition by field_name)
from my_empty_table

Right now the postgres doesn't emmit any results as the table is empty.


texts: 9000 rows affected message, but no rows actually changed

texts:
I'm having a problem with MySQL. When I run the code below, via phpmyadmin:

UPDATE wp_posts
    SET post_content = REPLACE
        (
        post_content,
        '31.media.tumblr.com/',
        '41.media.tumblr.com/'
        )
    WHERE post_content LIKE '%31.media.tumblr.com/%'
    AND post_content NOT LIKE '%.gif%';

It returns:

  9000 rows affected

If I make a search, it shows that it replaced all the needed things successfully. But if I go and hit the 'EDIT' option in phpmyadmin on one of the rows inside the post_content column, no changes are done inside it; everything stays the same.

What might be the reason?

For me there's something wrong with the code above. Maybe it returns NULL or something and stops the update. If I remove this part:

AND post_content NOT LIKE '%.gif%';

...it does work properly.

The database is a default WordPress one:

Example of data inside post_content:

some text

other text

some other text
...


texts: listen_addresses does not respect postgresql.conf

texts:
I have PostgreSQL 9.3, and I'm trying to set it up on CentOS. One part that has been driving me nuts is the listen_addresses setting.

I set it to * inside postgresql.conf:

listen_addresses = '*'

but when I look inside psql, I see it as localhost:

postgres=# show listen_addresses;
listen_addresses
-----------------
 localhost
(1 row)

I do restart the postgresql instance after changing postgresql.conf. Also, show config_file verifies that I'm changing the right postgresql.conf file. Moreover, I run ps aux | grep postgres and make sure that there are no rogue command line parameters (like -i or -c) but only -D to set up the data directory.

When I do select name,source,sourcefile,sourceline,setting from pg_settings where name like  '%listen%'; I see that source is default, sourcefile and sourceline are empty for listen_addresses (the setting is, as I said above, localhost)

When I do netstat -na | grep 15432 (15432 is my PostgreSQL port, I set it up inside postgresql.conf and also as an environment variable PGPORT), I see something like following:

tcp 0 0 127.0.0.1:15432 0.0.0.0:* LISTEN

I think if listen_addresses were * I'd have 0:0:0:0:15432 rather than 127.0.0.1:15432

So, how can I solve this problem?

Relevant questions:

listen_addresses set to '*' in postgresql.conf disappears every reboot of Ubuntu

Howto disable Postgres listening on TCP?

http://postgresql.1045698.n5.nabble.com/pg-ctl-stop-failure-td5755968.html

How to connect to an remote PostgreSQL database on Ubuntu using pgAdmin3?

Edit 1 When I start postgres with -i option and do show listen_addresses;, I do see *.


texts: Can't delete from a table with two conditions using PHP

texts:
I am trying to delete a row from a table using two conditions.
this is my code :

    <?php require 'database.php'; $id=0;$od=0; if(!empty($_GET['id_espece'])){ $id=$_REQUEST['id_espece']; }if(!empty($_GET['id_valor'])){ $od=$_REQUEST['id_valor']; } if(!empty($_POST)){ $id= $_POST['id_espece'];$od= $_POST ['id_valor']; $pdo=Database::connect(); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

         $sql = 'DELETE FROM mode_valorisation WHERE "id_espece" = ? and "id_valor" = ? ';
            $q = $pdo->prepare($sql);
            $q->execute(array($id,$od));
            Database::disconnect();

             header("Location: page-valo.php");
        }
    ?>

<form class="form-horizontal" action="page-supprimervalo.php" method="post" style="background: #89e29a; border-color: black; border-radius: 20px; padding: 5px;width: 500px; position: relative;right: -480px;" >
      <input type="hidden" name="id_espece" value="<?php echo $id;?>"/>
      <input type="hidden" name="id_valor" value="<?php echo $od;?>"/>
</form>

I Tried the solution proposed here :

  https://stackoverflow.com/questions/24400675/how-to-delete-a-record-in-php-that-has-composite-multi-column-primary-key

but it gives me the following error : `

  syntax error at or near "limit" LINE 1: ..._valorisation WHERE
  id_espece = $1 and id_valor = $2 limit 1 ^

`


texts: MYSQL - Operand should contain 1 column(s) - Nested Case statement

texts:
I have written the below query with nested case statements. The syntax checks out but when I run it I receive error code 1241: Operand should contain only 1 column(s). I have attempted to remove the parenthesis from the SELECT statements but it does not help and indicates there is a syntax error.

This query is intended to select a data subset based on current time. The first case selects data from today's first shift. The second nested case determines if the current time the night portion of second shift (Between now and midnight 2nd shift) or the early morning portion of second shift between  midnight and 4:30. If it is after midnight then date from today and today-1 is selected. Any ideas?

SELECT CASE WHEN current_time() BETWEEN CAST('6:00:00' AS TIME) AND CAST('16:30:00' AS TIME)
    THEN
        (SELECT infeedweight, outfeedweight, yield, time_stamp, num_checks_passed, num_checks_failed,TIME(time_stamp)
            FROM workers_totals
            WHERE time_stamp >= curdate())
    ELSE
        CASE WHEN (current_time() BETWEEN CAST('16:30:00' AS TIME) AND CAST('23:59:59' AS TIME))
            THEN
                 (SELECT infeedweight, outfeedweight, yield, time_stamp, num_checks_passed, num_checks_failed,TIME(time_stamp)
                 FROM workers_totals
                 WHERE time_stamp >= CAST(CONCAT(curdate(),' 16:30:00') AS DATETIME)  and CAST(CONCAT(curdate(),' 23:59:59') AS DATETIME))
            ELSE
                 (SELECT infeedweight, outfeedweight, yield, time_stamp, num_checks_passed, num_checks_failed,TIME(time_stamp)
                 FROM workers_totals
                 WHERE (time_stamp >= CAST(CONCAT(curdate(),' 00:00:00') AS DATETIME)  and CAST(CONCAT(curdate(),' 04:59:59') AS DATETIME))
                 OR (time_stamp >= CAST(CONCAT(curdate()-1,' 16:30:00') AS DATETIME)  and CAST(CONCAT(curdate()-1,' 23:59:59') AS DATETIME)))
            END
    END


texts: Select all rows that have all of the following column values

texts:
I have the following tables Genres, Films, Directors. They have the following schema:
CREATE TABLE GENRES(
    GID INTEGER PRIMARY KEY,
    GENRE VARCHAR(20) UNIQUE NOT NULL
);

CREATE TABLE Films(
    FID INTEGER PRIMARY KEY,
    Title VARCHAR(45) UNIQUE NOT NULL,
    DID INTEGER NOT NULL,
    GID INTEGER NOT NULL,
    FOREIGN KEY (DID) REFERENCES Directors(DID),
    FOREIGN KEY (GID) REFERENCES Genres(DID)
);

CREATE TABLE Directors(
    DID INTEGER PRIMARY KEY,
    First_Name VARCHAR(20) NOT NULL,
    Last_Name VARCHAR(20) NOT NULL
);

I want to write a query that will allow me to select all of Director information for every director that has made atleast one movie in the same genre(s) as another director. For example if Stanley Kubrick has made films in genres 'Sci-Fi', 'Thriller', and 'Crime', I want to select all the directors who have made at least 1 sci-fi AND 1 thriller AND 1 crime film.
I've tried the query seen below but this will give me directors who have made atleast 1 sci-fi OR 1 thriller OR 1 crime film.
SELECT DISTINCT D.DID, D.First_Name, D.Last_Name
FROM Directors D
LEFT JOIN Films F
ON F.DID = D.DID
LEFT JOIN Genres G
ON G.GID = B.GID
WHERE G.Genre IN (
  SELECT DISTINCT G1.Genre
  FROM Generes G1
  LEFT JOIN Films F1
  ON F1.GID = G1.GID
  LEFT JOIN Directors D1
  ON D1.DID = D1.DID
  WHERE D1.First_Name = 'Stanley'
  AND D1.Last_Name = 'Kubrick'
);

Additionally, I am not able to check before hand which Genres the director in question has been involved with. The query should work with the only given information being the Directors First and Last name.


texts: How do I include nulls during comparisons in SQL Server?

texts:
So basically in SQL Server a NULL means there is no value and thus can't be compared which returns some unexpected results.

For example, the following query doesn't return rows where value IS NULL but I want it to:

SELECT *
FROM table
WHERE
  (value != 26)
  AND date IS NULL
  AND last_modified >= '5/21/2014'

I understand I can do the following as a workaround but seriously? Having to add parentheses and check if is null for every single field every single time I want to include it? Seems ugly, not intuitive and crazy.

SELECT *
FROM table
WHERE
  (value != 26 OR value is null)
  AND date IS NULL
  AND last_modified >= '5/21/2014'

I mean I know NULL is not a value and thus can't be compared but can't you infer that it is, in fact, definitely not 26? If 26 is something and NULL is nothing and nothing is not something then NULL is not 26. Seems logical to me.

Does anyone know how I can, in a cleaner way, include nulls in my results when using comparisons without having to include an explicit check each and every time. Also turning off nulls on my tables is definitely not an option.

Edit:

My real problem of why I don't want to do it the way I showed was not exposed. So here goes. I am writing a program that let's you build queries to database tables and let's the user dynamically create filters and such which essentially at the end of the day constructs a SQL statement and gets the results to display to the user. The fields chosen by the user can be any and/or all fields in a given database and if I have to literally put that ISNULL check on every single field that would be really inefficient and make looking at the SQL super ugly. My program is table-definition-agnostic meaning I don't care what's in your table and don't want to know what your table's definition is. I just want you to pick a table, choose some fields to filter on with equals, not equals, in, not in, etc ... and then click a button to view the results of that request.


texts: How do I compare Schemas for Multiple Databases to a True Copy?

texts:
We have a database for each customer, over 700 databases. All the schemas are supposed to be exact same.
We have Source Control database, and want to schema compare to the 700. Sometimes schema in Production get out of line/ drift away from actual Source Control Master.

How do I compare schemas from multiple databases to a Visual Studio SSDT Project, or a gold copy empty Database in Production?

I looked at various techniques, which may not be optimal.

Compare to SSDT Project

This person utilizes a recursive script: Schema Compare multiple databases at once

However author utilizes file size, which I do not like as difference indicator.

'My technique for comparing file sizes is to open a CMD Window by using Shift-RightClick on the folder they are in, choose ‘Open Command Window Here’, then run a ‘DIR’, which will list the files with the size in bytes. '

Extract the Gold .dacpac:
 sqlpackage.exe /a:Extract /scs:Server=MyLaptopSQL2014;Database=Test; /tf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac

 Compare to the target databases:
 sqlpackage.exe /a:Script /sf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac /tsn:MyLaptopSQL2014 /tdn:Test1 /op:C:UsersKevin3NFDocumentsSQLScriptsDACPACSDeltasTest1.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True
 sqlpackage.exe /a:Script /sf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac /tsn:MyLaptopSQL2014 /tdn:Test2 /op:C:UsersKevin3NFDocumentsSQLScriptsDACPACSDeltasTest2.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True
 sqlpackage.exe /a:Script /sf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac /tsn:MyLaptopSQL2014 /tdn:Test3 /op:C:UsersKevin3NFDocumentsSQLScriptsDACPACSDeltasTest3.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True
 sqlpackage.exe /a:Script /sf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac /tsn:MyLaptopSQL2014 /tdn:Test4 /op:C:UsersKevin3NFDocumentsSQLScriptsDACPACSDeltasTest4.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True
 sqlpackage.exe /a:Script /sf:C:UsersKevin3NFDocumentsSQLScriptsDACPACSTest.dacpac /tsn:MyLaptopSQL2014 /tdn:Test5 /op:C:UsersKevin3NFDocumentsSQLScriptsDACPACSDeltasTest5.sql /p:DropObjectsNotInSource=True /p:DropIndexesNotInSource=True

Compare to Gold Copy Database In Production:

This technique doesn't look at default indicators or index column names, triggers, stored procedures., etc

Different Ways to Compare SQL Server Tables Schema and Data

So what is the optimal way to compare schemas to many databases? I do not need to know the results, I just need a Flag indicator at end of day, Different Y/N.
It would be nice to use a DMV method and display yes/no results in a simple table, but SSDT will work also.


texts: Group values: CASE text, ELSE field value

texts:
I have a ROAD table:

+----+------------+
| ID | ROAD_CLASS |
+----+------------+
|  1 | ARTERIAL A |
|  2 | ARTERIAL B |
|  3 | ARTERIAL B |
|  4 | ARTERIAL C |
|  5 | ARTERIAL C |
|  6 | ARTERIAL C |
|  7 | COLLECTOR  |
|  8 | COLLECTOR  |
|  9 | LOCAL      |
| 10 | LOCAL      |
+----+------------+

The data type for the ROAD_CLASS field is NVARCHAR2.

I would like to create a view that groups all arterial roads in to a single ARTERIAL category, but leaves the other road classes as-is:

+------------+
| ROAD_CLASS |
+------------+
| ARTERIAL   |
| COLLECTOR  |
| LOCAL      |
+------------+

How can I do this?

What I've tried:

I've successfully done part of it:

SELECT
      (CASE
          WHEN ROAD_CLASS = 'ARTERIAL A' THEN 'ARTERIAL'
          WHEN ROAD_CLASS = 'ARTERIAL B' THEN 'ARTERIAL'
          WHEN ROAD_CLASS = 'ARTERIAL C' THEN 'ARTERIAL'
          --ELSE ROAD_CLASS
      END) AS ROAD_CLASS_GROUPED
FROM
    USER.ROAD
GROUP BY
      CASE
          WHEN ROAD_CLASS = 'ARTERIAL A' THEN 'ARTERIAL'
          WHEN ROAD_CLASS = 'ARTERIAL B' THEN 'ARTERIAL'
          WHEN ROAD_CLASS = 'ARTERIAL C' THEN 'ARTERIAL'
      END

+--------------------+
| ROAD_CLASS_GROUPED |
+--------------------+
| null               |
| ARTERIAL           |
+--------------------+

However, instead of the other road classes being output as null, I'd like to get their field values (COLLECTOR and LOCAL).

When I try adding ELSE ROAD_CLASS to the CASE statement (as demonstrated in the SQL comment) I get an ORA-12704: character set mismatch error.


texts: SQL server database log full for application databases

texts: We have an issue that our application database gets log full because we have a schedule job to write to a flat table for reporting purpose every hour in the application database. I wonder if we should create another reporting database, and write the data into the flat tables in reporting database, will it take care of the log full issue since we are now writing to a different database?


texts:
I'm experimenting with MySQL replication with MySQL 5.5.52.
My main problem is: how to do "apt-get upgrade" on a master-slave setup properly?

Until now I upgraded the slave then failed over and upgraded the other node as a slave.

So far It worked without problems, but now I found the following:
As I upgraded the slave it created some binlogs:

mysql.CREATE TABLE IF NOT EXISTS db (...)
mysql.CREATE TABLE IF NOT EXISTS host (...)
mysql.CREATE TABLE IF NOT EXISTS func (...)
...

As this was the slave node these changes are not replicated to the master, they are lost after the failover.
If I repeat this on the other node after its role is changed from master to slave, the same will happen.

So the changes made by the upgrade will never be committed to the replicated database.

Is this an issue?

How can I avoid this?

Can I upgrade the master-slave setup without (significant) downtime, at all?

Update:
So what happens if I do it like I did it in the past?

Node A : master
Node B : slave

On node B (slave): I issue apt-get upgrade, MySQL gets patched. Some binlogs appear on Node B, containing the statements like above. As this is a slave these changes are not replicated to the master.

After Node B is patched I switch the roles (with Corosync-Pacemaker).

After the failover:

Node A : slave
Node B : master

Now the Master MySQL on Node B contains the data that was on Node A prior the failover. As the changes caused by the patching were on a slave, they are NOT in the DB.

If I now patch Node A as a slave the same happens.

So in the end I will have a patched MySQL containing the exact same data it contained prior the patch. All changes the apt-get upgrade process made are "discarded".

^this is wrong^

So it is not discarded, but only applied as soon as the Slave (B) becomes master.

Does this mean I should not wait after patching one node (so patch them both in one run) and always keep the MySQL versions the same?


texts: Return values at more/less specific time points

texts:
In PostgreSQL 10.0, I have a big table with two columns:

CREATE TABLE xs (
  ts timestamp with time zone NOT NULL,
  x integer NOT NULL
)

Now, if I have 100 given time points, how do I return exactly 100 x-es that are right before (<=) these time points (for each of those points)?

For just a single one this would be very easy:

SELECT x FROM xs WHERE ts <= '2018-03-13 11:41:47.167973+00' LIMIT 1

But how to do this efficiently for a 100? Something like:

SELECT x FROM xs
  WHERE ts FIRST_LESS_THAN_EQUAL_IN
    ('2018-03-13 11:41:47.167973+00',
     '2018-03-13 11:41:47.198564+00',
     '2018-03-13 11:41:47.555668+00',
      ...
     '2018-03-13 11:41:57.766888+00')

Obviously, there is an index on ts.


texts: On EC2 Why do I need individual EBS volumes for journal, log, and data?

texts:
According to this MongoDB tutorial which explains how to manually deploy MongoDB on EC2, one of the steps states that you should have:

"Individual PIOPS EBS volumes for data (1000 IOPS), journal (250 IOPS), and log (100 IOPS)."

Why do I need individual EBS volumes for journal, log, and data?
Can I just combine these into one EBS volume?


texts: Trying to recover/import/transfer database from MySQL data dir

texts:
I used phpMyAdmin->export to create a backup of my database but I have now discovered it did not export the whole database, some tables was missed/refused.

However at the time of doing the export I also saved the whole data directory located at mysql/data/ which has many .ibd and .frm filetypes, which seem to reflect the tables I used to have.

I have tried to copy the entire data directory and paste it into my new setup, but I am not sure if the versions of MySQL / phpMyAdmin are the same, how can I find the MySQL version from my old data directory? Possibly in one of those .frm / .ibd files?

When I do paste the entire data folder, some of the tables in the database are working like normal but some display the following error.

#1033 - Incorrect information in file: '.\db\a_table.frm'

Also it seems every table which shows this same error message is marked in use under Collation in the Structure view in phpMyAdmin

What can I do to fix this? What does it mean?

I am wondering if I re-create my old OS setup, it could work?
Is there a better way?


texts: How to customize mssql-cli to remove Time line from output and return green/red for message output?

texts:
When ever I execute any command using mssql-cli version 1.0.0 command prompt tool there are two lines displayed:
Time: 0.252s
Commands completed successfully.

Questions:

Is there a way to remove line with "Time" info from output?
Is there a way if "Command completed successfully" to be displayed in green colour and if error appears in red colour? It would be easier to spot if there is everything OK or there is an error?

Regards


texts: Query to polybase external table giving error "Access to the remote server is denied because no login-mapping exists."

texts:
we have successfully created all pre-requisite of creating external tables using SQL Server 2017 Standard Edition CU22 Polybase.
The issue we have is the non-sysadmin login can't query those external tables .It shows below error.

Msg 7416, Level 16, State 2, Line 29
Access to the remote server is denied because no login-mapping exists.

The sysadmin login can query external tables without any issue.
Is there anybody saw this issue and can help to resolve it.


texts: SSAS Tabular model table load from query with #temp table fails

texts:
We have several situations where complex queries are being used for creating/populating tables in our SSAS tabular model.

The actual use cases cannot be presented here, due to both proprietary information and pure length of queries, but the following test case illustrates the situation:

SELECT s.Servername, i.instanceID, i.InstanceName, i.ClientVersion, i.MajorVersion
  INTO #InstanceList
  FROM ServerLookup s
 INNER JOIN InstanceLookup i
         ON i.ServerID = s.ServerID

SELECT i.Servername, i.InstanceName, i.ClientVersion, i.MajorVersion, im.EntryDescription, im.MonitorID
  INTO #InstanceMonitoring
  FROM #InstanceList i
 INNER JOIN InstanceMonitor im
         ON im.InstanceID = i.InstanceID

SELECT i.ServerName, i.instanceName, m.MonitorName, m.MonitorDescription
  FROM #InstanceMonitoring i
 INNER JOIN MonitorLookup m on m.MonitorID = i.MonitorID

The query runs fin in SSMS, but when we attempt to create a table in our tabular model, it throws the following error message:

  Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error. '.

There is no further information on the error.

I haven't been able to locate any documentation stating that temp tables can't be used in creating a tabular model table, and I'm reluctant to tell my developer community it can't be done without some information to back it up.


texts: Should Full Text Filter Daemon Service be a cluster resource?

texts:
Our newly installed and configured SQL Server Always On Availability Failover Cluster with SQL Server 2016 Enterprise Edition runs with four instances and six nodes. It includes the DB Engine Service and the Agent Service in each Cluster Node's resources. Everything works fine.
But now we figured that the fill-text Filter Daemon Launcher (FTFDL) service exists on each of the nodes and is randomly running, independant if the fact where the Sql-Instance is currently running. Some nodes have four FTFDL services running, while only one instance is located there. Others have one or two of them running.
Question: Should we add this service as a cluster resource? Why is it getting started and stopped abviously randomly on the failover cluster instances resutling in multiple services active without the relevant instance active? What is best practice here?


texts: Postgresql delegated authentication with S4U2 not working

texts:
I am trying to authenticate to Postgres with Kerberos using S4U2 delegation with a service principle impersonating the user.
I have delegated the principal 'HTTP/sp01.domain' to login as 'username' using S4U2. I am performing the following steps:

kdestroy -A

kinit -kt /etc/httpd/conf/httpd.keytab HTTP/sp01.domain

kvno -k /etc/httpd/conf/httpd.keytab -U username -P HTTP/sp01.domain postgres/db01.domain

klist

Ticket cache: KEYRING:persistent:0:krb_ccache_mqdrewy
Default principal: HTTP/sp01.domain

psql -h db01.domain -d test_db -U username

FATAL:  GSSAPI authentication failed for user "username"
LOG:  provided user name (username) and authenticated user name
(HTTP/sp01.domain) do not match

If i set the map like this, the authentication succeeds:

testmap    HTTP/sp01.domain       username

But the issue is that service principle 'HTTP/sp01.domain' has to impersonate as different users and each user has to be identified uniquely at Postgres level.

The issue seems to be that KRB5/GSSAPI authentication fails when user != principal (http://www.postgresql-archive.org/BUG-4824-KRB5-GSSAPI-authentication-fails-when-user-principal-td2129227.html)

How can I get Postgres to authenticate with a delegated user using S4U2?


texts:
Let's say I have a SELECT statement as follows:

SELECT ..., field1
  FROM ...
 GROUP BY field1

Now let's say I want to return a second field, field2. As it happens, field2 is functionally dependent on field1 (for example, field1 and field2 could be from the same table and field1 is the primary key of that table). I now have two equivalent ways to add field2 to the query:

Option 1:

SELECT ..., field1, field2
  FROM ...
 GROUP BY field1, field2

Option 2:

SELECT ..., field1, MIN(field2)  /* or some other, arbitrary aggregate function */
  FROM ...
 GROUP BY field1

Again, since field2 functionally depends on field1, both queries should return the same result set.

Are there any good reasons to prefer one option over the other?