texts: Missing rows in MyISAM tables with 10-20 billion records

texts:
I've got 2 tables with 10-20 billion records each.

Currently these are in MyISAM (MySQL).
They only require to be built and indexed once, after this only SELECT is used.
They are currently partitioned into 100 partions each.
MyISAM is currently used because it has lower storage, has fast SELECT and supports LOAD DATA INFILE, REPAIR WITH X THREADS (for index building) and supports having the data & index files in a different directory from default (which is required because the file sizes are too large for the partition that the rest of MySQL runs on, which is an SSD.)

This is on a single server with single quad-core i7 processor and 64GB RAM, running CentOS 6.

The table structure is:

id bigint(20) UNSIGNED
word1 mediumint(8) UNSIGNED
word2 mediumint(8) UNSIGNED
origyear smallint(5) UNSIGNED
cat tinyint(3) UNSIGNED
pibn int(10) UNSIGNED
page smallint(5) UNSIGNED
pos smallint(5) UNSIGNED
num tinyint(3) UNSIGNED

The indexes are:
(Note that the primary key is over id & word1 as the partitioning is done over word1 and it is required to be part of the primary key to be used as the partitioning value.)

PRIMARY id, word1
INDEX word2, word1, pibn
INDEX origyear, word1
INDEX cat, origyear
INDEX pibn, word1
INDEX pibn, page, word2
INDEX word1, word2, num, pibn, page

The problem is that half of the rows are missing after importing. I can confirm that the rows exist and have unique keys as I have checked this in the flat files that the tables are built from (when they were first imported with LOAD DATA INFILE.) Luckily I have a backup of these flat files so it would not be difficult to reimport the entire tables (except that it takes about a month to import and index it all.)

I have no idea why these rows are missing, I guess it's just because MyISAM can't keep up with the amount of data.

I need advise on how to fix this, or a better storage engine that can be easily migrated to without having to change all the code in the scripts that use this data (all the SELECT statements.)

These tables are used in combination with other MyISAM & InnoDB tables in some SELECT statements, so it is necessary that they are part of an SQL database supporting that.

I've been looking at TokuDB but I'm not sure if it will be better. Plus is not does appear to support having the data in a different directory to default, which is required since I have only 300GB of SSD and these 2 tables are obviously several TB in size.


texts: I am using the git plugin for DBeaver. While I can commit, I am not seeing an option to push the commit over to master. Am I missing something?


texts:
The normal way to convert an enum value to a string in PostgreSQL is to cast it (with ::text) or to simply rely on implicit conversion when, say, concatenating it to another string:
testdb=# CREATE TYPE color AS ENUM ('red', 'green', 'blue');
CREATE TYPE
testdb=# CREATE TABLE object (id serial, name text, color color);
CREATE TABLE
testdb=# INSERT INTO object (name, color) VALUES ('table', 'red'), ('chair', 'green');
INSERT 0 2
testdb=# SELECT name || color FROM object;
  ?column?
------------
 tablered
 chairgreen
(2 rows)

However, there's a catch: such conversions seem not to be considered immutable by Postgres. As a consequence, you get an error if you try to use them in a place where an immutable expression is expected, such as in the definition of a GENERATED column:
testdb=# ALTER TABLE object ADD COLUMN namecolor text GENERATED ALWAYS AS (name || color) STORED;
ERROR:  generation expression is not immutable
testdb=# ALTER TABLE object ADD COLUMN colorastext text GENERATED ALWAYS AS (color::text) STORED;
ERROR:  generation expression is not immutable

This seems unintuitive, and is inconvenient, since I'd like to be able to concatenate enum values onto strings in a computed column.
Thus, I have two questions:

Why does Postgres consider converting an enum to a string to not be immutable? Is this behaviour a bug?
Is there a workaround that will let me persuade Postgres such a conversion is immutable (and are there any risks I should be wary of)?


texts: Non-clustered index covering a clustered index

texts:
I have a table that has the following indexes:

IXC clustered index that includes a single column : A asc.

IX1 that includes two columns : A asc, B asc.

In this case ICX seems like a duplicate index of IX1. Is it a good idea to delete ICX and make IX1 the clustered index in that case? Or is there scenarios where this would be a bad idea?

DDL example :

CREATE TABLE [dbo].[foo] (
    [A]      NVARCHAR (20)  NOT NULL,
    [B]      INT           NULL,
    [C]      NVARCHAR (20)  NOT NULL,
    [D]      NVARCHAR (20)  NOT NULL,
    [E]      NVARCHAR (20)  NOT NULL,
);

GO
CREATE CLUSTERED INDEX [IXC]
    ON [dbo].[foo]([A] ASC);

GO
CREATE NONCLUSTERED INDEX [IX1]
    ON [dbo].[foo]([A] ASC, [B] ASC);


texts: Can't connect using SqlManager for Oracle, but Sql+

texts:
I'm using EMS Sql Manager for Oracle to connect to some Oracle databases but I receive the error

ORA-12154: TNS:could not resolve the connect identifier specified

I just re-setup my PC, due to SSD & RAM upgrades, and I converted my old machine in a VM.

This VM connects successfully and the settings seem to be the same, in fact I copied the tnsnames.ora from one client to another (and I use it in Sql Manager).

I can't connect even if I use direct, with connection string, this time it says

ORA-06413: Connection not open

If I use Sql+, it correctly resolve my TNS identifier and connects correctly to all databases. Same for Oracle Enterprise Manager.

This is a piece of the tnsnames.ora I use:

CLADB_CLA2014 =
  (DESCRIPTION =
    (SOURCE_ROUTE = ON)
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = tcp)(HOST = CLA2014)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = CLADB)
      (SERVER = DEDICATED)
    )
  )


texts:
Possible Duplicate:
  SQL database restore failed

I have a c# application that is connected to my sql server 2005 database, i am working on the application to insert some entries in the database and due to power failure system got down and from that time onwards i am getting this error. I refresh the database, make a copy of it, delete it & try to attach it again and thus the error occurs.

  The log cannot be rebuilt because there were open transactions/users
  when the database
      was shutdown, no checkpoint occurred to the database, or the database was read-only. This  error could occur if the transaction log
  file was manually deleted or lost due to a hardware  or environment
  failure.

If i use the log file while attaching the db, i got this error

  The log scan number (1224:41:1) passed to log scan in database 'test' is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.


texts: Is there a more efficient way to set up this table

texts:
I'm looking to add a new feature in our production site that will show users 25 items they most recently viewed.  I already record every view into various tables based on the item type, but querying on the union of these tables can't be efficient, because those tables have (in some cases) millions of rows, and for the new table, I'm only concerned with the user's 25 most recent views.

My initial thought was to make a new table and write additional code to add records to (and delete from) this table with the following conditions:

1) each combination of user_id and item_id should be unique; ie for a given user_id, any item should appear no more than once
2) to keep the table small / fast, no more than 25 items should be stored for a given user.  Once a user_id has 25 items the oldest view would be removed whenever a new view is added for that user_id.

so in order to do this, I need to query if the given combination exists, and then if the given user_id has 25 rows, the user's oldest row would need to be deleted.

I thought there may be a better way to do this and I investigated MySQL 'views' but from what I read it sounds like creating a view based on the existing tables would not be efficient since it would use all the rows but no indexing.

Is there a better way to do this (maybe triggers?  or some way within the mysql schema?) or is my best bet "brute force"; to create additional code to manage the new table, on top of the code I already have to record every view?

This is the basic schema for the new table:

| Field                 | Type
+-----------------------+-----------------
| customer_id           | int(11)
| item_id               | int(11)
| view_date             | datetime

thank you in advance for any advice or suggestions.


texts: Is it safe to use "NOT BLOCKING" option on HSQL backup?

texts:
The HSQL documentation has the following to say about the "NOT BLOCKING" option on backups:

  Hot backup is performed if NOT BLOCKING is specified. In this mode, the database can be used during backup. This mode should only be used with very large databases. A hot backup set is less compact and takes longer to restore and use than a normal backup set produced with the BLOCKING option.

Our databases can be quite large, so we're definitely interested in not blocking the application during backup. Based on this description, it sounds like it should be "safe" to use NOT BLOCKING. My main concern is that it would backup "partial" transactions if changes were being made during backup.

Since there is no mention of data integrity issues, can I assume it's safe to use NOT BLOCKING if I'm not concerned about backup size or restore time?


texts: Issues using pt-kill to kill log queries

texts:
I'm trying to get pt-kill to work to kill queries running longer than n.

The command as I'm running now is;

pt-kill --busy-time 15 --print -u root -p `cat mysqlpw` --victims all --log /var/log/pt-kill.log --match-all

The problem is that I'm seeing lots of queries under 15 seconds being logged;

# 2016-08-06T20:29:34 KILL 187563 (Sleep 1 sec) NULL
# 2016-08-06T20:29:34 KILL 204826 (Sleep 0 sec) NULL
# 2016-08-06T20:29:34 KILL 204819 (Execute 0 sec) SELECT <snip>

Obviously I have no intention of killing these queries, so i haven't been able to actually use this in anger yet.

Am I missing something in the args? Maybe its an issue with Maria10.1? I'd love to be able to use this.

Thanks.


texts:
I just want to login for first time, from pgAdmin III via localhost on openSuSE 13.1 64x.
My pg_hba.conf:

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident

postgres=# \l
                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 mydb      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |


texts: What sequence when changing the master MySQL server, but not IP?

texts:
I change the replica to the master server. But the IP master remains the same.

* The second replica itself will continue to work, without changing the settings?
* On the old master, just change the settings and make
    * RESET SLAVE;
    * CHANGE MASTER TO ...
    * START SLAVE;

The second replica works itself. And old master started as replica.
It is normal? I did the right thing?


texts: Order by x DESC - but only continuous rows

texts:
I have a table with an ID (integer) and a text field (the name). My current query looks like this:

SELECT id, name FROM table WHERE id <= X ORDER BY id DESC

X is an ID I get from another query. What this query currently does is it outputs all the entries lower than the ID I put into, ordered by ID.

What I'd like to achieve is that this is only done until the first "gap" in the IDs - for example, with a table like this:

id      name
1       Test 1
2       Test 2
3       Test 3
6       Test 6
7       Test 7
8       Test 8
9       Test 9

I'd like to have a query which only returns the following set for X = 8:

id      name
8       Test 8
7       Test 7
6       Test 6

Because then, there is a gap in the IDs (ID 5 is missing), and then I don't need the other lower IDs.

How could that be done?


texts: SQL Server: how to avoid sysadmin rights but give adequate rights

texts:
I have a collegue who want to get an access to one SQL Server 2008 instance. I'll have to give him rights to this instance. He should have rights to do is e.g.

Add and modify server logins
Add and modify maintenanace plans (e.g. create backups from the databases)
Schedule Agent jobs

I don't want to give him sysadmin rights, what rights should be given?


texts: Creating a custom thesaurus dictionary on RDS

texts:
We're deployed on RDS using Postgres 11.4, and I'm hoping that there is some way to define a custom thesaurus dictionary. I think the answer is a hard no, based on this StackOverflow thread
and this AWS thread.

