texts: Is it possible to index a computed column used in ORDER BY?

texts:
I am having problems with my mysql server, because of not properly written queries. I did not use indexes, because I do not know how to add them on computed columns, in a query that has SELECT COUNT(*) AS b ... ORDER BY b.

Looks like it is impossible, so if it is indeed impossible, how could I re-manage my queries?

SELECT COUNT(downloaded.id) AS downloaded_count
    , downloaded.file_name
    ,uploaded.*
FROM `downloaded` JOIN uploaded
ON downloaded.file_name = uploaded.file_name
WHERE downloaded.completed = 1
AND uploaded.active = 1
AND uploaded.nsfw = 0
AND downloaded.datetime > DATE_SUB(NOW(), INTERVAL 7 DAY)
GROUP BY downloaded.file_name
ORDER BY downloaded_count DESC LIMIT 30;

EXPLAIN

+----+-------------+------------+------+---------------+-----------+---------+--------------------------+------+----------------------------------------------+
| id | select_type | table      | type | possible_keys | key       | key_len | ref                      | rows | Extra                                        |
+----+-------------+------------+------+---------------+-----------+---------+--------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | uploaded   | ALL  | file_name_up  | NULL      | NULL    | NULL                     | 3139 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | downloaded | ref  | file_name     | file_name | 767     | piqik.uploaded.file_name |    8 | Using where                                  |
+----+-------------+------------+------+---------------+-----------+---------+--------------------------+------+----------------------------------------------+

UPDATED:

with ORDER BY;

Showing rows 0 - 29 ( 30 total, Query took 0.1639 sec)

without ORDER BY;

Showing rows 0 - 29 ( 30 total, Query took 0.0064 sec)

UPDATED #2:

table: uploaded (Total 720.5 KiB)

