texts: Is MySql's LAST_INSERT_ID() function guaranteed to be correct?

texts:
When I do a single row INSERT to a table that has an AUTO_INCREMENT column I'd like to use the LAST_INSERT_ID() function to return the new AUTO_INCREMENT'ed value stored for that row.

As many Microsoft SQL Server devs and admins no doubt are aware the equivalent functionality in SQL Server (SCOPE_IDENTITY and @@IDENTITY) hasn't been without its problems.

I know the MySQL docs state:

  The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENTvalue generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

(source)

and even go so far as to say:

  Using LAST_INSERT_ID() and AUTO_INCREMENT columns simultaneously from multiple clients is perfectly valid.

(source)

Are there any known risks or scenarios that may cause LAST_INSERT_ID() not to return the correct value?

I'm using MySQL 5.5 on CentOS 5.5 x64 and Fedora 16 x64 and the InnoDB engine.


texts:
Identical tables, identical columns, identical records, but why different record sizes?

USE [test]
GO

CREATE TABLE [dbo].[mybit](
    [col1] [bit] NOT NULL,
    [col2] [bit] NOT NULL,
    [col3] [bit] NOT NULL,
    [col4] [bit] NOT NULL,
    [col5] [bit] NOT NULL,
    [col6] [bit] NOT NULL,
    [col7] [bit] NOT NULL,
    [col8] [bit] NOT NULL,
    [col9] [bit] NULL
)
GO

INSERT INTO [dbo].[mybit]
VALUES (1,1,1,1,1,1,1,1,NULL)
GO 2

DBCC IND(test, mybit, -1);
GO

DBCC TRACEON(3604);
DBCC PAGE(test, 1, '#pagenumber', 1);

Both records are 10 bytes in size.

CREATE TABLE [dbo].[mybit2](
    [col1] [bit] NOT NULL,
    [col2] [bit] NOT NULL,
    [col3] [bit] NOT NULL,
    [col4] [bit] NOT NULL,
    [col5] [bit] NOT NULL,
    [col6] [bit] NOT NULL,
    [col7] [bit] NOT NULL,
    [col8] [bit] NOT NULL,
)
GO

INSERT INTO [dbo].[mybit2]
VALUES (1,1,1,1,1,1,1,1)
GO

ALTER TABLE [dbo].[mybit2] ADD [col9] [bit] NULL
GO

INSERT INTO [dbo].[mybit2]
VALUES (1,1,1,1,1,1,1,1, NULL)
GO

DBCC IND(test, mybit2, -1);
GO

DBCC TRACEON(3604);
DBCC PAGE(test, 1, '#pagenumber', 1);

One record is 9 bytes in size and another is 10.


texts: Count where two or more columns in a row are over a certain value [basketball, double double, triple double]

texts:
I play a basketball game which allows to output its statistics as a database file, so one can calculate statistics from it that are not implemented in the game. So far I've had no problem caluclating the statistics I wanted, but now I've run into a problem: counting the number of double doubles and/or triple doubles a player made over the season from his game statistics.

The definition of a double double and a triple double is as follows:

Double-double:

A double-double is defined as a performance in which a player accumulates a double-digit number total in two of five statistical categories—points, rebounds, assists, steals, and blocked shots—in a game.

Triple-double:

A triple-double is defined as a performance in which a player accumulates a double digit number total in three of five statistical categories—points, rebounds, assists, steals, and blocked shots—in a game.

Quadruple-double (added for clarification)

A quadruple-double is defined as a performance in which a player accumulates a double digit number total in four of five statistical categories—points, rebounds, assists, steals, and blocked shots—in a game.

The "PlayerGameStats" table stores statistics for each game a player plays and looks as follows:

CREATE TABLE PlayerGameStats AS SELECT * FROM ( VALUES
  ( 1, 1,  1, 'Nuggets',    'Cavaliers',  6,  8,  2, 2,  0 ),
  ( 2, 1,  2, 'Nuggets',     'Clippers', 15,  7,  0, 1,  3 ),
  ( 3, 1,  6, 'Nuggets', 'Trailblazers', 11, 11,  1, 2,  1 ),
  ( 4, 1, 10, 'Nuggets',    'Mavericks',  8, 10,  2, 2, 12 ),
  ( 5, 1, 11, 'Nuggets',       'Knicks', 23, 12,  1, 0,  0 ),
  ( 6, 1, 12, 'Nuggets',         'Jazz',  8,  8, 11, 1,  0 ),
  ( 7, 1, 13, 'Nuggets',         'Suns',  7, 11,  2, 2,  1 ),
  ( 8, 1, 14, 'Nuggets',        'Kings', 10, 15,  0, 3,  1 ),
  ( 9, 1, 15, 'Nuggets',        'Kings',  9,  7,  5, 0,  4 ),
  (10, 1, 17, 'Nuggets',      'Thunder', 13, 10, 10, 1,  0 )
) AS t(id,player_id,seasonday,team,opponent,points,rebounds,assists,steals,blocks);

The output I want to achieve looks like this:

| player_id |    team | doubleDoubles | tripleDoubles |
|-----------|---------|---------------|---------------|
|         1 | Nuggets |             4 |             1 |

The only solution I found so far is so awful it makes me puke ... ;o) ... It looks like this:

SELECT
  player_id,
  team,
  SUM(CASE WHEN(points >= 10 AND rebounds >= 10) OR
               (points >= 10 AND assists  >= 10) OR
               (points >= 10 AND steals   >= 10)
                THEN 1
                ELSE 0
      END) AS doubleDoubles
FROM PlayerGameStats
GROUP BY player_id

... and now you're probably also puking (or laughing hard) after reading this. I didn't even write out everything that would be needed to get all double double combinations, and omitted the case statement for the triple doubles because it's even more ridiculous.

Is there a better way to do this? Either with the table structure I have or with a new table structure (I could write a script to convert the table).

I can use MySQL 5.5 or PostgreSQL 9.2.

Here is a link to SqlFiddle with example data and my awful solution I posted above: http://sqlfiddle.com/#!2/af6101/3

Note that I'm not really interested in quadruple-doubles (see above) since they don't occur in the game I play as far as I know, but it would be a plus if the query is easily expandable without much rewrite to account for quadruple-doubles.


texts: Do shorter names exist for "one-to-many", "one-to-one", "many-to-many" relationships?

texts:
In terms of generic variable naming or even everyday geek discussion, do shorter and "cleaner" terms exist for one-to-one, one-to-many, and many-to-many relationships.

For example, in discussion of religion, one can say "relating to many denominations of Christianity", but it's much shorter to say, "ecumenical". In computer science, "Your arrangement of words and punctuation is incorrect" is less awkwardly said, "You have a syntax error."
Do such terms exist for the common database cardinalities?


texts: InfluxDB on Raspberry Pi 3 and load issues

texts:
first of all, I'm not an database expert at all, but I managed to set up a system fitting my monitoring needs for more than one year now. Unfortunately, now the problem arises. Short summary of my system:

Raspberry Pi 3 (Raspbian buster) is running InfluxDB 1.8.5
Python script is running as a service, writing ~20 datapoints every 5sec plus additional several 100 per month on demand. I would say, that is not too much in total.

My database is now >1GB:
/var/lib/influxdb $ sudo du -hd1
12K     ./meta
39M     ./wal
1.1G    ./data
1.2G    .

I started to notice several days/weeks ago, that my system got really laggy. uptime reported load averages >4. I suspected the pretty old sd-card and switched to a proper new one, first only by putting the old image to the new card, later I really reinstalled everything and restored the influx data. It got better, but not really good. uptime reported now load averages in the range of 2. I added a monitor for uptime in my python script and it looks like this:

(at ~22.30 I restarted influxdb)
I did some more analysis and found that I can read the influx log with the command sudo journalctl -u influxdb.service. In the result I find lots of lines with the content similar to
Apr 26 21:04:22 xxx influxd[5598]: ts=2021-04-26T19:04:22.133912Z lvl=info msg="Error replacing new TSM files" log_id=0TksnBt0000 engine=tsm1 tsm1_strategy=full tsm1_optimize=false trace_id=0TktwGCG000 op_name=tsm1_compact_group db_shard_id=534 error="cannot allocate memory"

And here my knowledge really stops. Does anyone know what the issue is and what I can do to "repair" my database without loosing more data?
EDIT:
After some more research, my conclusion is that the root cause is my 32bit raspberry pi operating system with the limited RAM. Influx performs some kind of periodic housekeeping where the database is compressed and this fails on my system with slightly >1GB of database.
After all, it is just a hobby for me and I never really used proper databases. Influx was quite appealing for me, at least what I read made sense and I was able to set everything up quite easily. For my purpose I think of a workaround that I monitor my data with a high granularity every year in a new database. With that, I should not suffer the size limit of the individual databases. I'll have an additional database for the daily key measures. This should only consume some MB after several years.
Do you think that could work out or could there be some other pitfall?


texts: Whats SQL Server NVARCHAR(max) equivalent in MySQL?

texts:
Using SQL Server you just have to give the "MAX" parameter to the length of a text data type, but in MySQL there's no such a thing.

According to Ispirer:

  "n" is the maximum number of characters, optional

  Range:    1 ⇐ n ⇐ 21845 (65535 bytes is the maximum row size shared among all columns)

Does it mean that:

[SQL Server] "NVARCHAR(MAX)" == [MySQL]"NVARCHAR(N)"

Or do i have to say NVARCHAR(21845) as NVARCHAR(MAX) in MySQL?


texts: Upgrading from mysql 5.7 on FreeBSD to mariadb or MySQL8

texts:
I am looking into upgrading some major components of a webserver (php, apache, mysql).

I've done the update to 5.7 from 5.6. I've updated to php7.3.

What I wonder now is: I see that a mysql community edition is available (version 8). After a first install it failed to start so I rolled back to 5.7.
Now I'm wondering if it's worth the trouble migrating to mysql8 or even if I better would update to mariadb. And in the latter: which version of mariadb would I use or which one corresponds with mysql5.7 which I currently have installed.

Is mariadb/mysql8 stable?

For clarity. This is a production server. I'm not looking for new fancy features. But perhaps some more recent versions have better performance.

Thanks a lot in advance!


texts: How to implement table purge with partition on SQL Server 2012?

texts:
I've read this question:
How to remove a table partition

But it's not what I want.

I'd like to setup a table, using partitioning on a date/time column to have 1 month partitions. Afterwards, configure a job that executes montlhy and drops the partition(s) that have records over 9 months old.

Can I set this up using SQL Server 2012 Enterprise?


texts: How do you truncate a `date` (not `timestamptz`) to month?

texts:
Postgres has date_trunc which operates on timestamp or interval, and:

  Values of type date and time are cast automatically to timestamp or interval, respectively.

In other words we can use date_trunc for date values with a cast:

select date_trunc('month',current_date)::date;
┌────────────┐
│ date_trunc │
├────────────┤
│ 2014-12-01 │
└────────────┘

But is this timezone safe—if the current date is in Daylight Saving Time and the start of the month is not or vice versa will the correct date result?


texts: SQL Server Replication, is it possible to have "upload only" syncronization on some articles?