Is there any way to define a thesaurus within the database, and not from an external file? The docs, even for PG 12, do not suggest anyting of the kind.

I'm asking what sounds like a silly question, so here's what makes it less inane. Following some instructions from Erwin Brandstetter,
I've defined custom dictionary that combines the simple dictionary with the skip word definitions from the english dictionary.

CREATE TEXT SEARCH DICTIONARY data.simple_skip_dictionary (
    TEMPLATE = pg_catalog.simple,
    STOPWORDS = english
);

CREATE TEXT SEARCH CONFIGURATION data.simple_skip (COPY = simple);
ALTER  TEXT SEARCH CONFIGURATION data.simple_skip
   ALTER MAPPING FOR asciiword WITH data.simple_skip_dictionary;  -- 1, 'Word, all ASCII'

CREATE INDEX record_changes_log_detail_old_value_tsv_gin
    ON record_changes_log_detail
    USING GIN (to_tsvector('simple_skip', old_value));

SELECT *
FROM   record_changes_log_detail
WHERE  to_tsvector('simple_skip', old_value) @@ to_tsquery('simple', 'double-horn');

This all works fine on RDS, and the PG docs here also refer to exteral files. I suspect I'll find I'm out of luck because what I'm looking for is a custom thesaurus dictionary, not leveraging an existing file that may already be defined on RDS.

Does anyone know of a trick to create a custom thesaurus dictionary on RDS, or should I just put this one on the "reaasons not to use RDS" list?

Putting another PG in Front

Assuming (as Laurenz Albe likely confirms) that a custom thesaurus is not possible on a hosted PG without file system access, what about putting another PG in front to generate the text search vector? So, set up one or more PG instances that pre-process the data and then push the vector up to our RDS Postgres?

The question buried in there is "does the thesaurus come into play during the query, or only during the indexing?" The to_tsquery() call obviously wouldn't have access to the thesaurus, but I'm imagining that it might not matter. The query term wouldn't normalize identically, but I'm hoping that's part of the point of the digestion-with-thesaurus process."

I know this question is remedial, I'm just getting my head around FTS in Postgres. It's a pretty substantial topic area and I've only been able to digest the docs in bits and pieces.


texts: Find unmatched rows between two tables dynamically

texts:
I have a function here that is supposed to take two tables as arguments and check if they are the same.

create or replace function testing.equal_tables(
    varchar,
    varchar)
    returns void as
$$
begin

    execute 'select *
     from
     (select * from ' || $1 ||'
     except
     select * from ' || $2 || ') a
     union
     (select * from ' || $2 || '
     except
     select * from ' || $1 || ');'
    ;

end;
$$ language plpgsql;

When I call it with these two tables, one with 20 rows and one with 10 rows, I get only the empty set, which is not the correct result:

select testing.equal_tables('ee1', 'ee2');

When I modified the function to return a string of the statement, it came back correctly, but that still doesn't help because I'd like to execute the return string using a function, prepared statement, or something. Is there any way to make this function work?


texts: exported .SQL file won't import completely in mySQL

texts:
I am running MySQL on an Apple server OS X 10.6 (and am a complete newbie to SQL). I am trying to import a .SQL export file that was generated by an application called MailSteward Pro. The .SQL file is an email database about 88 Gigs in size. When I do the import operation in Sequel Pro, I see a progress bar that goes almost all the way to the end (takes hours, shows no problems), but then suddenly shows a message that says

  [ERROR in query 650009] You have an error in your SQL syntax: check the manual that corresponds to your MySQL server version for the right syntax to use near "\d\">+B$?\"+vq+q6]\Vss<:t/qs4>{]:>' at line 1

what in the world does this mean and how do I figure out why I can't import my saved .SQL file? It goes most of the way to the end and shows only 260,000 emails instead of 290,000. What's my next step - how do I debug this and import as much as possible of this database? Maybe check it for errors somehow, or have it ignore the bad record and move past it to import the rest?


texts: Clients timing out with no apparent reason

texts:
I have a SQL Server 2005 server that's been running fine most of the time.

Today the users started to get time outs when opening new connections, including myself when I tried to open another tab on SSMS.

I've looked everywhere for the problem but I haven't found anything actually wrong with the server!

It's over 10.000 seconds of PLE, >99.80 cache hit ratio, Target Memory of 10GB and using only about 4GB. The server has 16 processor and the load is well below 20%. The I/O is pretty low, most activity comes from logging and CHECKPOINTs and only rarely the Lazywriter has to write stuff to disk.

This server is always been pretty underutilized, and still today I started getting timeouts when opening new connections.

The server has been restarted and the problem persists.

My first thought would be to blame the network, but I have remote DAC setup and I've been able to connect to the server without any delay when using the DAC, so I'm not so sure it's the network.

How can I track this problem down and diagnose what's going on? What tools or techniques can help me solve this?


texts: Refer to renamed columns in WHERE clause

texts:
SELECT name, gdp/population as capita
FROM world w1
WHERE continent = 'Europe'
AND capita >
(
    SELECT gdp/population
    FROM world
    WHERE name = 'United Kingdom'
);

How come I can't refer to capita in the WHERE clause? It returns this error:

Unknown column 'capita' in 'where clause'

I can however, run the query if I replace capita with gdp/population.

I don't want to do gdp/population > .... How can I do capita > ...? Or is this not possible?


texts: How to insert into junction table using triggers

texts:
Sorry in advance if this is "basic SQL." I wanted to know how to update my junction tables automatically. For example, these are my tables.

Artist and Song are base tables and SongArtist is the junction table. Everything in SongArtist is PK and FK.

CREATE  TABLE IF NOT EXISTS `Artist` (
  `artistID` INT NOT NULL AUTO_INCREMENT ,
  `artistName` VARCHAR(150) NOT NULL ,
  PRIMARY KEY (`artistID`) )
ENGINE = InnoDB

CREATE  TABLE IF NOT EXISTS `Song` (
  `songName` VARCHAR(150) NOT NULL ,
  `songID` INT NOT NULL AUTO_INCREMENT ,
  PRIMARY KEY (`songID`) )
ENGINE = InnoDB

CREATE  TABLE IF NOT EXISTS `SongArtist` (
  `songID` INT NOT NULL ,
  `artistID` INT NOT NULL ,
  PRIMARY KEY (`songID`, `artistID`) ,
  INDEX `fk_Artist_Artist_idx` (`artistID` ASC) ,
  INDEX `fk_Song_Song_idx` (`songID` ASC) ,
  CONSTRAINT `fk_Song_Song`
    FOREIGN KEY (`songID` )
    REFERENCES `Song` (`songID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `fk_Artist_Artist`
    FOREIGN KEY (`artistID` )
    REFERENCES `Artist` (`artistID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB

I created some triggers like this, but they don't seem to work as I can't do INSERT INTO and add a new row when I only know one field of the junction table because I have two columns that are PK.

CREATE
TRIGGER after_song_insert AFTER INSERT
ON Song
FOR EACH ROW
BEGIN
    INSERT INTO SongArtist (songID) values (songID);
END;
CREATE
TRIGGER after_song_update AFTER UPDATE
ON Song
FOR EACH ROW
BEGIN
    INSERT INTO SongArtist (songID) values (songID);
END;
CREATE
TRIGGER after_song_delete AFTER DELETE
ON Song
FOR EACH ROW
BEGIN
    DELETE FROM SongArtist (songID) values (songID);
    END;
$$

DELIMITER ;

What should I do?


texts: Semi-additive measures for other dimensions

texts:
I am looking for a way to do semi-additive aggregations on a non-time dimension.  The model I am working with is similar to a snapshot inventory warehouse.  Specifically I am analyzing processes running on a compute farm.  I have one special dimension, processes, and several other regular dimensions.  The problem is I would like to sum facts, such as memory consumption, on all the regular dimensions, but last child of each process.

If a processes was seen using 1GB of ram at one point and later seen using 2GB, saying the processes is using 3GB of ram is silly, but two processes each using a gig of ram I'd like to how that as 2GB.


texts: Postgresql Update Table row values by Grouped Column

texts:
First of all a little bit of background:

I currently have a table which contains call data both inbound and outbound from my business, up until recently there was no api_id data available, however this has come in with the last update to my suppliers API, as such i have some historical call data that can be grouped by call_id.

What i am looking to do is update the existing rows in the database that do not have an api_id with a random string of characters grouping them by their call_id.

| ... | call_id | api_id |
|------------------------|
| ... | 123     | AAA    |
| ... | 456     |        |
| ... | 456     |        |
| ... | 788     |        |
| ... | 789     |        |
| ... | 789     |        |

Would then become:

| ... | call_id | api_id |
|------------------------|
| ... | 123     | AAA    |
| ... | 456     | GHA    |
| ... | 456     | GHA    |
| ... | 788     | ZPM    |
| ... | 789     | LFF    |
| ... | 789     | LFF    |

Effectively an update by grouped query, which i have done a search for but was unable to find anything.

If anyone here could help or point me in the right direction it would be much appreciated.

Database Technology: Postgresql 9.5.4


texts: Why does `row_to_json(...)` accept a sub-query?

texts:
I recently encountered this example (via a tutorial and other answer):

 SELECT row_to_json(x)
 FROM (SELECT id, title FROM my_table) x;

I'm confused about the use of row_to_json(x). I thought it only accepted rows, but it seems to also accept a sub-query. I found this counter-intuitive.  According to the documentation, it accepts a record: row_to_json(record [, pretty_bool]). Other examples online use a row() constructor: row_to_json(row(col1, col2)). Does this mean a sub-query is the same as a record and a row value/composite value?


texts: How can I import CSV data stored in a bytea?

texts:
I have a CSV file that can be loaded into a table easily with COPY FROM, but the catch is that the file is stored in the database in a bytea field.

Is the only way to use COPY on this data to write it out to a temporary file, or is there some clever trick, perhaps involving COPY FROM STDIN?


texts: Cannot create a row of size 11842 which is greater than the allowable maximum row size of 8060

texts:
The suspect table has 299 nvarchar(max) columns, 1 integer column and 1 nvarchar(255) column. Please do not ask me why, this is 3rd party vendor database. Anyway, according to my math, in the worst case scenario we should be well within 8060 limitation:

24x299 + 4 + 510 = 7690

Insert from external source fails with the above error during import process using vendor's import tool. Am I missing something in my calculation? Where possibly 11842 might come from? Any ideas?

Thank you!


texts:
There are many ways one can envision the solution, from the top of my head:

Two table approach:

Field table with (name, field_name, type, description, meta) attributes
Form table with (name, created_by) [name being foreign-key]

Code-generation approach:

New tables created on the fly

Example of what will come in from the user (e.g.: via an HTTP POST):

{
    "name": "dinner_time",
    "fields": [
        {"field_name": "hungry", "type": "boolean", "description":"", "meta": [""]]},
        {"field_name": "time", "type": "datetime", "description":"time now", "meta": [""]]}
    ],
    "created_by": "AT"
}