CREATE TABLE IF NOT EXISTS `uploaded` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `sid` int(1) NOT NULL,
  `file_name` varchar(255) NOT NULL,
  `file_size` varchar(255) NOT NULL,
  `file_ext` varchar(255) NOT NULL,
  `file_name_keyword` varchar(255) NOT NULL,
  `access_key` varchar(40) NOT NULL,
  `upload_datetime` datetime NOT NULL,
  `last_download` datetime NOT NULL,
  `file_password` varchar(255) NOT NULL DEFAULT '',
  `nsfw` int(1) NOT NULL,
  `votes` int(11) NOT NULL,
  `downloads` int(11) NOT NULL,
  `video_thumbnail` int(1) NOT NULL DEFAULT '0',
  `video_duration` varchar(255) NOT NULL DEFAULT '',
  `video_resolution` varchar(11) NOT NULL,
  `video_additional` varchar(255) NOT NULL DEFAULT '',
  `active` int(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`id`),
  FULLTEXT KEY `file_name_keyword` (`file_name_keyword`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3328 ;

table: downloaded (Total 5,152.0 KiB)

CREATE TABLE IF NOT EXISTS `downloaded` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `file_name` varchar(255) NOT NULL,
  `completed` int(1) NOT NULL,
  `client_ip_addr` varchar(40) NOT NULL,
  `client_access_key` varchar(40) NOT NULL,
  `datetime` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=31475 ;


texts: mysql: ERROR 1040 (HY000): Too many connections and can't restart because of Error: 28 (No space left on device)

texts:
My app crashes because there are too many connections open and mysql does not make me log in as root showing the same problem.
After restarting mysql, it does not restart because of no space on disk


texts: Pull out top stored procs with high recompile rate

texts:
Is there any other easy way other than running profiler that I can pull out the worst offending stored procs with high recompiles?

Any helpful DMVs?


texts: What is difference between mongodump and mongoexport?

texts: I know that when we have to take full backup at database & collection level we use mongodump but with mongodump we can take backup of a particular record also so what is difference between mongodump and mongoexport?


texts:
I am designing a very small database (c. 250 rows). I have a number of datapoints for each record, which under normalization rules, ought to be in separate tables.

For instance:

Percentages of a number of different categories (which should add up to 100)
Multiple sources of data (for instance a record may come from up to 4 sources of data - the reference within these data sources should be recorded
Columns with very different units for instance I want to store residential units and Gross Floor area delivered (different units).

All these fields are optional, none are mandatory.

I am considering not normalizing in case 1, because I want a data integrity check that all the relevant columns in each database row add up to 100%.

For the other two cases, it seems that it is a lot of work to normalise for not a lot of gain. For 3, I would have to design 2 additional tables, one to store the data:

| ID(prim key) | project(FK)          | type(int) | value(float) |
|--------------|----------------------|-----------|--------------|
| 1            |  1                   | 1         | 200.3        |

and another to store the column metadata:

| ID(prim key) | typeID(FK)  | Description |
|--------------|-------------|-------------|
| 1            | 1           |    GFA(m2)  |

rather than just do this in the main table:

| RecordID(prim key) | GFA(m2) | Residential units | .... |
|--------------------|---------|-------------------|------|
| 1                  | 200     | 25                | .... |

With such small data, my main concern is updating things if the schema changes, but I can't think of a reason that will be harder with the de-normalised data.#

Can anyone give me a good reason to normalise for cases 2 and 3?

Thanks


texts: Dynamic access to record column in plpgsql function

texts:
How can I address a column from a record in a plpgsql function dynamically?

In the following snippet I have access to a variable entity.colname that contains the column that should be checked in the IF constrol structure. I'm looking into how I can replace the foobar part in the snippet below with entity.colname. Is that possible in plpgsql?

IF NEW.foobar IS NULL THEN
    RAISE EXCEPTION '% cannot be null', foobar;
END IF;

This is something that could but doesn't work.

IF NEW.entity.colname IS NULL THEN
    RAISE EXCEPTION '% cannot be null', entity.colname;
END IF;

The example above is just for illustration what I'm looking to achieve, don't judge the functionality. :)

I'm using PostgreSQL 9.1.


texts: What is proper database design for a drupal table with an order field?

texts:
I am adding a custom widget to an instance of Drupal 6.x & MySQL 5.5 and came across a problem with updating rows.

I have a table of recipe ingredients where multiple ingredients are tied to a single recipe by node id (nid) & version id (vid).

The primary key is vid, nid & order.

The vid & nid are related to the recipe nid & vid fields.

The table schema is:

+-----------------+------------------+
| Field           | Type             |
+-----------------+------------------+
| vid             | int(10) unsigned |
| nid             | int(10) unsigned |
| order           | int(10) unsigned |
| name            | varchar(255)     |
| unit_of_measure | varchar(32)      |
| quantity        | int(10) unsigned |
+-----------------+------------------+

The problem comes when attempting to reorder the ingredients.

For instance:

+-----+-----+-------+---------+-------------------+----------+
| vid | nid | order |  name   |  unit_of_measure  | quantity |
|  5  |  1  |   1   | Chicken |        Lb         |    1     |
|  5  |  1  |   2   |  Rice   |        Cup        |    2     |
|  5  |  1  |   3   |  Thyme  |        Tbsp       |    3     |
+-----+-----+-------+---------+-------------------+----------+

I want to move Thyme to the top of the list but I can't change the order for Thyme to 1 since that primay key already exists (Chicken).
I can't move Chicken down to order 2 because Rice is already there, etc...

My position is that we should add a unique auto-incrementing int field that will be the sole primary key.  Which will enable us to reorder the rows without incident with the possibility that two rows might end up with the same nid, vid, & order.

My coworkers position was that to add a unique auto-increment int field is bad design because there should never be two different rows that have the same vid, nid & order.  But following this belief there are two ways to implement a reorder of the rows

Update each row's order with some large number (ie- 1001, 1002, 1003) so that the original order is no longer conflicting, then update each row with the correct order values (1, 2, 3).
Delete each row that has the same nid & vid, then insert all the rows again in the correct order.

From the database's perspective, what is the correct approach?


texts: Any potential cons associated with enabling backup checksums when performing backups?

texts: I noticed that Ola Hallengren's backup sp has backup checksums disabled by default.  This made me curious to know if there are any potential cons (i.e. increased CPU, IO, long backup times, etc) associated with enabling backup checksums for sql server backups?


texts: What are the real life cases for using Oracle advanced queuing?

texts:
What are the cases when Oracle advanced queuing is the preferred mechanism for implementing functional requirements? For example, money transfer from bank account A to bank account B might theoretically be considered as two different operations, and might be implemented separately - first, enqueue money transfer from account A (update), then enqueue money trasfer to account B (update). However, it's obvious it can't be done like that because those two operation should be done in one consistent operation - in transaction.

Maybe advanced queuing should be only considered when developing stored procedures/functions that perform some logic that is both done internally (by doing DML operations and calling other local stored proc/fnc) and externally (by calling some webservices). When using such calls to webservices we can't wrap it all into consistent transaction, so the only way is to use some queuing mechanism...

Any real life detailed examples would be appreciated.

What I'm curious about is not the details of the technology itself, but rather what are the real life cases of using such messaging because I haven't done that before. Like, why do I need to pass some data (message payload) around?


texts: Query that Searches Duplicate Values Based on a Specific Value

texts:
I've been searching through forums all morning and haven't been able to make much progress. The system I work with has a particular table that will assign two unique values to a row - we'll say ID01, and ID02. ID01 is generated by the system locally, while ID02 is generated by an external interface that connects to our system. This normally works fine, except in the scenario that the interface might be running duplicates; in this case, it generates a unique ID02 from both instances of the interface, but both have the same ID01 - resulting in a duplicate entry in the user interface.

My goal is to write a query that can be run against the database that will show me all rows on the given table that have the same ID01, but a different ID02. I started with this, as this was what most of the forum questions I could find marked as the answer to similar questions:

SELECT Count(ID01), ID01, ID02
FROM Table
GROUP BY ID01, ID02
HAVING (COUNT(ID01) > 1)
ORDER BY ID01

The result I get back doesn't work the way I need it to, however - it gives me a list where both ID01 and ID02 are duplicate at the same time, which our system already corrects for automatically. I need it instead to show me every row where ID01 is the same (both the first and duplicate instance or instances), but ID02 is different. If it makes any difference, this is being done in SQL Server 2008.

Any help would be much appreciated.


texts: Why is column value going negative even with checks

texts:
I have a simple MySQL table with 3 columns id, balance and locked. I have a requirement where the locked column's value must be increased by x, sometimes by multiple processes running at the same time, such that balance - locked must always be at least x. The query for the same is as below:

UPDATE tablename
SET locked = (locked + x)
WHERE id = 1
    AND balance - locked >= x;

After the locked count is increased, the process does some job, and then comes back and reduces the balance and the locked counts using:

UPDATE tablename
SET balance = balance - x
    , locked = locked - x
WHERE id = 1;

The idea is to get lock for x items, then do some processing, and then reduce the balance by x and release the locks.

The issue I have is that after running the multiple processes for some time, at one point the balance column goes negative. What could be causing this issue?

I am aware of the race condition, and that is why I have the check in the where clause. The value still goes negative, even when balance is 2, and there are only 2 threads trying to lock 1 balance each.

I do check that the first update actually updates a row. Only if the first query affected rows is 1, then I go an do the processing work, and then reduce the locked counts. I ensure locked is never negative by having update query as

update tablename set locked = IF(locked - x > 0, locked - x, 0) where id = 1;


texts: Can I get pg_dump to simplify the ACL commands?

texts:
When using pg_dump with a database with non-default schema foo like so:

pg_dump --schema-only --schema='foo' -U myuser -d mydb -h myhost -W > mydb.sql

The SQL dump file is full of these seemingly redundant commands like this:

REVOKE ALL ON schema foo FROM PUBLIC;  -- Doesn't PUBLIC have no privileges by
                                       -- default on a schema created by myuser?
REVOKE ALL ON schema foo FROM myuser;
GRANT ALL ON schema foo TO myuser;  -- Why revoke at all if GRANT ALL is done after?

--
-- Again, why each of these revocation statements when myuser already owns
-- this schema? And why all of these duplicate revocation statements for PUBLIC?
--

REVOKE ALL ON {FUNCTION|TABLE|SEQUENCE} foo.some_thing(...) FROM PUBLIC;
REVOKE ALL ON {FUNCTION|TABLE|SEQUENCE} foo.some_thing(...) FROM myuser;
GRANT ALL ON {FUNCTION|TABLE|SEQUENCE} foo.some_thing(...) TO myuser;

There are numerous functions, sequences, and tables within the schema. So these commands take up a lot of lines and appear almost completely redundant.

I am not asking to remove all ACL commands with the --no-acl flag. I am asking why the ACL commands are so noisy and appear to be mostly redundant. I'd like to simplify them in the database or in pgAdmin3 so that a dump does not have all these lines. In other words, why can't it just be something like:

REVOKE ALL ON SCHEMA foo FROM PUBLIC;  -- If there are any privileges by
                                       -- default for PUBLIC, remove them.

I do not want to manually write this each time, I am asking if pg_dump can give the desired behaviour. Clearly I can write it myself, but that is not very helpful.

Note that this is for a database where I am not the superuser, myuser does not own mydb or the public schema of mydb.


texts: SP to return last ID and insert into a column using a trigger

texts:
I have written an SP to do the below:

i have a table called T1:
AS below:

CREATE TABLE [dbo].[T1](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [QNo] [nvarchar](50) NULL DEFAULT ('0'),
PRIMARY KEY CLUSTERED

T1 stores a number value in the Qno Column. AS so:
ID: 1            Qno: 0

When i insert into table QUDF, the last number (in this case currently is '0'), needs to be inserted into the QUDF column in the QUDF table. (it needs to fetch last number from T1 when inserting in QUDF)

columns in QUDF are:
Client_id, Contact_number, Code_ID, Type_id, QUDF

Once inserted, the Qno column in T1 needs to update to 1 (increment by 1 each time)

I have been on the forums but still nothing has resolved this. Please take note, only 1 row is being inserted at a time through the GUI

I have then created a trigger on the Opportunities table as this all needs to happen when an Opportunity is created by the user via the GUI.

SP:

ALTER PROCEDURE [dbo].[SP1_Test] @Opp_Id varchar (24), @Opp_Number int
    -- Add the parameters for the stored procedure here

AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    Declare @Qno varchar (50)
    Declare @NewQno varchar (50)

    SET @Qno= '0';

SELECT @NewQno =@Qno + 1 FROM T1 where ID = 1;

Insert into O_QUDF(Client_id,Contact_Number,Type_Id,Code_Id,O_QUDF)
values(@Opp_Id,@Opp_Number,1,0,@Qno)

Update T1 set Qno = @NewQno where ID = 1

END

GO

Trigger:
Trigger must only fire when Opp_type = 0

create TRIGGER [dbo].[Trg] ON  [dbo].[AMGR_Opportunity_Tbl] AFTER INSERT
AS
BEGIN

Declare @Opp_type varchar(24)
Declare @Opp_id int
Declare @Opp_number int

Select @Opp_type = Opp_Type from inserted
select @Opp_id = Opp_id from inserted
Select @Opp_number = Opp_Number from inserted

If @Opp_type = '0'

 BEGIN

Execute dbo.SP1_Test @Opp_id,@Opp_number

END

If someone can assist me in getting my scenario right - i am very new at this logic. I would greatly appreciate any suggestions.

SOLUTION to my issue was to simply add the following:

select @Opp_id = client_id from AMGR_User_Fields_Tbl;
Select @OppNo = Contact_number from AMGR_User_Fields_Tbl
 SELECT @NewQno = Convert(VARCHAR(750), Convert(INT, Qno) + 1)
    FROM T1 WHERE ID = 1;

To the code example below:

BEGIN
Declare @Opp_id varchar (24)
Declare @OppNo int
Declare @Opp_type int
DECLARE @NewQno VARCHAR(750)
declare @Qno varchar (50)

select @Opp_id = client_id from AMGR_User_Fields_Tbl;
Select @OppNo = Contact_number from AMGR_User_Fields_Tbl
 SELECT @NewQno = Convert(VARCHAR(750), Convert(INT, Qno) + 1)
    FROM T1 WHERE ID = 1;

If @Opp_type = '0'

 SELECT @NewQno = Convert(VARCHAR(750), Convert(INT, Qno) + 1)
    FROM T1 WHERE ID = 1;

    INSERT INTO O_QUDF(Client_id,Contact_Number,Type_Id,Code_Id,O_QUDF)
    values(@Opp_id,@OppNo,1,0,@NewQno)

UPDATE T1 SET Qno = @NewQno WHERE ID = 1

    End


texts:
We have asked our clients for certain data in a csv file format and we just got a .dmp file. I have been trying to open it using MySQL as if it were .dump but it doesn't work.

I have seen other posts where they restore .dmp files with Oracle but I have 0 experience with Oracle (and very little with databases overall). Is there any alternative to get the contents of the .dmp file into a csv format?


texts: Check Index Fragmentation on SQL Server Audit Queues

texts:
I've read this excellent article, http://rusanu.com/2010/03/09/dealing-with-large-queues/, and seem to be having the same issue, due to a low space issue, the service broker was turned off on a database, and the sys.transmission_queue table grew to around 40 million records.  Turning the broker back on significantly increased the size of tempdb, and now I'm wondering whether my service queues are suffering index fragmentation.

I would normally use dm_db_index_physical_stats to check for index stats, but this doesn't work on queues, so how do I found out about index stats on internal tables, or can I even do so in SQL Server 2005?


texts: Uniquifier on Non-Unique Clustered Index vs Unique Clustered Index

texts:
Lets say for example that I have a table structured this way:

ID1 int
ID2 int
ID3 int
DTM datetime

I'm only able to create a unique clustered index on the table if I include ID1, ID2, and ID3 (a combination of two of them or individually would lead to duplicate rows).

Would it be better performance wise to create a clustered index by ID1, ID2, and ID3? Or create a clustered index on just ID1 and have SQL Server add a uniquifier to the index to make each row unique?

I know that the uniquifier is 4 bytes in size (same for ints) so technically it would save 4 bytes per row if I make the latter index, but I'm not sure how this would affect my queries.


texts: Keep rows only if Date exists in both table

texts:
This is my first time here, so please bear with me.

Assuming i have 2 table,

Table 1
Date | Closing
1/1/2013 | 1
2/1/2013 | 2

Table 2
Date | Closing
1/1/2013 | 3
3/1/2013 | 4

What sql statements allow me to keep rows such that only Date that exists in both table remains?

End Result:

Table 1
Date | Closing
1/1/2013 | 1

Table 2
Date | Closing
1/1/2013 | 3


texts: Why BULK INSERT insert data in random order?

texts:
I have a csv file with 350,000 rows. Would like to insert data to temporary table in same order as is in csv file. I am trying to BULK INSERT using:

BULK INSERT ##AllRows
    FROM @FilePath
    WITH
    (
    FIELDTERMINATOR = '\n'
    )

Unfortunately BULK INSERT inserts data in random order. My header in every execution is in different row. I ran it on SQL Server 2016. Is there a possibility that in older versions of SQL Server order manner was different?

Using the FIRSTROW option does not recognize header as first row of file. File does not have any column for which we can order. In file always header is in first row.

It could be a coincidence but even with FIRSTROW=2 it is possibility that my header will be in table. I checked it. It looks like the more rows csv file contains the more chance that insert to table will be with random order.


texts: mariadb datadir change location raspberry pi

texts:
I've got a raspberry pi 3 and have an external HDD which I have yet to mount but what directory I make depends a little on how best I can move the datadir location.

So at the moment I have the basic install from apt-get install MariaDB and ive added a new user and then also installed phpmyadmin, everything is good.

But I'd like the data to be stored on the external hdd so it doesn't burn up the sd card.

I've read through a few online instructions, all with bad grammar/spelling and older versions of the software. For example they say to edit /etc/MySQL/my.cnf but the .cnf files seem to be different and I'm not 100% which files (or all files) need changing with the datadir and socket edits.

Which .cnf files need to be edited please?

Edit
If I add to /etc/MySQL/my.cnf the following
    [client-server]
    datadir=/my-new-directory
    socket=/my-new-directory/MySQL.sock
    port=3306
    socket=/my-new-directory/MySQL.sock

as described at https://www.tecmint.com/change-default-mysql-mariadb-data-directory-in-linux/

This would then be overwritten by the other .cnf files that are read after.


texts: PostgreSQL vagrant VM cannot create extension

texts:
I had some problems during installation of PostgreSQL and I eventually decided to go for a VM solution managed with vagrant, as explained in postgresql wiki.
(At the beginning my motivation was just to solve the installation issue but I find that a virtual machine solution has many advantages).
I started working with it and everything looked very good. But eventually I wanted to use crosstab(doc), however tablefunc extension was not installed in the virtual machine, and I can't find a way to install it.
The virtual machine creates a user for the application when you run it first time, which is fine, and it creates a database for the application as well. But if I try to install the extension with the application user
create extension tablefunc;

I get the following error (anyhow expected):

ERROR: permission denied to create extension "tablefunc"
SQL state: 42501
Hint: Must be superuser to create this extension.

The point is that I don't have the password for postgres user, so I cannot install the extension using pgAdmin. (This is already an issue that scares me a bit, if I have other problems and I need the administrator. But I've been browsing around and haven't found any comment about this.)
In postgres wiki, link above, it describes a way to connect to the VM as superuser, which I also tried,
vagrant ssh
sudo su - postgres

but again when I tried to run the command for the installation I was asked for the password of postgres user in the database. For example, one of the trials:
postgres@postgresql:~$ psql -h localhost -U postgres -d vic -c 'create extension tablefunc'
Password for user postgres:
psql: FATAL:  password authentication failed for user "postgres"

My question is then, if there is a way to install the extension for the vagrant VM (or anyway to run any command with the superuser). Also, I'll be much happier having the superuser password, so that I could manage everything from pgAdmin.
Many thanks for reading and for your help.


texts: Reading SQL Performance Counters using PowerShell script

texts:
I'm trying create a PS script to read some performance counter about my SQL for my Level 1 partners but I'm having a problem with the instance name.

I'm using this code:

$computer = "\\\" + $ENV:Computername
$instance = $computer + '\MSSQL$SG25DB'
write-Host $instance
Write-Output -InputObject "Use of Memory Buffer. The expected value must be greater than 80."
Get-Counter "$instance:Buffer Manager\Buffer cache hit ratio"

and the output is:

\\\\[hostname]\\MSSQL$SG25DB     <--- Here appears the proper value :)
Use of Memory Buffer. The expected value must be greater than 80.
Get-Counter : The specified counter path could not be interpreted. At
line:5 char:12
+ Get-Counter <<<<  "$instance:Buffer Manager\Buffer cache hit ratio"
+ CategoryInfo          : InvalidResult: (:) [Get-Counter], Exception
+ FullyQualifiedErrorId : CounterApiError,Microsoft.PowerShell.Commands.GetCounterCommand

I have verified with Write-Host that the value of variable $instance is properly constructed but when I apply it to the Get-Counter command its fails. Do you have any idea? Could be related with the special character $? How can I make run it properly?


texts: How to calculate Day on the base of Hours Condition.?

texts:
SELECT
    Empcode,
    cast(Date as date) As Date,
    MIN(CASE WHEN INOUT = 1 THEN date END) AS INOUT_INTIME,
    MAX(CASE WHEN INOUT = 2 THEN date END) AS INOUT_OutTime,
    DATEDIFF(Hour ,MIN(Date), Max(Date)) AS Hours
FROM
    HR2 AS t
GROUP BY
    Empcode,
    CONVERT(date, date);

I want when Hours=>8 then Day=1 and Hours<=8 then Day =0

please guide..


texts: Cannot login in as root user after MySQL installation (RHEL)

texts:
I am new to MySQL.
I installed MySQL (version 8) in RHEL 7.7 using yum . Now , I am trying to configure MySQL.

[root@devdbvm278 ~]# cat /etc/my.cnf
# commented lines removed for readability
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Once the installation was done, I started the mysqld using

# systemctl start mysqld

Now, when I try to log in as root user, I get the following error.
I tried to fix it using some stuff I found from googling. But, no luck.
Any idea what is wrong ?

[root@devdbvm278 ~]# mysql -u root
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@devdbvm278 ~]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@devdbvm278 ~]#