texts:
Within SQL Server's merge replication, I know how to make publication's article download-only (either with update restrictions on subscriber database or not) - @subscriber_upload_options parameter in sp_addmergearticle, or corresponding choices in the SSMS's GUI.

What I would like to achieve though is the reverse behavior on some articles. So that records that originate on the subscriber's side travel up to the publisher during syncronization, but not the other way around. And that updates made on the publisher are not sync'ed back to the subscriber, while updates made on subscriber do go up to the publisher. I.e. exactly reverse of the download-only articles.

Is this possible to achieve? Either with "standard" configuration options of the replication or with some manual hacks?


texts: How to add unique key dependant on another composite unique key

texts:
I have products related to a custom pages, I want to display these products on the page in a specific order for this I have created a new table;

CREATE TABLE `am_landing_page_products`
(
  `rel_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Relation ID',
  `page_id` smallint(6) NOT NULL COMMENT 'Xlanding ID',
  `product_id` int(10) unsigned NOT NULL DEFAULT '0' COMMENT 'Product ID',
  `position` int(11) NOT NULL DEFAULT '0' COMMENT 'Position',

  PRIMARY KEY (`rel_id`),

  UNIQUE KEY `UNQ_AM_LANDING_PAGE_PRODUCT_LANDING_ID_PRODUCT_ID` (`page_id`,`product_id`),

  UNIQUE KEY `uniq_position` (`page_id`,`position`),

  KEY `IDX_AM_LANDING_PAGE_PRODUCT_PRODUCT_ID` (`product_id`),

  CONSTRAINT `FK_AM_LANDING_PAGE_PRD_PRD_ID_CAT_PRD_ENTT_ENTT_ID`
    FOREIGN KEY (`product_id`)
    REFERENCES `catalog_product_entity` (`entity_id`)
    ON DELETE CASCADE ON UPDATE CASCADE,

  CONSTRAINT `fk_landingpage`
    FOREIGN KEY (`page_id`)
    REFERENCES `am_landing_page` (`page_id`)
    ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1

Here product_id and page_id columns are compositely unique, now I want that for each unique combination of page_id and product_id, the position should be unique. Can someone help me with this.

Edit

I added position to unique key also, this will make the things, but is there any way where I enter page_id and product_id and the position is populated with incremented value for that combination?


texts: Optimizing mysql queries running on 50 million rows

texts:
Is there any technique/advice in order to optimize the performance of the queries below?

According to my needs, my average db size will be approximately 30mill. rows per day, so every second less, will make huge difference.

My DB engine is innoDB and I'm using 1 core CPU, with 2GB RAM.

Exec Time : 7,5 sec (5,5 mil. rows)

SELECT vpn_group, username, from_interface_addr_ip
FROM SystemEventsR
WHERE (timestamp > ( NOW( ) - INTERVAL 10 MINUTE ) AND
SysLogTagflag=1 AND
username !='')
GROUP BY username

Exec Time : 88,4 sec (5,7 mil. rows)

SELECT syslogtagid, DeviceReportedTime, class, definition, SystemEventsR.SysLogTag,
COUNT(id) AS Records,
explanation, action
FROM SystemEventsR,oa_syslogtags
WHERE  (SystemEventsR.SysLogTag = oa_syslogtags.syslogtag AND
flagid = 1 AND DATE(timestamp) = DATE(NOW()))
GROUP BY SystemEventsR.SysLogTag
ORDER BY Records DESC

Any help would be appreciated.


texts: How to insert SP results in a multiple tables

texts: I have an sp it results in 3 sets. Now the problem is i want insert these 3 result sets in to 3 temp tables. In these three results are different structures and no.of columns also different in these results.


texts: MariaDB 10.2 InnoDB internal data dictionary problem

texts:
I've recently upgraded my MariaDB installation on a Windows Server 2012 to a stable release 10.2.6.
However I'm having a problem with one particular table.

Each time MariaDB starts, it logs the following:

  2017-05-23  1:28:53 4180 [ERROR] InnoDB: SYS_TABLES::TYPE=1697 page_compression:0 page_compression_level:3 atomic_blobs:1

  2017-05-23  1:28:53 4180 [ERROR] InnoDB: Table dbname.tablename in InnoDB data dictionary contains invalid flags. SYS_TABLES.TYPE=1697 SYS_TABLES.N_COLS=2147483655

I've tried REPAIR on this table, but it says the table does not exist in the engine.

If I try to drop this table, I get

  2017-05-23  1:58:07 5076 [ERROR] InnoDB: SYS_TABLES::TYPE=1697 page_compression:0 page_compression_level:3 atomic_blobs:1
  2017-05-23  1:58:07 5076 [ERROR] InnoDB: Table dbname.tablename in InnoDB data dictionary contains invalid flags. SYS_TABLES.TYPE=1697 SYS_TABLES.N_COLS=2147483655

  2017-05-23  1:58:07 5076 [ERROR] InnoDB: incorrect flags in SYS_TABLES

  2017-05-23  1:58:07 5076 [ERROR] InnoDB: Table dbname.tablename does not exist in the InnoDB internal data dictionary though MariaDB is trying to drop it. Have you copied the .frm file of the table to the MariaDB database directory from another database? Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.

Afterwards, MariaDB thinks this table does not exist, though InnoDB doesn't think so. When I try to rename a newly created table to the same name, I get:

  2017-05-23  2:03:30 5076 [ERROR] InnoDB: Possible reasons:

  2017-05-23  2:03:30 5076 [ERROR] InnoDB: (1) Table rename would cause two FOREIGN KEY constraints to have the same internal name in case-insensitive comparison.

  2017-05-23  2:03:30 5076 [ERROR] InnoDB: (2) Table dbname.tablename exists in the InnoDB internal data dictionary though MySQL is trying to rename table dbname1.tablename to it. Have you deleted the .frm file and not used DROP TABLE?

  2017-05-23  2:03:30 5076 [Note] InnoDB: Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.

  2017-05-23  2:03:30 5076 [ERROR] InnoDB: If table dbname.tablename is a temporary table #sql..., then it can be that there are still queries running on the table, and it will be dropped automatically when the queries end. You can drop the orphaned table inside InnoDB by creating an InnoDB table with the same name in another database and copying the .frm file to the current database. Then MySQL thinks the table exists, and DROP TABLE will succeed.

One of the solutions on the Internet is to recreate the whole database, which is not an option for me due to a long downtime.

So far I've ended up creating a table with the same structure using Aria engine. But how can I get it working with InnoDB?

Any tools out there to work with that InnoDB data dictionary?

Additionally, trying to SELECT from information_schema.INNODB_SYS_TABLES crashes the whole server.


texts:
I'm a newbie at Database Designing. What I want to do is perform several actions at once.

I have a main users table which holds all the data for a user at a website. Then I have a primary key as the user_id in this table. This user_id is also the referred in various other tables.

So, what I want to do is connect all those table data to the main table so that when the main user is deleted from the users table then all the data related to that user is also deleted. How can I do that?

I know there is a function of foreign keys somewhere but I'm a really amateur programmer.


texts: Best practices for ensuring up to date sequences after table import

texts:
We perform weekly table moves between several schema using expdp and impdp. Some tables have sequences associated with the ID columns and I'd like to ensure that the sequences are always up to date on the destination table after a load.

Is the following sequence of events a safe and acceptable way of handling this?

Backup sequence properties to a table
Drop sequences
Load tables (with metadata)
Find which has the greatest last cached value, backup sequence table or new imported sequence.
Update accordingly

Are there any pitfalls with this method, and if so are there tried and tested methods?


texts: Can someone explain why select with nolock will query a potion of updated data?

texts:
I was reading the answer from here (from stackoverflow, I think should ask in here)

  NOLOCK means placing no locks at all.

  Your query may returns portions of data as of before UPDATE and
  portions as of after UPDATE in a single query.

I get that nolock will not place lock to the table, so other people can query the same time.

From the answer and example it show, it fetch data while the data is being updating.

Why does that happen?

I am assuming for normal select it will try place lock on table, so when update statement is executed, it place a lock on the row or page.  Then when I try to run select statement, it cannot put the lock until the update statement lock is released.

But in this case because the select statement doesn't try to put lock on the table, so it can run without waiting for the update statement release the lock?


texts: Postgres heap vs SQL Server clustered index

texts:
I am transitioning from SQL Server to Postgres, and one of the biggest things for me to digest is the non-existence of the "clustered key" that sorts the data in Postgres.

Can someone share their thoughts on how Postgres avoided the need for an internally sorted dataset and how it works with large heap tables and still supply exceptional performance?


texts:
Let's see if I can edit this and put the whole procedure in.

I am trying to convert an Oracle database to MySQL. I have all the tables, keys, indexes, and views converted. I now need to convert a stored procedure to MySQL.

I have most of it done, and there is only one hang up on my code:

set dns1_tmp = X.X.X.X;
SET dns2_tmp = X.X.X.X;

This gives me an error of 1064 Syntax Error: Missing semicolon

I have tested the rest of my procedure, and it works fine. It creates it, runs it, and retrieves data from it, but only if I remove those two lines.

Any ideas on what I can do?

Whole stored procedure:

DELIMITER //

USE `TEST`//

DROP PROCEDURE IF EXISTS `proc_IN`//
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_IN`
(IN DNIS VARCHAR(20),
IN MSISDN VARCHAR(20),
IN AVPAIR1 VARCHAR(20),
IN AVPAIR2 VARCHAR(20),
IN GROUPID VARCHAR(20),
OUT DNS1 VARCHAR(15),
OUT DNS2 VARCHAR(15),
OUT AUTHSTAT VARCHAR(100))
BEGIN
declare dns1_tmp varchar(15);
declare dns2_tmp varchar(15);
set dns1_tmp = X.X.X.X;
SET dns2_tmp = X.X.X.X;
DECLARE avpair1_tmp varchar(15);
DECLARE avpair2_tmp varchar(15);
DECLARE grpid_tmp varchar(15);

DECLARE C_USER CURSOR FOR SELECT AVPAIR1, AVPAIR2, DNS1, DNS2, GROUPID FROM GRP, ALLMEMBER WHERE ALLMEMBER.GROUPID=GRP.GROUPID
    UNION
    SELECT AVPAIR1, AVPAIR2, DNS1, DNS2, GROUPID FROM GRP;
OPEN C_USER;
FETCH C_USER INTO AVPAIR1, AVPAIR2, DNS1, DNS2, GROUPID;
LOOP
    FETCH C_USER INTO avpair1_tmp, avpair2_tmp, dns1_tmp, dns2_tmp, grpid_tmp;
    INSERT INTO duplog VALUES(DNIS, MSISDN, avpair1_tmp, avpair2_tmp, dns1_tmp,dns2_tmp, grpid_tmp, SYSDATE);
END LOOP;
IF C_USER%ROWCOUNT > 1 THEN
        INSERT INTO duplog VALUES(DNIS, MSISDN, AVPAIR1, AVPAIR2, DNS1,DNS2, GROUPID, SYSDATE);
            SET AUTHSTAT := 'ok';
        elseif C_USER%ROWCOUNT = 1 THEN
            SET AUTHSTAT := 'ok';
ELSE
            SET AUTHSTAT := NULL;
END IF;

CLOSE C_USER;
COMMIT;
END //
DELIMITER ;

UPDATE:

I have tried single quotes, double quotes, and back ticks. When I do the code in this fashion

set dns1_tmp = 'X.X.X.X';
SET dns2_tmp = 'X.X.X.X';

I get a different error of Syntax Error: Missing end


texts: Shortcuts/Snippets to select a block of code

texts:
I am using SQL Server Management Studio 2016 on Windows 10. I'm tired of using shift/ctrl+up/down/right/left keys to select the code I want to run. I'm wondering whether there are shortcuts/snippets to select a block of code that separated from other code by blank lines?

Here is a code example:

select *
from tab1

select *
from tab2

select *
from tab3

Say, my cursor is inside the middle block and what's the best way to select the middle block?


texts: Why does referencing a variable in a join predicate force nested loops?

texts:
I came across this issue recently and couldn't find any discussion of it online.

The query below

DECLARE @S VARCHAR(1) = '';

WITH T
     AS (SELECT name + @S AS name2,
                *
         FROM   master..spt_values)
SELECT *
FROM   T T1
       INNER JOIN T T2
         ON T1.name2 = T2.name2;

Always gets a nested loops plan

Attempting to force the issue with INNER HASH JOIN or INNER MERGE JOIN hints produces the following error.

  Query processor could not produce a query plan because of the hints
  defined in this query. Resubmit the query without specifying any hints
  and without using SET FORCEPLAN.

I found a workaround which does allow hash or merge joins to be used - wrapping the variable in an aggregate. The plan generated is significantly lower costed (19.2025 vs 0.261987)

DECLARE @S2 VARCHAR(1) = '';

WITH T
     AS (SELECT name + (SELECT MAX(@S2)) AS name2,
                *
         FROM   spt_values)
SELECT *
FROM   T T1
       INNER JOIN T T2
         ON T1.name2 = T2.name2;

What is the reason for this behaviour? and is there a better workaround than the one that I found? (that perhaps doesn't require the extra execution plan branches)


texts:
I have a PostgreSQL table with a key (bigint) and a value (double). The table has tens of billions of rows. I have a single btree on the (key,value) for aid lookups by key. The table is never updated.

The only query I perform on this table is an equality predicate on the key to fetch the corresponding value, which makes use of the B-tree.

The storage consumed by PostgreSQL is terrible here. It stores the OID, key, value in the table and stores key, value in the index. I am in essence storing everything twice!

How do I configure this table so that it is space efficient? Ideally, how can I store the tuple just once in the B-tree.


texts: I am using Db2 Recovery Expert and having error with adding Managed Databases

texts:
I have Db2 11.1 installed in my windows machine and created a Linux 32 bit in my virtualbox (unfortunately my intel doesn't allow me to change the virtualization method..anyway).

I installed Recovery Expert 5.1 in this Linux box and was able to sucessfully configure my repository database (which again, resides in my windows machine). I saw 15 new connections to the repository database, all was fine. So it was the time to add a managed database. That fails with the error CTGRI0011E. Which for me doesn't make sense because I am using the same IP address, port number, user and password for when I had set my repository database.

The Datastore information:

The repository details:

When adding the managed database:

The error finally:

If that was an error with firewall, remote access, etc...wouldn't it fail when I created my repository? It looks like a bug to me, but everytime I get back to some support, I get that things work as designed.


texts: Insert a file into PostgreSQL BYTEA field using PHP

texts:
The previous answers have been horribly complex for the scope of this project.
I want to take the $_POST result as a variable and insert it into a BYTEA field of a table.

$_SESSION['username'] = $username;
$file = $_POST["file"];

file is the file itself submitted from the form on the previous page

pg_prepare($conn, "register_user_info", 'INSERT INTO fileDB.assignment(pawprint, name, number, section, file) VALUES ($1, $2, $3, $4, $5)');
pg_execute($conn, "register_user_info", array($username, "1", "1", "1", $file));

I need to insert the file itself into the file column of my database. How can I make this happen?


texts: use alter user to lock all user accounts except scott

texts: How do I use alter user to lock all user accounts and password expire them while picking out select users to exclude from the command?


texts:
I am taking over a new project which is having ~2500 SPs! and would like to know at current state what is the maximum / minimum time taken by each of the stored procs. Also IO, Logical Reads used by each of the SPs. Would like to benchmark it that way. After each new drop would use this benchmark to compare whether the changes done has helped or not.

Can DMVs or Extended Events be of help here? or do i need to run a trace covering my complete work load to capture this?


texts: Joining tables and seeing Duplicate Records

texts:
I Use the query below to produce values from a table (BSCV1), based on results from another table (MshiftV1) using an inner Join on names and timestamps. I call the output "Correlation".

WITH Meanshift
AS
(
Select TOP 20
  Timestamp
  ,Name
  ,Value
From  MshiftV1
order by Value desc
)
select
Timestamp
,Name
,Value
From BSCV1
inner join Meanshift
ON (Meanshift.Name = LEFT(BSCV1.Value, 12) and   BSCV1.Timestamp = Meanshift.Timestamp )

I need to create another table called "Final_Meanshift" where I use the "Meanshift" subquery in the SQL code above but restrict it to only show values that also exist in the "Correlation" output.

I tried this:

WITH Meanshift
AS
(
Select TOP 20
  Timestamp
  ,Name
  ,Value
From  MshiftV1
order by Value desc
)
,Correlation
AS
(
select
Timestamp
,Name
,Value
From BSCV1
inner join Meanshift
ON (Meanshift.Name = LEFT(BSCV1.Value, 12) and   BSCV1.Timestamp = Meanshift.Timestamp )
)
Select TOP 20
  Timestamp
  ,Name
  ,Value
From  MshiftV1 as MSHIFTV2
Left Join Correlation on Correlation.Timestamp = MSHIFTV2.Timestamp AND Correlation.NAME = MSHIFTV2.NAME

This almost gets the job done, except that it produces duplicates of every record in "Final_Meanshift". NOTE: it is a "many-to-one" relationship (Many Correlation Matches to One Meanshift Match)

Any ideas?
Thanks!


texts: Export and import a table without a specific column

texts:
I'm trying to export a table with an Auto Increment rowid column and then import it in another server.
Considering that the column is primary key and can't accept duplicate keys if i import rows after backup from old server, new rows with same rowid would be added to new server.
i also don't want to change start number for Auto Increment in new server and cause gaps in rows.
so i thought one solution can be exporting new rows without rowid and letting new server to assign new rowid to them. but how can i do that directly with mysql or with help with phpMyAdmin ?


texts: WiredTiger prefix compression for index on MongoDB

texts:
According to the MongoDB documentation (at least this blog entry https://www.mongodb.com/blog/post/new-compression-options-mongodb-30), prefix compression is enabled by default on MongoDB.

However, when I look at one collection, I see this:

db.myCollection.stats()
{
...,
    "wiredTiger" : {
        "metadata" : {
            "formatVersion" : 1
        },
        "creationString" : "...,prefix_compression=0,prefix_compression_min=4"
...
}

Does prefix_compression=0 means that my indexes are not prefix-compressed ?


texts: Queries for unique names from a site table using multiple criteria

texts:
So I'm just learning SQL but have wound up as a database manager. The software they use is overall easy but is very restrictive in the reports it can create, though it does allow custom SQL reports.

As such, I need to find a way to do one (or all) of several things, #4 now being the most important:

Select unique site names which have been referred to at some point.
Select unique site names and how many times they have been referred to.
Select unique site names and the last date they were referred to.
Select unique site names which have never been referred to.

And do each of these without including sites which have been deleted.
Being new to SQL, I haven't even figured out #1 yet.

Relevant tables and columns:

╔══════════════╦═════════════════╦════════════════════════╗
║    Table     ║    Columns      ║    Settings            ║
╠══════════════╬═════════════════╬════════════════════════╣
║ site         ║ site_id         ║ PK, int, not null      ║
║ 207 columns  ║ name            ║ varchar(100), not null ║
║              ║ deleted         ║ smalldatetime, null    ║
╠══════════════╬═════════════════╬════════════════════════╣
║ site_service ║ site_service_id ║ PK, int, not null      ║
║ 192 columns  ║ site_id         ║ in, not null           ║
║              ║ deleted         ║ smalldatetime, null    ║
╠══════════════╬═════════════════╬════════════════════════╣
║ calls        ║ call_id         ║ PK, int, not null      ║
║ 183 columns  ║ call_num        ║ char(10), not null     ║
║              ║ call_date       ║ datetime, null         ║
║              ║ deleted         ║ smalldatetime, null    ║
╠══════════════╬═════════════════╬════════════════════════╣
║ referrals    ║ referral_id     ║ PK, int, not null      ║
║ 5 columns    ║ call_id         ║ int, not null          ║
║              ║ site_id         ║ int, null              ║
║              ║ site_service_id ║ int, null              ║
╚══════════════╩═════════════════╩════════════════════════╝

I just found this working query in the custom reports of the program. It is labeled "Referral statistics-Existing sites".

SELECT rl_ss_s.name, COUNT(cl.call_num) AS [NumberOfCalls]
FROM calls cl
LEFT JOIN referral rl ON cl.call_id=rl.call_id
LEFT JOIN site_service rl_ss ON rl.site_service_id=rl_ss.site_service_id
LEFT JOIN site rl_ss_s ON rl_ss.site_id=rl_ss_s.site_id
WHERE (cl.call_date BETWEEN 'ASK LATER: DATETIME' AND 'ASK LATER: DATETIME')
AND cl.deleted IS NULL
AND rl_ss_s.deleted IS NULL
AND cl.client_group_id='1'
GROUP BY rl_ss_s.name

I removed the WHERE filter for cl.client_group_id (as I'm not sure how it helps), then ran it. It does appear to meet goals 1 & 2, but has a glitch. Among the records contained in the report is a single blank-name site with roughly 4 times as many calls as the most referred to named site. As such, I'm not sure if it's combining all the deleted sites or something else. I can easily accommodate for it in the generated Excel file by deleting that line if it is combining deleted sites, I just have to figure out what it's doing.

I know what most of the functions in that example do, I just haven't figured out how to put it together to do what I need.

If you need any more information I have not included or clarification on anything, please let me know.
Any ideas how I put this together to achieve one (or all) of these goals?


texts: Safe online dumping of an InnoDB database in the presence of DDL changes

texts:
When dumping an InnoDB database using mysqldump, it is often recommended to use the --single-transaction and --skip-table-locks options.  This allows for minimal blocking of other concurrent reads + writes to the database.

However, there is the following nasty gotcha in the documentation:

  While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log position), no other connection should use the following statements: ALTER TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.

My backups are automated and periodic.  I have no easy way to ensure that no one is going to issue such a command against a table while the dump is in progress.

So, I was wondering if there is a simple way to raise the isolation level of the dump from 'repeatable read' to 'serializable' and whether or not this would solve this gotcha while still minimizing blockage of concurrent reads + updates?

I believe that running with --lock-tables or --lock-all-tables instead doesn't suffer from the above gotcha, but I believe these approaches cause far more blocking.

I'm wondering if there is a happy middle ground between the two approaches?  Or, if I want a surely safe and consistent dump then I still need to go with --lock-tables, or even maybe --lock-all-tables, instead of --single-transaction --skip-lock-tables?

Thanks!


texts:
I'm creating a custom extension for PostgreSQL. I can get the extension to create successfully, but my foo--0.1.sql is getting rather large and complex. I'd like to break it out into multiple files for ease of maintenance but I can't figure out from the docs how to do this.

It seems like I could go one of two routes:

Use a built-in functionality with PG's extension building infrastructure to automatically use all of my .sql files.
Use the Makefile to concatenate all of my .sql files when the extension is first built.

My questions are whether #1 is even possible (I can't see anything in the docs - it only references the single extension--version.sql file that is required).

Also, how would I do #2? I don't have much experience with Makefiles and can't quite figure it out.

Thanks!


texts: Dynamically change column names in query in PostgreSQL

texts:
There is a table with a bunch of columns. I need to capture the column name and dynamically generate queries based on a given column.
Table definition:
id int,
at_11 charcater varying(50),
at_12 charcater varying(50),
at_13 charcater varying(50),
at_14 charcater varying(50)

And the sample rows in the table are:
1,0,0,0,0
2,0,'Low',0,0
3,'High',0,0,0

CREATE OR REPLACE FUNCTION return_at_query(p_column character varying)
 RETURNS TABLE(at_id character varying, v_id numeric)
 LANGUAGE plpgsql
AS $function$
declare
    v_cnt int;
begin
       RETURN QUERY EXECUTE
       format('SELECT $1, id
               FROM tab1 WHERE $1 <> 0'
            )
   USING p_column;
end;$function$;

When I call the function by passing in a column name value 'at_11', I expect to see the results as 'High',3. But I am getting the whole list of rows instead. How can I accomplish this?


texts: What specific tests need to be performed under Database migration from DB2 to Oracle?

texts:
Currently I am going to be testing (I am a Data Analyst) of a larger migration project where they are planning to migrate from DB2 to Oracle.
My question may be little broader; however any information which relates to the following questions would certainly be helpful.

1) How to validate both under Source and Target tables for:

Matching of Total Number of tables
Matching of Data fields under each
table

2)  How to validate from Front end (UI), so that right data is getting populated under right table:
Challenge

There are 500+ UIs, What is the
best way to test in an Optimized way
(to avoid any duplicate testing)
Should I also focus on table mapping
to UI screen

3)  Any specific challenges I need to see under migration

Datalock, Spacing issue, Noisy words


texts: Does InnoDB DROP INDEX release disk space without OPTIMIZE TABLE?

texts: We are using MySQL 5.6 InnoDB storage engine and innodb_file_per_table is ON. We have some huge tables (> 100GB) with hundreds of millions of rows. For some huge tables, index space accounts for more than half of the table space. We found some indexes are redundant (thanks to Percona Toolkit). Could we simply drop the redundant indexes to release the disk space used by the index without OPTIMIZE TABLE?


texts:
Hi I have a view which is used in lots of search queries in my application.
The issue is application queries which use this view is is running very slow.I am investigating this and i found out a particular portion on the view definition which is making it slow.

create view Demoview AS

Select
    p.Id as Id,
    ----------,
    STUFF((SELECT ',' + [dbo].[OnlyAlphaNum](colDesc)
        FROM dbo.ContactInfoDetails cd
        WHERE pp.FormId = f.Id AND ppc.PageId = pp.Id
        FOR XML PATH('')), 1, 1, '') AS PhoneNumber,
    p.FirstName as Fname,
From
---

This is one of the column in the view.
The scalar function [OnlyAlphaNum] is making it slow,as it stops parallel execution of the query.

The function is as below;

CREATE FUNCTION [dbo].[OnlyAlphaNum]
(

@String VARCHAR(MAX)

)

RETURNS VARCHAR(MAX)
WITH SCHEMABINDING
AS
BEGIN

   WHILE PATINDEX('%[^A-Z0-9]%', @String) > 0

        SET @String = STUFF(@String, PATINDEX('%[^A-Z0-9]%', @String), 1, '')

RETURN @String

END

How can i convert it into an inline function.?
I tried with CASE ,but not successful.I learned that while loop functions cannot me converted to ITVF.It can be converted to MTVF.Another option is Tally Table which i have no idea about.
Can someone help me to convert it to TVF?


texts:
I am new to SQL and PHP. I'm having a problem with selecting the last record from my table. I run the following query:

SELECT * FROM 2521118 ORDER BY id DESC LIMIT 1

My table name is 2521118 and the fields are:

id (set for auto-incrementing)
timestamp
meter_readings

Here is my error:

  Error in query (1064): Syntax error near '2521118 ORDER BY id DESC LIMIT 1' at line 1

Can someone please help me out with this?


texts: ~2 Hours after a large insert SQL Server gets "I/O requests taking longer than 15 seconds to complete"

texts:
This problem has been haunting me for a while but I actually fixed the queries that caused the problems (after I finally found them) but i'm curious if it's an SQL Process that's the underlying problem our that our raidcontrollers are shit or what the problem truely is. So here's the scenario that's been bothering us.

@ 11:10~ and 01:10~ the server would give these IO errors, even through Remote desktop the servers would fail, management studio would give timeouts, everything would point towards imminent failure of the cluster.

(The servers are identical, we run Windows Server 2012 standard, clustered in an AlwaysOn running SQL Server 2012 Enterprise edition).

Around 5-15 minutes later, the servers would come back to live (without anything in the eventlogs except for the Event ID 833 which is the "well known": SQL Server has encountered 16870 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\XXXXXXXX.mdf] in database [XXXXX] (10).  The OS file handle is 0x0000000000001284.  The offset of the latest long I/O is: 0x0000041fb3c000).

Now I was running profilers to find out which queries were running, doing anything I could to figure out what would cause the servers to just freeze up and refuse to service properly. The event log aims towards a specific Database to I searched in that direction and yesterday I found a table that looked odd to me.

The specific table was 11 gigs large, had indexes worth 23 gigs (haven't looked into those yet but that seemed wrong to me) and had 80 million records. Considering the table should hold around 2 million and be growing relatively slowly this sparked my interested and I found three stored procedures which fill this table.

All three storedprocedures had flaws which made them insert not a few hundred records but over 500.000 each time they ran. (These are now fixed so the issue doesn't present itself but I feel there is an underlying problem here).

These stored procedures would run @ 09:00 and 23:00 (excatly 2 hours before the problems occurred). The procedures themselves would take around 5 minutes to run to completion.

Now I'm wondering why it would take the servers two hours to start dying on me after the actual storedprocedures in question would already have been done. I don't run any index maintenance at that point, or anything else for that matter (it's the first thing I checked for). For all I know nothing runs @ the specific times. Could it be SQL Server does some 'self maintenance' and notices that some indexes need to be defragmentated or something even if I haven't asked for any such thing?

Could it be our raidcontrollers (or even the SSD's that are connected to them) that are flawed and after A LOT of activity decide to show signs of degration after 2 hours?

I simply can not explain this behavior and I'm worried our production servers might be unreliable (which would suck since they're around 4 months old). Now i've fixed this issue last evening, and for 24 hours we haven't had this problem (I wouldn't call it 'resolved' unless it's been stable for multiple days) but I'm still very curious what the hell my servers are doing.


texts: How to three way inner join to three way outer join in MySQL?

texts:
We are currently joining three MySQL tables based on the summation of a common key within each table:

select a.common_key, a.val, b.val, c.val from
  (select common_key, sum(val) val from table_a group by common_key) as a,
  (select common_key, sum(val) val from table_b group by common_key) as b,
  (select common_key, sum(val) val from table_c group by common_key) as c
where a.common_key=b.common_key and a.common_key=c.common_key;

This works fine, but now we want to include in our results values from a, b or c for which there is no matching values in the other tables.

In other words, we want to go from a three way inner join to a three way outer join, but we are having a hard time figuring out both the syntax and
also how much harder a processing task it would be for our database.

As requested, here is what we get from SHOW CREATE TABLE table_a\G:

+---------+-----------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                |
+---------+-----------------------------------------------------------------------------------------------------------------------------------+
| table_a | CREATE TABLE `table_a` (
  `common_key` int(11) DEFAULT NULL,
  `val` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
--------------------------------------------------


texts: Backup internals - What happens when a backup job is running - in terms of locking and performance overhead in SQL Server?

texts:
For MySQL I know the database is backed up table by table in SQL Statements, this results in locking and if you update columns while backing up you might end up in integrity problems.

To my understanding this does not apply for the Microsoft SQL Server, but how does the SQL Server handles this? Is there some internal freeze to keep the db consistent?

Also I heard that backing up is single threaded meaning it uses only one core, assuming you backup to a single file. Also assuming you have a multicore machine, for example 16 cores, or at least a significant larger number than one.

From my personal experience I never had issues when taking backups, neither locking nor overhead issues, but my experience is limited.
That's why I always recommend turning backup compression on in the server properties.

So what happens when a backup job is running? And also are there significant differences for the different versions? for example 2008,2012 and 2014 (not the licenses).


texts: Single Publication With Multiple Subscribers

texts:
What should I be aware of before adding multiple subscribers to single publication?

Obvious ones I can think of are server capacity and network bandwidth.

Should I be aware of anything else?


texts: Is there possible chache query or save it in memory in MySql? Mayby Should I use something other?

texts:
I have complicated query (some joins and ordering), that I want to execute very often. Results of query can change very rarely.

Query gets words form given language and category sorted by frequency, so it have two parameters - language and category.

I heart about materialized views, or databases in memory, bu I do not have experience enough to consider cons and props in reasonable way.

I want to have possibility execute this query for these parameters, and next not execute it until I decide manually, that it must be refreshed. In practice, all application based on executing this query many times, so I do not know if can I create one view in memory, when database use innodb.

I can change mysql on something else, and innodb too if is it recommended.


texts: How to get last level of a hierarchical list, group by some field

texts:
Given the schema:

create table containers (
    id int primary key,
    name text,
    product_id int,
    lot int,
    qty int,
    parent_id int);

create table orders_items (
    id int,
    position int,
    container_id int);

Where containers is a hierarchical table with an unknown number of levels.

containers.parent_id = containers.id OR null

A container can hold other containers, this is a containers tree structure:

  23 Box 40 Parent  [ID: NULL, Lot: NULL, Qty: SUM(20+20)=40]
   |
   |--22 Bag 20     [Parent ID: 23, Lot: NULL, Qty: SUM(10+10)=20]
   |   |--21 Bag 10 [Parent ID: 22, Lot: 701, Qty: 10]
   |   |--22 Bag 10 [Parent ID: 22, Lot: 703, Qty: 10]
   |
   |--19 Bag 20     [Parent ID: 23, Lot: NULL, Qty: SUM(10+10)=20]
       |--17 Bag 10 [Parent ID: 19, Lot: 700, Qty: 10]
       |--18 Bag 10 [Parent ID: 19, Lot: 701, Qty: 10]

For us a container is considered as one expeditionary unit, depending on customer requirements a container can be a simple plastic bag of 10 pieces, or a full pallet with 10 boxes of 5000 pieces each one. Only containers of last level has a lot assigned, parent containers can hold different lots, but always belonging to same product.

We need to list all products included in a single order, group by lot.

Different products can't have the same lot.
One container can only hold containers of the same product.

Sample data:

insert into containers values
  (23, 'Box 40',  2, null, 40,  null)
, (16, 'Pallet',  1, null, 120, null)
, (12, 'Bag 20',  1, null, 20,  14)
, (13, 'Bag 20',  1, null, 20,  14)
, (14, 'Box 40',  1, null, 40,  16)
, (19, 'Bag 20',  2, null, 20,  23)
, (22, 'Bag 20',  2, null, 20,  23)
, (5,  'Bag 20',  1, null, 20,  7)
, (6,  'Bag 20',  1, null, 20,  7)
, (7,  'Box 40',  1, null, 40,  16)
, (1,  'Bag 10',  1, 500,  10,  5)
, (2,  'Bag 10',  1, 501,  10,  5)
, (3,  'Bag 10',  1, 502,  10,  6)
, (4,  'Bag 10',  1, 500,  10,  6)
, (8,  'Bag 10',  1, 600,  10,  12)
, (9,  'Bag 10',  1, 601,  10,  12)
, (10, 'Bag 10',  1, 502,  10,  13)
, (11, 'Bag 10',  1, 501,  10,  13)
, (15, 'Box 40',  1, 600,  40,  16)
, (17, 'Bag 10',  2, 700,  10,  19)
, (18, 'Bag 10',  2, 701,  10,  19)
, (20, 'Bag 10',  2, 703,  10,  22)
, (21, 'Bag 10',  2, 701,  10,  22);

insert into orders_items values
(1, 1, 16),
(1, 2, 23);

The order number 1 has two details lines, containers 16& 23, I need to get all lots included in these containers.

In this example the result should show this rows:

+----+------------+-------------+------+------+------------+
| id |    name    |  product_id |  lot |  qty |  parent_id |
+----+------------+-------------+------+------+------------+
|  1 |  'Bag 10'  |           1 |  500 |   10 |          5 |
|  2 |  'Bag 10'  |           1 |  501 |   10 |          5 |
|  3 |  'Bag 10'  |           1 |  502 |   10 |          6 |
|  4 |  'Bag 10'  |           1 |  500 |   10 |          6 |
|  8 |  'Bag 10'  |           1 |  600 |   10 |         12 |
|  9 |  'Bag 10'  |           1 |  601 |   10 |         12 |
| 10 |  'Bag 10'  |           1 |  502 |   10 |         13 |
| 11 |  'Bag 10'  |           1 |  501 |   10 |         13 |
| 15 |  'Box 40'  |           1 |  600 |   40 |         16 |
| 17 |  'Bag 10'  |           2 |  700 |   10 |         19 |
| 18 |  'Bag 10'  |           2 |  701 |   10 |         19 |
| 20 |  'Bag 10'  |           2 |  703 |   10 |         22 |
| 21 |  'Bag 10'  |           2 |  701 |   10 |         22 |
+----+------------+-------------+------+------+------------+

Group by lot:

|----------|---------|-----|----------|
| Order ID | Product | Lot | Quantity |
|----------|---------|-----|----------|
|    1     |    1    | 500 |    20    |
|    1     |    1    | 501 |    20    |
|    1     |    1    | 502 |    20    |
|    1     |    1    | 600 |    50    |
|    1     |    1    | 601 |    10    |
|    1     |    2    | 700 |    10    |
|    1     |    2    | 701 |    20    |
|    1     |    2    | 703 |    10    |
|----------|---------|-----|----------|

I've created a rextester example with this values.


texts: Constraint to prevent overlapping Time periods

texts:
I have a pretty simple table that stores a StartTime and an EndTime, both with the data type of Time(0).  I need to enforce a constraint that prevents any overlapping. 'Touching' time periods are OK, like 2pm-3pm and 3pm-4pm.  I have created a function to use for the constraint but I am having a hard time dealing with time periods that span the midnight hour.  Any help would be appreciated!

SQL Fiddle

This is driving which hours that a product is available every day.  So unfortunately I can not attach an exact date to these time periods.

If the product is always available, the StartTime and EndTime would be the same (i.e. 00:00:00 and 00:00:00). I know this is probably bad design.

The active periods are just for a 24 hour window.

The requirement for EndTime to be greater than StartTime was removed (phew!).  I have accepted the answer below, thank you!


texts: MySQL - Maintain History of Records but Using Most Recent

texts:
Let's say I'm developing a database for a transportation service which owns certain vehicles and hires drivers which are assigned to specific vehicles. The database comprises of the following tables:

vehicles

id | number | brand | model | color | date_registered

drivers

id | passport_number | first_name | last_name | dob | address | date_registered

vehicle_drivers

id | vehicle_id | driver_id | date_assigned

daily_vehicle_reports

id | vehicle_id | milage | remaining_fuel | problems_detected | observations | recommendations | report_date

Where daily_vechicle_reports records the state of the vechile at the end of each day.

At the end of each month, I would generate data on all reports for the given month. Now my problem is, given that the possibility that at any given day, the driver of any vehicle can be changed, the data on the previous drive remains in the database, and normally, to generate the data I would use a query similar to the following:

SELECT
    dvr.*,
    v.number,
    v.date_registered,
    d.passport_number,
    d.first_name,
    d.last_name,
    d.dob,
    d.address
FROM daily_vehicle_reports dvr
INNER JOIN vehicles v ON(v.id = dvr.vehicle_id)
INNER JOIN vehicle_drivers vd ON(vd.vehicle_id = v.id)
INNER JOIN drivers d ON(d.id = vd.driver_id)
WHERE MONTH(dvr.report_data) = 4
AND YEAR(dvr.report_data) = 2019

This would give me repeated data for the reports are multiple drivers would be found for the same vehicle. How can I modify the query so as to include ONLY the last driver assigned by that date (the date of the report)?


texts: How to delete mdf file in use but not attached?

texts: I have a mdf file that is not attached but when I try to delete it Windows Explorer fails due to the file being in use. SQL Manager also says it's in use when I try to attach the MDF so that I can delete it. Is there a way to kill the connection and delete the file without stopping the SQL service?  Thanks


texts:
I have an Oracle 11g XE set up in Moscow, Russia, and therefore on MSK time zone. When a client in the same timezone stores a DATE variable from Java with, say time set to 12:00, the time is changed to 08:00 when I read it back. The Oracle data type is DATE (no TZ) and there is no timezone handling in my Java client.

The output of the following queries to check timezone settings in Oracle are:

select dbtimezone, sessiontimezone from dual;
DBTIMEZONE = +00:00
SESSIONTIMEZONE = Europe/Moscow  (which I guess is +03:00)

So the sessiontimezone is probably the culprit, but why does it affect non _TZ columns and what is the best way to solve it?


texts: Should PgBouncer be installed on web application server or database server?

texts:
I have a django website where the application and database server are different. I need to install pgbouncer to enable database pooling, however, I'm confused about where to install it: web application server or database server?

Note that in this set up, there is exactly 1 application server and 1 database server.


texts: Limit TLS and Cipher Suites on SQL Server 2016-2019

texts:
I'm wanting to configure some SQL 2016+ servers to use only TLS 1.2+ and a subset of Cipher suite options (no ciphers considered weak or compromized).  I'm having trouble tracking down how to configure these options.

Where would I find the documentation in how to do this, and does anyone have any examples... for comparison, I'm using the following options with NginX, and could probably limit SQL Server to a subset of 256bit only.

ssl_ciphers ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-CHACHA20-POLY1305:ECDHE-RSA-CHACHA20-POLY1305:DHE-RSA-AES128-GCM-SHA256:DHE-RSA-AES256-GCM-SHA384;


texts: How to crosstab with a composite "row name"

texts:
I have following table with id(intger), project (varchar), month (varchar), hours (integer)

CREATE TABLE foo (id,project,month,hours)
    AS VALUES
    ( 1 , 'A', 'aug', 1 ),
    ( 1 , 'A', 'sep', 3 ),
    ( 1 , 'B', 'aug', 2 ),
    ( 1 , 'B', 'sep', 5 ),
    ( 2 , 'A', 'aug', 2 ),
    ( 2 , 'A', 'sep', 4 );

I wanted below table

|id | project | aug | sep |
|1  | A       | 1   | 3   |
|1  | B       | 2   | 5   |
|2  | A       | 2   | 4   |

I read this can be possible with an array but it seems like documentation is confusing. Can someone shed some light on this?


texts:
I have this related tables:

And I need to get those devices with no driver assigned, in other words the device returned on this query shouldn't be on driver_has_device table since it'll not have driver assigned, can any give me some help on this query?


texts: Error when restoring database from disk using a variable

texts:
I have the following query in SQL Server 2014. The backup of the database completes in approx 150 seconds but the restore/verify step fails with an error.

Can you tell me where I'm going wrong?

I don't understand why I can save the backup using a variable, but I'm unable to restore using the same variable.

Msg 137, Level 15, State 2, Line 36
Must declare the scalar variable "@BackupPath".
Msg 319, Level 15, State 1, Line 36
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Line 36 is the RESTORE VERIFYONLY line.

DECLARE @BackupPath varchar(1000)
SELECT @BackupPath = (SELECT N'F:\Backups\DBNAME_' + REPLACE(convert(nvarchar(20),GetDate(),120),':','-') + '.bak')
PRINT @BackupPath

BACKUP DATABASE [DBNAME] TO  DISK = @BackupPath WITH COMPRESSION, NOFORMAT, NOINIT, NAME = N'DBNAME-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
DECLARE @backupSetId as int
SELECT @backupSetId = position from msdb..backupset where database_name=N'Partner' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'DBNAME' )
IF @backupSetId is null
BEGIN
       RAISERROR(N'Verify failed. Backup information for database ''Partner'' not found.', 16, 1)
END
RESTORE VERIFYONLY FROM  DISK = @BackupPath WITH  FILE = @backupSetId,  NOUNLOAD,  NOREWIND
GO


texts: azure sql - create index, does DB have to be offline?

texts:
New to Azure SQL. Simple example to ask question.
Table Cars.
Want to add an non clustered index to Cars table.
Takes a long time, say 30 min during staging test deploy.

When upgrading production Azure SQL, can we leave the database online while building a table index? During this time while the table is building, can applications send inserts/updates to this table?

Or do we need to take the production DB offline for 30 to build this index?


texts:
I have a SQL Server instance that is used solely for the purpose of reporting and also for preparing data to be loaded into Analysis Services.  Its data comes from another SQL Server instance which is the production instance.

As this database can be rebuilt at any time from the production OLTP database, we've been thinking about putting its log file in memory (using something like RAMDisk).

I've done just that and, to my surprise, we haven't gained much performance; it's become only about 2x better.

Given that I'm no longer writing the log to disk, why is the performance gain this low?  The CPU is not reaching anywhere near 100%.  Where is the bottleneck now?

Note: We have also tested successfully with minimally logged operations using the trace flag 610, but for the purpose of this question please just ignore this.


texts: Table with only one column because of many-to-many relationship?

texts:
Right now I have three tables: person, alias, and person_alias. As suggested by the names of tables, one person can have multiple aliases and one alias can be used by many people, and hence I add a joining table person_alias during the process normalization.

However, the table alias essentially only contains one column (if I do not choose to use numeric surrogate key). In addition, let's say the table person also contains a bunch of other useful information that is irrelevant to the question here.

I am not a bit stuck at justifying setting up the entity relationship map this way. What is the problem that I delete the table alias all together? Or do I misunderstand many-to-many relationship/normalization?


texts: Why is MySQL using this index? Can I force it to use another?

texts:
I have a query, and when I call EXPLAIN for it, it appears that my db is using an unhelpful index and ignoring a useful index.
EXPLAIN SELECT id, full_name, agent_id, brand_id FROM `mytable`
WHERE (
    `mytable`.`brand_id` IN (21264)
    OR
    `mytable`.`agent_id` IN (4, 5, 6, 7, 9, 11)
    )
    AND (full_name IS NOT NULL AND full_name != '')
    AND (full_name LIKE '%kath%' OR id = 12 OR id = 0)
    ORDER BY full_name ASC LIMIT 15

(That query was auto-generated, hence the strange parentheses.)
The table has a few inidices:
+-----------+------------+------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name                               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------+------------+------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| mytable |          0 | PRIMARY                                |            1 | id          | A         |     1329001 |     NULL | NULL   |      | BTREE      |         |               |
| mytable |          1 | mytable_by_agent_id                    |            1 | agent_id    | A         |        9561 |     NULL | NULL   | YES  | BTREE      |         |               |
| mytable |          1 | mytable_by_brand                       |            1 | brand_id    | A         |        4598 |     NULL | NULL   | YES  | BTREE      |         |               |
| mytable |          1 | mytable_by_full_name                   |            1 | full_name   | A         |     1329001 |     NULL | NULL   | YES  | BTREE          |         |               |
| mytable |          1 | index_mytable_on_brand_id_and_agent_id |            1 | brand_id    | A         |          11 |     NULL | NULL   | YES  | BTREE      |         |               |
| mytable |          1 | index_mytable_on_brand_id_and_agent_id |            2 | agent_id    | A         |        1205 |     NULL | NULL   | YES  | BTREE      |         |               |
+-----------+------------+------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

And I 'd expect the query to make use of index_mytable_on_brand_id_and_agent_id, but instead, EXPLAIN reveals that it's using mytable_by_full_name:
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: consumers
         type: range
possible_keys: PRIMARY,mytable_by_agent_id,mytable_by_brand,mytable_by_full_name,index_mytable_on_brand_id_and_agent_id
          key: mytable_by_full_name
      key_len: 258
          ref: NULL
         rows: 811112
        Extra: Using where

How can I get this to use the other index? Why did it choose an index which won't help (because of the % at the start of the LIKE)?


texts: Backing up file groups with restore to earlier backup

texts:
I have a task: I have created a database with a filegroup lamb that has a few tables.

I need to extract a csv into a table in a filegroup named lamb and back up that filegroup.

Then extract another csv file again and back up again.

But I need to be able to restore the first backup.

What is the backup and restore method? Is it possible to restore only the lamb filegroup?


texts: What is the fastest way to verify that a json[] array is not empty?

texts:
I want to verify that an json[] column the_array is not empty. For a normal array, I could check on:

the_array != '{}'

However, that does not work for a json[], as demonstrated below:

select '{}'::json[] != '{}'
ERROR:  could not identify an equality operator for type json

I could use the following check instead:

array_length(the_array, 1) != null // yes, array_length on an
                                      empty array returns null

My fear is that array_length() would iterate over the whole array to count the number of items and then return that count. In my case, I don't need the actual size of the array, I just need to know whether it is empty or not.

So, does array_length() iterate over the whole array? And if so, is there a better way to check if a json array is empty?


texts: Combine various temporal criteria to filter rows

texts:
I am trying to query a database table with some filters. I have a form through which a user will create query attributes:

When user will click on search it will pass such values:

 {camera_id: "9879"
, from_date: "2015/05/15"
, to_date: "2016/08/15"
, interval: "120"
, schedule: "{"Monday":["3:0-4:0"],"Tuesday":["1:30-3:30"]
             ,"Wed…":["0:0-3:0"],"Saturday":[],"Sunday":["1:0-3:0"]}"}

interval has one of these names and values:

"All", "0"
"1 Frame Every 1 min", "1"
"1 Frame Every 5 min", "5"
...
"1 Frame Every hour", "60"
...
"1 Frame Every 24 hours", "1440"

For example: "1 Frame every 1 min". If 5 rows in snapshots have been taken within 1 min, the query shall only return the first.

schedule is being sent by using the FullCalendar jquery plugin. Meaning:

Day: Monday,    Time: 03:00 AM to 04:00 AM
Day: Tuesday,   Time: 01:30 AM to 03:30 AM
...

Table snapshots:

  snapshot_id integer
, camera_id   integer
, created_at  timestamp

I want to select rows passing the filters. Temporal filters apply to created_at. I want rows between from_date and to_date, considering the schedule, and only get the first row per interval.


texts: How would i create a report involving multiple many to many relationships

texts:
Lets say table A has a many to many relationship with table B, and table B has a many to many relationship with C. Two junction tables are created for AB and BC to form the many to many relationships.
How would i query these tables so that i can return all the related records between tables A, B, and C?

Is a setup like this a sign of bad design?

Edit: To list my specific issue, I have a table of electronic devices. Each device could have a feature such as wifi, and each feature could apply to that device. Now each feature could pose a security risk, and that same risk could apply to multiple features. So I have many devices, with many features, and many security risks.
My goal is to get the results of a question in this form "For each device, show me each feature, and the security risks that it creates".


texts:
I have two tables books and books_data. books can have multiple books_data, books_data can have only one book. books_data table has date column by which I want to sort the books table. When trying to do this, the code returns duplicates. But the duplicates appear only when trying to paginate results, e.g. if I provide LIMIT 10 OFFSET 0 for first query and LIMIT 10 OFFSET 20 for the second, some of the records from first query will appear in the second query too. If I specify LIMIT 20 OFFSET 0 there are no duplicates. Here's my query (it may be not perfect, because it is generated by Rails ORM Active Record):

SELECT DISTINCT "books"."id", "books_data"."date" FROM "books"
LEFT OUTER JOIN "books_data" ON "books_data"."book_id" = "books"."id"
ORDER BY books_data.date DESC LIMIT 10 OFFSET 0;


texts: Use quoted schema in an insert statement executed thorough PreparedStatement in Java

texts:
I created an unquoted table my_table under a quoted schema "Quoted_User".

When executing the statement

INSERT INTO "Quoted_User".my_table (custom_id) VALUES (DEFAULT)

using the Intellij Oracle browser it works, but when I'm performing the same statement through a PreparedStatement in Java Oracle is complaining that the schema is not existing. Here's the prepared statement Java line:

statement = connection.prepareStatement(insertSQL, new String[]{custom_id});

and here is insertSQL as shown in the inspector:

INSERT INTO \"Quoted_User\".my_table (custom_id) VALUES (DEFAULT)

It's like Oracle is removing the quotes, and I can say this because I noticed the uppercase letters in the error message which are always returned when the object is considered to be case insensitive.

Error returned by Oracle:

java.sql.SQLSyntaxErrorException: ORA-04043: object "QUOTED_USER" does not exist

There should be a way to use quoted schema names with prepared statements I guess.

Thanks for your hints,

Mattia


texts:
Morning all!  I have a program and database I created that pulls down data from a website (player cost for MLB teams for a game) and I aggregate that data and use it for various things.

One of the things I need to find is the total cost per team for the cheapest 10, 20, and 30 players.  I've tried a number of suggestions I've seen online and none seem to work for me.

My database has the following fields:

Player | Overall | Sell | Buy | Series | Dash | Team | League | Division | Position | Pull_Date_Time

Here's what I currently use to get the total cost for each team:

select team, sum(buy) from Players
where series = 'Live' and pull_date_time = '2019-04-09 08:46:04'
group by team
order by sum(buy)

I need the same data - just only selecting the cheapest 10, 20 and 30 players for each team and grouped by team like shown above.  I do need to include the where clause that I'm currently using.  I don't need this to be just 1 query - I fully expect that each count (10, 20, 30) will need it's own query.


texts:
can you explain what meaning of hard code query? i'm newbie
 like this query:

Query :

SELECT Nama,
SUM(case when motor='vario' then 1 else 0 end) vario,
SUM(case when motor='beat' then 1 else 0 end) beat,
SUM(case when motor='supra'then 1 else 0 end) supra,
SUM(case when motor='megapro'then 1 else 0 end) megapro,
SUM(case when motor='cb150'then 1 else 0 end) cb150
FROM matrixreport
GROUP by Nama;

Is it hard code?


texts:
Take this example:

SELECT SalesOrderID,
       CustomerID,
       SalesPersonID,
       OrderDate
FROM Sales.SalesOrderHeader
ORDER BY OrderDate,
         SalesOrderID DESC
OFFSET 0 ROWS
FETCH NEXT 10 ROWS ONLY;

I understand ORDER BY when one column is specified but how does the ORDER BY clause order the table by two or three columns?  Which one is ordered first?  What if the highest value in the SalesOrderID column does not correlate with the highest value in the OrderDate column?


texts: Multiple tables vs rows for timescaledb / postgresql

texts:
I am designing a postgresql/timescale database where I will be recording trades on multiple stocks. For each stock I will be saving the following columns: Trade time, Attribute 1, Attribute 2, Attribute 3
As far as I can tell I have 2 options of saving this data:

Creating 1 table for each Stock
Using 1 giant table and adding a column with the Stock ID

I will frequently need to access information across time for single stocks but also often for all stocks at once. Which is the better solution for me to choose here.


texts: 64-Bit OLEDB Provider for ODBC (MSDASQL) issues

texts:
I need to create a Linked Server from SQL 2008 SP1 64 bit instance located on a Windows 64 bit 2003 SP2 server towards a Mysql Server. As many of you know MSDASQL provider for 64 bit systems was put available by Microsoft later in a hot fix. This is what I have done so far:

Install MySQL connector.
Install MSDASQL provider.
Create ODBC connection with correct credentials. Test is succesfull.

When I try to create a Linked Server specifying the new DSN created I get the following error:

  Cannot initialize the data source
  object of OLE DB provider "MSDASQL"
  for linked server "MYSQL". OLE DB
  provider "MSDASQL" for linked server
  "MYSQL" returned message "Specified
  driver could not be loaded due to
  system error  998 (MySQL ODBC 5.1
  Driver).". (Microsoft SQL Server,
  Error: 7303)

I mention that I didn't reboot the server after installing the hotfix containing the OLEDB provider since it is a Production server and I must have a very good reason for doing this. Is the restart required?

I installed process monitor and I saw that before throwing the error SQL tries to get access to two paths:

Path: C:\Program Files\MySQL\Connector ODBC 5.1\myodbc5.dll.2.Config
Result: NAME NOT FOUND

Path: C:\Program Files\MySQL\MySQL Server 5.1\share\charsets\
Result: NAME NOT FOUND

I gave full rights on C:\Program Files\MySQL\Connector ODBC 5.1 to user which tries to get the access.

Does anybody know how I can fix this?

Thanks,


texts: Transaction and error handling - `statement_timeout`

texts:
I am trying to create very simple transaction which will set timeout for a single query.

The transaction looks like:

BEGIN;
SET LOCAL statement_timeout TO 8000;
SELECT pg_sleep(8);
COMMIT;

When I run it throws exception as expected

sql> BEGIN
[2019-01-25 09:36:01] completed in 2 ms
sql> SET LOCAL statement_timeout TO 8000
[2019-01-25 09:36:01] completed in 3 ms
sql> SELECT pg_sleep(8)
[2019-01-25 09:36:10] [57014] ERROR: canceling statement due to statement timeout

but I am unable to run another query. I need to run manually ROLLBACK;

sql> SELECT 1
[2019-01-25 09:38:21] [25P02] ERROR: current transaction is aborted, commands ignored until end of transaction block
[2019-01-25 09:38:21] [57014] ERROR: canceling statement due to statement timeout

Is it possible to somehow catch the exception and do ROLLBACK in transaction explicitly? I thought this handles the database engine.


texts: Can you use multiple types of indexes in one composite index

texts:
I've built a covering index for a pretty big query which has already halved the select time.

Now, all the columns are just INT(11)'s so there isn't a problem there but one of the columns is a VARCHAR(255) whose performance I'm able to improve by using a FULLTEXT index.

Right now I'm forcing my query to use my covering index as somehow the optimizer doesn't think my index would be faster even though it is, but now I'm wondering if it's possible to specify which indexing algorithm to use per column on the composite index so that I can do:

id INT(11) - BTREE

code VARCHAR(255) - FULLTEXT

col3 INT(11) - BTREE

col4 INT(11) - BTREE

col5 INT(11) - BTREE

col6 INT(11) - BTREE

If I preview the SQL for adding the index on the table, at the end it says USING BTREE; so I was wondering if it's possible to specify it on a per-column basis


texts: Connect to remote EC2 Postgresql database

texts:
I want to setup a connection between a webserver and a PostgreSQL 9.3 server. Both are hosted on Amazon EC2 servers and are part of the same security group. I can't get the connection working. The same goes for a direct connection from my desktop to Postgres, something we have done with another Postgres server, the one which is going to be replaced by this one.

This is what I did up til now:

Checked Amazon Security Groups: Server is part of the Default group, and the Default group accepts traffic from my local IP for all ports: this works for other servers and I can connect to the server using SSH.
IPTables firewall service is stopped on the server.
PostgreSQL is running: I can setup an SSH tunnel and then connect using PGadmin, open databases etc. So the server is running, and the login is correct. Even if this would work for connecting from my desktop, it is not a solution for the connection between the servers. I suspect though that if I get this working, the other connection will work as well.
pg_hba.conf: I copied the connection list from the other server, and my local IP is in here, as well as the local IP (10.x.x.x) of the Amazon webserver.

After installing the server I changed the database password for the postgres user using MD5. As the connection over the SSH tunnel worked, I guess this should work for all connections automatically, but I'm not sure. I haven't setup the other server, and when I query the postgres users table (pg_catalog.pg_user), I don't see any difference between the two servers.

I'm not 100% sure about the security groups, so I'm going to look into that again, but besides that I don't know where to look.

How can I get the connection working?

Update

Netstat output

netstat -an | grep 5432
tcp        0      0 127.0.0.1:5432              0.0.0.0:*                   LISTEN
unix  2      [ ACC ]     STREAM     LISTENING     63088  /tmp/.s.PGSQL.5432
unix  2      [ ACC ]     STREAM     LISTENING     63087  /var/run/postgresql/.s.PGSQL.5432

Telnet

Unable to connect to remote host: Connection refused


texts: MariaDB: User defined attributes with Dynamic Columns

texts:
I am trying to put together some pieces when it comes to my current task to allow the user to create some User Defined Fields.

While doing my research I came across the Dynamic Columns feature of MariaDB.
Has anyone got a use case for the latter and how they have been benefited from that?

If that's not a recommendable solution for User Defined fields, what are your thoughts on EAV(Entity-Attribute-Value Implemetation) pattern?

[ITEM]
======
id
name
attributeId
languageId

[ITEM_DESCRIPTIONS]
==================
id
itemID
description
languageId


texts: what is the password to user mongodb when mongodb is installed in ubuntu?

texts:
When I installed mongodb in ubuntu, user mongodb and group mongodb are created in /etc/passwd and /etc/group. But I have no idea of the password to these user and group. I have to run mongod as root user, which is not suggested. Someone suggests to change the owner of mongodb data and log files to mongodb:mongodb. But I have no idea how to run mongod as mongod?
Any idea?


texts: Selectively load data using Oracle 11g r2 External Table Preprocessor

texts:
I am using the Preprocessor feature in Oracle 11g r2 to load several data files in a table. However, the raw files seem to have certain rows that shouldn't be imported with the rest. For example:

col1    col2    col3    col4
 A       1       2       3
 A       2       3       4
 B       ab      bc      cd

So, I want to only load the rows with col1 = 'A' and load the rows with col1 = 'B' into another table. How can I do this inside the processor?


texts:
I have a unique situation, where I need to anonymize a column quickly. By that I mean any way to remove the data, whether it is NULL'ing, blanking or something else. There can be up to 20 million records to update and there are no indexes on the column I am updating.

I have tried a couple of things, for instance:

Update TABLE Set COLUMN = NULL

This clearly is the worst performing query. I have modified it to exclude any current NULLs or blanks from that set, but it is still very slow.

I have tried to drop and re-create the column, which so far is instant. But unfortunately because of business reasons, the column order is important, so this ruins that. (For the purpose of the question assume reordering columns is impossible).

Recently I tried to change the column type to char(1), then back to text - which had better performance. However, after seeing it run lightning fast with a drop and re-create column, I am curious if there is a way to do this with keeping the column order intact. Clearly SQL Server is capable of producing ~20 million records as NULLs instantly - there must be a clever way around this?


texts: What is the best smallest data type size for 19 digit number?

texts:
I'm using this code to get a mix of Date and Time to use it like a unique ID

DateTime.Now.ToString("yyyyMMddHHmmssfffff")

In SQL Server 2017 I'm confused as to which best smallest size of data type would be that I can use to store this string.

What is the best smallest data type: bigint, timestamp or varchar(19)?


texts: Auto Switch DB to primary back (persistent mode?) when available in a High Availability Always On settings

texts:
We have Always On Availability Group settings, and we have a preferred DB that needs to be in primary all the time even after it reboots.

Once a month or so, these 2 servers DB1 and DB2 reboot for upgrade/updates, however whichever come back online first becomes the primary with HA on, but we prefer to always set DB1 as primary when it comes back online no matter what.

Are there any configs for this, or should we create a trigger?

Thank you


texts: Why is the value - despite of using GREATEST() - still out of range?

texts:
I tried the following to decrease a counter after a post was deleted by using GREATEST() so it does not result a negative value:

SELECT GREATEST(0, posts - 1)
FROM users
WHERE id = 123

but it returns:

[Err] 1690 - BIGINT UNSIGNED value is out of range in '(`db1`.`users`.`posts` - 1)'

posts returns 0:

SELECT posts
FROM users
WHERE id = 123

An the following returns 0 as expected:

SELECT GREATEST(0, 0 - 1)

So what I'm doing wrong?


texts: PostgreSQL synchronized commit performance

texts:
I'm setting up a pair of PostgreSQL servers. Because cash is short I can only afford one high class server and some crapy backup box. If the expensive high end box should catch fire, operations can live with the slower backup system for a while.

I was wondering how much performance does the backup server need? It receives WAL and only has to apply it (I prosume that is the way syncrhonized commits work?). Is WAL always easy to apply, or would for example a delete with many checks on foreign key constraints, also have to do these constraint checks on the slow backup box?

Is the PostgreSQL WAL logical or image based? I'm not looking for absolute numbers, more a kind of answer like: "Applying WAL over TCP in sync commit mode on the hot backup will be cheap in any szenario, except for blahblah".


texts: PostgreSQL pl/perl trigger, differentiate null vs empty

texts:
I've been trying to speed up a generic auditing trigger that I had written in pl/pgsql some time back.  On update, it generates a list of columns in the table being updated and inserts rows in the auditing table recording any changes (by table, column, data before, data after, etc).  The same trigger function is used across multiple tables.

I'm toying with pl/perl as it seems to be much faster for the task at hand, but I seem to have run into a problem on differentiating between NULL and empty string ('') value in the database.

In my mind if a column goes from NULL to an empty string (or vice versa), that's a change that I need to record.  However, using the available new/old column references ($_TD->{new/old}{$columnName}), I can't seem to differentiate between columns that are actually null and those that contain the empty string.  Columns that I know are null are being caught by both the empty and the undef checks, as are columns that I know are empty.

    if($_TD->{new}{$column} eq '') {
        elog(NOTICE, "New value in column $column is empty");
    }
    if($_TD->{old}{$column} eq '') {
        elog(NOTICE, "Old value in column $column is empty");
    }
    if($_TD->{new}{$column} eq undef) {
        elog(NOTICE, "New value in column $column is not defined");
    }
    if($_TD->{old}{$column} eq undef) {
        elog(NOTICE, "Old value in column $column is not defined");
    }

I suspect that I'm doing something silly here, but maybe I'm trying to do something that I simply can't do.  Any advice?

Edit - Using Postgres 8.4.4 for what it's worth

Edit - After looking at filiprem's post below (and lots more testing), I ended up with this, which seems to be working:

    my %newrow = %{$_TD->{new}};
    my %oldrow = %{$_TD->{old}};

    my $valChanged;
    while (($column,$value) = each %newrow) {

        $valChanged = 0;

        if($newrow{$column} ne $oldrow{$column}) {
            $valChanged = 1;
            elog(NOTICE, "Values in column $column differ.  New: $_TD->{new}{$column}, Old: $_TD->{old}{$column}");
        }
        elsif(!defined($newrow{$column}) && defined($oldrow{$column})) {
            elog(NOTICE, "New row contains nulled out field");
            $valChanged = 1;
        }
        elsif(defined($newrow{$column}) && !defined($oldrow{$column})) {
            elog(NOTICE, "New row contains newly populated field");
            $valChanged = 1;
        }

        if($valChanged) {
            ### Update audit table
        }
    }

It catches the difference between empty strings and NULLs and logs them all appropriately in the audit table.


texts: Dealing with co-dependent not null columns

texts:
Say I have a table entities (subclassed by people and organizations tables), where each entry requires a primary_email_id. The entities table looks like this:

entities
========
id INT AUTO_INCREMENT
full_name VARCHAR(255)
primary_email_id
primary_address_id
primary_tel_id

This primary_email_id is a foreign key to a table contacts, which is structured like this:

contacts
========
id INT AUTO_INCREMENT
entity_id INT NOT NULL
type VARCHAR(255) NOT NULL -- ex: "tel", "email", "address", "fax", etc.

and has a companion table contact_attributes:

contact_attributes
==================
id INT AUTO_INCREMENT
contact_id INT NOT NULL
name VARCHAR(255) NOT NULL -- ex: "ext" or "username"
value VARCHAR(255) NOT NULL -- ex: "+25" or "coolboss27"

I think it makes sense requiring that the primary_email_id be NOT NULL and that the entity_id in the contacts table be not null, but this co-dependence creates a problem. When creating an entity for the first time, we must have a primary_email_id to put in the row. But to do this, we must create a contact (and contact_attribute of email: ...@...). However to create that contact, we need to have already created the entity we're trying to create. So we are stuck.

I guess that means that I cannot have both the entity_id in contacts and the primary_email_id in entities be NOT NULL, and presumably primary_email_id is the one that should be nullable. This could be partially solved by including the actual primary_email_address instead of an id, but then the table is no longer normalized and I definitely don't want that.

Is there something I'm missing here or is making primary_email_id nullable just a necessary tradeoff?


texts: Should I nest dependent outer joins in SQL Server?

texts:
I have heard mixed information on this and am hoping for a canonical or expert opinion.

If I have multiple LEFT OUTER JOINs , each dependent on the last, is it better to nest them?

For a contrived example, the JOIN to MyParent depends on the JOIN to MyChild:
http://sqlfiddle.com/#!3/31022/5

SELECT
    {columns}
FROM
    MyGrandChild AS gc
LEFT OUTER JOIN
    MyChild AS c
        ON c.[Id] = gc.[ParentId]
LEFT OUTER JOIN
    MyParent AS p
        ON p.[id] = c.[ParentId]

Compared to http://sqlfiddle.com/#!3/31022/7

SELECT
    {columns}
FROM
    MyGrandChild AS gc
LEFT OUTER JOIN
    (
    MyChild AS c
    LEFT OUTER JOIN
        MyParent AS p
            ON p.[id] = c.[ParentId]
    )
    ON c.[Id] = gc.[ParentId]

As shown above these produce different query plans in SS2k8


texts: SQL Server Backup Sizes Differential, Compressed Backup Sizes

texts:
I'm trying to backup my organization's production databases on SQL Server 2008 R2 Standard.  I'm using a T-SQL script to backup with DIFFERENTIAL and COMPRESSION selected.

I'm astounded at the size of the backups relative to the mdf file sizes.  For example, we have one sharepoint database that is 307 mbs in mdf.  It's backup--with differential and compression selected--is 280 mbs.  How is this compressed or differential?

I've tried backing up using .7z and zip and it doesn't really achieve any more compression.

Am I missing something?  Why would a differential backup compressed be almost the size of the mdf?

Thanks!


texts: Conditional Where Statement Involving a NULL value?

texts:
Trying to find a way to pass off a third value within a 'bit' field. I would like to be able to utilize NULL as a searchable value. As in 'True', 'False', or 'NULL', the only issue is I'm unsure how to go about it. My initial thoughts were some kind of 'if' branch within the statement, but it doesn't appear to allow this kind of syntax. Here is my current WHERE clause. I am trying to make it possible to have [ERROR_FREE] registered as '0', '1' or 'NULL' based on a variable passed from my C# application.

WHERE [TBL_OUTBOUND_REVIEW].[ACTIVE_DIRECTORY] LIKE @UserName AND
      CONVERT(date, [TBL_OUTBOUND_REVIEW].[DATE_SUBMITTED]) LIKE @DateSubmitted AND
      [TBL_OUTBOUND_REVIEW].[ERROR_FREE] = @Status AND
      [TBL_OUTBOUND_REVIEW].[LOCATION] LIKE @Location AND
      [TBL_OUTBOUND].[UDF_0] LIKE @UDF0
ORDER BY [TBL_OUTBOUND_REVIEW].[DATE_SUBMITTED] DESC;

More or less I'd like to accomplish this :

WHERE [TBL_OUTBOUND_REVIEW].[ACTIVE_DIRECTORY] LIKE @UserName AND
      CONVERT(date, [TBL_OUTBOUND_REVIEW].[DATE_SUBMITTED]) LIKE @DateSubmitted AND
              IF @Status IS NULL
               [TBL_OUTBOUND_REVIEW].[ERROR_FREE] IS NULL AND
              ELSE
               [TBL_OUTBOUND_REVIEW].[ERROR_FREE] = @Status AND
      [TBL_OUTBOUND_REVIEW].[LOCATION] LIKE @Location AND
      [TBL_OUTBOUND].[UDF_0] LIKE @UDF0
ORDER BY [TBL_OUTBOUND_REVIEW].[DATE_SUBMITTED] DESC;

... Though I know that doesn't work. :\


texts: How to create a table in MySQL with automatic numeration of columns?

texts:
Example. My data is from Table name:BIKE

Bike_No   Repair_Date     Repair_Cost
--------  -----------     ------------
ABC1234   2013-01-05      50.00
BMX5678   2013-02-04      75.00
ABC1234   2013-01-25      20.00
BON3333   2013-03-06      80.00
DEB1111   2013-08-04      40.00
ABC1234   2013-09-06      50.00

I want to see the repair cost for each bike based on
a date range (example: from 01/01/2013 to 31/03/2013) and result table must
display the repair cost with Bike_No and the affected months as columns.
How do we generate the affected months as columns based on the queried date range?


texts: When should indexes be dropped and recreated?

texts:
We are constructing a data warehouse that initially will be 1 TB and will grow around 20gigs every month.

For certain tables we are doing daily ETL processes and others we are doing weekly/monthly.

When there is a data import going into a table, is it necessary to drop and recreate the indexes?

Is there ever a point to dropping and recreating indexes or are they automatically updated?

The statistics are set to update automatically.

Thank you so much for your help and guidance.

I got this genius script:

SELECT 'ALTER INDEX [' + ix.name + '] ON [' + s.name + '].[' + t.name + '] ' +
       CASE WHEN ps.avg_fragmentation_in_percent > 40 THEN 'REBUILD' ELSE 'REORGANIZE' END +
       CASE WHEN pc.partition_count > 1 THEN ' PARTITION = ' + cast(ps.partition_number as nvarchar(max)) ELSE '' END
FROM   sys.indexes AS ix INNER JOIN sys.tables t
           ON t.object_id = ix.object_id
       INNER JOIN sys.schemas s
           ON t.schema_id = s.schema_id
       INNER JOIN (SELECT object_id, index_id, avg_fragmentation_in_percent, partition_number
                   FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL)) ps
           ON t.object_id = ps.object_id AND ix.index_id = ps.index_id
       INNER JOIN (SELECT object_id, index_id, COUNT(DISTINCT partition_number) AS partition_count
                   FROM sys.partitions
                   GROUP BY object_id, index_id) pc
           ON t.object_id = pc.object_id AND ix.index_id = pc.index_id
WHERE  ps.avg_fragmentation_in_percent > 10 AND
       ix.name IS NOT NULL

from here:

http://weblogs.asp.net/okloeten/archive/2009/01/05/6819737.aspx

Do you suggest I run this script daily and based on the findings run the generated code?


texts: Switching from 2 large collections to multiple small ones in MongoDB 3.4

texts:
I know this question is similar to other ones. But...

Only that we are switching from 2 very large collections (15Gb in size, constantly growing).

Basically those 2 large collections keep different data for our clients. I'm thinking of creating 2 collections for each client. In terms of size, they will be from 20MB to a few GB each (depending on client size).

Now the pros of having these is that when we loose clients, we need to remove their data. And will be much easier using dropCollection.

The other one is the index, as we keep the client Id on each document in the large collection. And this is not needed, we can remove this index.

About sharding. Now those 2 large ones are sharded across 3 servers. With the small ones, what would be best:

automatically create and shard them when a client comes in (we create collections, add the shard key, all automatically).
only shard the large clients, manually.

What happens if there are lots of small collections (like 10.000, each of 30mb let's say) vs a very large one?


texts: Unicode error with \u0000 on COPY of large JSON file into Postgres

texts:
I'm using Postgres 9.4 on Ubuntu 14.04.

I have a 30GB JSON file that I'm trying to COPY into Postgres.

But I keep getting the following error:

COPY <table>(comment_jsonb) FROM '<json file>' WITH (format csv, quote e'\x01', delimiter e'\x02', escape e'\x01');
ERROR:  unsupported Unicode escape sequence
DETAIL:  \u0000 cannot be converted to text.

I tried searching for the offending Unicode string to remove it, but because it's \u0000, which is NULL, I can't seem to get a regex to work. And when I print it into console, I have no idea where the NULL is, because (at least my guess is) it seems to print as nothing (though I have no idea what it is actually doing).

Is there a way to skip these errors?

Alternatively, how could I replace any instances of that Unicode in my JSON file?


texts:
I have a PG (12) database with two tables:

RESERVABLES

id               uuid

RESERVATIONS

id               uuid
reservable_id    uuid
reserved_between tsrange

Where reservations.reservable_id is a foreign key on reservables.

Now, given a tsrange (i.e. [2020-02-01 14:30, 2020-02-01 15:30]) I need to find a Reservable not reserved yet for that time interval. However, I have no idea how to proceed.

A big plus would be to find the Reservable with the most free time (fewer reservations) for that date.


texts: Restore script while setting up database mirroring on secondary server

texts:
I am trying to set up database mirroring for 10-15 databases . All these are around 1-2 TB in size. They have multiple groups and hence are scattered on multiple mount points on their primary server.

Now to begin with database mirroring , as best practise i want to make sure tat when database are restored on mirror they are on exact drives and paths as they were on primary. We have all the mount points and storage similar to primary.

Now this can be done manually but its way too tedious finding the path and drive and restoring database one by one. I see many PS scripts for restore but they just copy and restore db on default path. I know we can change using MOVE option but still finding current location of each files within those database is tedious.

Is there a better way of doing this that i am unable to figure out or a way i can prepare restore script for above case?


texts: How can we publish Aurora (Postgres compatible) logs to Cloudwatch?

texts:
I'm looking for a way to publish Aurora (pg compatible) db logs to cloudwatch.
In Postgres RDS, there is an option when we "modify" the instance to enable publishing db and upgrade logs to cloudwatch, however I see no such option in Aurora.

I found some documentation on how this can be achieved for Aurora MySQL but nothing for Aurora Postgres.

Is this currently feasible? Appreciate any help or pointers on this.

Thanks.


texts: PostgreSQL query with subquery doesn't working as expected

texts:
I'm trying to make a query that should return records based on a subquery. Here's an example:

  select ... where tablename like ('%subquery_result%');

The subquery is generating the string as expected:

appldb=# select '''%_p' ||
replace(substring(CAST(current_date - INTERVAL '1 MONTH' AS text), 1, 7),'-', '_') || '%''';
   ?column?
---------------
 '%_p2019_09%'
(1 row)

Using the string above, records are returned:

select tablename from pg_tables
where tablename like '%_p2019_09%' limit 2;
      tablename
---------------------
 part_p2019_09_26
 part_p2019_09_29
(2 rows)

But when I use the complete query I have no return:

appldb=# select tablename
from pg_tables
where tablename like ( select '''%_p' ||
replace(substring(CAST(current_date - INTERVAL '1 MONTH' AS text), 1, 7),'-', '_') || '%''' );
 tablename
-----------
(0 rows)

I have already tried to remove parentheses around the subquery. But the query got in error.

Did I miss any steps to ensure where clause to interpret the subquery?