How should I enable this use-case, and what should I be extra wary about?


texts: get max serial no. according to sum of its corresponding amount

texts:
I have a table with data as follows:

s_no  amount
1      500
2      500
3      500
4      500
5      500

I need a query to find the max(s_no) according to the sum(amount)
For e.g. when sum(amount)<=1600 i need 4 as result.I can get the desired output by using cursor but it would be easier if i could use query.. I tried a query as follows:

SELECT MAX(s_no) from table HAVING SUM(amount)<=1600

But it doesn't work. I would appreciate any kind of help.
Thank you.


texts: Trigger to drop table only if there are no records

texts: How do I write a DDL DROP_TABLE trigger that will only drop the table if there are no records?


texts: Phone number with different formats lookup

texts:
I have different phone formats stored in my db, such as :

727.170.4799
1-416-958-6390
1.561.374.4268
(813) 929-5892

There are approximately ~2 million records in this table so modifying would take a long time. And I need to find records by performing phone lookup.

One thing I control is the input, I can guarantee that it's going to be numeric. But what I don't know is whether they're going to type in numeric number with or without the country code so taking this number as an example 1-416-958-6390 both of these used in lookup:

14169586390
4169586390

Should return the same row (1-416-958-6390)

How do I do phone for phone numbers as efficiently as I can?

This is what I got but it's super slow (as expected) :

select * from patients
WHERE ( REPLACE(phone_number, '-', '') LIKE '%4169586390' )


texts: Error creating package, Failed to save package file, error 0x80040154 "Class not registered"

texts:
A few colleagues & I have installed the latest Visual Studio (SSDT) - BIDS.
We are unable to create packages, in Solution Explorer right clicking on SSIS Packages > New SSIS Package results in this infamous error:

TITLE: Microsoft Visual Studio
Error creating package
Failed to save package file "C:\Users\userid\AppData\Local\Temp\tmp9ABC.tmp" with error 0x80040154 "Class not registered".

Googling this error brings up results from 2008 and it pertains to MSXML. I've tried all the solutions, install MSXML6.0, install OWC, regsvr32 the DLLs, I ran procmon and there is no hint of the problem in the trace.
I am very confident this is a different bug to the 2008 one as none of the solutions work.
One of my colleagues installed BIDS at home and it works perfectly. The work network we are in is locked down, however we all ran the install as Admins and the ISO file was > 2gig so we don't believe a component was missed during the install, but this could be wrong.
Has anyone encountered this error? Anyone got any ideas of how to troubleshoot this?


texts: How to create table with default DATE column using { ts }?

texts:
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production

create table my_table (
    transaction_date DATE DEFAULT { ts '1900-01-01 00:00:00' } NULL
)

error --

ORA-00911: invalid character
00911. 00000 -  "invalid character"
*Cause:    identifiers may not start with any ASCII character other than
           letters and numbers.  $#_ are also allowed after the first
           character.  Identifiers enclosed by doublequotes may contain
           any character other than a doublequote.  Alternative quotes
           (q'#...#') cannot use spaces, tabs, or carriage returns as
           delimiters.  For all other contexts, consult the SQL Language
           Reference Manual.

As the above create statement is generated by a third-party software, what's the correct syntax without using date or to_date()?


texts: Increasing tempdb data files on 2 node Availability Group

texts:
I am planning to create additional tempdb files for 2 nodes in a SQL 2012 Availability Group. Unfortunately we do not have Instant File Initialisation turned on so the growth of the tempdb data files will be subject to PREEMPTIVE_OS_WRITEFILEGATHER waits for up to 30 minutes (based on testing).

I am concerned that these waits will affect the application/user experience so here is my proposal.

Make the change on Node B
Disable synchronisation
Failover to Node B on successful completion
Make the change on Node A
Fail back to Node A on successful completion
Enable synchronisation.

Does anyone see any issues with this ?

Thanks


texts: Near identical MySQL deployments behaving very different - High CPU Usage problem

texts:
So I have five identical websites, running on five machines provisioned in the same way. The only thing that differs between these installations are the language files and the languages of the text stored in MySQL tables.
Four of them have no problems what so ever. One is struggling a LOT under the same or somewhat less load than the other four.
I cannot understand why this is.
Things I've done so far:

Checked slow queries. All queries uses indexes and are in the realm of 0.0008 Sec execution time i.e. very fast
I've noticed that the thing that causes most trouble for this MySQL instance is UPDATE and INSERT, so much so, I've turned off the UPDATE's that were there, for this instance. Bear in mind that these UPDATE's doesn't cause a blip on the other servers.
Tried to eliminate external factors i.e. noisy neighbours (moved host) etc.

Worth noticing is that the machines are deployed the same i.e. a vanilla Debian 10 installation with a LEMP stack, nothing out of the ordinary at all.
Still, the problem persists. I can see the load of the machine struggling to keep under 1.00.
The other machines are in the 0.10 - 0.20 range all the time.
Looking at CPU for the MySQL process on this machine (with 2 CPU cores as the other machines have as well) it is quite often above 100%. The other machines are never - EVER - over 60% for the MySQL process.
So, any help is much appreciated.
Please do let me know if you need me to run a command that you need to see the output from in order to help.
Thanks.
EDIT  Spelling and clarifications


texts: Receiving duplicate results in one condition of JOINED query

texts:
I always end up here when Googling for solutions, so I thought I'd cut out the middleman since I'm unable to fix this via searching/forums. It's my first time posting, so please forgive any faux pas in posting etiquette. I have read through the related posts, but either I'm not savvy enough to see the connection to my problem(very likely) or there doesn't seem to be an issue quite like mine.

My environment is MySQLi

My issue is that I'm using a JOINED query that isn't working as intended.  The query is supposed to give me a result if it finds a category match, either on primary_cat, which is stored in the link's table, or via a secondary cat, which is stored in a linkcat table.  The issue that I'm running into is that my query is showing duplicate(always 2) results if it is matched on the primary_cat, but properly shows a single result when matched on the secondary

    $q_urls = "
    SELECT shortenified_urls.*
    FROM shortenified_urls
        LEFT JOIN linkcats
            ON linkcats.link_id = shortenified_urls.id
        WHERE
            (shortenified_urls.primary_cat = $vu_cat_view
            OR linkcats.cat_id = $vu_cat_view)
        AND shortenified_urls.is_social = '1'
        AND shortenified_urls.active = '1'
        ORDER BY date_added DESC
    ";

I have a screenshot of my problem, which can be found here: http://en.zimagez.com/zimage/screenshot-10162014-111452am.php