[root@devdbvm278 ~]# mysqld_safe --skip-grant-tables
-bash: mysqld_safe: command not found

[root@devdbvm278 ~]# mysqld --skip-grant-tables
2019-08-13T15:53:33.812294Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.17) starting as process 19578
2019-08-13T15:53:33.815432Z 0 [ERROR] [MY-010123] [Server] Fatal error: Please read "Security" section of the manual to find out how to run mysqld as root!
2019-08-13T15:53:33.815507Z 0 [ERROR] [MY-010119] [Server] Aborting
2019-08-13T15:53:33.815777Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.17)  MySQL Community Server - GPL.
[root@devdbvm278 ~]#

# mysqld --version
/usr/sbin/mysqld  Ver 8.0.17 for Linux on x86_64 (MySQL Community Server - GPL)


texts: SQL Server 2016 support for 32-bit systems

texts:
I understood that SQL Server 2016 was not going to support installation onto 32-bit machines. The cumulative update released on 15 November 2016 includes an x86 installer (SQL2016RTMCU3\x86\SQLServer2016-KB3194717-x86.exe).

Cumulative Update Package 3 for SQL Server 2016 RTM - KB3205413

I don't have a 32-bit machine to test this out on but would like to know what the situation is with 32-bit support for SQL Server 2016.


texts:
I install postgresql 9.2 on a Ubuntu 12.10 machine

I tried to import linux's user to postgresql

but only way I can figure out is

CREATE USER Jack WITH PASSWORD '1234';

and do it 10 times (if there are 10 users)!!

Is there any faster way to do it?


texts: SQL Server Cluster and Windows Server Storage Replication

texts:
I am currently investigating on how to setup a SQL Server stretch cluster in combination with storage replication for which I want to use Windows Server Storage Replication. However, I am no sure on how to configure the storage replication exactly and maybe you can help. :-)
I do not want to use Always On Availability Groups. In the end I want to use DTC and that does not seem to be supported in combination with Always On or SQL Server mirroring. The purpose of my setup is to have SQL Server highly available on site A and have a DR copy on site B, which also needs to be highly available if that is made active in case of a fail over. I configured a stretch cluster and that works fine, but I am struggling on how to configure the storage replication in combination with the CSVs.
I cannot find any documentation on the complete setup. I can find docs (MS and community) on how to configure Storage Replication in combination with Scale Out File Server or Hyper-V, but that does not talk on how configure the CSVs exactly and how to fail over to the other site. Every document says regarding the storage something like 'You need manual intervention to fail over'. I have read some documentation from Dell and HPE on how they use their storage replication solution, but that does not give me those details. How and what exactly you need to do on the Windows cluster side is not described. Secondly, they use their own mechanism to make sure the storage blocks are synced in the correct order.
I have 2 scenario's I tried, but these fail. I use my home lab here, so all servers are VMs and therefore use VHDs themselves. So the physical disk in scenario A, for example, is a VHD of a file server VM. All servers use Windows Server 2019.
Scenario A:

Configure Storage Replication between a physical disk on iSCSI target
server A and B
Create 2 iSCSI disks on that physical disk and make
them CSV in the cluster

In this scenario the physical disk will replicate the blocks of the iSCSI disks to the other site. This works fine, but there are several issues if I understand correctly:

The iSCSI disks on Site B cannot be added to the cluster, because they are read-only (not even visible) due to the Storage Replication configured.
In order to fail over, I cannot stop the cluster, switch the replication direction, add the iSCSI disks on Site B as CSV to the cluster. It seems that I need to remove the LUNS first and delete the iSCSI CSV disks from Site A from the cluster? Then add them again at Site B? This seems very cumbersome to me, but maybe this is how this should be done in order to fail over? Or am I missing something here?

Scenario B:

Configure Storage Replication between CSVs

In this scenario I create some iSCSI disks and add them as CSV to the cluster. That works fine. Then I wanted to configure replication via the Failover Cluster Manager. First add them as CSV, then right click on one and select 'Replication'.
This scenario fails, because it tells me that the disks are not of the same size. However, they are exactly the same size (I use the same Powershell command to create them) and are formatted with the same NTFS Allocation Unit size. Maybe I am missing something here?

So basically my question is how I can get Windows Server Storage Replication to work correctly in combination with the cluster configuration I am using?


texts: Concurrent requests not running in parallel

texts:
I am trying to run update statistics in parallel.

Say I start with 2 sessions: S1, S2

In S1 I run
Update Statistics A with fullscan and time it to take 2 minutes.

In  S2 I run Update Statistics B with fullscan and time it to take 2 minutes also.

Now I run both in parallel and I can see that both finish in around 2 minutes as expected.

Now I add a new session S3 (this issue doesnt just happen with the 3rd session or whatever, i'm not really sure when exactly it occurs but I have noticed it)

In S3 I run Update Statistics B with fullscan and time it to take 2 minutes also.

I run S1 AND S3 in parallel, and now notice both queries are taking 4 minutes to complete. If I run S1 and S2 in parallel instead, now it takes 2 minutes for both queries. If I run S2 and S3 in parallel, it takes 2 minutes. Basically it seems like there is some kind of restriction where it does not let S1 and S3 execute requests at the same time, regardless of the query. I have verified this by checking sys.dm_exec_requests while the two queries are executing, S1 and S3 keep alternating between running and runnable/suspended with only one query running at once. Any other combination of sessions has both sessions running at the same time.

I can't see any kind of blocking as well going on here.

What can cause this issue?


texts: Delete from table using FK columns, JOIN and LIMIT

texts:
This query below will never complete, since it always looks at the first 100,000 (user_type, user_ids). How can I write this to delete 100,000 records from the table with the FK reference (Product) to the UserTable table? In other words, the limit should happen after the join, but preferably in an efficient way? UserTable has an index on (user_type, user_id) and (shard_key) and Product has an index on (user_type, user_id)

It doesn't necessarily have to be exactly 100,000 - for example, each user could own up to 100 products and that would be fine if the records deleted were (avg. # of products * 100,000)

DELETE p.*
FROM (
    SELECT user_type, user_id
    FROM UserTable
    WHERE MOD(shard_key,20) != 7
    LIMIT 100000
) t
JOIN Product p ON (
    t.user_type = p.user_type AND t.user_id = p.user_id
)

EDIT: This is MySQL 5.6


texts: Choosing values from multiple columns based on latest value of one column

texts:
I have an Azure Cosmos DB that contains columns DateTime, userName, temperature, and bloodPressure, plus many other columns. There are several distinct userName values, and each userName has temperature and bloodPressure data collected at different values of DateTime. The temperature and bloodPressure values are different for each of those 15 times.
Here is an example of the data,
userName, DateTime, bloodPressure, temperature, heartBeat, hatSize
Curly, "2021-01-19 18:24:53", 121, 98, 60, 7.25
Larry, "2021-01-14 18:24:53", 125, 99, 80, 7.5
Mo, "2021-01-13 18:24:53", 123, 100, 70, 7.75
Curly, "2021-01-18 18:24:53", 120, 97, 50, 8
Larry, "2021-01-13 18:24:53", 119, 99, 75, 7.15
Larry, "2021-01-15 18:24:53", 115, 98, 85, 7.37
Mo, "2021-01-12 18:24:53", 110, 102, 100, 8.01
Mo, "2021-01-20 18:24:53", 130, 99, 110, 6.97
Larry, "2021-01-18 18:24:53", 127, 98, 72, 7.76
Curly, "2021-01-17 18:24:53", 126, 97, 82, 8.0

For each user, I would like to return the DateTime of their latest measurement, and their temperature and bloodPressure from their latest DateTime:
Larry, "2021-01-18 18:24:53", 127, 98
Mo, "2021-01-20 18:24:53", 130, 99
Curly, "2021-01-19 18:24:53", 121, 98

I tried this,
SELECT
    c.userName,
    MAX(c.DateTime),
    c.bloodPressure,
    c.temperature
FROM CheckupData c
GROUP BY
    c.userName

but got an error that GROUP BY should include the other outputs of SELECT. Since bloodPressure and temperature are always changing, it seems like I shouldn't use them in GROUP BY, because I only want the lastest values. What am I doing wrong?
(If it matters, my real database is larger: 50,000 userNames measured at 50 or so DateTime values each, and 30 or so columns).


texts: error while restoring database from mysql: ERROR 1006 (HY000)

texts:
I made a mysqldump from all my databases prior to fully deinstall and reinstall MariaDB. while restoring the databases with the following command, logged in as root:

MySQL -u root -p < /mnt/backups/190612alldb.sql

I'm getting the following error:

  ERROR 1006 (HY000) at line 22: Can't create database 'mydatabase'
  (errno: 2 "No such file or directory")

'mydatabase' is just one of several databases I previously dumped. when this error occurs none of the databases is restored.

what's going on here?


texts: how to design a database's table to store mysql spatial data in a parent child relation?

texts:
I want to store locations and polygon data in a parent-child relationship. I am using spatial data in MySQL and Google maps for polygon.

Examples:

1. World
1.1 US
1.2 China
1.3 Japan
1.4 South Africa
1.4.1 Pretoria
1.4.2 Cape Town
1.5 India
1.5.1 New Delhi
1.5.2 Chennai

etc.

CREATE TABLE IF NOT EXISTS `geom` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `g` geometry DEFAULT NULL,

  `location` varchar(100) NOT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=8 ;

select *
from geom
where st_contains(g,(select g from geom where id =3))and id!=3


texts: How to insert or update using single query?

texts:
I have a table test having columns id which primary key and auto incremented and name.
I want to insert a new record if annd only if there are no records.For example

input is id=30122 and name =john

if there are records with id 30122 then I have update the name column to john,if there are no records then I have insert a new record.

I can do using 2 queries like

select * from test where id=30122

if it has some records then I can use update test set name='john' where id=3012

or if it does not have records then I can use

insert into test(name) values('john')

But I wanted to use single query?

Can somebody tell if its possible?


texts: MySQL 8.0 cannot insert NULL values to date field

texts:
I'm trying to insert a data from file to MYSQL 8.0 using the InnoDB engine.

I'm using this query:

LOAD DATA INFILE '/var/lib/mysql-files/item_new_1_4.dat'
INTO TABLE Item FIELDS TERMINATED BY '|';

I'm getting an error message:

ERROR 1292 (22007) at line 1: Incorrect date value: 'NULL' for column 'i_rec_end_date' at row 1

The table was created by this query:

CREATE TABLE IF NOT EXISTS Item(
    i_item_sk integer ,
    i_item_id char(16) NOT NULL,
    i_rec_start_date date,
    i_rec_end_date date,
    i_item_desc varchar(200),
    i_current_price decimal(7,2),
    i_wholesale_cost decimal (7,2),
    i_brand_id integer,
    i_brand char(50),
    i_class_id integer default ,
    i_class char(50),
    i_category_id integer,
    i_category char(50),
    i_manufact_id integer,
    i_manufact char(50),
    i_size char(20),
    i_formulation char(20),
    i_color char(20),
    i_units char(10),
    i_container char(10),
    i_manager_id integer,
    i_product_name char(50),
    PRIMARY KEY (i_item_sk)
);

The data at line 1 is:

1|AAAAAAAABAAAAAAA|1997-10-27|NULL|Powers will not get influences. Electoral ports should show low, annual chains. Now young visitors may pose now however final pages. Bitterly right children suit increasing, leading el|27.02|23.23|5003002|exportischolar #2|3|pop|5|Music|52|ableanti|N/A|3663peru009490160959|spring|Tsp|Unknown|6|ought|


texts: SQL 2008 R2 Repair: Setup failed to install Windows PowerShell. Error code is -2145124329

texts:
When I try to Repair my SQL 2008 R2, I get the following error. How can I solve this?

TITLE: Microsoft SQL Server 2008 R2 Setup

The following error has occurred:

Setup failed to install Windows PowerShell. Error code is -2145124329

For help, click: http://go.microsoft.com/fwlink?LinkID=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=10.50.1600.1&EvtType=0x82728477%25400x237CA5E1%25401215%25400

BUTTONS:

OK


texts: Get rows where lastLoginTimestamp is in last 7 days

texts:
I'm trying to get the rows from a table where lastLoginTimestamp is in last 7 days and I'm doing as follow:

SELECT * FROM representative WHERE DATE_SUB(lastLoginTimestamp, INTERVAL 7 DAY)

lastLoginTimestamp is stored as a VARCHAR so this is where the problem is I though. How do get that rows in this case? Any help?

This are some of the values on that column to play with:

1427162434
1422557283
1395854997

Update

Based on the first answer I've changed my query to this:

SELECT
    *, from_unixtime(lastLoginTimestamp) AS lastLogin
FROM
    representative
WHERE
    DATE_SUB(
        from_unixtime(lastLoginTimestamp),
        INTERVAL 7 DAY
    )

But I'm getting the wrong values, for example I'm getting a row from this lastLogin = 2015-03-23 21:30:34 which is wrong


texts:
I am using pt-table-checksum for the first time and have some questions that I can't seem to resolve by reading the documentation on Percona's website.

Per the documentation, pt-table-checksum performs an online replication consistency check by executing checksum queries on the master. Does this script have to be run on the master? Can I run from another server that has no MySQL installed? I tried this:

pt-table-checksum --host=mysqlistance --user=user --password=password --replicate-check-only

But I get some errors indicated that it is unable to connect to the localhost. However, it specifies the IP instead of localhost.


texts:
Does SQL Server execute queries in parallel?
In other words, if I run a heavy query that takes 10 seconds to execute, and at the same time start another heavy query that takes 10 seconds, will the second query actually start after 10 seconds, or will they start both at the same time?


texts: What happens when you update a VARCHAR cell with a longer string?

texts:
Say I have a column of type VARCHAR(32) in a mySQL database.

On Spetmeber 1st I decide to store the string "tea", but on September 2nd I decide to update it to "coffee".

Clearly, if all the records in this table were squished right up against each other, and we tried to lengthen one record by 3 bytes, then all the records that appeared after this one would need to shift down by 3 bytes. Of course, this is ridiculous; there is no way any DBMS would ever resort to shifting thousands of possible entries.

So what exactly does mySQL do in this eventuality? Does it behave the same way for TEXT and BLOB types?

EDIT:
After reading this a day later, I realized that this question was fairly ambiguous. Here is an example that I hope will clear things up:

Say I have a table, fav_drinks with two columns:

user_id, which is an INTEGER
drink, which is a VARCHAR(32)

Pretend that this table is stored like this in memory:

[1,"juice",2,"tea",3,"soda",4,"hot chocolate"]

That is, all records are stored sequentially one after the other. If we need to update user 2's favourite drink from "tea" to "coffee", in theory we would need to shift down the entries for users 3 and 4. Of course, I don't think this is what would happen in a real database.

So, to reiterate the question, how does mySQL manage this specific case where one table entry suddenly requires more memory?


texts: Why does the order of parameters matter for sp_trace_create?

texts:
The first batch of the following script calls stored procedure sp_trace_create with parameters in documentation order; the second batch swaps the positions of parameters @tracefile and @options:

DECLARE @new_trace_id INT;

EXECUTE master.dbo.sp_trace_create
  @trace_id = @new_trace_id OUTPUT,
  @options = 0,
  @tracefile = N'C:\temp\TestTrace';

SELECT @new_trace_id AS [@new_trace_id];

EXECUTE master.dbo.sp_trace_setstatus
  @trace_id = @new_trace_id,
  @status = 2;
GO

DECLARE @new_trace_id INT;

EXECUTE master.dbo.sp_trace_create
  @trace_id = @new_trace_id OUTPUT,
  @tracefile = N'C:\temp\TestTrace',
  @options = 0;

EXECUTE master.dbo.sp_trace_setstatus
  @trace_id = @new_trace_id,
  @status = 2;
GO

The first batch creates a new trace, selects its id, and then closes the trace. One result set is returned:

@new_trace_id
2

The second batch fails with an error:

  Msg 214, Level 16, State 3, Procedure sp_trace_create, Line 1
  Procedure expects parameter '@tracefile' of type 'nvarchar(256)'.

Why does parameter order affect the output of stored procedure sp_trace_create? And why does it fail with such a misleading error message?


texts: Troubleshooting Powershell errors from SQL Agent job

texts:
I have a powershell script that I am trying to add in to a sql agent job, but the job step is failing with the following error

    A job step received an error at line 1 in a PowerShell script. The corresponding line is 'import-module SQLPS  -DisableNameChecking'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'Could not load file or assembly 'file:///C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules\SQLPS\Microsoft.SqlServer.Management.PSSnapins.dll' or one of its dependencies. This assembly is built by a runtime newer than the currently loaded runtime and cannot be loaded.

I assumed that the error was being caused by differences in the module path, so I explicitly set the path via $env:PSModulePath to match the path of my powershell session where the code runs fine.  I am pretty new to powershell, so any help you could provide would be appreciated

I am just doing some testing right now, so everything is running locally on my desktop.  I am running SQL Server 2012.


texts: SELECT operation with four joins results in bad performance

texts:
I'm having problems with a slow query. The query purpose is to get doers ids for X job who didn't exceed specified limits on realizations and also are in range of possible job places.
Resources

Query:
  SELECT DISTINCT doers.id
  FROM doers
  JOIN doer_locations dl
      ON dl.doer_id = doers.id
  JOIN job_places jp
      ON (jp.lat - 0.3147625620715557) < dl.lat
     AND (jp.lat + 0.3147625620715557) > dl.lat
     AND (jp.lng - 0.5001626620527362) < dl.lng
     AND (jp.lng + 0.5001626620527362) > dl.lng
  LEFT JOIN job_realizations jr
      ON jr.job_place_id = jp.id
     AND jr.status IN (1, 2, 3, 4)
  LEFT JOIN job_realizations jrpd
      ON jrpd.job_place_id = jp.id
     AND jrpd.doer_id = doers.id
     AND jrpd.status IN (1, 2, 3, 4)
  WHERE (jp.job_id = 1 AND doers.id IS NOT NULL)
  GROUP BY doers.id, jp.id
  HAVING COUNT(DISTINCT jr.id) < jp.realizations_per_place
     AND COUNT(DISTINCT jrpd.id) < jp.realizations_per_place_per_doer

Depesz explain

Raw explain analyze

Simplified Schema

Consideration
I'm not sure if I read the explain correctly but it seems it loses on performance especially when it calculates stuff on the run also HAVING COUNT(DISTINCT) seems pretty expensive.
Additional information
The type of both the lat and long columns is float.


texts:
As someone who develops websites and does a bit of SQL Server, it seems a no brainer to me that SQL Server should support a simple clause that indicates the results should only include a specific range of rows. For example, rows 30 through 39.

As it stands, implementing paging in SQL Server is actually a little beyond many web developers' capability when their main focus is not SQL.

In addition, it would greatly simplify queries that may already be complex due to joins and other conditions.

On top of that, I'm of the opinion that such a clause may stand a better chance of getting optimized internally over time because the logic would be entirely under Microsoft's control.

I brought this up some time ago in another forum and many of the replies seemed to suggest this would not be desirable.

Given that any existing techniques for paging would be left intact, what possible reason could there be for not adding such a clause? And it would be so much better for people like me.

Can anyone thing of a good reason why Microsoft hasn't done this?


texts: Storing Business Logic in Tables rather than Stored Procedure or Application

texts:
The requirement is that once the product has been delivered, user can update the logic as he wants.

An example of logic I have is follows-

An institute teaches English has following requirements -

At the time of registration student can choose either Writing or Reading or both.
Student can choose Speaking only if it is being offered in the same Term as well as it is starting within 2 weeks.

Applying this logic in the application or stored procedure is fairly simple. But as per requirements, above scenario could change.

How can we achieve something like above.


texts: Tuning a query with: index, window function?

texts:
Description

Environment: Centos 6-x64, Postgres Plus Advanced Server 9.3.
I have a table with 4 columns with data as below:

id    code_id            effective_date                 name
24      12       "2015-09-15 02:57:47.626751+00"      "dtsc_12"
429     215      "2015-09-15 02:57:47.626751+00"      "dtsc_215"
430     215      "2015-09-15 02:57:47.626751+00"      "dtsc_215"
465     233      "2015-09-15 02:57:47.626751+00"      "dtsc_233"
466     233      "2015-09-15 02:57:47.626751+00"      "dtsc_233"
468     234      "2015-09-15 02:57:47.626751+00"      "dtsc_234"

And I want to get all row with condition: group by code_id and max(effective_date) < current_timestamp. So, the result I want:

id     code_id             effective_date                name
24       12       "2015-09-15 02:57:47.626751+00"      "dtsc_12"
429      215      "2015-09-15 02:57:47.626751+00"      "dtsc_215"
465      233      "2015-09-15 02:57:47.626751+00"      "dtsc_233"
468      234      "2015-09-15 02:57:47.626751+00"      "dtsc_234"

My coding

create table win_a (
   id int not null primary key,
   code_id int,
   effective_date timestamp with time zone,
   name text
);

insert into win_a
select a,  a/2,  now() + trunc(random()  * 100) * '1 day'::interval, 'dtsc_' || (a/2)::int
from generate_series(1, 500) a
ORDER BY random() ;

create index win_a_indx on win_a using btree ( code_id, effective_date desc);

-- query 1
select a.*
from (
select id, code_id, effective_date, name
     , rank() over (partition by code_id order by effective_date desc, id) as rank
from win_a
where effective_date < current_timestamp
) a
where rank = 1 ;

-- query 2 -- false if in the same code_id -> have more than two same effective_date value
select a.*
from win_a a
join (
    select code_id, max(effective_date) as max_ef
    from win_a
    where effective_date < current_timestamp
    group by code_id ) b
on a.code_id = b.code_id and a.effective_date = b.max_ef;

-- query 3 -- false if in the same code_id -> have more than two same effective_date value
select a.*
from win_a a
where (code_id, effective_date) in
     (select code_id, max(effective_date) as max_ef
    from win_a
    where effective_date < current_timestamp
    group by code_id );

Question

Can I use index with window function? (with query 1, I try an index but Postgres still uses a seq-scan instead of an index-scan)
How can I improve my coding?


texts: How to split field and run sub query using it?

texts:
Given two tables T1 and T2 below, is there a way in MySQL 5.7.x to get such a list field from T1, so that none of its entries are present in T2?
T1.list is a regular VARCHAR, containing T2.vals delimited by ':'

From the example, I would like to get D:E field.

T1

id list
0  A:B:C
1  D:E
2  F:G

T2

vals
A
B
C
F
G


texts: Best way to automate the loading of data daily into google big query from SQL Server

texts:
Best way to load/copy/insert data from a SQL Server database into a  table in Google Big Query on a daily schedule.

I.E data from a table in a SQL Server database to a table in Google Big Query

I was hoping to use SSIS but there are no native data sources/destinations only 3rd party offerings?

I was thinking of using SSIS to create a CSV file then using Google BQ commandline push the CSV file to big query?

Does anyone have any other better suggestions/methods?


texts: Postgresql: pgadmin crashing every time when running long running query

texts:
My pgadmin is crashing every time whenever i am running query which takes more time. my postgresql version is 9.5

Below is my ram detail

             total       used       free     shared    buffers     cached
Mem:           31G        31G       228M       281M        56M        25G
Low:           31G        31G       228M
High:           0B         0B         0B
-/+ buffers/cache:       6.1G        25G
Swap:           0B         0B         0B

I can see that swap memory is 0B. does this causing the postgresql to respond slow?? if yes then what i can do to improve the performance.


texts: What exactly is "pinning" in relation to indexes?

texts:
When one talks about "pinning" in indexes, what exactly is this? Is there some other word/term I can search for, as google has not provided any solutions.

It is part of a test question, where students were asked to define and describe pinning in relation to indexes.

Does anyone have an idea?


texts: Move data from 4 shard, replicated mongodb cluster to a single server without downtime

texts: I need to move sharded data from four replica sets to a single remote machine without downtime. Can you describe the process step by step?


texts: Is there an advantage to not partition aligning an index?

texts:
I have the privilege of managing a large partitioned
OLAP table.  While reviewing this table I noticed one of the indexes is not aligned with the partitioning scheme.  Since the author is not available and carefully crafted Google searches have not returned any useful results I am not sure if this was intentional or accidental.

Is there any reason not to partition align an index on SQL Server 2008?


texts: Import .bak file in Microsoft SQL Server 2008 Service Pack 3

texts:
I have a backup of my database as a TestDatabase.bak file but I am unable to restore it.

When I try to restore I receive the following error message in SSMS:

  RESTORE could not start database 'FieldopsDevDynamic07042013'

The error log shows:

  Database 'FieldopsDevDynamic07042013' cannot be started because some
  of the database functionality is not available in the current edition
  of SQL Server.

How can I restore this database?


texts: Multiple join criteria with different keys?

texts:
I am having a hard time formulating this question for a targeted search and so none of the results I am getting seem to describe my exact situation.

I work in healthcare and our tables are enormous, so I need to keep my joins as efficient as possible as I am often joining across databases and with 5-10 tables. Our tables will often have multiple patient identifiers that are unique to the patient/encounter. So here is my question:

Should I join on as many identifiers as I can, or should I join on as few as I can that nets me the desired result? This is in Teradata and Patient_Encounter_Id is unique to a given patient encounter, therefore it is more granular than Patient_Id, with Facility_Id being the least granular item.

Version: Teradata 16.20.32.26

Example:

Select *
From tableA A
Join tableB B
  On A.Patient_Encounter_Id = B.Patient_Encounter_Id

VERSUS

Select *
From tableA A
Join tableB B
  On A.Patient_Encounter_Id = B.Patient_Encounter_Id
  AND A.Patient_Id = B.Patient_Id
  AND A.Facility_Id = B.Facility_Id

I tested one versus the other on a single big query:
With extra join criteria the query took 4:43
Using the minimum join criteria the query took 7:36

However the load on our systems is highly variable so I am not sure if this is generalizable and I cant really justify running 50 queries like this to test it. Thanks!


texts: Single AND operator and multiple OR operators

texts:
I wanted to select a record_id with a user_id of 1 and category_id can be 1, 2, or 3.
Every SELECT statement should return 1 record.

View my sample table here:

How do I do it in SQL? I tried this SQL statement:

SELECT record_id
FROM tbl_people
WHERE category_id=1 OR category=id=2 OR category_id=3
OR category_id=4 OR category_id=5 OR category_id=6
AND user_id = 1;

...But it does not work.


texts: I can't connect to an instance of SSMS from other servers SSMS

texts: I can log in to the server and connect to the instance of SSMS in the server but I can't connect to the same instance from other servers using ssms. how can I fix this?


texts:
I have an application which needs to send emails through Oracle triggers. I understand that in Oracle 11g you must have an ACL file to grant certain schemas access to the network. My plan was to create a separate ACL file for each user that needs access to the network, and then check that it has been created properly by using the following query:

SELECT * FROM DBA_NETWORK_ACLS;

However, it seems that only one record is ever returned for this query - always the latest ACL that's been created. Does this mean that you can only ever have one ACL? If so, presumably this means that when installing this application on a client's server, I must just add privileges to their existing ACL rather than trying to create a new one?

Thanks!

UPDATE

These are the commands I'm using:

--create ACL for USER1
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(ACL => 'testacl1.xml',
                                  DESCRIPTION => 'ACL',
                                  PRINCIPAL   => 'USER1',
                                  IS_GRANT    => TRUE,
                                  PRIVILEGE   => 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL => 'testacl1.xml',
                                     PRINCIPAL => 'USER1',
                                     IS_GRANT  => TRUE,
                                     PRIVILEGE => 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(ACL => 'testacl1.xml', host => '*');

--create ACL for USER2
DBMS_NETWORK_ACL_ADMIN.CREATE_ACL(ACL => 'testacl2.xml',
                                  DESCRIPTION => 'ACL',
                                  PRINCIPAL   => 'USER2',
                                  IS_GRANT    => TRUE,
                                  PRIVILEGE   => 'connect');
DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(ACL => 'testacl2.xml',
                                     PRINCIPAL => 'USER2',
                                     IS_GRANT  => TRUE,
                                     PRIVILEGE => 'resolve');
DBMS_NETWORK_ACL_ADMIN.ASSIGN_ACL(ACL => 'testacl2.xml', host => '*');

The problem is that testacl2 seems to be overwriting testacl1. Does this mean that I can only have one ACL file, or one ACL file per host?


texts: what does "if @@trancount > 0 commit tran" mean?

texts:
What does this operation mean please?

if @@trancount > 0 commit tran

and after that

SET IMPLICIT_TRANSACTIONS ON

or

SET IMPLICIT_TRANSACTIONS OFF

I do not understand the meaning of this sequence of commands even though I searched a lot online.


texts: SSMS 2017 Changing SSIS Package Version During Deployment

texts:
Steps taken, can be reproduced:

Develop SSIS package in SSDT 2012, project deployment model.
Deploy project to server A, SQL Server 2012. Package version
internal.packages is 6.
Deploy project from server A to server B, SQL Server 2012. Package
version internal.packages is 8 on server B.

If I deploy directly to both servers from SSDT 2012 there is no problem, but deploying from SSMS 2017 is bumping the version number.

Is there a way to stop that from happening during project deployment in SSMS 2017?  Has anyone else encountered this?


texts: Create database role to allow read access to all tables; write access to some

texts:
We have a database with over 1000 tables. I need to create a role that allows read only on all tables, as well as write access to two specific tables.

I was messing around with database roles, but whenever I went to add the tables, I had to hand select all 1000... is there a better way to do this?


texts:
I have following: 2 nodes one of which is current production db and another is for fail recovery. I made base backup on first node with following command pg_basebackup -h 127.0.0.1 -U postgres -D bckp -Ft -z -P -R -x and got bacup file base.tar.gz. Now i'm trying to recover it on failover node. I cleared data directory and unzipped there contents of the backup. As I do not need wal logs from master node to stream to this server i should create recovery.conf with at least restore_command specifying command that will copy wal logs from archive directory to xp_log. But it was already there in xp_log. There was file 000000010000000000000017. As i'm new to postgres and linux I decided to move this file to archive directory and set recovery.conf contents to restore_command = 'cp /var/lib/postgresql/archive/%f /var/lib/postgresql/data/%p' then started postgres. It printed ot logs

2015-10-26 14:06:16 UTC [16-3] LOG:  starting archive recovery
2015-10-26 14:06:16 UTC [16-4] LOG:  restored log file "000000010000000000000017" from archive
2015-10-26 14:06:16 UTC [16-5] LOG:  redo starts at 0/17000060
2015-10-26 14:06:16 UTC [16-6] LOG:  consistent recovery state reached at 0/17000160
cp: cannot stat ‘/var/lib/postgresql/backup/000000010000000000000018’: No such file or directory
2015-10-26 14:06:16 UTC [16-7] LOG:  redo done at 0/17000160
2015-10-26 14:06:16 UTC [16-8] LOG:  restored log file "000000010000000000000017" from archive
cp: cannot stat ‘/var/lib/postgresql/backup/00000002.history’: No such file or directory
2015-10-26 14:06:16 UTC [16-9] LOG:  selected new timeline ID: 2
cp: cannot stat ‘/var/lib/postgresql/backup/00000001.history’: No such file or directory
2015-10-26 14:06:16 UTC [16-10] LOG:  archive recovery complete
2015-10-26 14:06:17 UTC [16-11] LOG:  MultiXact member wraparound protections are now enabled
2015-10-26 14:06:17 UTC [30-1] LOG:  autovacuum launcher started
2015-10-26 14:06:17 UTC [1-2] LOG:  database system is ready to accept connections

Finally it restores and works fine but there are unclear moments for me:
With restoring of 000000010000000000000017 i understand, but why it tries to recover 000000010000000000000018 as it's not there? And what are 00000002.history and 00000001.history files and why it tries to find them?


texts: Strategies/Schema for Unique Constraint depending on Grandparent ID

texts:
I'm using Rails 4 with Postgres (newest version). Wondering about design theory and best practice.

Here is my current schema:

create table Company
(
  id int not null primary key
)

create table Project
(
  id int not null primary key,
  company_id int not null references Company
)

create table Orders
(
  id int not null,
  project_id int not null references Project,
  po_number int
)

1) My question is how to create a unique constraint on Orders po_number scoped to a Company. I can't think of a way at the database level, so is that suggesting that my schema is designed poorly?

2) Would it be wise to add company_id to Orders? Are there negatives to doing this?