I have tried playing with a DISTINCT nested query(nested because I need all the data from the result, but can't seem to figure out how to get DISTINCT to do that without one), but I'm truly terribad at this and can't figure out the solution on my own.

Any help in getting this fixed would be greatly appreciated.  Thanks for your time!


texts: Freeing disk space after dropped database

texts:
I'm working on a dev system, and I have been restoring to a database, say "foo", that I'm using for dev purposes. As I'm working through the kinks, I've just been running DROP DATABASE foo. However, I quickly realized I ate up all the space on my disk. Crap.

Does VACUUM FULL, from a different logical database, free up space from the database that I previously dropped (foo)? I tried this from a different logical database, and free space was reclaimed, but I don't THINK it was enough to account for all of the CREATE DATABASE/DROP DATABASE calls I made. It might have just VACUUM'ed the logical database I ran from.

There must be a way to reclaim that space without doing a total database init?

EDIT

So I reinitialized the database from a backup, roughly following these steps. After the restore, I've reclaimed a TON of space on disk! This works for now, but any help regarding how to cleanup a dropped database would still be useful.

EDIT 2

So I've managed to collect some more information about this issue... Here's what I've come up with as an example:

Initial partition size:
                       Size   Used  Avail Use% Mounted on
                        25G   8.1G    16G  35% /apps1

After creating my new database and populating it:
                        25G    18G   6.4G  73% /apps1

After Dropping the database using "DROP database mydb" from a separate logical DB:
                        25G    13G    11G  56% /apps1

So it appears to me that the new DB took up ~9.6 GB on disk. However, after dropping it, the reclaimed disk space only grew by ~4.6G. So, there's roughly 5 GB of space that makes me wonder what's going on!?

And it continues this cycle when I recreate, populate, and drop again.

Does anyone have any idea what's lingering after a "DROP DATABASE" command is issued?


texts: Recommended Way to Store Logical Expressions

texts:
Say, I have the following SQL table:
| ID | GameId   | PlanId | MinAgeLimit | Amount |
|:--------------|--------|-------------|-------:|
| 1  | Cricket  |   P1   |   10        |   500  |
| 2  | Cricket  |   P2   |   15        |  1000  |
| 3  | Football |   P1   |   10        |   750  |
| 4  | Football |   P2   |   20        |  1000  |
| 5  | Football |   P3   |   22        |  2000  |
| 6  | Chess    |   P2   |   20        |   800  |

Now, I can have some logical expressions for different 'ReferrerId', like:

If Referrer=ABC, then do not display items where PlanId=P2.
If Referrer=PQR, display only items where GameId=Cricket.
If Referrer=XYZ, display items where (GameID=Cricket OR GameID=Chess) AND MinAgeLimit>10 AND AMOUNT>500

What is the recommended approach to store these custom rules?
PS: I do not want to hard code these rules in my Java program, as I have plans to further provide an interface where the Referrer can update these rules through a self-service portal.


texts: Primary key auto increment best practices

texts:
Apologies if something similar has already been asked, I wasn't sure of the best term to search for to get what I was after, so please let me know if there is already a bunch of other related questions and I'll close it!

To the problem!

I have a MySQL database table that has an auto incrementing primary key, the rows in this table get removed and re-added in stages automatically once a day via a cron job php script on a web server.

This script removes and re-adds about 110,000 rows a day, and therefore the primary key number increases by the same number each day. It's currently on about 4 million.

With my limited knowledge of databases, I'm not sure if this is bad practice or not. Years down the line this number is going to be huge! Can it actually get too big? It's currently a BIGINT field type.

I don't have any programmatic need to have this unique primary key - I won't ever be searching based on this number, however it is the only unique Identifier per row.

I can't just reset the index because the whole table is not truncated - only part of it per product category.

What's the best (most efficient) way to handle this scenario?


texts: Can database objects be made case insensitive while keeping strings case sensitive?

texts:
In Microsoft SQL Server (2014) it is possible to choose between a case insensitive and case sensitive collation.

My reason for using a case-sensitive collation would be to make "test" = "TEST" return false.

Yet, what I would like to preserve is that when a table "TEST" exists, writing a query like select * from test; would still work. When the database has a case-sensitive collation it doesn't, because I would need to write it like select * from TEST;

Is there a way to set "object collation" and "string collation" separably?


texts:
In which types of projects, MongoDB is the best suited?
Can we use MongoDB in large applications?
Why Open Source CMS and framework doesn't have support for MongoDB?
How it is different than other open source RDBMS like MySQL and PostgreSQL?


texts: Understanding `dbcc showcontig` Record Size

texts:
I am comparing two tables.  They are:

CREATE TABLE Table1(
    Column1 [bigint] NOT NULL,
    Column2 [VARBINARY](max) NULL,
    Column3 [int] NULL,
    Column4 [DATETIME] NULL,
    Column5 [INT] NULL,
    Column6 [DATETIME] NULL,
    [RowVersionId] [timestamp] NOT NULL,
 CONSTRAINT [PK_BagImage] PRIMARY KEY CLUSTERED (Column1 ASC)
)

This is the second table:

CREATE TABLE Table2(
    Column1 [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    Column2 [BIGINT] NULL,
    Column3 [VARCHAR](1) NOT NULL,
    Column4 [int] NOT NULL,
    Column5 [varchar](11) NOT NULL,
    Column6 [varchar](13) NOT NULL,
    Column7 [datetime] NOT NULL,
    Column8 [bigint] NULL,
    Column9 [bit] NOT NULL,
    Column10 [uniqueidentifier] NOT NULL,
    [RowVersionId] [timestamp] NOT NULL,
 CONSTRAINT [PK_Table2] PRIMARY KEY CLUSTERED ( Column1 ASC)
)

Neither of them have any non-clustered indexes.  They do each have a foreign key, but the query plans for looking them up are lightening fast! (Clustered Index Seek that takes 00:00:00).

When I copy 55K of rows into the first one it takes 45 minutes.  When I copy 7.7 million rows into the second it takes 7 minutes.

That all seems to make sense to me. (copying images should take longer, right?)

But I wanted to calculate a rough IO for this operation.  So I ran dbcc showcontig on these tables.

Table   MinimumRecordSize  MaximumRecordSize   AverageRecordSize
Table1        75                  123                81.733
Table2        101                 101                101

So here is where I am getting confused.  If the average record size is 20 less for the table with the images in it, then why does it take so very much longer to insert the data?  Especially when I am inserting 7 Million rows more into the second table.


texts: Does mysqlhotcopy  block SELECT statements from different clients?

texts:
I would like to create a backup of different MyISAM tables on a server using mysqlhotcopy. These tables are about 6GB in size. I would like to know, does mysqlhotcopy block users from SELECTing (reading) data from the tables while taking the backup?

Thanks in advance, Tim


texts: automated data sync after Primary node is down

texts:
I have this scenario, using version 2.2.2:

Node A, Primary, Priority: 10, Wireless network
Node B, Secondary, Priority: 9, VM Server1
Node C, Secondary, Priority: 8, VM Server1

Sequence:

In Node A, insert {data:1}.
Data gets replicated to Node B and Node C.
Disconnect Node A from wireless network (We do not have enough infrastructure to simulate the network cable being pulled out from the machine, thus, I disconnect from the wireless network to hopefully achieve something similar).
Node B becomes primary. Insert {data:2}.
A quick check on Node C, data does not gets replicated. In Node C, I ran rs.syncFrom(Node B) but the data still doesn't get sync.

Am I missing something here because I am expecting that data will get replicated automatically from Node B to Node C after Node A is down.

[Edit]
Note on further test:

If Node A, B and C is in the same VM, the data inserted in Node B
does gets replicated to Node C. This issue seems to happen only when
Node A gets disconnected from the wireless network.
I've also tried shutting down Node A in sequence number 3, instead of disconnecting it from the wireless network. When Node A is shut down, data inserted in Node B does get replicated to Node C. So again clearly this happens only when Node A is disconnected from the network. I am wondering if it is the same if the network cable is unplugged.


texts: How to determine whether a PostgreSQL Hot Standby is fully mirrored?

texts:
I have set up a hot standby of a PostgreSQL server. It all seems to be working, but I just want to be sure that I'm not missing something. In /var/lib/pgsql/9.2/data/pg_log/postgresql-Wed.log I have the following:

LOG:  creating missing WAL directory "pg_xlog/archive_status"
cp: cannot stat `/var/lib/pgsql/9.2/wal/00000002.history': No such file or directory
LOG:  entering standby mode
cp: cannot stat `/var/lib/pgsql/9.2/wal/0000000200000031000000B4': No such file or directory
LOG:  streaming replication successfully connected to primary
LOG:  redo starts at 31/B47BFAC0
LOG:  consistent recovery state reached at 31/B73624A0
LOG:  database system is ready to accept read only connections

I am concerned about the missing WAL files. Can anyone confirm that, as long as it reaches a consistent state, the hot standby contains all the data of the master?

Everything else I check indicates that it's ok; for example, running psql -x -c "select * from pg_stat_replication;" on the master looks good, and adding a new record on the master replicates. I just want to be sure that there won't be anything missing from the slave.


texts: Scripting a Maintenance Plan in SQL Server Express

texts:
How would I run each aspect of Ola Halengren's sql maintenance plan on SQL Express?

It's my understanding that each job should be run in a specific order.  I'm sure that a .bat or cmd file can do this, but not sure where/how to start. SQL Express does not show the Server Agent, so the jobs do not display.

Can I view them another way and still use the scheduler, or would I have to script those? Would I have to run that from Task Scheduler, or can the SQL Agent scheduler handle this? Any help/direction you could provide is greatly appreciated.

On a side note, I'm also wondering if jobs can be scheduled to run after another job completes, as opposed to specifying a time to run the job?


texts:
In my environment (oracle 19c) I can see several SYSRAC sessions. What is the purpose of this session?

SQL> select username,status from gv$session where type <> 'BACKGROUND';

USERNAME                       STATUS
------------------------------ ------------------------------
SYS                            INACTIVE
SYSRAC                         INACTIVE
SYSRAC                         INACTIVE
SYSRAC                         INACTIVE
SYSRAC                         INACTIVE
SOE                            ACTIVE
SYSRAC                         INACTIVE
SOE                            ACTIVE
SOE                            ACTIVE
SYS                            ACTIVE
SOE                            ACTIVE

USERNAME                       STATUS
------------------------------ ------------------------------
SOE                            ACTIVE
SYSRAC                         INACTIVE
SYSRAC                         INACTIVE

I need to know because sometimes I use a script to kill all sessions and I dont know if I always need to exclude this session of my script (where username <> 'SYSRAC')

I've already research about it, but I just found about SYSRAC role from oracle 12c r2

  The following is a list of new features or enhancements provided with
  Oracle Database 12c Release 2 (12.2):

  New Administrator Role

  Oracle Database 12c Release 2 (12.2) provides support for separation
  of duty for Oracle Database by introducing the SYSRAC role for Oracle
  Real Application Clusters (Oracle RAC) management. SYSRAC, like SYSDG,
  SYSBACKUP, and SYSKM, enables you to enforce separation of duty and
  reduce reliance on the use of SYSDBA on production systems. The SYSRAC
  role is the default mode and is assigned only the priveleges required
  for connecting to the database by the clusterware agent on behalf of
  the Oracle RAC utilities such as srvctl.


texts: MariaDB / mYSQL Order BY PrimaryKey DESC vs DESC set by Default

texts:
What do you guys think about:
After each query, I put "Order BY PrimaryKey DESC"
 VS
I set "PrimaryKey Sort by DESC Default" and each query I don't need to put Order By there.

Which one will be faster and skip the sort?
OR both of them do sort? nothing optimize ?


texts:
I have some data stored as an unsigned tinyint.  When displaying it I want it to be zero padded only if it is 1 digit in length.  I have issues with LPAD(CAST(NUMBER AS CHAR CHARSET UTF8),2,'0') when I have three digit long numbers.  How do I get what I'm looking for?

Example of data in table:

NUMBER
1
2
3
...
10
11
...
101
102

What I currently get when running with LPAD

LPAD_NUMBER
01
02
03
...
10
11
...
10
10

What I want

UNKNOWN
01
02
03
...
10
11
...
101
102

SOLUTION I USED

select case
    when number between 0  and 9 then
        LPAD(CAST(number AS CHAR CHARSET UTF8), 2, '0')
    else
        CAST(number AS CHAR CHARSET UTF8)
    end as newNum;

This gets me what I'm looking for and is based off of the accepted answer.


texts:
What database front end do you use?
It should let you manage databases, tables, indexes and ideally even data records.
And it should ideally be free so we can all use it too.

Databases that it could work with:

MySQL
MS SQL
Oracle
SQLite

One answer per frontend, to let voting do its trick.


texts:
Turkey government has announced that Turkey will stay on GMT+3.

We've waited for OS pathces to update tzdata on PostgreSQL.
Now, timezone patch for Windows is installed.

On Linux, restarting PostgreSQL service is enough.
But on Windows, restarting did not help.

postgres=#select extract(timezone from '2017-01-01'::timestamptz);
date_part
---------
7200

On linux;

postgres=#select extract(timezone from '2017-01-01'::timestamptz);
date_part
---------
10800

I need to update timezone datas for PostgreSQL on Windows.

Can anyone help me to solve this?

Also I am sure that timezone change patch is installed.

PostgreSQL versions are 9.2 and 9.5


texts:
I have a request to uninstall one of the test instance which is part of Always ON. I googled around for a clean decommission but did not find steps for Always ON Instance uninstallation. Would be great if Steps can be shared here or any link if you are aware of. As per me I would :

Take database backup
Take note of Availability group listener and database involved.(For future if they want it back)
Remove the Databases from the AG.
Remove the AGs
Uninstall the instance as a normal standalone instance.(For all nodes).

Please add or suggest if this needs any changes.


texts: PostgreSQL GIN pg_trgm default operator class

texts:
I am using GIN indexes with the pg_trgm module for indexing varchar fields.  So, to define an index I have to write something like this:

CREATE INDEX gin_field_idx ON table_name USING gin (field gin_trgm_ops);

If I exclude the operator class (git_trgm_ops) and write:

CREATE INDEX gin_field_idx ON table_name USING gin (field);

PostgreSQL will raise an error:

  ERROR:  data type character varying has no default operator class for
  access method "gin"
  HINT:  You must specify an operator class for the
  index or define a default operator class for the data type.

How do I define a default operator method from the module?

This does not help. It describes how to define a new operator class from extension operators and functions. But I have to define the default operator class from a module.

Any help will be appreciated :-).


texts: Is it possible to rewrite this Function for optimization?

texts:
I need help optimizing this function as it takes more than 2 minutes currently which is far from optimal. This function basically gives you the name of column, corresponding table and list of unique values contained in the column.

Function Definition:

CREATE OR REPLACE FUNCTION get_pubcol(
    included_tables name[] default '{}',
    included_schema name[] default '{}'
)
RETURNS table(schema_name text, table_name text, column_name text, column_value text)
AS $$
begin
  FOR schema_name, table_name, column_name IN
      SELECT c.table_schema, c.table_name, c.column_name
      FROM information_schema.columns c
      JOIN information_schema.tables t ON
        (t.table_name = c.table_name AND t.table_schema=c.table_schema)
      WHERE (c.table_name=ANY(included_tables) OR included_tables='{}')
        AND (c.table_schema=ANY(included_schema) OR included_schema='{}')
        AND t.table_type='BASE TABLE'
  LOOP
    EXECUTE format('SELECT ARRAY_AGG(DISTINCT %I) AS column_value FROM %I.%I',
       column_name,
       schema_name,
       table_name
    ) INTO column_value;
    IF column_value is not null THEN
      RETURN NEXT;
    END IF;
 END LOOP;
END;
$$ language plpgsql;

Function call:

SELECT * FROM get_pubcol('{}', '{public}')

Explain Analyze Result:

"Function Scan on get_pubcol  (cost=0.25..10.25 rows=1000 width=128) (actual time=121235.178..121235.199 rows=73 loops=1)"
"Planning time: 0.049 ms"
"Execution time: 121237.436 ms"

Query Result:

schema_name table_name column_name   column_value
"public"    "MARA"  "Lab/Office"    "{" ",BL1,GL1,GP1,KL1,KP1,NaN,SL1,SP1,WL1,WL2,WP1}"


texts: self-joins in recursive CTE: `recursive reference must not appear more than once`?

texts:
This is tested with PostgreSQL 9.6, but it's a general SQL question.

Is it possible to use self-joins of the recursive table in a recursive CTE (rCTE)?

I tried the following rCTE containing a self-join of the recursive term x,

WITH RECURSIVE
x (id) AS (
  SELECT 1 id UNION ALL SELECT x1.id+x2.id FROM x x1, x x2
  WHERE x1.id < 5 AND x2.id < 5 AND x1.id = x2.id
  )
SELECT * FROM x;

, which hopefully should be equivalent to:

WITH RECURSIVE
x (id) AS (
  SELECT 1 id UNION ALL SELECT id+id FROM x WHERE id < 5
  )
SELECT * FROM x;

But the former rCTE generates an error:

ERROR:  recursive reference to query "x" must not appear more than once
LINE 3:   SELECT 1 id UNION ALL SELECT x1.id+x2.id FROM x x1, x x2

Is there a fundamental reason why the recursive reference must not appear more than once? Or is this just a limitation of the PostgreSQL implementation?

Also, is there a work-around?


texts: Shrink database or DB files not reducing size even after dropping biggest table

texts:
Our MDF file was originally 550GB in size (log is just 10MB), and the biggest table we had [HourlyCounters] was 140 million rows with 580 columns (varchar[50]). It's a table with a very inefficient schema, but there's nothing we can do about that.

Anyways, after moving some data around, I was able to drop [HourlyCounters]. So now, the new table (with the same name) has 50K rows. I then ran SHRINKDATABASE and SHRINKFILE, and the MDF went down about 40GB. I know that's a decent chunk, but I know for a fact that this dropped table was larger than that (the current backup of that table with only 7M rows is 63GB).

How can SQL Server release all that free space? The new version of [HourlyCounters] doesn't currently have any indexes, but I know the old one did have them.

It's worth mentioning how I went about dropping the table:

Rename the original table [HourlyCounters] to [HourlyCounters_Old] with a right-click in SSMS and Rename. This table had indexes.
Create a new table [HourlyCounters] with no indexes so that processes that stored in this table are not affected.
Create table [HourlyCounters_New] where I would store the most recent data from [HourlyCounters] (now called [HourlyCounters_Old]). [HourlyCounters_New] now has 7M rows.
Drop [HourlyCounters_Old]

Other info that may help:
The drive where the data files are stored (E:) is 1.89TB, with 1.44TB used and 468GB free.

This is the result of the following query:

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0
AS AvailableSpaceInMB
FROM sys.database_files;

name      AvailableSpaceInMB
DB        53490.812500
DB_log    441.703125

Thanks.


texts: Is index like key(timetime) usable when we query: SELECT * ....... ORDER BY timetime DESC

texts: When we index a timestamp field like timetime. Can we use it for ORDER BY timetime DESC or is it just usable for ORDER BY timetime ASC OR ORDER BY timetime? Does mySQL use this index when doing vise verse ordering?


texts: What drawbacks are there in scheduling an event to run every too often,say after every 60 seconds?

texts:
I have this event i want to run every 60 seconds but i wonder if this will take up many resources in my database.

 CREATE EVENT myevent
    ON SCHEDULE EVERY 60 SECOND
    DO
       UPDATE yourdb.yourtable SET yourcolumn = RAND();

If make my event to run every too often,will it impact on the performance of my database?.


texts: Get disk space percentage free with SQL Server 2005?

texts:
I am currently using SQL Server 2005 and (undocumented I believe) master..xp_fixeddrives to get free space on my database volumes as part of my monitoring.

However, this only gives me an absolute number of MB free. What I really need is percentage free.

Is there another way in SQL Server 2005 to get this? If not, is there some other light-weight way to get it? If I can, I want to avoid installing a Java JRE, or Perl, or Python on my database server. Perhaps vbscript, or a small Windows executable on the file system?

Yes, I know I can Google this, and I have. It looks like there are a few ways to accomplish it, and I'm curious how my DBA brethren have handled this.


texts: How to SELECT all records where there is not another record with the same key within a given time offset?

texts:
What is the best way to perform an SQL query to return all records where there is not another record with a matching id and a timestamp within X time?

So, for example, given the database table sample below and a timeframe of 5 hours, I would want the query to return the sample result.

Sample Data:

id | timestamp
01 | 2015-08-03 03:00:00
01 | 2015-08-03 14:00:00
01 | 2015-08-03 15:00:00
01 | 2015-08-03 16:00:00
02 | 2015-08-03 4:00:00
02 | 2015-08-03 5:00:00
03 | 2015-08-03 2:00:00
03 | 2015-08-03 5:00:00
03 | 2015-08-03 10:01:00

Sample Result:
01 | 2015-08-03 03:00:00
01 | 2015-08-03 16:00:00
02 | 2015-08-03 5:00:00
03 | 2015-08-03 5:00:00
03 | 2015-08-03 10:01:00

...since these are the only records for their respective ids where no other records with matching ids occur in the following 5 hours.

My current solution is to essentially query the entire table and then loop through each record querying on a local datatable. It works but is highly inefficient. Is there an SQL way of doing this? I have looked around for awhile, and I am coming up short. But, I might just not be searching correctly. There is probably some simple solution that I am missing, but I am still pretty new to SQL.

Thanks!

Update:
I should have included the current query I am using for reference. Note that "id" in the sample above really refers to "deviceid" in my actual query. It is:

SELECT id                                   AS 'ID',
       eventsink.elevatortripevent.deviceid AS 'DeviceID',
       completed                            AS 'Created',
       digitalposition                      AS 'DigitalPosition',
       buildingstate                        AS 'State'
FROM   eventsink.elevatortripevent
       JOIN md.elevator
         ON md.elevator.deviceid = eventsink.elevatortripevent.deviceid
       JOIN ea.elevatoreventevent
         ON ea.elevatoreventevent.deviceid =
            eventsink.elevatortripevent.deviceid
WHERE  completed >= '8/2/2016 8:13:34 PM'
       AND completed <= '8/3/2016 8:13:34 PM'
ORDER  BY eventsink.elevatortripevent.deviceid

Attempt at Implementing Answer by @irimias

SELECT id              AS 'ID',
       trip1.deviceid  AS 'Device ID',
       trip1.completed AS 'Created',
       digitalposition AS 'Digital Position',
       buildingstate   AS 'State'
FROM   eventsink.elevatortripevent trip1
       LEFT JOIN eventsink.elevatortripevent trip2
              ON trip1.deviceid = trip2.deviceid
                 AND trip1.completed < trip2.completed
                 AND trip1.completed > Dateadd(hh, -5, trip2.completed)
       JOIN md.elevator
         ON md.elevator.deviceid = trip1.deviceid
       JOIN ea.elevatoreventevent
         ON ea.elevatoreventevent.deviceid = trip1.deviceid
WHERE  trip2.deviceid IS NULL
       AND trip1.completed >= '8/2/2016 8:13:34 PM'
       AND trip1.completed <= '8/3/2016 8:13:34 PM'
ORDER  BY trip1.deviceid

Attempt at Implementing Answer by @dnoeth

WITH cte
     AS (SELECT eventsink.elevatortripevent.deviceid,
                completed,
                Lead(completed)
                  OVER (
                    partition BY eventsink.elevatortripevent.deviceid
                    ORDER BY completed) AS nextTS
         FROM   eventsink.elevatortripevent)
SELECT id                                   AS 'ID',
       cte.deviceid AS 'DeviceID',
       completed                            AS 'Created',
       digitalposition                      AS 'DigitalPosition',
       buildingstate                        AS 'State'
FROM   cte
       JOIN md.elevator
         ON md.elevator.deviceid = cte.deviceid
       JOIN ea.elevatoreventevent
         ON ea.elevatoreventevent.deviceid =
            cte.deviceid
WHERE  ( nextts IS NULL
          OR completed < Dateadd(hh, -5, nextts) )
       AND completed >= '8/2/2016 8:13:34 PM'
       AND completed <= '8/3/2016 8:13:34 PM'
ORDER  BY cte.deviceid


texts: Conditional field compare based on the value of another field (Case statement?)

texts:
I have two tables

Alerts(iscomplete, threshold, isup, coinid)
Coins(coinid, LastPriceBtc)

Coins table is updated regularly with prices (going up n down).

I want to trigger alerts that meet the thresholds but I am having difficulty writing the correct query and making it efficient.

Basically what I would like to do is, if alert is not complete (!IsComplete) then if the IsUp field is 1 (up/true) then I want to check if the price of coin (Coins.LastPriceBtc) >= the threshold (Alerts.threshold), and if the IsUp field is 0 (down/false) then check to see if the Alerts.threshold <= Coins.LastPriceBtc.

Here is what I got so far

select  *
from    alerts a
inner
join    coins c
on      c.coinid = a.coinid
where   a.IsComplete <> 1
and     (   (a.IsUp = 1 and a.Threshold >= c.LastPriceBtc)
         or (a.IsUp = 0 and a.Threshold >= c.LastPriceBtc)
        )

Is this the most efficient way to write this query?


texts: For alignment optimized table is bigger than original table - why?

texts:
In another question I learned that I should optimize the layout from one of my tables to save space and have better performance. I did this, but ended up with a larger table than before and performance did not change. Of course I did a VACUUM ANALYZE. How comes?

(I see that the index sizes won't change if I only index single columns.)

This is the table I was coming from (I've added sizes + padding):

                               Table "public.treenode"
    Column     |           Type           | Size |          Modifiers
---------------+--------------------------+------+-------------------------------
 id            | bigint                   | 8    | not null default nextval( ...
 user_id       | integer                  | 4+4  | not null
 creation_time | timestamp with time zone | 8    | not null default now()
 edition_time  | timestamp with time zone | 8    | not null default now()
 project_id    | integer                  | 4    | not null
 location      | real3d                   | 36   | not null
 editor_id     | integer                  | 4+4  |
 parent_id     | bigint                   | 8    |
 radius        | real                     | 4    | not null default 0
 confidence    | smallint                 | 2    | not null default 5
 skeleton_id   | integer                  | 4    | not null

With real3d being defined as

CREATE TYPE real3d AS (
  x real,
  y real,
  z real);

I changed this layout to the following:

                                Table "public.treenode_new"
    Column     |           Type           | Size |            Modifiers
---------------+--------------------------+------+--------------------------------
 id            | bigint                   | 8    | not null default nextval(' ...
 project_id    | integer                  | 4    | not null
 location_x    | real                     | 4    | not null
 location_y    | real                     | 4    | not null
 location_z    | real                     | 4    | not null
 editor_id     | integer                  | 4    | not null
 user_id       | integer                  | 4    | not null
 creation_time | timestamp with time zone | 8    | not null default now()
 edition_time  | timestamp with time zone | 8    | not null default now()
 skeleton_id   | integer                  | 4    | not null
 radius        | real                     | 4    | not null default 0
 confidence    | real                     | 4+4  | not null default 5
 parent_id     | bigint                   | 8    |

If I am not mistaken, I should save 66 bytes per row (138 is one original row, 72 is a new row). This however is not happening: With 7604913 in these tables, the original table had a size of 1020 MB. The new  table has a size of 1159 MB. I used pg_size_pretty(pg_relation_size('<tablename>')) to measure the sizes. So what am I missing?

One note: all but the last four columns are inherited from another table (of which I of course had to change the layout, too).

Update: After running VACUUM FULL as suggested by Erwin Brandstetter, the new table needs only 734 MB.


texts: MySQL Fast Database Data Validation Master/Slave

texts:
I'm moving a MySQL database from the US to Australia and have a requirement where downtime is kept to an absolute minimum.

The database is 400GB is size, replication is working without any issues. However, performing data validation takes days with pt-table-checksum.

To ensure that the database integrity is maintained up until the point of cutting over, is there a better tool than pt-table-checksum to validate the data is consistent between the master and slave? Or can pt-table-checksum be optimised to run faster?

Please note that initial testing with pt-table-checksum across the different geographic regions was performed where the databases were both idle

Any advice is appreciated!


texts: Escape a quote in SSIS Flat File Conn Manager

texts:
I am trying to import a simple text file, that has a few "extra" quotation marks in it, and I am trying to see if there is a simple way to accommodate this.

To reproduce the issue, I have a simple three line file I want to import:

"FirstName","LastName","ZIPCODE"
"Tim","Harkin","11111"
"Tim","Harkin"","11111"
"George","Washington","12345"

The extra quote in the third line is throwing off the import.  Looking at the file connection manager, I have the following settings:

However, looking at the preview, the extra quote then ends up "hiding" the column delimiter:

Any suggestions to get the connection manager to ignore that extra quote, and interpret each line as they exist in the flat file?


texts: Select Count(*) FROM with-clause-named table order by a, b not working

texts:
I have a series of tables defined in a stored procedure to eventually return to the application like this, then culminating to a simple table which contains distinct values from a previously defined table and the count of how many times that distinct value repeats.  For some reason it does not seem to be working...

...stored procedure...

OPEN p_retcursor FOR
WITH r AS
(SELECT ...
FROM MYDATATABLE),

s  AS
(SELECT ...
FROM r
WHERE ...)
,

t AS
(SELECT ...
WHERE ...
JOIN ...
ON ...
FROM s)

SELECT t.*, COUNT(*) as distinctABcount
FROM t
GROUP BY t.columnA, t.columnB

How do I get this to work?  I guess technically columnA and columnB are not columns of a table, but are columns of t so I think the order of evaluation is a bit messed up due to the fact that t is not an actual table...
Query fails around the count(*) column part and may be failing on the GROUP BY.

Thanks.

Update 1:
I have a theory that if I string together all the named tables into subqueries inside the from clause it will work... but that does not seem like a solution which will produce readable code considering this stored procedure is hundreds of lines long...

Update 2:
The theory was wrong, the derived table was not the problem, but as pointed out by the answer, you cannot ask for more columns than what you have set in the group by clause.  I fixed it by correcting the final section of the query like suggested:

select t.columnA, t.columnB, COUNT(*) as distinctABcount
FROM t
GROUP BY t.columnA, t.columnB

which runs correctly.


texts: Trigger that records values for 5 columns from 3 tables when a value for column Z in table 3 is of a certain value

texts:
I am new to triggers and SQL in general. Sorry in advance if this forum is only for experts and this is a newbie question...

I need to create a trigger that fires when someone tries to submit a grade(evaluation) of "5" in bookinglog_table for grade column. This will then grab the values of 5 columns that gave that grade of 5, and put them in a new table (so a fourth table) that I already created.

But the 5 columns are from 3 different tables (for example, fname=si_table, lname=si_table, phone=contact_table, email=contact_table, and grade=bookinglog_table.)

So tables are si_table si, contact_table c, and bookinglog_table b

What they have in common is si.student_id=c.student_id, c.event_id=b.event_id, and si.test=b.test

I have a trigger that works only if I bring in columns from table 3 which is where the INSERT or UPDATE grade originally comes from.

So here is the new table, we'll call it grade_table in which I tested the trigger that WORKS.

CREATE TABLE grade_table (
grade NUMBER (1),
test1 NUMBER(5),
test2 NUMBER(8));

And the trigger that DOES work with the above:

CREATE OR REPLACE TRIGGER b_trigger1
AFTER INSERT OR UPDATE ON bookinglog_table
FOR EACH ROW
WHEN (NEW.eval = 5)
DECLARE
BEGIN
INSERT INTO grade_table
    VALUES (:NEW.grade, :NEW.test1, :NEW.test2);
    DBMS_OUTPUT.PUT_LINE('Recorded student information into grade_table');
END;
/

But I don't want test1 and test2 values from the bookinglog_table. I want fname, lname, phone, email from the other two tables, as well as the grade from the bookinglog_table.

Any tips, hints or suggestions? I've tried creating a SELECT statement within the trigger, but I'm not sure when I need aliases, if I need to put :NEW somewhere, or if I need an IF statement instead, etc..


texts: SQL Server 2008 - Adding a column and a filtered index within a single transaction?

texts:
This is more of a general question. Why can you not add a filtered index within the same transaction scope as adding the new column in the first place? The solution is of course simple, just make two transactions where you manually cancel the changes in made in the first one, should it return an error.

But all the same, I'm interested in the technical reasoning behind this phenomena, especially considering I've done this before. Have I activated some interesting overzealous checking on SSMS? Example code below, and thanks!

--CREATE TABLE TEST_TABLE (ID INT IDENTITY(1,1) PRIMARY KEY)

BEGIN TRY
    BEGIN TRAN

        IF NOT EXISTS (SELECT * FROM sys.columns WHERE name = 'NEWCOL_ID'
            AND OBJECT_NAME(object_id) = 'TEST_TABLE')
        BEGIN
            ALTER TABLE TEST_TABLE
            ADD NEWCOL_ID INT NULL

            CREATE NONCLUSTERED INDEX NEWCOL_ID_IDX
            ON TEST_TABLE (NEWCOL_ID ASC) WHERE NEWCOL_ID IS NOT NULL
        END

    COMMIT TRAN
END TRY
BEGIN CATCH
    BEGIN
        ROLLBACK TRANSACTION
        PRINT(ERROR_MESSAGE())
    END
END CATCH;


texts: ORACLE: Changing the grouping of a data to be based on month breakdowns

texts:
To keep this short and to the point, I am looking at modifying the grouping for this SQL query to go from NAME to Month/yy

Here is a example of the script, and screenshots of the output file

This is what I am trying to accomplish.

Any ideas on how to approach this?

SQL SCRIPT:

SELECT A.NAME
     COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '10' THEN A.ORDER_NO ELSE NULL END)) AS OCT11_ORDERS
     , COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '10' THEN A.ORDER_NO ELSE NULL END) AS OCT11_LINES
     , COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '10' THEN A.INVOICE_NO ELSE NULL END)) AS OCT11_INV_COUNT
     , COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '10' THEN A.INVOICE_NO ELSE NULL END) AS OCT11_INV_LINES