texts: Copy data between two databases periodically

texts:
Possible Duplicate:
  How to update one database from another?

I need to know how to copy data across to another SQL Server database periodically. Both databases are in the same SQL server; I think I need something similar to MySQL events (database level) or a cron job.

I want to:

Copy data from database1..sametable to database2..sametable using a SQL command
Run this statement every 15 minutes


texts: pg_upgrade: language "plpythonu" is not trusted

texts:
I'm trying to perform PostgreSQL 9.6 -> 10 upgrade with link method:

/usr/lib/postgresql/10/bin/pg_upgrade --link \
 --old-datadir=/var/lib/postgresql/9.6/main \
 --new-datadir=/var/lib/postgresql/10/main \
 --old-bindir=/usr/lib/postgresql/9.6/bin \
 --new-bindir=/usr/lib/postgresql/10/bin \
 --old-options=' -c config_file=/etc/postgresql/9.6/main/postgresql.conf' \
 --new-options=' -c config_file=/etc/postgresql/10/main/postgresql.conf' \
 --old-port=5432 \
 --new-port=5433

however the upgrade process always fails on "untrusted languages":

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 535968; 0 0 ACL LANGUAGE "plpythonu" postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  language "plpythonu" is not trusted
DETAIL:  GRANT and REVOKE are not allowed on untrusted languages, because only superusers can use untrusted languages.
    Command was: GRANT ALL ON LANGUAGE "plpythonu" TO "mydb";

Any idea how to fix this? I've already tried dropping plpythonu extension before upgrade.


texts: Trouble installing percona 5.6 on Google compute engine debian

texts:
I've been trying to install percona server on a fresh Google compute engine instance.

I haven't installed mysql server previously.
I followed the instructions from here.

I get this when I use:

apt-get install percona-server-server-5.6 percona-server-client-5.6

Setting up percona-server-common-5.6 (5.6.15-rel63.0-519.wheezy) ...
Setting up percona-server-client-5.6 (5.6.15-rel63.0-519.wheezy) ...
Setting up percona-server-server-5.6 (5.6.15-rel63.0-519.wheezy) ...
[ ok ] Stopping MySQL (Percona Server): mysqld.
2014-01-22 04:54:28 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated.     Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2014-01-22 04:54:28 21278 [Note] Plugin 'FEDERATED' is disabled.
2014-01-22 04:54:28 21278 [Note] InnoDB: The InnoDB memory heap is disabled
2014-01-22 04:54:28 21278 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2014-01-22 04:54:28 21278 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-01-22 04:54:28 21278 [Note] InnoDB: Using Linux native AIO
2014-01-22 04:54:28 21278 [Note] InnoDB: Using CPU crc32 instructions
2014-01-22 04:54:28 21278 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2014-01-22 04:54:28 21278 [Note] InnoDB: Completed initialization of buffer pool
2014-01-22 04:54:28 21278 [Note] InnoDB: Highest supported file format is Barracuda.
2014-01-22 04:54:29 21278 [Note] InnoDB: 128 rollback segment(s) are active.
2014-01-22 04:54:29 21278 [Note] InnoDB: Waiting for purge to start
2014-01-22 04:54:29 21278 [Note] InnoDB:  Percona XtraDB (http://www.percona.com) 5.6.15-    rel63.0 started; log sequence number 1625987
2014-01-22 04:54:29 21278 [Note] RSA private key file not found:     /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2014-01-22 04:54:29 21278 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
2014-01-22 04:54:29 21278 [Note] Binlog end
2014-01-22 04:54:29 21278 [Note] InnoDB: FTS optimize thread exiting.
2014-01-22 04:54:29 21278 [Note] InnoDB: Starting shutdown...
2014-01-22 04:54:30 21278 [Note] InnoDB: Shutdown completed; log sequence number 1625997

 * Percona Server is distributed with several useful UDF (User Defined Function) from Percona Toolkit.
 * Run the following commands to create these functions:

    mysql -e "CREATE FUNCTION fnv1a_64 RETURNS INTEGER SONAME 'libfnv1a_udf.so'"
    mysql -e "CREATE FUNCTION fnv_64 RETURNS INTEGER SONAME 'libfnv_udf.so'"
    mysql -e "CREATE FUNCTION murmur_hash RETURNS INTEGER SONAME 'libmurmur_udf.so'"

 * See http://www.percona.com/doc/percona-server/5.5/management/udf_percona_toolkit.html for more details

[FAIL] Starting MySQL (Percona Server) database server: mysqld . . . . . . . . . . . . . . failed!
invoke-rc.d: initscript mysql, action "start" failed.

What am I doing wrong?

When I type /etc/init.d/mysql start
I get

[....] Starting MySQL (Percona Server) database server: mysqld/etc/init.d/mysql: fork: Cannot allocate memory
/etc/init.d/mysql: line 48: /usr/bin/tail: Cannot allocate memory
/etc/init.d/mysql: line 47: /bin/grep: Cannot allocate memory
/etc/init.d/mysql: line 46: /usr/bin/tr: Cannot allocate memory
/etc/init.d/mysql: line 45: /usr/sbin/mysqld: Cannot allocate memory
/lib/lsb/init-functions: fork: Cannot allocate memory


texts: Timeout expired error. Is it because of a query or server settings?

texts:
I am getting SQL Server timeout error in my production server, every hour
(~ for 5 minutes or more) during night time. This SQL server database is supporting a .NET application. I have scheduled an email job to send errors to me and I get errors with details as below.

System.Data.Entity.Core.EntityCommandExecutionException: An error occurred while executing the command definition. See the inner exception for details. ---> System.Data.SqlClient.SqlException: Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception: The wait operation timed out
   --- End of inner exception stack trace ---
   at System.Data.SqlClient.SqlCommand.<>c.<ExecuteDbDataReaderAsync>b__174_0(Task`1 result)
   at System.Threading.Tasks.ContinuationResultTaskFromResultTask`2.InnerInvoke()
   at System.Threading.Tasks.Task.Execute()
--- End of stack trace from previous location where exception was thrown ---
   at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
   at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
   at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.<ExecuteStoreCommandsAsync>d__c.MoveNext()
   --- End of inner exception stack trace ---

I am trying to identify what is happening at that time which causes this timeout error.

SQL Server version: 2008 R2
Physical Memory: 24 GB
SQL server Max memory: 18 GB

Transaction log backup runs every hour for example transaction log back runs at 4:00 am and the timeout occurs at 04:01.But this behavior is not consistent and timeout doesn't occur every hour.

Result of:

SELECT * FROM sys.dm_os_wait_stats AS dows WHERE dows.wait_type = 'THREADPOOL';

 wait_type  waiting_tasks_count  wait_time_ms   max_wait_time_ms    signal_wait_time_ms
 THREADPOOL     15223          68891                1086           4

i noticed in my Job Activity Monitor that Shrink database job which runs every week failed during its last run.I don't know its implications.Any thoughts?


texts: Postgres server is not responding to a nodejs request

texts:
I have access to a remote postgres DB from pgAdmin4 and I also could access from nodejs using a Mac. Right now I'm using the same code to access the DB in Windows. The code for my connection is the following:

const { Client } = require('pg'); //Importing the Postgres package
const hosts= require('../hosts'); //Using the file containig all hosts
const connectionData = { //Begin creating the connection settings object
   host: hosts.DBHost, //DB host
   port: hosts.DBPort, //DB hosts port
   database: hosts.DB, //DB
   user: hosts.DBUser, //DB user
   password: hosts.DBPassword, //DB user password
 }

My test is the following:

var client = new Client(connectionData); //New client instance using the above connection settings
client.connect(); //Open the connection to the database()
sql = "select * from myTable";
client.query(sql)
  .then(response => {
    console.log ({"data": response}); //This isn't shown
  })
  .catch(err => {
    console.log({"error": err}); //This isn't shown neither
  })

No error, no exception, the DB server doesn't respond!

Why isn't the server responding?


texts: What permissions does the service account need to use database mail?

texts:
I have an SQL Server 2012 instance running under a managed service account. I've configured database mail with one account and am trying to send a test email, but I'm getting some errors in the server's event logs:

Database Engine Instance=MYINSTANCE;Mail PID=2132;Error Message:The update to the database failed. Reason: The EXECUTE permission was denied on the object 'sysmail_logmailevent_sp', database 'msdb', schema 'dbo'.
Database Engine Instance=MYINSTANCE;Mail PID=2212;
Exception Type: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
Message: The read on the database failed. Reason: The EXECUTE permission was denied on the object 'sp_readrequest', database 'msdb', schema 'dbo'.
Data: System.Collections.ListDictionaryInternal
TargetSite: Microsoft.SqlServer.Management.SqlIMail.Server.Objects.QueueItem GetQueueItemFromCommand(System.Data.SqlClient.SqlCommand)
HelpLink: NULL
Source: DatabaseMailEngine

If I make the account running the SQL Server process a sysadmin, this error goes away, and mails send successfully. However, all the research I've done suggests that giving this account the DatabaseMailUserRole role in the msdb database should be sufficient. I've done this and still receive the same errors.
I took a look in the BOL but couldn't find anything.


texts: Cross Server and Instance Login SSIS SID or ID Issues

texts:
I have some challenging login/SID/ID issues with authentication between databases on separate instances and servers.  We have a Development, Test, Pre-Production and Production server/instance environments.  They do not have the same logins in each environment.  There are developers etc. in Development, tester logins in Test, and Production does not have many of those logins.  We created a separate GIS instance in order to synchronize logins for our GIS section, because it used specific schemas where the logins had to match.  I have scripts to re-synch the database users to the appropriate login, but I have not found a way to re-synch the schemas within a database to the present, but different logins on the migrated server.

Also, we were running SSIS packages that used a specific GIS user to communicate with other databases within the instance, but when we split GIS database out to a separate instance with synched logins, the user cannot communicate with the databases that it used to in the previous instance.

I know we could use contained databases, but we're still running SQL Server 2008 R2 and it's not an option right now.

I need to know:
1) Is there a way update schema references like users in a database.  Can I script all schema ownership to dbo, then blast them away and recreate the ownership to make them match again?  Or, do you need to delete the schema (and all object associated) to match the schema/logins like I think you do?

2) How does SSIS use a login to communicate with a different server/instance in another database where the local login has priviledges?  Does it use the local login or the SID/ID of the incomming login from the SSIS running on it's server/instance?

3) Have any of you folks run into this and how did you deal with it?

Our servers contain a number of different databases that run 24/7 and I can't just take the server down for a day to try to re-synch all logins and rescript all users back into respective databases.  And logins MUST be different on the development and test servers.

Thanks a bunch in advance for any suggestions.

Robb


texts: Best way to copy tables without a primary key or unique constraint?

texts:
Looking into synchronizing tables in a database across multiple servers using a DML method like MERGE, only to realize that some of the largest tables that take up the majority of the total size of the database do not have a primary key or unique constraint and therefore have duplicate data. As a result, doing a MERGE results in a full table scan, which explodes tempdb. For business reasons, I cannot alter the table structure or data itself.
Is there a way to copy these tables on a routine basis other than backing up and restoring the entire database?