-- NOV 11
     , COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '11' THEN A.ORDER_NO ELSE NULL END)) AS NOV11_ORDERS
     , COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '11' THEN A.ORDER_NO ELSE NULL END) AS NOV11_LINES
     , COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '11' THEN A.INVOICE_NO ELSE NULL END)) AS NOV11_INV_COUNT
     , COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '11' THEN A.INVOICE_NO ELSE NULL END) AS NOV11_INV_LINES
-- DEC 11
     , COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '12' THEN A.ORDER_NO ELSE NULL END)) AS DEC11_ORDERS
     , COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '12' THEN A.ORDER_NO ELSE NULL END) AS DEC11_LINES
     , COUNT(DISTINCT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '12' THEN A.INVOICE_NO ELSE NULL END)) AS DEC11_INV_COUNT
     , COUNT(CASE WHEN TO_CHAR(A.INVDATE,'MM') = '12' THEN A.INVOICE_NO ELSE NULL END) AS DEC11_INV_LINES

FROM test.test1 A
WHERE TO_DATE(A.INVDATE) BETWEEN TO_DATE('01-OCT-11') AND TO_DATE('30-SEP-12')
AND COMPANY = '30'
AND CUST_GRP IN ('3','4')
GROUP BY A.NAME

OUTPUT FILE:

DESIRED OUTPUT

For the desired output, all of the columns should be counts

Thanks!


texts: Differential Backup Issue - Why? Is this Possible?

texts:
I am using SQL Server 2014 and this is the situation:

I have server A and server B.
Overnight ETL is processed on server A.
After the loading process completes, database X gets backed-up (with CHECKSUM and RESTORE VERIFYONLY to ensure reliability) and then get sent to server B.
Server B receives the bak file and then restore the database there.

I want to use differential backup strategy so that:

Full backup is only done only on Saturday
i.e. Full backup on server A on Saturday -> ship to server B -> Restore the full backup on server B
The rest of the days will be differential backup
i.e. Differential backup on server A -> ship to server B -> Restore the differential backup on server B

I have tried but I got an error, saying:

  the log or differential backup cannot be restored because no files are ready to rollforward.

Not sure why. I checked sys.database_files on server A and server B, and I can see that the differential_Base_LSN and differential_base_GUID are the same.
Anywhere/anything else to check?

By the way, on step 2 above, when I am restoring the diff backup on server B, do I always need to restore both the Full backup + the differential backup each time?

I only restored the differential backup WITH RECOVERY (and got that error message) because the full backup was restored already the day before.

To clarify: Yes, I want the db on server B to be readable between differentials. How can I get around that? Is my only option to RESTORE FULL (WITH NORECOVERY) + RESTORE DIFF (WITH RECOVERY) combo sequence each night?

Any guidance will be much appreciated.


texts: Is there a way to use PIVOT syntax to accomplish the following?

texts: I have table_A and want to have a result like table_B.  I can accomplish this by using a WHILE loop with a counter a loop 12 times to get my data in the format I want in table_B.  Wondering if there was a cleaner way.  Thank you!


texts: How often will a FOR EACH STATEMENT trigger execute if the operation is caused by an FK constraint with UPDATE CASCADE?