texts: Enforce a single NULL end date per foreign key value using an index

texts:
I want to implement a Check Constraint across a date range and foreign key.

I have a table created as:

Primary Key
Foreign Key
DateStart
DateEnd

The ForeignKey can appear many times in the table as long as there is a value in the EndDate. But there should only ever be one entry for the ForeignKey where the DateEnd is Null (the active version).

Apologies if this is simple, but I've been trying to use SQL Server GUI to put in an Constraint expression, but I can't get it to work.

Any help, or pointers to help would be appreciated.


texts: Problem inserting into table from trace file consumes all space in tempdb

texts:
I'm working with SQL Server 2008 SP3 Enterprise Edition (64-bit)

I'm trying to import a trace file of about 3.5 MB (4667 rows) into a table as following:

INSERT INTO DBTRACE with (rowlock)
SELECT * FROM ::fn_trace_gettable('D:\DBTRACES\TRACE.trc', DEFAULT)

It keeps executing until it fills up the tempdb (2 data files of 30 GB each) then the following error comes:

  Could not allocate space for object '' in database 'tempdb' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup. [SQLSTATE 42000] (Error 1105). The step failed.

I already restarted the service and keeps doing it, yesterday it was running fine.

I imported the same trace file into another table in another server and it works successfully and took about 1 second.

Have anyone ever dealt with something like that? I would appreciate any help.


texts: uncommitted xmin during vacuum, what to do?

texts:
Running vacuum on two specific tables in my postgresql 10 database results in the following error:

VACUUM (FULL|ANALYZE|FREEZE|) table;
ERROR:  uncommitted xmin 359569171 from before xid cutoff 435784199 needs to be frozen

The tables are otherwise functioning, I can query/insert/update them (I can query the whole tables without any error). I can also REINDEX them. However, this error stops the nightly VACUUM ANALYZE on the whole database prematurely. The xmin value value seems to be constant, the cutoff value increases monotonously.