texts:
I understand that a trigger on table t defined with FOR EACH STATEMENT will run once when I execute an UPDATE t ....

Now, when t is defined with FOREIGN KEY ... REFERENCES a ... ON UPDATE CASCADE, and I update N rows in a, will that cause the trigger to be called once, or N times?

Put differently, are changes to a table cascaded by an FK constraint more like a single UPDATE, or more like a series of UPDATEs?


texts: Always On log files bigger than database files

texts:
We had SQL RAP and we got warning that Transaction log files larger than the database files.

They advised me to remove databases from Always ON, change recovery mode to SIMPLE and then shrink log files and put it back to Always On. And change maintenance plans. T-LOG backup is running every hour. But again next day  transaction log files are larger than the database files.

As this is running SharePoint 2013 databases, they said it could be do to the migration of SharePoint 2010 databases to 2013, but I have same behavior on another Always On cluster, which is not SharePoint. I have tried also to implement Ola Hallengren's maintenance script with same results. On other machines without AO there is no such problems.

So I am not sure why is this happening, is it related to Always On? I know that Always On AG Databases need bigger Log files, so would this be normal behavior in AO?

I have looked at Transaction log is growing very fast. I only have log_reuse_wait=2 in between log backups, everything looks to be working as expected but it is not.

I checked the log send queue and REDO queue size, and it looks good, as it only has "2 Log backup You are waiting for a log backup to occur." while it waits for next log backup. AOAG health is OK and bandwidth is more than enough. Log backup is every 15 minutes.


texts: After upgrade of SQL Server 2000 database to SQL Server 2008 the view is not sorted on the group by column

texts:
I have two tables

Location Master ([LocatioNo],[LocationName],[Description])
Location Data ([LocationNo],[LocationCode],[DDate],[BillNo],[Model],[Quantity],[SNo])

(source: staticflickr.com)
](http://www.flickr.com/photos/77377790@N08/6782618850 "Click to view on Flickr")

and a view involving the 2 tables which was created in SQL Server 2000

CREATE VIEW [dbo].[QueryLocation]
AS
  SELECT TOP (100) PERCENT
       dbo.LocationData.LocationNo, dbo.LocationData.LocationCode,
       dbo.LocationData.DDate, dbo.LocationData.BillNo,
       dbo.LocationData.Model,
       SUM(dbo.LocationData.Quantity) AS Quantity,
       dbo.LocationMaster.LocationName
  FROM
       dbo.LocationData
  INNER JOIN
       dbo.LocationMaster ON dbo.LocationData.LocationNo = dbo.LocationMaster.LocatioNo
  GROUP BY
       dbo.LocationMaster.LocationName, dbo.LocationData.LocationNo,
       dbo.LocationData.LocationCode, dbo.LocationData.DDate, dbo.LocationData.BillNo,
       dbo.LocationData.Model ORDER BY dbo.LocationData.BillNo

When the following query is execute in SQL Server 2000 the result in the grid was sorted on billno column.

SELECT *  FROM QueryLocation

(source: staticflickr.com)
](http://www.flickr.com/photos/77377790@N08/6782618858 "Click to view on Flickr")

After upgrade to SQL Server 2008 the same query when executed is not sorted on bill no field

Advice why this is not sorted on the group by column

Thanks in advance


texts: Alternatives to running query for rarely changed data everytime on large table

texts:
I have an internal web application running and everytime a user goes to the "search" view it queries three different tables in the db to generate values for three dropdowns in the view.

It's basically running a

SELECT DISTINCT (PortName)
FROM Ports
ORDER BY PortName ASC

But the table contains ~10'000'000 rows and is under quite heavy load which means that from time to time the loading time for the page (due to loading the dropdowns with data) can be upwards of 10-15 seconds.

So, is there a better way to do this, for example running some script at certain intervals and creating a table/view/whatever at a different location so as to offload querying the big table just to have 80 rows returned from the 10'000'000 in the main table?


texts:
I have a table that returns data like

ID
Code
Error
Date
VB1
VB2
VB3
VB4

2
E1
Uncap Error
2021-02-25
8
0
0
9

3
E1.2
Recap Error
2021-02-25
3
0
0
6

4
E2
Recap Error
2021-02-26
1
1
0
0

5
E3
Tip failure
2021-02-25
0
0
0
0

6
E4
Tip failure
2021-02-26
0
0
0
0

But I need to get it to look like

ID
Code
Error
2021-02-25 VB1
2021-02-25 VB2
2021-02-25 VB3
2021-02-25 VB4
2021-02-26 VB1
2021-02-26 VB2
2021-02-26 VB3
2021-02-26 VB4

2
E1
Uncap Error
8
0
0
9
0
0
0
0

3
E1.2
Recap Error
3
0
0
6
6
1
1
0

5
E3
Tip failure
0
0
0
0
0
0
0
0

So looking to pivot on the date but also on each VB#,I can get to work with one VB# but not a range
Please advise


texts:
If a database is to hold a large amount of eg tickets for concerts, should I make a new table for each concert, then keep a table connecting venues with concerts?

I would like each ticket ID for each concert to start from 1, not be a running total of every single ticket.

Or should I keep them in one table and have 2 seperate columns for (ticket ID) AND (ticket number for that event)?


texts:
I'm trying to build a query to show rows in a Customer table that match rows exactly in 2 columns (Address 1 and Address 2) but then show only if a 3rd column is not matching.

Customer ID Status  Address 1 Address 2
----------  ------ ---------- ----------
    2        Free   1 Main St. Apt 2
    3        Paid   1 Main St. Apt 2
    5        Free   2 Main St. Apt 1
    7        Free   2 Main St. Apt 1

So in the above data example I would want the query to only show that Customer IDs 2 and 3 match on Address 1 and Address 2 but do not match by Status. It would be like an exception report showing those data matches. Hope this makes sense.


texts: If a VARCHAR(MAX) column is included in an index, is the entire value always stored in the index page(s)?

texts:
I'm asking this out of curiosity, being inspired by this question.

We know that VARCHAR(MAX) values longer than 8000 bytes are not stored in rows, but in separate LOB pages. Subsequently retrieving a row with such value requires two or more logical IO operations (essentially, one more than otherwise would theoretically be necessary).

We can add a VARCHAR(MAX) column, as INCLUDEd, to a unique index, as demonstrated in the linked question. If this column has values that exceed 8000 bytes in length, would such values still be stored "inline" in the index leaf pages, or would they also be moved to LOB pages?


texts:
Is there any way to export data from an Oracle SQLPlus console, without using the exp or expdp commands? I don't have sysdba privileges.

I'm not a real Oracle guru so I'm using Navicat to export but it just takes too long. I would prefer to do it locally from command line, but don't know how.

I have 1 day until they close down the server and I really need that data.


texts:
My TempDB disk size is 100GB.

Out of that I have made below split:

80GB/8 = 8 Data files.
15GB for Log file.
Remaining 5GB I have left for autogrowth for last eight datafile and log file(Unrestricted).

This completes my disk 100GB.

Problem is: For TempDB Disk alert I have set for threshold 90% above.But now I have assigned all space for TempDB eight files and log file and autogrowth enabled for last data file,my Disk is showing in Red.

Is it a problem ?


texts: Where clause in select does not work with Inequality of guid

texts:
I want to query our CRM Db.
I have a backup of CRM Db and the existing one.
There is a custom entity,in both of them I have created.
Some of my custom entity records has deleted from the existing db.
I want to compare which records has deleted.
Number of existing records is 17 and the backup contains 19 records.
The custom entity is named new_services.
The backup database is named test2 and the existing one is MSCRM The table name is new_services and
When I query this equal ones :

select * from test2.dbo.new_services as t1,
 MSCRM.dbo.new_services as t2
where t1.new_service_Id = t2.new_service_Id;

And return correct records,
But when I try this :

select * from test2.dbo.new_services as t1,
 MSCRM.dbo.new_services as t2
where t1.new_service_Id != t2.new_service_Id;

It returns 308 records !
The new_service_id is the guid of the record.
Where is wrong?


texts:
I'm trying to execute compression script GET_COMPRESSION_RATIO from here:
And this line gives me an error:

comptype => DBMS_COMPRESSION.comp_for_oltp,

  PLS-00302: component 'COMP_FOR_OLTP' must be declared

In oracle documentation, they say "The DBMS_COMPRESSSION package is owned by user SYS, and must be created by SYS. The execution privilege is granted to PUBLIC. Subprograms in this package are executed using the privileges of the current user."

I granted PUBLIC role to my user but don't know how to create  this package by SYS user. Or the problem lays somewhere else?


texts:
I dumped a database (sys_data)  which is very big (800GB, all data in one ibdata file) from a remote server. But the dump was blocked at a table (tb_trade_376).
My dump command:

mysqldump -uxx -pxx -h192.168.1.xxx --single-transcation sys_data > /home/sys_data.sql

When the dump was blocked:

show processlist;
5306612 | root | 192.168.1.161:57180 | sys_data
   | Query  | 23955 | Sending data | SELECT /*!40001 SQL_NO_CACHE */ * FROM `tb_trade_376`

On the other hand I can dump the table tb_trade_376 successfully if I just dump the table only.

mysqldump -uxx -pxx -h192.168.1.xxx \
  --single-transcation sys_data tb_trade_376 > /home/tb_trade_376.sql

This works well and quickly!
The table tb_trade_376 has about 700,000-800,000 rows.

What is the next step in investigating why I can't dump the whole database? How can I make it work?


texts: Designing a (many to many) to many relationship

texts:
Currently I have the following situation. I have a plant_species table with the following attributes.

plant_species
id(PK), name, latin_name, description, synonym

A plant species can have one or more useful parts

useful_part
id(PK), name, latin_name

Since a useful part can be assigned to one or more plant species I created the table plant_part. With the plant_part table I created a many to many relationship with an extra attribute (description).

plant_part
id(PK), plant_species_id (FK), useful_part_id (FK), description

Every plant part can have a many images or have many bioactive substances since many plant species can have the same useful part but the the look can be different (image) or the bioactive substance that it contains can be different also. Based on that I created the following tables.

image
id(PK), name, image_url, etc.
bioactive_substance
id(PK), name

Every plant part can have one or more bioactive substances but the bioactive substance can be assigned to one or more plant parts but the content (number in %) can be different so another many to many table is required so I created the following.

plant_part_bioactive_substance
[plant_part_id, bioactive_substance] (composite PK), content, plant_part_id (FK), bioactive_substance (FK)

Also every plant part can have one more images but since plant species can have images also I created the following table so I avoid populating the image table with foreign keys and if in the future I'll need to store images for something else I'll just need to add a another junction table.

plant_part_image
[plant_part_id, image_id] (composite PK), plant_part_id (FK), bioactive_substance (FK)

Do you think this kind of approach/design is correct? If it helps I'm using MySQL and I'm going to build a REST API on top of this database.


texts: Using (NOLOCK) in a READ COMMITTED transaction block

texts:
We're using SQL Server 2000.

I'm wondering what the risks are if we use a NOLOCK hint within a READ COMMITTED transaction block, for example:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
GO
BEGIN TRANSACTION
IF EXISTS(SELECT * FROM clients (NOLOCK) WHERE clientID = 23249)
    BEGIN
        PRINT 'TODO Update client'
    END
ELSE
    BEGIN
        PRINT 'TODO Insert client'
    END

COMMIT TRANSACTION

My concern is that the NOLOCK hint will allow a ditry read, despite the isolation level of the transaction block.

Is this possible?

PS: I know there is a debate to be had about why I am using a locking hint the in first place, but I would just like to know what the risks are in this scenario.


texts:
Per my reading books on SQL Server 2008 Internals and Troubleshooting (borrowed from local library in Illinois) by Christian Bolton, Brent Ozar etc. I am trying to seek understanding and confirmation on SQL server and lots of searching on the web I would appreciate if someone can confirm or correct my understanding.

Every query or operation that requires query memory grant will need work space memory. In general query using Sort, Hash Match Join, Parallelism (Not sure about this), Bulk Insert (not sure), Index Rebuild etc. will need query workspace memory..

Workspace Memory is part of SQL Server buffer pool (it is allocated as part of buffer pool) and maximum workspace memory is 75% of memory allocated to buffer pool. By default a single query can not get more than 25% of workspace memory (in SQL 2008/SQL 2012 -- controlled by Resource Governor default workload group out of the box).

Seeking a confirmation of my understanding

1) Considering system with 48 GB RAM and max server memory configured to 40 GB does this mean max workspace memory is limited to 30 GB and a single query can not get workspace memory (query memory) more than 10 GB. So if you have a bad query working with a billion rows that is doing massive hash join and need more than 10 GB of memory (workspace memory) would it even care to  go through this memory grant queue or right away spill to the disk?