How can I fix this? From the very few instances on the web with a similar error, (e.g. https://stackoverflow.com/questions/50354521/postgres-corruption-error-duplicate-rows-with-the-same-primary-key), it seems there should be some bad rows, but I cannot seem to find them.


texts:
I have this table:

Name        Value      Sequence
-------------------------------
test        A1         1
test        A2         3
test2       A20        5
test2       A10        8

I am after grouping the Name and get Value+ and Value- like this:

Name        Value+         Value-
-----------------------------------
test        A1             A2
test2       A20            A10

Rules are:

The Value from row with lower Sequence  goes to Value+
The Value from row with higher Sequence goes to Value-
There are only two rows per name

How can I do it?


texts: Speed up LEFT JOIN with OR operator in ON clause


texts: NON-Random UUID keys in custom distributed DB

texts:
Is there any reason other than security for using random (or even semi-random) UUIDs as primary keys to database records?

Is there any reason why I should not WANT to be able to use the UUID to lookup a record? In my case I think that would actually be a FEATURE.

I'm creating a distributed / replicated database with security builtin to it at the SQL level. To access a record, the client MUST first obtain a "grant" that allows the client to access records in "partitions". Each record has an indexed int field that will be checked with every SQL operation using a clause like 'WHERE partid IN (123, 234, 345)'. This is mostly to make queries fast but it is also used to for security / access control. To get those partition ids in your security context, you must first obtain a suitable grant.

So I don't care if someone knows the key of a record. They either have explicit permission to access it or they don't. Obscuring keys by making them random seems like a completely orthogonal characteristic. No?

The part about security and grants is really not critical. I was just trying to stress that I have a security strategy at the SQL level (described more here) and thus I don't (think I) need the UUIDs to be totally random. So there is no minimal reproducible example to post. It's just a conceptual question mostly. A UUID can be unique and not be random. I'm just trying to figure out if there are unforeseen issues with using non-random UUIDs are a primary key.


texts:
In some limited testing with Redis Cluster I've identified the following conditions where a failed master would be unable to failover:
The obvious, all the slaves have failed
All of a master's slaves on the majority partition are out of sync with their master
A master and all of it's slaves are all in a minority partition
Used memory is greater than the "maxmemory" config (if keys don't expire)

We plan on monitoring for these types of conditions, so ops can intervene before a Redis cluster goes unhealthy.

What other points of failure does a Redis Cluster have?


texts: What prerequisites package is needed for Oracle 11g + 12c on the same OL7 server?

texts:
I need to install 3 Oracle homes on the same Oracle Linux 7 server, but it's not clear to me what prerequisites I need to install before proceeding.

What I'm planning to set up, is a two-standalone-instance (11.2 + 12.2) server and an Oracle Grid infrastructure for taking advantage of the benefits of the Oracle Restart.

As a prerequisite, on OL7 we need to run:

yum install oracle-rdbms-server-11gR2-preinstall -y   (in case on Oracle 11.2)
yum install oracle-database-server-12cR2-preinstall -y   (in case on Oracle 12.2)

Considering I'm planning to install the following Oracle homes

12.2 Grid infrastructure
12.2 Database
11.2 Database

should I run both the aforementioned yum commands, or only 12cR2 prerequisite is actually required?


texts: Is there a postgresl server configuration that will let me expose an untrusted language like pl/R while still maintaining security?

texts:
I am building a postgresql database and I want users to be able to run their own R and python scripts at the database rather than pulling it all down locally.  Unfortunately, pl/R and pl/python are untrusted, so I can't just let 20 users have pl/R access to my database for fear some person will do something bad to my core database.

Is there a server configuration that will let me provide this flexibility to my users but still stop them from having inappropriate access to my database?  For example, can I set up a special linked database just for them, that is close enough to the data to make it useful while still having security separation? (or anything else, just trying to give an illustrative example).


texts:
Select
  sum(column_a) as a,
  sum(column_b) as b
  round(a / b)
From
  table
Group by column

In the example above I am summing two column, and I would like to use those 2 results in calculating another result, how can I do this? round(a / b) is not going to work, is just as an example of what I want to do.


texts:
I am going to make a KPI table, however I've yet to decided where to make it. In essence its going to show weekly numbers derived from our customer database.

*Weeknumber

*New users (users with startdate within that week)

*Total users (users with startdate before that week, and enddate after that week/NULL)

This is going to be expanded (New users pr sales channel for example). The result is that users  should be able to compare these numbers week for week. I do not know if this is possible to make in DAX (Using dax, can I filter week by week, sales channel or other dimensional traits?) or if I should do this in sql.


texts:
I trying to pivot a table creating the columns dynamically according to the rows.
I'm using the clause PIVOT XML, but I'm not getting parse the XML column to columns.
My table looks like this:

I would like this:


texts: Database Structure with Recurring Payment

texts:
I'm working on a new database structure for a new website.

I need to accept 2 types of payment : OneShot and Recurring (monthly)

In a single order (basket), I can put a product "OneShot" and "Recurring" and paid both.

Here is my actual structure (I do not provide all columns) :

Transaction table can have multiples lines. If I make an order with OneShot Product ID:89 - 70$ and Recurring Product ID:129 - 29$/Month

Table Order
OrderId / OrderNumber / CustomerID / TotalAmount / CompanyId
998 / AA-22-ER / 11 / 99$ / 2

Table OrderItem
OrderId / ProductId / UnitPrice / Quantity / Created
998 / 89 / 70$ / 1 / 2017-09-17
998 / 29 / 29$ / 1 / 2017-09-17

Table Transaction
TransactionID / OrderID / Status / Amount / Created
129 / 998 / P / 99$ / 2017-09-17 ==> OneShot + First Recurring Payment
345 / 998 / P / 29$ / 2017-10-17 ==> recurring payment
523 / 998 / P / 29$ / 2017-11-17 ==> recurring payment

How can I deal with rebill and/or refund?

For example, I want to refund the OneShot Product ID:89, do I need to stock this information in Transaction Table with Status colum? But how can I know quickly which OrderItem is still paid?

In another way, how can I deal with recurring payment? Hope I'm  clear in my question.

I think, I have a beginning of a solution.

Customer cannot buy 2 subscriptions products in one sale.

Here are my use cases :

In one Sale, 1 product is normal (70$), 1 is a subscription for 12 months (29$/Month). Customer ask a refund on december, so subscription stop at this moment.

Table Orders
ID  /   ClientID    /   Amount  /   AmoutVAT    /   Reference   /   Company_ID  /   Created /
998 /   112 /   99$ /   89$ /   2-20170927-SZZ-345  /   2   /   2017-09-17

Table OrderItem
ID / OrderId / ProductId / UnitPrice / Quantity / Created / Expire_Date
1021 / 998 / 89 / 70$ / 2017-09-17 /    2099-12-12
1022 / 998 / 29 / 29$ / 2017-09-17  /   2017-12-17

Table Transaction
TransactionID / OrderID / Status / Amount / Created / Refund
129 / 998 / P / 99$ / 2017-09-17 / 0
345 / 998 / P / 29$ / 2017-10-17 / 0
523 / 998 / P / 29$ / 2017-11-17 / 0
657 / 998 / R / 29$ / 2017-12-17 / 29$

Table TransactionEvent
TransactionID / Event   / Created   /   Amount  /   Reason
657 /   R   /   2017-12-23  /   29$ /

One sale with 1 normal product (19$)

Table Orders
ID  /   ClientID    /   Amount  /   AmoutVAT    /   Reference   /   Company_ID  /   Created /
997 /   189 /   19$ /   16$ /   2-20171027-XXS-990  /   2   /   2017-09-17

Table OrderItem
ID / OrderId / ProductId / UnitPrice / Quantity / Created / Expire_Date
1009 / 997 / 78 / 19$ / 2017-09-17 /    2099-12-12

Table Transaction
TransactionID / OrderID / Status / Amount / Created / Refund
119 / 997 / P / 19$ / 2017-09-17 / 0

One sale with 1 subscription product for 29$/month on 12 months

Table Orders
ID  /   ClientID    /   Amount  /   AmoutVAT    /   Reference   /   Company_ID  /   Created /
1002    /   234 /   29$ /   25$ /   2-20171029-AAS-120  /   2   /   2017-10-29

Table OrderItem
ID / OrderId / ProductId / UnitPrice / Quantity / Created / Expire_Date
1044 / 1002 / 99 / 29$ / 2017-10-29 /   2018-01-29

Table Transaction
TransactionID / OrderID / Status / Amount / Created / Refund
789 / 1002 / P / 19$ / 2017-10-29 / 0
844 / 1002 / P / 19$ / 2017-11-29 / 0
901 / 1002 / P / 19$ / 2017-12-29 / 0


texts: pt-table-checksum with slaves using non-standard ports

texts:
Trying to run pt-table-checksum to detect slave drift in MySQL replication. My slaves are not using the standard TCP listening port however, and so when I run the tool I get an error:

Cannot connect to P=3306,h=,p=...

It still checksums the master's tables, but without being able to connect to the slaves it's not very useful. I can't figure out how to specify alternate ports for the slaves when running the command. My current command looks like

pt-table-checksum -h  -P 3388 -p


texts: Trigger for removing group with no users left in

texts:
I have three tables: USERS, GROUPS, and GROUP_MEMBERSHIP

CREATE TABLE USERS (
ID BIGSERIAL NOT NULL,
NICKNAME VARCHAR(20) NOT NULL constraint USER_EXISTS UNIQUE,
PRIMARY KEY (ID)
);

CREATE TABLE GROUPS (
ID SERIAL NOT NULL,
NAME VARCHAR(20) NOT NULL constraint GROUP_EXISTS UNIQUE,
PRIMARY KEY (ID)
);

CREATE TABLE GROUP_MEMBERSHIP (
ID SERIAL NOT NULL,
ID_USER BIGINT NOT NULL constraint USER_IN_GROUP UNIQUE,
ID_GROUP INT NOT NULL,
PRIMARY KEY (ID)
);

Now I am trying to create a trigger, which would fire after deleting a user from a group. If the group has no users left, then the group is deleted:

CREATE OR REPLACE FUNCTION DELETE_EMPTY_GROUPS()
RETURNS TRIGGER AS
$$
BEGIN
        IF NOT EXISTS(SELECT * FROM GROUP_MEMBERSHIP WHERE ID_GROUP = OLD.ID_GROUP)
        THEN
               SELECT DELETE_GROUP(OLD.ID_GROUP);
        END IF;
        RETURN NEW;
END
$$
LANGUAGE 'plpgsql';

DROP TRIGGER TR1 ON GROUP_MEMBERSHIP;
CREATE TRIGGER TR1
       BEFORE DELETE
       ON GROUP_MEMBERSHIP
       EXECUTE PROCEDURE DELETE_EMPTY_GROUPS();

Unfortunately, it does not work. I get the following error:

  ERROR:  record "old" is not assigned yet DETAIL:  The tuple structure
  of a not-yet-assigned record is indeterminate.

I tried BEFORE and AFTER, both resulted in the same error. What is the correct way of doing this?


texts: Update single table with mutually exclusive threads

texts:
I have 2 threads (for example 2 different sql sessions) reading rows of the same table in parallel. They will do some processing and update a column to inform the row has been processed.

Thread id:88 will read batch of 5  rows and write into Thread variable
Thread id:99 will read batch of 10 rows and write into Thread2 variable

(So I can see if the processing does overlap)

This is the table structure:

And the database has a READ COMMITTED SNAPSHOT isolation level with row versioning. Version 2012.

The execution will be as following:

Thread 88 will read 1 to 5 rows (that have not been processed or not being processed), lock them and update the thread column to 88 (to inform the thread 88 processed it)

Then (or simultaneously), thread 99 will start and fetch next 10 records And it will pick 6 to 15 (as 1..5 are processed or currently being processed by thread 88).

This is the stored procedure I am using for thread 88:

DECLARE @Thread INT = 88
DECLARE @Continue INT = 1000

DECLARE @Id int
DECLARE @InUse INT

while (@Continue > 0)
BEGIN
    DECLARE MY_CURSOR CURSOR
    LOCAL STATIC READ_ONLY FORWARD_ONLY
    FOR
        SELECT TOP 5 Id
        FROM Names WITH (READPAST, UPDLOCK)
        WHERE inUse = 0
        ORDER BY 1

    BEGIN TRAN
        OPEN MY_CURSOR

        FETCH NEXT FROM MY_CURSOR INTO @Id

        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @InUse = InUse FROM Names where id = @Id

            IF @InUse = 0
            BEGIN
                UPDATE Names
                SET InUse = 1, Thread = @Thread
                WHERE id = @Id
            END

            FETCH NEXT FROM MY_CURSOR INTO @Id
        END
        CLOSE MY_CURSOR
        DEALLOCATE MY_CURSOR

        IF @Id IS NULL
            SET @Continue = 0

    COMMIT

    WAITFOR DELAY '00:00:00:90';
    SET @Continue = @Continue - 1
END

And this for thread 99 (They are the same, just changing number of records to process and the column to inform the thread):

DECLARE @Thread INT = 99
DECLARE @Continue INT = 1000

DECLARE @Id int
DECLARE @InUse INT

WHILE (@Continue > 0)
BEGIN
    DECLARE MY_CURSOR CURSOR
    LOCAL STATIC READ_ONLY FORWARD_ONLY
    FOR
        SELECT TOP 10 Id
        FROM Names WITH (READPAST, UPDLOCK)
        WHERE inUse = 0
        ORDER BY 1

    BEGIN TRAN
        OPEN MY_CURSOR

        FETCH NEXT FROM MY_CURSOR INTO @Id

        WHILE @@FETCH_STATUS = 0
        BEGIN
            SELECT @InUse = InUse
            FROM Names
            WHERE id = @Id

            IF @InUse = 0
            BEGIN
                UPDATE Names
                SET InUse = 1, Thread2 = @Thread
                WHERE id = @Id
            END

            FETCH NEXT FROM MY_CURSOR INTO @Id
        END

        CLOSE MY_CURSOR
        DEALLOCATE MY_CURSOR

        IF @Id IS NULL
            SET @Continue = 0
    COMMIT

    WAITFOR DELAY '00:00:00:90';
    SET @Continue = @Continue - 1
END

When running both simultaneously this is the output:

The batches are gone, and I don't have more 5 and 10 together. For example, thread 88 has a random number of continuous rows processed)

Now the questions:

Is ok to use (READPAST, UPDLOCK) where inUse = 0 to read mutually exclusive records?
If 1 is true, why it starts good and then the batches are gone...?
Anyway, It seems the threads are not overwriting each other as there is no row with Thread 1 and Tread 2 with same Thead id. Is this code good to  read mutually exclusive records?

Thanks!


texts:
I have a Windows 7 client that has a data driven application that connects to our SQL Server 2008 R2.
How can I view all the traffic between this software and the DB?
I would mainly like to see queries and responses.


texts: UNION ALL vs CASE - SQL query optimization

texts:
I need to retrieve the count of both approved and unapproved data from my_table.
And I have tried both queries and succeeded. Which query is optimized and why. Please give suggestion

DDL Query
CREATE TABLE my_table( id int(11) UNSIGNED NOT NULL,
 approved TINYINT(1) NOT NULL,
 invoice_no varchar(9) NOT NULL,
 deleted_at timestamp NULL DEFAULT NULL)

First Query
SELECT
    COUNT(CASE WHEN approved=1 THEN id END) as approved,
    COUNT(CASE WHEN approved=0 and deleted_at is null THEN id END) as unapproved
    FROM my_table

Second Query
 SELECT COUNT(id)
 FROM my_table
 WHERE approved=1
 UNION ALL
 SELECT COUNT(id)
 FROM my_table
 WHERE approved=0 and deleted_at is null

I have run command explain extended and attached the screenshot


texts: How to determine if pg_dumpall is still running

texts:
Environment:

Postgres 8.4

Debian Squeeze LTS

Recently we switched from a backup solution around hot_backups to using pg_dumpall.

This solution used a pid file as long as the hot_backup was running, once finished, it would remove the pidfile.

For (re)storing the Dump on another server we would use a script to check for the pid, and transfer the Dumpfile if no PIDfile exists.

As the hot_backup was quite easy to spot in case something went wrong (f.e. more than 24h dumptime) Issues which arised were promptly figured out and fixed.

As we now use pg_dumpall i am in the research of how to better determine if the dump is finished or still ongoing.

Currently we use the same PID approach as before.

However i would like to have a query/command/something to check if the dump is still running or not from within postgres.


texts: As a SQL Server DBA, what do I need to know about the meltdown/spectre vulnerabilities?

texts:
If you didn't hear, a set of related vulnerabilities were recently discovered that impact virtually all processors sold over the last decade. You can find more technical detail about the meltdown/spectre vulnerabilities on InfoSec.SE.

As a SQL Server DBA, what do I need to understand about this?

If we don't share our SQL Servers (or our vm farms) with other companies, is this still a risk?

Will this be simply an OS patch? Or are there patches/hotfixes available for SQL Server that are required to address this vulnerability? Which SQL Server versions will be patched?

Some articles are predicting a 5-30% performance impact, especially in highly virtualized environments. Is there any way to predict what the performance impact on my SQL Servers might be?


texts: Consistency Errors running CHECKDB on a couple of tables in SQL Server 2014

texts:
While running DBCC CHECKDB I am getting consistency errors on a couple of tables in SQL Server 2014.

I have tried REPAIR_REBUILD and rebuilding indexes on those tables but these are not helpful to repair the tables.

I have the corrupted Page IDs with me. By using this, can I get the exact corrupted rows?

Or else, how can I repair the tables without allowing data loss?

Also, I want to know, how can I find out the exact corrupted Rows.

I am running CHECKDB on a table of size 300 GB, which is in a database of size 500 GB. The error that I am getting is as follows:

We take full backups on daily basis. I have a good backup of 6th Jan 2017 . Post this date, all the backups have corrupted data. We don't have log backups.

How can I do a page only restore?

It is a SharePoint database server. As a DBA without going to support case to Microsoft, what else can I do in this situation?  The application team want to know the exact corrupted rows so that they can delete those rows.


texts:
I have a small and very old access DB, it is just one .mdb file and it was made before Access 2003.

How can I import it to SQL server running on AWS RDS? I actually only need to import one table from it which has around 600 rows.


texts:
We have a Java application that uses a connection pool with Oracle database, during the week these conections are frequently refreshed (closed and opened again), the problem is at some point (after two or three days) the application is unable to acquire new connections, it get stuck on that line:

Runner004" prio=10 tid=0x00007f4b2c119800 nid=0x12b6 runnable [0x00007f4b00151000]
   java.lang.Thread.State: RUNNABLE
        at oracle.jdbc.driver.T2CConnection.t2cCreateState(Native Method)
        at oracle.jdbc.driver.T2CConnection.logon(T2CConnection.java:530)
        at oracle.jdbc.driver.PhysicalConnection.connect(PhysicalConnection.java:662)
        at oracle.jdbc.driver.T2CDriverExtension.getConnection(T2CDriverExtension.java:54)
        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:560)
        at java.sql.DriverManager.getConnection(DriverManager.java:571)
        at java.sql.DriverManager.getConnection(DriverManager.java:215)
        at TesteDB.getConnection(TesteDB.java:238)
        at TesteDB$Runner.run(TesteDB.java:285)

Version information:

Oracle Instant Client 12.1.0.2.0 on Linux.
The database version is 11.2.0 or 10.2.0.

Troubleshooting we did, when the database stopped to respond:

Connect through SQLPLUS doesn't respond anything. It just waits forever.
TNSPING doesn't respond anything.
lsnrctl status doesn't respond anything.
Nothing on alert.log
We rebooted the listener, same problem.
We rebooted the database service, it started to work.

We have absolutely no idea what we should look into, any suggestions?


texts:
I would like to disable InnoDB dead-lock detection and automatic rollback, as per the requirement we like to rely on innodb_lock_wait_timeout (because of heavy concurrency).

  The lock wait timeout value does not apply to deadlocks when
  innodb_deadlock_detect is enabled (the default) because InnoDB detects
  deadlocks immediately and rolls back one of the deadlocked
  transactions. When innodb_deadlock_detect is disabled, InnoDB relies
  on innodb_lock_wait_timeout for transaction rollback [...]

How can we achieve the above in MySQL 5.7.13? I couldn't find any variable innodb_deadlock_detect.


texts: Can't do any changes to a job in SQL 2000

texts:
I have a job in my SQL 2000 server, this job is used to execute a DTS, i want to change the schedule of this job.

but every time i try to change of delete or stop this job i get the error message

ERROR 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server. The job was not saved.

i have a system shutdown coming up in the weekend and i cant stop this job...