2)  If a query doing a massive sort operation has been assign a workspace memory of 5 MB and during the query execution of the query if query optimizer realize that due to bad statistics or missing indexes this query will actually need 30 MB of workspace memory it will immediately spill to tempdb. Even if system has plenty of workspace memory available during the execution once the query exceeded the granted workspace memory during the execution it will has to spill to the disk. Does my understanding is correct?


texts: Select count of timestamp formatted field in short date format to give number of rows

texts:
I have a field titled DTUpdated and it is a timestamp field.
I am trying to select a count of the dates in the rows that are xx old. i.e. 0-3 months, 3-6 months etc.
I am not at the writing date selection criteria for the selection yet as i am stuck getting the first part of the query to simply count the occurrences of each day in a results pane.
The query i have so far is as follows but it is not grouping the data by the days as returned.
SELECT CONVERT(VARCHAR(10),[SCAData].[dbo].[Camera].[DTUpdated],105) AS Date_Updated,
COUNT(DTUpdated) AS No_of_Rows
FROM [SCAData].[dbo].[Camera]
GROUP BY DTUpdated
ORDER BY DTUpdated;

Result:
Date_Updated    No_of_Rows
03-10-2006      1
03-10-2006      1
05-10-2006      1
05-10-2006      1
05-10-2006      1
05-10-2006      1
05-10-2006      1
05-10-2006      1
05-10-2006      1
06-10-2006      1
06-10-2006      1
06-10-2006      1
06-10-2006      1
06-10-2006      1

Can someone please help or also suggest how to then get these back based on date selections of last 3 months, 3-6 months?
Many thanks in advance.


texts: Slow proc Used to take a few seconds now takes well over a minute

texts:
I'm at a new place and working on several issues. One item that was passed to me was this proc that used to take a few seconds is now taking well over a minute or timing out.

The data hasn't changed too drastically.

The filesReceived table has 902,596 records.
The fileuploads table has 629,862 records.

We run SQL2016.
Can you see if there is anything I can do to help speed this back up?

The plan:
https://www.brentozar.com/pastetheplan/?id=BJc4DsywZ


texts: SQL Server: SET NOCOUNT ON still returning data

texts:
Following the example from this page, I'm unable to stop a stored procedure from returning a select dataset.

Executing this:

SET NOCOUNT OFF;
GO
-- Display the count message.
SELECT 'should be displayed'
GO
-- SET NOCOUNT to ON to no longer display the count message.
SET NOCOUNT ON;
GO
SELECT 'should NOT be displayed'
GO
-- Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO

Will return me this:

Is this a bug? Is there something wrong? I'm on a SQL Server 13.0.4206


texts: Grant access to all objects (with a few exceptions) to a role

texts:
In SQL Server 2014, I want to create a role that will have access (select, delete, update, insert, execute) to all the tables, views, stored procedures, except a certain list of tables that contain sensitive data that only the "sa" user should have access to.

I tried the following:

Using SQL Server Management studio, in the database (TESTCOMPANY) -> Security -> Roles -> Database roles, create the database role "CompanyAdmin".

Run the following query:

GRANT  SELECT,INSERT, UPDATE, DELETE, EXEC ON DATABASE::TESTCOMPANY TO CompanyAdmin

Then:

DENY SELECT, INSERT, UPDATE, DELETE ON TestTable1 TO CompanyAdmin;
--Repeat for all tables to secure

In Management Studio, server -> Security -> Logins, create a new login "testuser". Server Roles = "public" only.
Then go to TESTCOMPANY -> Security -> Users -> New user:

User Type: SQL user with login
User name: testuser
Login name: testuser
Default schema: dbo
Membership: CompanyAdmin

Once logged in as testuser, all seems fine, I see all tables except the ones in the DENY permissions. BUT:

If, in the future, the "sa" user creates a view on the secured tables, the testuser user has access to it and can view the data it returns
In SQL Server Management Studio, I can go to Server -> Security -> Logins and I see the "sa" login properties

Am I taking the right approach or is there a better way to accomplish what I want to do?

It's the database for the accounting application of our company and the software is designed to have everything in the same database.


texts: Changing POSTGRES column datatype - should I drop indexes and then re-create?

texts:
I have a number of postgres tables with a column of type INT which I need to change to BIGINT. Each partition of the table contains about 100 million rows, my intention is to detach the individual partitions ALTER the column type, and then re-attach.

My question is this, the column that needs updating is indexed. Is likely to be quicker to drop the index, alter the column type and then re-create the index? Or should I leave the index in place and just change the column type?

note that the column values aren't going to change, just the datatype (not sure if that's relevant or not!)

thanks


texts: Why is MySQL Access denied after .bashrc edit?

texts:
I added this line
#mysql password
alias mysql='mysql -umilenko -p*myrealpass*** -hlocalhost'

Anyway,it does not work
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'milenko'@'localhost' (using password: YES)

Also
mysql -u root -p
mysql: [Warning] Using a password on the command line interface can be insecure.
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

Version
mysql  Ver 8.0.23-0ubuntu0.20.04.1 for Linux on x86_64 ((Ubuntu))

I am not admin,I just need to use sql for my project.
How to solve this problem?


texts: How to unnest and GROUP BY elements of a JSON array?

texts:
Given the band table, with a json column holding an array :

id | people
---+-------------
1  | ['John', 'Thomas']
2  | ['John', 'James']
3  | ['James', 'George']

How to list the number of bands each name is part of?
Desired output:

name   | count
-------+------------
John   | 2
James  | 2
Thomas | 1
George | 1


texts: Why is the SELECT timeline different though the WHERE clause & EXPLAIN plan are same?


texts: What's a more natural column name for Tables whose name share a column name?

texts:
I have a database that's used by an ASP.NET MVC project through EntityFramework. Currently there are some tables that have columns with the same name, i.e.

CREATE TABLE HairColor(
    HairColorID int IDENTITY CONSTRAINT PK_HairColor PRIMARY KEY
    ,HairColor varchar(15) NOT NULL
)

but once EF goes through its thing it changes the HairColor property to HairColor1 because classes can not have properties which share its name.

So my question is, what would be better: HairColor.Name or HairColor.Color?


texts: MySQL got signal 11 error and slave is down

texts:
My slave server is down and mysqld unable to start. What should I do?

Below follows the error logs:

2014-09-26 12:51:53 14166 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
2014-09-26 12:51:53 14166 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2014-09-26 12:51:54 14166 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
2014-09-26 12:51:54 14166 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
2014-09-26 12:51:55 14166 [ERROR] InnoDB: Unable to lock ./ibdata1, error: 11
2014-09-26 12:51:55 14166 [Note] InnoDB: Check that you do not already have another mysqld process using the same InnoDB data or log files.
(...)


texts:
Oracle version: 11g r2
OS: win server 2008

SQL> alter database open;
  alter database open
  *

  ERROR at line 1:
  ORA-01122: database file 5 failed verification check
  ORA-01110: data file 5: '[file_path]'
  ORA-01210: data file header is media corrupt

  SQL> alter database datafile '[file_path]' offline;
  alter database datafile '[file_path]' offline
  *

  ERROR at line 1:
  ORA-01145: offline immediate disallowed unless media recovery enabled

  SQL> archive log list;
  Database log mode              No Archive Mode
  Automatic archival             Disabled
  Archive destination            USE_DB_RECOVERY_FILE_DEST
  Oldest online log sequence     8231
  Current log sequence           8235

  SQL> shutdown immediate;
  ORA-01109: database not open

  Database dismounted.
  ORACLE instance shut down.
  SQL> startup mount;
  ORACLE instance started.

  Total System Global Area 2.7392E+10 bytes
  Fixed Size                  2188768 bytes
  Variable Size            1.5032E+10 bytes
  Database Buffers         1.2348E+10 bytes
  Redo Buffers                9183232 bytes
  Database mounted.

  SQL> recover database;

  ORA-00283: recovery session canceled due to errors
   ORA-01110: data file 5: '[file_path]'
   ORA-01122: database file 5 failed verification check
   ORA-01110: data file 5: '[file_path]'
   ORA-01210: data file header is media corrupt


texts: Database design where % of rows have unused attributes/columns

texts:
Is there a theory/practice that promotes a design by analysing the usage of attributes of a table, such that given a threshold of unused columns over a percentage of rows would suggest that the attributes do not actually belong that that table, but instead in a relationship?

example:

users
| id | name  | completed_sign_up |
| 1  | Bob   | null |
| 2  | Sarah | false |
| 3  | Penny | true |

The above false and nil cases are the same, so instead one could say completed_sign_up is not a property of users, but instead should be a join table on it, eg:

sign_ups
| id | user_id |
| 10 | 3


texts:
I have read many blogs here and there about how to configure tempdb data files but i didnt found any information regarding the tempdb log file.

Here's the strategy that I'm presently using with my tempdb:

I have used the recommendations of Paul Randal on how to split my tempdb data files
I have set the size of my tempdb data files to their maximum and disabled autogrowth. For example, i have 100gb of free disk space and set the size of my 8 tempdb data files to 10gb each. This prevent fragmentation on the disk as recommended by Brent Ozar and also I have 20gb free for my log file.

But like I said, nobody is talking about the tempdb log file. What should I do with it? On my setup, this file is at the same place as the tempdb data files. What is the size and the autogrowth value that I should use with the tempdb log file?