texts:
I am trying to start mysql 5.5 service on ubuntu 14.04 but it fails.
I noticed in dmesg the following error:
[ 1201.519101] init: mysql post-start process (22140) terminated with status 1
[ 1201.539004] init: mysql main process (22165) terminated with status 1
[ 1201.539016] init: mysql main process ended, respawning
[ 1201.542554] init: mysql post-start process (22166) terminated with status 1
[ 1201.562121] init: mysql main process (22196) terminated with status 1
[ 1201.562134] init: mysql respawning too fast, stopped

How can I address this?
I recently made some changes to my.cnf to innodb - related configuration in case if this is causing the issue:
# Read the manual for more InnoDB related options. There are many!
innodb_read_io_threads          = 16
innodb_write_io_threads         = 16  #To stress the double write buffer
innodb_buffer_pool_size         = 28000M
# innodb_log_file_size          = 256M #Small log files, more page flush
innodb_log_files_in_group       = 2
innodb_file_per_table           = 1
# innodb_log_buffer_size            = 512M
innodb_flush_method         = O_DIRECT
innodb_flush_log_at_trx_commit      = 0
innodb_file_format          = BARRACUDA
# innodb_log_file_size          = 512M


texts: Is there any value in adding an already indexed column to a composite index?

texts:
The title says it all. Does the query optimizer see composite indexes where each column is already indexed as redundant? Or, is it used when each of the composite index's columns are in the WHERE clause? For example....

index1('col1')
index2('col2')
index3('col1','col2')

select some_column from table where col1 = this;                    //uses index1
select some_column from table where col2 = that;                    //uses index2
select some_column from table where col1 = this and col2 = that;    //uses index3

Explain... seems to indicate it chooses an index with the least number of pertinent columns.

Thanks.


texts: SQL named instance cannot be connected by a specific user

texts:
SQL server 2008 R2.
It has default instance running and a couple of named instance.
- I can connect to both default instance and named instances.
- User can connect to default instance but he is not able to connect to named instances.
- I asked the user to connect "tcp:ServerIP\instancename" and it works

I thought then it could be that UDP port 1434 is not allowed (which is important for names instance connection), but I am not able to connect the usual way (servername\instancename)
My system administrator tells me all the ports are allowed to this hosting network on the SQL boxes including the UDP ports.

I have 2 questions:
1. What could be blocking the user/s in a particular network not be able to connect to it?
2. Is it bad if I allow the user to connect to is using "tcp:ServerIP\instancename" ? Why don't usually people don't use this?
3. What does it mean when a user can connect to an instance using "tcp:ServerIP\instancename" but not "ServerIP\instancename"

Thanks for your time.


texts: Backup policy for Oracle 11g database in non-archive mode

texts:
My database is in non-archive mode and its size is 5 TB (monthly 200 GB increment). Currently we are taking the full backup monthly. As database growth is high, backup size and time had increased behold the acceptable limit. Hence we would like to take online RMAN backup by enabling the archive mode before starting the online backup and disable the archive mode after completion of online backup.

1) Are we able to recover the database till last backup using above backup policy?
2) Is it required to take archive log backup as well?

This DB only contains historical data and on daily basis an import will be done using dump files from the source DB. One month export dumps will be kept as backup, hence complete recovery till point of failure is not desired. Export dumps can be used to recover the database.


texts: Version of Witness Server for mirroring SQL 2012

texts: Can I use SQL 2008 R2 as a Witness Server for 2 instances of SQL 2012 Servers being mirrored?


texts: Handle no more connections or out of shared memory

texts:
I have a golang program that does computation on data in multiple threads at once, all pulling their data from Postgres. The number of threads depends on a previous result. There can therefore be hundreds of threads trying to pull data from Postgres at the same time.

The golang sql library allows one to specify a connection limit, which prevents postgres from running out of shared memory or free connections.

If I hardcode the maximum number of connections I will run out of connections when something else is connected. On the other hand if I hardcode a too low number of allowed connections from the golang program performance will be unnecessary limited.

What would be the best way to allow the go program to use as many as possible connections, without running into the limits. I imagine this number to be a variable one depending on the number of other services that are connected to the database at that time.

I am thinking of running PgBouncer between the database and the golang program, hopefully accepting all the connections from the golang program, allowing as many as possible through, but blocking the rest until connections become free. I am however unsure if PgBouncer does this, but I will be testing this next.

Is there perhaps another method of having a connections pool that will block connections when no real connections are free? Blocking, not refusing, as refusing a connection will mean I have to add retry logic to my golang program.


texts:
When I try to open a file using the file open dialog, sqldeveloper freezes for almost a minute. After that the dialog opens at the user temp folder.

I think it is trying to access some unavailable network location:

However, I can't figure out how to make it "forget" that location. I tried to select a local file in the dialog, but next time all repeats.


texts: How to resolve "the specified account already exists" error during SQL Server 2008 R2 installation

texts:
I recently had a problem while installing Visual Studio 2012 and SQL Server 2008 R2 on a new development machine. My installation sequence was Visual Studio 2012 then install SQL Server 2008 R2.

An error occurred during the middle of SQL Server installation:

  The specified account already exists.

I have spent my day trying to fix this problem.


texts: How do I delete only related records in a multi-key MERGE in SQL Server?

texts:
Suppose you have something like this:

Source table (variable):

Values (
  LeftId INT NOT NULL,
  RightId INT NOT NULL,
  CustomValue varchar(100) NULL
)

Target table:

Mapping (
  LeftId INT NOT NULL,
  RightId INT NOT NULL,
  CustomValue varchar(100) NULL
)

I want to merge Values into Target with the following rules:

Match on source.LeftId = target.LeftId AND source.RightId = target.RightId

when matched in target, update CustomValue
when not matched in target, insert

Delete any unmatched values in the target that do match a LeftId in the source, i.e. only delete records that related to the LefIds of what I'm merging.

(That last rule is hard to describe, sorry!)

For instance:

Source:

1, 10, foo
1, 11, foo

Target:

1, 10, bar
1, 12, foo
2, 20, car

Merge result:

Result Target:

1, 10, foo (updated)
1, 11, foo (inserted)
1, 12, foo (deleted)
2, 20, car (unchanged)

So...

Here's what I have so far, which takes care of update and insert:

MERGE Mapping AS target
USING (SELECT LeftId, RightId, CustomValue FROM @Values)
  AS source (LeftId, RightId, CustomValue)
  ON target.LeftId = source.LeftId
    AND target.RightId = source.RightId
WHEN NOT MATCHED THEN
  INSERT (LeftId, RightId, CustomValue)
  VALUES (source.LeftId, source.RightId, source.CustomValue)
WHEN MATCHED THEN
  UPDATE SET
    CustomValue = source.CustomValue;

How do I do the delete part of my rule?


texts: How to do a FullText Search on Sql Server Table which includes special characters?

texts:
I am attempting to do a fulltext search on a table where one of the rows is a log and contains characters such as ,.: and so on. Currently when a query is ran using the 'Contains' keyword which includes any of those characters no rows are returned. Is there any advice on the best route that can be take for this search?
Here is what my current query look like at the moment:

ALTER Procedure [dbo].[searchLog]
    @searchValue varchar(150)
    AS
    Declare @myquote varchar(10)
    Declare @wildcard varchar(5)
    set @myquote ='"'
    set @wildcard ='*'
    set @searchValue = @myquote + @searchValue + @wildcard +@myquote
    BEGIN
    SET NOCOUNT ON
    SELECT  o.ID, o.Name, o.DateRan, s.Name AS Job, o.Start, o.[End], o.Status, o.Logs
    FROM    dbo.o AS o LEFT OUTER JOIN
                dbo.j AS j ON o.Job = j.ID LEFT OUTER JOIN
                dbo.s AS s ON j.Script = s.ID
WHERE CONTAINS ((o.Name, o.Logs),  @searchValue)
ORDER BY o.ID DESC

Example of Logs being searched:

"Sunday, July 7, 2019 1:20:00 PM
 Seeking c:\xxxx\xxxx\log1_date.txt
 Seeking c:\xxxx\xxxx\log2_date.txt
 Seeking c:\xxxx\xxxx\log4_date.txt
 Monday, July 8, 2019 1:20:00 PM   FTP AND PGP COMPLETED Processed 0 files ."


texts: PostgreSQL - How can I manage WAL archiving for different databases on a server?

texts:
I'd like to use WAL archiving for incremental backup of my database. But I have 2 databases on my server, and I can't afford the disc space for backup of both of them. So I only want to backup one of them. I didn't find anything about multiple databases in PostgreSQL documentation or anywhere else. Or I didn't find anything about specifying the database name for WAL backup.
It will be appreciated if someone can help me with this.


texts:
I currently have 2 tables in my DB, stores and products. Each product has it's own ID 1-2000, and each store has its own id 1-200.

My question on DB design, is how do i keep track of which products each store has?

example

store 1 has products 1, 5, 10, 200
store 2 has products 2, 5, 7, 100
store 3 has products 1, 7, 10, 300
etc...

Should I have a column in my stores table that has id's stored as a string separated by a column such as {1;5;10;20} (with the possibility of thousands of entries in the 1 string) ?
Should I have another table that is just [id|storeID|productID] (with the possibility of stores(x)products in the table) ?


texts: How do I select 1st row of each table and insert that data to a new table

texts:
i have 5 tables (tab1, tab2, tab3, tab4 and tab5) and all the 5 tables has same table
definition. I want to select 1st row of each table and insert those 5 rows in
new table(newtab). I was only able to write query that select 1 row from one table and
insert that into newtab. How can i select 1st 5 rows from all the table and insert them
into newtab in one query.

In this case i have to deal with only 5 tables so its easy using union but when i want to select 1st rows from 100 tables then the query will become but lengthy using UNION.


texts: Non clustered index on partitioned table

texts:
Using SQL Server 2012. I have a huge table that is partitioned. I need to create a non clustered index on it.

After I created the index, a job that somewhere switches partition fails because the index I create. (The job works fine once I remove the index)

My question is what are the things I need to take in account while creating the index on partitioned table.


texts:
I have 2 tables Part and PartDtl.  PartDtl contains one row for each order that has been placed for that part.

PartNum | Description | ...
1234    | 1234 desc   | ...
2345    | 2345 desc   | ...

PartNum | Qty | DueDate     | ...
1234    | 12  | 2015/11/15  | ...
1234    | 66  | 2015/11/17  | ...
1234    | 45  | 2015/11/18  | ...
1234    | 33  | 2015/11/16  | ...
2345    | 77  | 2015/11/12  | ...
2345    | 43  | 2016/01/01  | ...

I use the follwing sql to get the data:

select
  p.PartNum,
  pd.Quantity,
  pd.DueDate
from
  Erp.Part as p,
  Erp.PartDtl pd
where
  p.Company = pd.Company and
  p.PartNum = pd.PartNum and
  pd.StockTrans = 'TRUE'
order by 1, 3, 2

The problem is that some parts have lots of orders placed and I only need to see the first few.  For example:

PartNum | Qty | DueDate
1234    | 12  | 2015/11/15
1234    | 33  | 2015/11/16
1234    | 66  | 2015/11/17
2345    | 77  | 2015/11/12
2345    | 43  | 2016/01/01

I haven't been able to figure out how to have the first 3 orders due of each part.

I have tried a sub-query, but haven't figured out the syntax.

Can you show me the way, please.

nb - there are other fields which are shown in the sql which I don't think are relevent to this query.  I'm using a recent MS SQL-Server database.


texts: If positive, sum all items. If negative, return each one

texts:
I am needing to find a way to SUM() all of the positive values for num and return the SUM() of all positive numbers and an individual row for each negative number.  Below is a sample DDL:

Create Table #Be
(
    id int
    , salesid int
    , num decimal(16,4)
)

Insert Into #BE Values
    (1, 1, 12.32), (2, 1, -13.00), (3, 1, 14.00)
    , (4, 2, 12.12), (5, 2, 14.00), (6, 2, 21.23)
    , (7, 3, -12.32), (8,3, -43.23), (9, 3, -2.32)

And this is my desired output (positive numbers for each salesid SUM() and negatives get an individual line returned):

salesid    num
1          26.32
1          -13.00
2          47.35
3          -12.32
3          -43.23
3          -2.32


texts: Trying to phpMyAdmin import a database with a different name

texts:
I've been searching for answers and couldn't find anything suited for my exact situation :

I have a magento website and i'm trying to "duplicate" the website into another one selling other types of products ,as i just have to change some design-features like logo, some colors, etc.

Problem is this, i export the database from my original website and it's called superdupergen_db .

When i try to import it on the 2'nd website which has topcompresor_db database name, i get name colliding error.
Can someone help me by explaining how to bypass this ?

Thanks !

Chris


texts: I am in charge of a mysql database which is about 12 gb, and it grows pretty quickly (was 3gb a few months ago). I wanted to know what measures i can take when the database grows even larger (lets say the 50-100gb area) to keep the performance good. I heard about sharding and archiving but it was pretty overwhelming so i was hoping to get some clarification here. Thank you very much


texts: How to mark the default row to select in a many to many relationship

texts:
To start off, I have the following tables:

products - a list of products
images - a list of images (which may or may not belong to a product)
product_images - a join table to connect products to images (this table just has id, product_id, and image_id)

I need a way to mark one image as the default image to use for a product, but I'm not quite sure how to set it up. I've thought of a few options but none of them feel correct.

Options I've thought of:

Add an image_id field to the products table for the default image. This seems bad cause the image on the product might not be an image that belongs to that product (I could take care of this on the application side, but I'm looking for more elegant solutions)
Add a column on the product_images table that marks that image as default, and add a unique constraint on the combined product_id field and default field (the product_images table would then look like: id, product_id, image_id, default). I've never used unique constraints before, and I'm not sure if they're the right way to go.

Hope you guys have some better advice!


texts: PostgresQL Delete Trigger does not delete row

texts:
I'm currently creating archive records for my database, where each table x has a corresponding x_archive table. My solution was to create a trigger for each table that needed replication that would insert the deleted data into the archive table. The trigger seems to run just fine, with the data being replicated to the archive table. The original data, however, is not deleted. The trigger only seems to run on the first delete query. If I run the same delete statement again (on the original data), both the row in the original table and the archive table is deleted. I've also tried to create trigger functions that are specifically tailored to inserting into a specific table, but the same result occurs. Any help is greatly appreciated.
Trigger function
CREATE OR REPLACE FUNCTION archive_record()
RETURNS TRIGGER AS $$
BEGIN
    RAISE NOTICE 'Running trigger';
    EXECUTE format('INSERT INTO %I.%I SELECT $1.*', TG_TABLE_SCHEMA, (TG_TABLE_NAME || '_archive'))
    USING OLD;
    RETURN NULL;
END;
$$ LANGUAGE PLPGSQL;

Example trigger
CREATE TRIGGER delete_test
    AFTER DELETE
    ON test
    FOR EACH ROW
    EXECUTE PROCEDURE archive_record();

Example table
create table test
(
    id                  serial primary key,
    name                varchar(128) not null,
);

Example archive table
CREATE TABLE test_archive (
) INHERITS(test);


texts: how to get the marks in ‘Math’ of ‘English’ topper for each class in mysql for student result data

texts:
I need to find the marks in ‘Math’, of ‘English’ topper for each class in MySQL for student result data.

Output schema: class, roll_no, math_marks

(Here expected result image not exact answer)

my table is as,

CREATE TABLE resultdata
(
  class TINYINT UNSIGNED NOT NULL,
  roll_number TINYINT UNSIGNED NOT NULL,
  subjects VARCHAR (15) NOT NULL,
  marks TINYINT UNSIGNED NOT NULL
);

Populate it:

INSERT INTO resultdata(class,roll_number,subjects,marks) VALUES
(8, 1, 'math', 98),
(8, 1,'english', 88),
(8, 1,'science', 96),
(8, 1,'computer', 94),
(8, 2,'math', 78),
(8, 2,'english', 89),
(8, 2,'science', 87),
(8, 2,'computer', 99),
(9, 1,'math', 90),
(9, 1,'english', 85),
(9, 1,'science', 85),
(9, 1,'computer', 85),
(9, 2,'math', 95),
(9, 2,'english', 30),
(9, 2,'science', 40),
(9, 2,'computer', 80),
(10, 1,'math', 85),
(10, 1,'english', 90),
(10, 1,'science', 65),
(10, 1,'computer', 45),
(10, 2,'math', 50),
(10, 2,'english', 95),
(10, 2,'science', 65),
(10, 2,'computer', 85),
(10, 3,'math', 51),
(10, 3,'english', 61),
(10, 3,'science', 71),
(10, 3,'computer', 81);


texts: Does the Query Optimizer Prefer to Query on Constants before Columns?

texts:
I think I have found the answer for this already but I am hoping to get some additional perspective.

Assume we are JOINing two tables together on a shared column and each table then has a different column we are going to do a Constant search on.  When we build an index to support the query, for each table do we want to put the JOINing column first, or the Constant column first?  I am thinking now it is the Constant column first.  When I look at a query plan for a different query that prompted this question, it appears it tries to create a subset of each table and then JOIN them together.  Instead of JOINing the two tables togeather and filtering down from there.

EX: Joining Shipments to Customers where Shipment is Shipped and Customer is Active

SELECT [Columns]
FROM Shipment S
   INNER JOIN Customer C
      ON S.CustomerID = C.CustomerID
WHERE S.IsShipped = 1
AND C.IsActive = 1

I am thinking the two best indexes to use are below.  Because the Query Optimizer would prefer to scan the Constant first then JOIN on the 2nd column instead of JOINing the two tables together and filtering on the constant after that.

CREATE NONCLUSTERED INDEX [IX_IsActive-CustomerID] ON [dbo].[Customer]
(
    [IsActive] ASC,
    [CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_IsShipped-CustomerID] ON [dbo].[Shipment]
(
    [IsShipped] ASC,
    [CustomerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Instead of:

CREATE NONCLUSTERED INDEX [IX_CustomerID-IsActive] ON [dbo].[Customer]
(
    [CustomerID] ASC,
    [IsActive] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [IX_CustomerID-IsShipped] ON [dbo].[Shipment]
(
    [CustomerID] ASC,
    [IsShipped] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

Is that right?


texts: How to change MySQL 8.0 data folder. Can I use OneDrive folders?

texts:
I'm trying to create a DataBase for a very little program that just I and 2 more friends use, so the database will be little.

What I want to do is to share this database with these 2 friends, so I thought about storing the data in a OneDrive shared folder.

At the moment, I'm using a txt file as a "database". Which is placed in a shared OneDrive folder, so when my friends execute the program, it can read the data from there and make it sort of real time "online".

The thing is that I can't find the my.ini file in my

C:\Program Files\MySQL\MySQL Server 8.0\

directory, so I can't change the data folder.

Another trouble I have is that I also don't have the Data folder in that directory, instead I've found it in this one:

C:\Program Files\MySQL\MySQL Workbench 8.0 CE

Is it possible to do what I want to do? And how should I proceed?

Do you think I should be using MySQL 5.x version?

Thanks


texts: SQL Server 2012 always-on backup, full vs copy-only

texts:
I need a quick clarification on copy-only backup vs full backup as it pertains to transaction log numbering and restore if required.

I have SQL Server 2012 setup with always on for multiple databases. The availability groups are setup with preferred backup on replica.

only copy-only and transaction log backups are possible on replica. Does this mean I will need to do a full backup on primary if a restore of the transaction log is required?

Thank you


texts: How to make a FULLTEXT search with ORDER BY fast?

texts:
I'm trying to get a simple FULLTEXT match to be faster when using order by on another column on a table with over 100 million rows. The basis is one table with a fulltext on two columns and I want to search the database but order it by either the primary (least/most recent) or popularity. Is it possible to make a FULLTEXT with an order by on another indexed column fast? SQL Fiddle below with schema and explains of all queries:

See SQL Fiddle #1

What's very fast so far is denormalization of search columns in a separate table and a join but I would rather not have another table if not necessary. SQL Fiddle below (denormalized query at the end):

See SQL Fiddle #2


texts: Is there a way to find the partition or subpartition name where my ID belongs

texts:
I know if I need to select from any partition I need to provide partition name

SELECT * FROM TABLE PARTITION(P1);

What if I need to know which partition a particular value belongs using a sql query then?

SELECT PARTITION_NAME FROM TABLE WHERE DOC_ID = 'S00102981655537O';

Error

ORA-00904: "PARTITION_NAME": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:
*Action:
Error at Line: 25 Column: 8

Oracle version: 11g


texts: Mysql:Need to upload data to database, get duplicate key error - how can dup keys be ignored

texts:
I'm uploading data to a db (mysql 5.1.42) from a dump file using mysql command line and get duplicate key error.

This is the format I use:

  /mnt/temp# mysql -u username -p -h url_of_db_master -D farm2  <
  db_dump_part_file_a

Hoe can I tell mysql to ignore duplicate keys?

Thanks !


texts:
Good evening,

I'm currently up to try to improve the performance of some of my queries.
As far as I know, statements like "IN" or "NOT IN" are even faster with a large amount of values if a subquery on an indexed field without conditions is used.

SELECT * FROM table1 WHERE field1 NOT IN (SELECT index_field FROM table2)

When it comes to use conditions at table2, the query becomes realy slow on a large amount of data.

SELECT *
FROM table1
WHERE
    field1 NOT IN (
        SELECT
            index_field
        FROM table2
        WHERE
            user_id = '2'
    )

I was thinking about using LEFT Join but when I need to to the filtering for the user_id, it also becomes slow.

How may I solve this problem? Currently, I've no clue about that.

The original query looked sth. like this

SELECT
    i.*
FROM stream_item si
LEFT JOIN user__publisher_item ui
    ON ui.user_id = 2
    && ui.item_id = si.item_id
INNER JOIN item i
    ON i.id = si.item_id
WHERE
    si.stream_id IN (5,7,8,9,19,24,29,42,43,44)
    && ui.id IS NULL || (
        ui.`read` = 0 || ui.saved = 1
    )
GROUP BY
    si.item_id
ORDER BY
    si.`found` DESC
LIMIT 0,10

id      select_type     table   type        possible_keys                                                                           key                 key_len     ref                 rows        Extra
1       SIMPLE          si      index       PRIMARY,stream_2_item,stream_id_found                                                   stream_2_item       4           \N                  663236      Using temporary; Using filesort
1       SIMPLE          ui      eq_ref      user_id_item_id,user_2_item,user_id_read_saved_hidden,user_id_saved,user_id_hidden      user_id_item_id     8           const,si.item_id    1           Using where
1       SIMPLE          i       eq_ref      PRIMARY                                                                                PRIMARY             4           si.item_id          1

I have a table which represents the n:m relationship between stream and items.
An unique item can be accessible via multiple streams.
Based upon this i have a state-table for user dependent states of an item.
Currently, the user-state-entry will only be generated, if the item was read or saved the first tim. Otherwise, there is no entry in the user_item table.
In the beginning, this query was fine but now, it takes a lot of time. Even if I leave out the "INNER JOIN" it takes 7 seconds on the current tables with around 500k rows each.

The table-structure in the background is as follows:

stream
stream_item //n:m relation between stream and items
item //unique items
stream_group //user-defined groups of multiple streams
stream_group_streams //_n:m relation between stream_groups and streams_
user
user_item //state-table that holds the user-dependent states of an item - if there is no state, no row exists for an item

I already asked this question at Stackoverflow but added my 2 Edits with further information to late so that I haven't got an answer anymore.


texts: MySQL - run SELECT statement on another server without defining the table structure

texts:
In MySQL I can query information on another server using federated tables, as long as I've defined the same table structure locally.

In MS SQL Server, however, I can run any SQL statement against a linked server. Is it possible to do the same thing in MySQL?


texts:
A relatively simple question but from all my research I have been unable to find a decent answer.

Which is technically more correct, open your cursor after your handlers have been defined or before you actually use your cursor?

For example,

BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE a CHAR(16);
  DECLARE b, c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;
  OPEN cur2;

  read_loop: LOOP
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF b < c THEN
      INSERT INTO test.t3 VALUES (a,b);
    ELSE
      INSERT INTO test.t3 VALUES (a,c);
    END IF;
  END LOOP;

  CLOSE cur1;
  CLOSE cur2;
END;

The above statements seem to be standard in most examples however would it be possible to do

BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE a CHAR(16);
  DECLARE b, c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM test.t1;
  DECLARE cur2 CURSOR FOR SELECT i FROM test.t2;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  SELECT x,y,z INTO @X, @Y, @Z FROM TableA WHERE X=1;

  IF (@X >= @Y) THEN

      OPEN cur1;
      OPEN cur2;

      read_loop: LOOP
        FETCH cur1 INTO a, b;
        FETCH cur2 INTO c;
        IF done THEN
          LEAVE read_loop;
        END IF;
        IF b < c THEN
          INSERT INTO test.t3 VALUES (a,b);
        ELSE
          INSERT INTO test.t3 VALUES (a,c);
        END IF;
      END LOOP;

      CLOSE cur1;
      CLOSE cur2;
  ELSE
      OPEN cur1;

      read_loop: LOOP
        FETCH cur1 INTO a, b;
        IF done THEN
          LEAVE read_loop;
        END IF;
          INSERT INTO test.t3 VALUES (a,b);
      END LOOP;

      CLOSE cur1;
  END IF;
END;


texts:
I'm currently programming a minecraft survivalgames minigame network and am currently setting up a database for it. I have decided to store the possible maps for my spigot instances that run on my physical servers in a table. They will contain a reference to a chest set. Every chest set will contain an itemset, which is a selection of itemstacks, that also are stored in a table. This will make it possible to make the items in the chests be of a great variety and have them pretty much unpredictable.
So in total I now have 4 tables. The first one looks like this:

The CHESTSET row has the ID of the CHESTSET-Table as foreign key. I do this to make every map have an associated chestset with it. The chestset-table also has a row with the ID of it, and 5 Item-Sets, so in total you can define up to 5 different types of chests. These chests will contain random items of the itemset's that are referenced in them (the random selection will be done in my plugin, this is only about storing the selection possibilities).

Every itemset contains up to 10 items, that the contens of an individual chest on the map will be composed of.

The properties of the items themselves will be stored in yet another table, this table gives them an id, and then all the relevant properties, like the item-type, the display name, the subid, etc etc. This table gets pretty big, so I'll just post a dummy, which has all relevant properties.

Now here starts my problem. I've been able to add the ID-field of my CHESTSET-Table as a foreign key to my MAPS-Table. This makes sense to me because every map will have a set of predefined chest candidates.  I now planned to add the ID-column of my ITEMSETS-Table as foreign key to all 5 of my ITEMSET - X-rows, but I'm somehow not able to do that.
Am I thinking in the right way? For me, the multiple tables are a possibility to store multiple values in the place of one. So I store the items in a set, which  can be combined to form chest groups, which then are selected for each map. I think of the foreign key's as a way to basically "insert" the objects of another table in there. However, a column seems to be limited to a one time use as a foreign key.
What I want is that all of my 5 row's in  my CHESTSET-table are associated with an entry of the table ITEMSET, and likewise the entry's of the row's ITEM - x in my table ITEMSETS with an entry of the table CHESTITEMS. How can I achieve that, if  I cant make all of them a foreign key linked to the respective ID? Also I'm fairly new to Database design, so if this is a really dumb question, I apologize in advance.


texts:
Firstly apologies if this is a duplicate, I am fairly new to SQL and so Im not sure what the correct terminology to use in my searches

So I have a database which records motor races, with the following simplified schema

race_table
==========
race_id PK
race_date timestamp
average_speed Decimal
max_speed Decimal

drivers
=======
driver_id PK
driver_name text
date_started timestamp

driver_races
============
driver_id FK
race_id FK

If each driver has ~1000 races spread over 2/3 years

How would I go about querying the overall % change (positive of negative) in their average race speed for a given date range, for example

% Change in first 6 months

Joe Smith - 5% increase
Andy James - 4% increase

% Change in first 12 months

Joe Smith - 8% increase
Lewis May - 6% increase

UPDATE: More detail on % Change

The % Change could be calculated using linear regression, (least-squares-fit would be suitable), the average change is effectivly the y-difference on a line-of-best-fit, where each point is a race, x is the race_date and y is the average_speed for that race.

Postgres's regr_slope will give the gradient of the line which is effectivly the same as the %change

SELECT regr_slope(r.average_speed, EXTRACT(EPOCH FROM r.race_date)) as slope
    FROM race_table as r, driver_races as dr
    WHERE dr.race_id = r.race_id
    AND d.driver_id = 1

This gives the figure I want, but I now need to apply is against all users, sorted by 'slope'


texts: Store fields that are subset of other fields on same table

texts:
Just wondering what the optimal design pattern would be for the following data I'm trying to model.
I have a table that stores information for Orders that have been placed by customers.
Orders can be either Invoiced or Opened.
Currently, the table stores data about the total Order information (so...invoiced+open).
I.e. totalAmount, totalCost, etc...
The Invoiced and Open information has to be calculated in code and then Updated per row. Technically speaking, this information can just exist in memory of the app after the calculations. But..I figure...storage is cheap and it'll probably be useful to store this data in the table for later on.
However, I feel like it's kinda awkward and can lead to large rows.
I.e I'd have
totalAmount,
totalCost,
openAmount,
openCost,
invoicedAmount,
invoicedCost
Are there any better ways to do this?
Thanks!


texts:
Below is my script but I keep getting an error.

-- Table: public.codepool

-- DROP TABLE public.codepool;

CREATE TABLE public.codepool
(
    codepoolseq bigint NOT NULL DEFAULT nextval('codepool_codepoolseq_seq'::regclass),
    codenumber character(50) COLLATE pg_catalog."default",
    CONSTRAINT codepool_pkey PRIMARY KEY (codepoolseq)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE public.codepool
    OWNER to postgres;

The error I get is:

  ERROR: relation "codepool_codepoolseq_seq" does not exist SQL state: 42P01


texts: SQL Loader intermittently throws "ORA-12516: TNS:listener could not find available handler with matching protocol stack"

texts:
I am running a weekly batch job which is loading data from txt/csv files to database using SQL Loader. But SQL Loader intermittently throws below error:

  SQL*Loader-704: Internal error: ulconnect: OCIServerAttach [0]
  ORA-12516: TNS:listener could not find available handler with matching protocol stack.

As understood from similar posts, we have increased maximum number of sessions/connections limit which reduced the failure cases to lower side, but not totally resolved the similar issue. Do we also need to increase the maximum number of concurrent processes, as in the below screen shot I can see that oracle concurrent processes are exhausted? If not, please share your thought process if any other solution to the cause.


texts: What's difference between these 2 statements?

texts:
Which is correct? or both? Why?

1st:

INSERT INTO table (num)
VALUES (SELECT COUNT(*) + 1 FROM table);

2nd:

INSERT INTO table (num)
VALUES (SELECT COUNT(*) FROM table + 1);


texts: mySQL - Cannot add Foreign Key Constraint

texts:
I'm trying to add a foreign key constraint but, mySQL is not taking it.  No detailed error, just Cannot add foreign key constraint

Here is my code from an export dump...
thanks for any assistance

-- create tables
CREATE TABLE `calltarget` (
  `idCallTarget` int(11) NOT NULL,
  `idImportCTBatch` int(11) NOT NULL,
  `idBroker` int(11) NOT NULL,
  `idCallMaker` int(11) NOT NULL,
  `FirstName` varchar(40) DEFAULT NULL,
  `LastName` varchar(40) DEFAULT NULL,
  `StreetAddress` varchar(60) DEFAULT NULL,
  `City` varchar(30) DEFAULT NULL,
  `Province` varchar(10) DEFAULT NULL,
  `PostalCode` varchar(7) DEFAULT NULL,
  `MobilePhone` varchar(13) DEFAULT NULL,
  `HomePhone` varchar(13) DEFAULT NULL,
  `Email` varchar(80) DEFAULT NULL,
  `Broker` varchar(80) DEFAULT NULL,
  `Carrier` varchar(60) DEFAULT NULL,
  `Notes` varchar(250) DEFAULT NULL,
  `Status` int(11) DEFAULT NULL,
  `CUser` int(11) DEFAULT NULL,
  `Created` datetime DEFAULT NULL,
  `MUser` int(11) DEFAULT NULL,
  `Modified` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

...

CREATE TABLE `importctbatch` (
  `idImportCTBatch` int(11) NOT NULL,
  `idBroker` int(11) NOT NULL,
  `idCallMaker` int(11) DEFAULT NULL,
  `ImpFileName` varchar(80) DEFAULT NULL,
  `ImpDate` datetime DEFAULT NULL,
  `ImpStep` int(11) DEFAULT '0',
  `Created` datetime DEFAULT NULL,
  `CUser` int(11) DEFAULT NULL,
  `Modified` datetime DEFAULT NULL,
  `MUser` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- add primary keys
ALTER TABLE `calltarget`
  ADD PRIMARY KEY (`idCallTarget`);

ALTER TABLE `importctbatch`
  ADD PRIMARY KEY (`idImportCTBatch`);

-- set autoincrement
ALTER TABLE `importctbatch`
  MODIFY `idImportCTBatch` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=8;

ALTER TABLE `calltarget`
  MODIFY `idCallTarget` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=182;

-- restrict deletion of importctbatch when calltarget records exist
ALTER TABLE importctbatch ADD
CONSTRAINT `fk_importctbatch_calltarget_idImportCTBatch`
     FOREIGN KEY (`idImportCTBatch`)
     REFERENCES `calltarget` (`idImportCTBatch`)
     ON DELETE RESTRICT;


texts:
I am working on Consolidation INDEX.
I have 3 index on a table

Create NONCLUSTERED INDEX IX1 on TableA(a asc,b asc) INCLUDE(U,V,X,Y,Z)
Create NONCLUSTERED INDEX IX2 on TableA(a asc,b asc) INCLUDE(K,L,X,Y,Z)
Create NONCLUSTERED INDEX IX3 on TableA(a asc,b asc,c asc) INCLUDE(U,V,X,Y,Z)

So as the composite key is same and the order is also same.
I thought we can have 1 index rather than  3

 Create NONCLUSTERED INDEX IX4 on TableA(a asc,b asc,c asc) INCLUDE(U,V,X,Y,Z,K,L)

So , i have 2 Question:
1 : Will creating new index for the 3 will work?
2 : Some developers tell me that difference in include statement as now I have (U,V,X,Y,Z,K,L) all in one can have significant difference in performance so ,I can't club them in 1?


texts:
i have this scenario with this query that trasform multiple rows key/values in a columns.

SELECT
  t1.*, t2.*, u.name, u.email, u.username,
  MAX(CASE WHEN t2.datakey = 'latitude' THEN t2.data END) AS latitude,
  MAX(CASE WHEN t2.datakey = 'longitude' THEN t2.data END)AS longitude
FROM `#_social_fields_data` AS t1
INNER JOIN `#_social_fields_data` AS t2 ON t1.uid  = t2.uid
LEFT JOIN `f9leh_users` AS u ON t1.uid = u.id
GROUP BY t1.uid

and i need to add a where clause to call the latitude value
but adding clause for example

where latitude > 0

and after i need to call the colum to replace the new a.lat with latitude column name

 $distance_col_expression = "(((acos(sin((".$post['socialmembers']['location_lat']."*pi()/180)) *
                            sin((a.lat * pi()/180))+cos((".$post['socialmembers']['location_lat']." * pi()/180)) *
                            cos((a.lat * pi()/180)) * cos(((".$post['socialmembers']['location_lng']."- a.lng)
                            *pi()/180))))*180/pi())*60*1.1515)
                            ";
                        $query .= ' AND '.$distance_col_expression .' <= '.$post['socialmembers']['search_radius'];

it not work. Says invalid column name. I have tried with other names but seems that this name generated is temporary.

Please can suggest me, How can i do?
Thanks in advance


texts: What's the differences between sys.dm_exec_query_stats and sys.dm_exec_cached_plans?

texts: As per my understandings, from sys.dm_exec_query_stats, we can cross apply other DMFs to get the statement level cached plans and the query text. From sys.dm_exec_cached_plans, we can get the cached plans and the query text (by cross apply other DMFs). So what's the differences?


texts: Replication Conflict tables in SQL Server 2000

texts:
Due to previous several replications during these years in a database I'm working with, there are several tables with names with this format : *onflict_AllData_[Tablename]
which * can be a, b, ...

none of them has any record on that.

I really don't know what they are, and how I can remove them safely? Does it harmful for current replications running currently?

Note: the DB Version is SQL server 2000.


texts:
I would like to hear your opinion about handling different kinds of pricing. We have a product table which looks almost like this:

+----+------+-----------+-----------+
| ID | Name | price_old | price_new |
+----+------+-----------+-----------+
| 1  | ball |   12.00   |   8.00    |
+----+------+-----------+-----------+

EDIT:

Now we would like to handle different kinds of pricing. Not only decimals (like in price_old and price_new), but also like "against any reasonable offer". There is only one option, static price OR some kind of text. What do you think what's best? Separate table for pricing, or extra columns. I think it's not so smart to change price_old and price_new to varchar instead of decimal, so more kinds of pricing are available in one column... Cause it can cause trouble I guess.

Further we have different scenarios of availability of products on our site. A product can be available for 24 hours, or while stocks last. Guess it would be a good idea to have a separate table for this also? I always think it's not a good idea to have different columns to choose from in one table, like if one column is empty use the other one. Not that flexible... But what do you guys think?


texts: Will adding more indexes speed up using RedGate SQL DATA compare?

texts:
We want to use RedGate's SQL Data Compare to migrate user data from one DB to a DB on a completely different provider. This will be a time-sensitive operation as we do not want our site offline for too long and there will be a lot of data that needs to be moved. We do not want any I/O on the table during this time.

Has anyone using RedGate found that adding additional indexes on some of our comparison criteria columns (i.e. DateCreated, DateModified) will affect the time to perform this? Our initial testing is at about 25-30 min for comparison of the source -> target DBs at the moment.


texts: Incorrect unit allocation size. error comes when Tuning the Database

texts:
Here I am Using the SQL IDERA tool for checking the Index and performance of the Database, then i found below error, can any one tell what is it talking about.

  Incorrect unit allocation size. Drive C: has a queue length average of
  7.26. This drive is also responsible for writing the transaction logs sequentially and performing the read/write of SQL Server pages which
  is typically random

  This results in latency currently measured at 0.0036ms as the head
  moves between sequential transactional log writing and the random
  Reading/Writing of SQL Server pages.  The unit allocation size has
  been detected to be 4KB.  SQL Server works far more efficiently with
  unit allocation sizes of 64KB and larger.

  Recommendation: It is recommended that for busy databases the
  Transaction logs reside on a different physical drive than the data
  files and that a unit allocation size of 64KB or larger be used.


texts: Postgres “CREATE TABLE AS (SELECT …)” stuck

texts:
I am using Python with psycopg2 2.8.6 against Postgres 11.6
When I am running a query
CREATE TABLE tbl AS (SELECT (row_number() over())::integer "id", "col" FROM tbl2)

Code is getting stuck (cursor.execute never returns), killing the transaction with pg_terminate_backend removes the query from the server, but the code is not released. Yet in this case, the target table is created.
Nothing locks the transaction. The internal SELECT query on its own was tested and it works well.
I tried analysing clues on the server and found out the following inside pg_stat_activity:

Transaction state is idle in transaction
wait_event_type is Client
wait_event is ClientRead

The same effect is happening when I am running the query from within SQL editor (pgModeler), but in this case, the query is stuck on "Idle" state and the target table is created.
I am not sure what is wrong and how to proceed from here.
Thanks!
(This is a repost from https://stackoverflow.com/questions/63290472/postgres-create-table-as-select-stuck, since as pointed by https://stackoverflow.com/users/1422451/parfait, it may not be an SQL problem)


texts: Do filtered indexed views cause contention on the underlying table when rows are added to the table that fall outside the filter?

texts:
If I create an indexed view on a table and the query on that table includes a filter, when new records are added to that table that don't fall into that filter (and therefor wouldn't show in the indexed view) is there any extra contention from the indexed view against the table as those records are being added to it?

Example:

CREATE VIEW FilteredIndexedView WITH SCHEMABINDING AS

SELECT TextColumn1, TextColumn2, DateColumn1
FROM dbo.BoringTable
WHERE DateColumn1 < '1/1/2019';

CREATE CLUSTERED INDEX IX_FilteredIndexView_DateColumn1 ON FilteredIndexView (DateColumn1);

INSERT INTO dbo.BoringTable (TextColumn1, TextColumn2, DateColumn1)
SELECT 'SomeText', 'SomeOtherText', '12/11/2019'; -- Some date that falls outside the filter of the indexed view


texts: Table not dropped correctly in SQL Server Express 2008

texts:
I've dropped a table but it is still listed in sys.tables. What's more confusing is that I can create the table again as one user, but if I log in as another user with the same permissions, I can't (I get a message that the table already exists). I need to recreate the table as this second user.

I've managed to log in as the sa user and run DBCC checkdb, but it hasn't done anything to resolve the problem. How can I remove the record in sys.tables permanently (I'm assuming this is the cause of the problem).


texts: Why can't a differential backup specify its base?

texts:
This is my first DBA.SE post, so please inform me of any mistakes, thanks!
I am a new DBA (not an IT pro, just no one else in the company to do it), so the more basic the explanation the better. I have been reading about database backup strategies (or, as I have learned to call them, "restore strategies").  I understand what Full, Differential, and Transaction Log backups do, but I want to know why a differential backup can only be based on the most recent full backup.
If a differential backup is everything that has changed since the last full backup, then why can't the differential be based off of any backup of my choosing?  To be more clear, I'm asking about specifying the base when the backup is taken, not when restoring. I am assuming that when restoring you would choose the correct base and corresponding differential to perform the restore (not using a differential made from base B to restore from base A).
What is the reason that prevents this functionality from being possible?  I figure that there must be a reason, I just don't know what it is.
Note: I understand that the base cannot be specified, but my question is why not?  (I'm also not interested in discussion about "why would you?")
Analogy
Here's an analogy for how I understand a differential backup:
I have an Excel file with some data in cells.
On day 1, I make a copy of this file and store it somewhere else (the "full backup").
On day 2, I look at the file and compare it to the backup copy that I made on day 1, and I note all the cells that have changed and what their new values are (a "differential backup").  I am not noting every change made to a cell, only what its final value is. If cell A1 started as "Alfred", changed to "Betty", "Charlie", then "Dave", I would only note that "A1 is now Dave".
On day 3, I compare the current file with the backup file again and note the changes (another "differential backup" with the same base as day 2).  Again, only noting final values per cell at the time observed, not all values that the cell has been throughout the day.
On day 4, I compare again and note changes again. Continuing with cell A1, now it says "Sarah", even if it was 10 other names throughout the day, and all I note is "Now A1 is Sarah".
On day 5, my file gets messed up; so, I look at the backup copy that I made on day 1, then the final states noted on day 4, and I apply the changes noted to the backup copy and now I have the file "restored" to how it was on day 4.  So, I look at the backup made on day 1, see that on day 4 cell A1 ended as "Sarah", and change the backup cell A1 to be "Sarah".
Why would it matter if I had made another backup copy ("full") of the file on day 2?  Why wouldn't it still be possible to compare (read, "take a differential backup of") the file on day 3 or 4 with the copy made on day 1?  As I understand it, SQL Server would require me to compare (when taking another differential backup) to a full backup made on day 2 (if one had been made)- no other option.


texts: Can I move the undo log outside of ibdata1 in MySQL 5.6 on an existing server?

texts:
I've been growing concerned about the large size of ibdata1 that can never shrink even when using file-per-table on innodb

Moving the undo log files outside seemed logical but this procedure seems rather complicated:

http://dev.mysql.com/doc/refman/5.6/en/tablespace-splitting-undo.html

Does anyone have hands-on experience accomplishing this on a live server?

This line makes it sound like it is impossible once mysql is already installed and running:

this feature can only be enabled when initializing a MySQL instance

So too late now?


texts: I have a MariaDB running on one server that contains a particular table that I want somehow to have access to on another server on the same private network.The second server is also running MariaDB. Can this be achieved with the CONNECT storage engine? If so, can you point out any examples of how to do it? Thank you!


texts: SSDT Schema Compare shows same objects to add and delete in destination

texts: When using schema compare, it shows the objects in dbo schema as objects to add in destination and same objects to delete as well. After inspecting the definitions, they appear to be the same. Why aren't they recognized as such? I would think if there are differences it will show them next to each other and fall under the Changes category. During the Build, it returns an unresolved reference to Schema [dbo] error. Anyone have this issue before?


texts: Select columns dynamically from sql server table

texts:
Lets say I have a table that actually store record which looks like

Create Table dbo.Info
(
 SN int primary key identity(1,1),
 FirstName nvarchar(50) not null,
 LastName nvarchar(50) not null,
 Gender char(1) default 'M',
 Age int check(Age>0)
)

Now for reporting purpose I only need this table column name and save in another table

Create Table Report.InfoColumnOrder
(
 SN int primary key identity(1,1),
 UserId int,
 ColumnName nvarchar(100)
)

thus record looks like

1, 1, FirstName
2, 1, LastName
3, 1, Gender
4, 2, LastName
5, 2, FirstName
6, 2, Age
7, 2, Gender

Now when user with id 1 login and wants report from table Info
select command should look like

Select FirstName, LastName, Gender from dbo.Info

whereas for a user with id 2 select command will be

Select LastName, FirstName, Age, Gender from dbo.Info

Since, user can order the column from client side select statement should be dynamic. Is it possible?


texts: Dynamic MySQL partitioning based on Unix Time

texts:
My DB design includes multiple MYISAM tables with measurements collected online,

Each row record contains auto-incremented id, some data and an integer representing Unix time.

I am designing an aging mechanism, and I am interested to use MySQL partitioning to partition each such table based on Unix time dynamically.

Say that I am interested that each partition will represent single month of data, last partition should represent 2 months, if records arrive for the next not represented month, the partition that represented 2 months should be reorganized to represent single month, and new partition should be created representing 2 month (1 taken from the last partition and 1 for future measurements),

Additionally, when a new partition is created i am interested that the oldest partition will be dropped.

I have found and interesting link to similar approach to what i have described your-magical-range-partitioning-maintenance-query.

What type of partitioning I should use (my Unix time is not a unique key, and how would I use Unix time for partitioning purposes)?
How would I design the partitioning to be fully dynamical based on new records added to the tables?


texts:
Problem

I need to create a chart of User Retention over time, similar to this:

Ignoring the percenages for a minute, I have a query that shows unique users for a given "cohort" and then the number of users that come back. However, with the volume of data we've acquired over the last few weeks, the query no longer finishes.

Query

;WITH dates AS
(
-- Set up the date range
SELECT convert(date,GETDATE()) as dt, 1 as Id
UNION ALL
SELECT DATEADD(dd,-1,dt),dates.Id - 1
FROM dates
WHERE Id >= -84
)
, cohort as (
-- create the cohorts
SELECT dt AS StartDate,
    convert(date,CASE WHEN DATEADD(DD, 6, dt) > convert(date,GETDATE()) THEN convert(date,GETDATE()) ELSE DATEADD(DD, 6, dt) END) as EndDate,
    CONCAT(FORMAT(dt, 'MMM dd'), ' - ', FORMAT(CASE WHEN DATEADD(DD, 6, dt) > GETDATE() THEN GETDATE() ELSE DATEADD(DD, 6, dt) END, 'MMM dd')) as Cohort,
    row_number() over (order by dt) as CohortNo
FROM dates A
WHERE  DATEPART(dw,dt)=1
)
 , cohortevent as (
-- The complete set of cohorts and their events
select c.*, e.*
from cohort c
left join Event e on e.eventtime between c.StartDate and C.EndDate
)
, Retained as(
-- Recursive CTE that works out how long each user has been retained
select c.StartDate,c.EndDate,c.CoHort,c.CohortNo,c.EventId,c.EventTime,c.Count,c.UserID, case when Userid is not null then 1 else 0 end as ret
from cohortevent c
union all
select c.StartDate,c.EndDate,c.CoHort,c.CohortNo,c.EventId,c.EventTime,c.Count,c.UserID, ret+1
from cohortevent c
join Retained on Retained.userid=c.userid and Retained.CohortNo=c.CohortNo-1 and Retained.eventid<c.eventid
)
, WeeksRetained as (
-- Get the highest number of weeks, which is the actual number per user (could probably be combined with previous CTE)
select StartDate, Enddate, Cohort, userID,
    case when max(ret)=1 then '<1W' else '+'+convert(varchar,max(ret)-1)+'W' end as Weeks
from Retained
group by StartDate, Enddate, Cohort,userid
)
-- Finally pivot this by the number of weeks
select *
from
(
select StartDate, EndDate, Cohort, Weeks, count(distinct userID) as UserCount
from WeeksRetained
group by StartDate, EndDate, Cohort, Weeks
) src
pivot
(
sum(UserCount)
for Weeks in ([<1W], [+1W], [+2W], [+3W], [+4W], [+5W], [+6W], [+7W], [+8W], [+9W], [+10W], [+11W], [+12W])
) piv
OPTION (MAXRECURSION 0);

Environment

All of the tables are CTE's except for "Event" which has two main columns we care about, UserId and EventTime.

What I've tried

I've added indexes on both UserId and EventTime. I noticed the DTUs (this is an Azure SQL instance) were maxing out originally, but I've vertically scaled the database instance so the DB runs at 70% DTU usage and it's still not completing in 30+ minutes. There are currently only 40k rows in Event.

Execution Plan Link

https://www.brentozar.com/pastetheplan/?id=HkB6xClKH


texts:
I currently have a query where I'm doing two subqueries to get X, Y data:
SELECT
  t.series AS week,
  ( ... ) X,
  ( ..., AND ... ) Y,
  ROUND(( ... ) * 100) / ( ..., AND ... ), 2) Z
FROM series_tmp t

Y is kind of subset of X, since I apply just an additional condition to the existing ones, if X is:
SELECT COUNT(*)
FROM t1
INNER JOIN t2
ON t2.id = t1.another_id
WHERE t2.something = 1
AND t1.date BETWEEN t.series AND t.series + INTERVAL 6 DAY

Then Y has an additional AND condition:
SELECT COUNT(*)
FROM t1
INNER JOIN t2
ON t2.id = t1.another_id
WHERE t2.something = 1
AND t1.date BETWEEN t.series AND t.series + INTERVAL 6 DAY
AND t1.some_state = 'x state'

And for the value of X I need to take those two results - X and Y and do some calculation. Since I can't use the aliases, I have to use a subquery, right? But in that case it seems too much.
Is there a way to reuse those subqueries? It seems to be too much of the same.
I'm using MySQL 5.6 so I'm not able to use CTEs :(
PS: series_tmp comes from this wonderful idea (thanks to them).


texts: SQL Server displayed 'Grantor' vs system tables result

texts:
When I select server properties -> permissions, SQL server appears to display the 'grantor' as the (sa-level) login of the person who created the login. But when I select from the system tables (see below), the 'grantor' is "correctly" displayed as 'sa'. If this is correct, where does properties -> permissions get its 'grantor' data from? Or is my join incorrect ?

My system tables join:

select prin.name [Grantor], prin2.name [Grantee], perm.*
from sys.server_permissions perm
    left join sys.server_principals prin
        on perm.grantor_principal_id =prin.principal_id
    left join sys.server_principals prin2
        on perm.grantee_principal_id =prin2.principal_id

The actual question here is: where is the 'proximate' grantor (ie, the logged-in person creating a new login) retrieved from (stored) in Server Properties -> Permissions ? Obviously not in the tables I've used in the join.


texts: How can I mimic functionality of SQL Server `TYPE` in Oracle?

texts:
I apologize if question title is vague, but I was not able to formulate it better. What I'd like to have is an alternative to TSQL CREATE TYPE, so I can use it in column definition.

For instance, I need "money" type in Oracle which is technically NUMBER(18,4) in my system, and each table will have "money" column instead of underlying NUMBER for consistency purpose. There are some other "types" I'd like to have aliases rather than using them directly.

Unfortunately, I didn't find acceptable solution yet except giving up and using standard types with constraints(or size specification) for each column. The only approach that seems to work is to create OBJECT type, but it will add unnecessary complexity to the system which I'm trying to avoid.

A very nice Oracle feature, SUBTYPE, doesn't work in this case . As far as I understand, SUBTYPE (and subtypes from STANDARD) creates PL/SQL datatype which cannot be used in SQL. I understand there are various reasons for that.

I wonder what is the best practice for such a problem.

Thank you.

Update
It seems so far that I've listed all possible approaches, and it I'd rather close the question. Not sure if it may be useful for anyone; in case it is, I don't remove the question for the time being.


texts: Best type to use in a composite key when one of the values varies in type

texts:
I am building a data store for the content of multiple blog sites which have been scraped.  Each of these sites is going to have an entry in a Blog table

BlogId                  Url                         Version
int, PK, identity       varchar(2500), not null     int, not null
-----------------       -----------------------     -------------
                1       'http://site1.com/                      1
                2       'http://site2.com/                      1
                3       'http://site2.com/                      2

Then there will be a Post table:

BlogId                Url                        PostId
int, FK, not null     varchar(2500) not null     ?

For most blogs that I'm looking at, I can easily discern a PostId value that is unique for them.

For all blogs, the Url will be unique for a BlogId value.  However, the Url is frequently subject to change.

However, there are a few blogs that I can't discern a good PostId value when scraping the HTML.  In those cases, I can get the datetimeoffset that a post was published on.  The accuracy of the publishing frequency is on the minute level.

The question is, I need to build a composite natural key (I won't use it as a primary key because I'll need to relate to other tables and don't want to carry this along to those tables) using the BlogId and the PostId but I'm not sure what I should type the PostId as.  I've been thinking of two options:

sql_variant - I could use an int for most of my sites, and that would be fine and then use datetimeoffset for the ones where I can't easily discern a unique numeric PostId
bigint - The PostId would fit in here and I can encode the datetimeoffset as a bigint (2011-11-11 13:59 would become 201111111359)
Or some other approach I haven't thought about.

Some other key facts:

The Post table will only have a few thousand records in it initially (if there are more than five thousand, I'd be surprized)
There are related tables for tags, categories, etc, which will be joined with this table (maybe a few thousand records at most)
The growth of any of these tables won't be astronomical.  Maybe a another five thousand records a year (that's being conservative on my part but you never know)
This key will only be used to create an index/constraint, currently, I don't foresee the use of it in queries (there are other attributes that I am linking to which are of more concern).


texts: Should I add index when indexes are greater than RAM in mysql

texts:
Data_length + index_length are greater than innodb_buffer_size, and I have a huge table where the select took longer than 5 minutes. Should I resolve this issue by adding another index on a column, or it will just getting worse?

Here is the table schema:

| record | CREATE TABLE `record` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `altitude` smallint(6) DEFAULT NULL,
  `counter` double DEFAULT NULL,
  `datetime` datetime NOT NULL,
  `details` longtext,
  `deviation` smallint(6) DEFAULT NULL,
  `engine` smallint(6) DEFAULT NULL,
  `event` smallint(6) DEFAULT NULL,
  `fuel_level` double DEFAULT NULL,
  `gsm_level` int(11) DEFAULT NULL,
  `latitude` double NOT NULL,
  `longitude` double NOT NULL,
  `satellite` smallint(6) DEFAULT NULL,
  `speed` smallint(6) DEFAULT NULL,
  `time` time NOT NULL,
  `treated` tinyint(1) NOT NULL DEFAULT '0',
  `id_driver` bigint(20) DEFAULT NULL,
  `id_vehicle_device` bigint(20) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `FK_ai9y9d52lx1xvtaiv3w0x60a5` (`id_driver`),
  KEY `FK_ilwlg0mt87bxyemx4tov1elni` (`id_vehicle_device`),
  KEY `treated_record` (`treated`),
  CONSTRAINT `FK_ai9y9d52lx1xvtaiv3w0x60a5` FOREIGN KEY (`id_driver`) REFERENCES `driver` (`id`),
  CONSTRAINT `FK_ilwlg0mt87bxyemx4tov1elni` FOREIGN KEY (`id_vehicle_device`) REFERENCES `vehicle_device` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=129117058 DEFAULT CHARSET=latin1 |

here is the select :

select count(*) from record where datetime between '2017-07-01' and '2017-07-30' and id_vehicle_device = 2;

and here is the explain select :

| id | select_type | table  | partitions | type | possible_keys                | key                          | key_len | ref   | rows   | filtered | Extra

|  1 | SIMPLE      | record | NULL       | ref  | FK_ilwlg0mt87bxyemx4tov1elni | FK_ilwlg0mt87bxyemx4tov1elni | 8       | const | 264902 |    11.11 | Using where

This query SELECT FLOOR(SUM(data_length+index_length)/POWER(1024,2)) InnoDBSizeMB FROM information_schema.tables WHERE engine='InnoDB'; give me :

+--------------+
| InnoDBSizeMB |
+--------------+
|       375462 |
+--------------+

while innodb_log_file_size = 32G.


texts: How does DBMS ensure durability without flushing

texts:
I was watching this video, and at a certain point (don't recall the exact minute) the speaker says that the DBMS doesn't immediately flush the file when updates happen. He explains that IO operations are too slow to be performed every time you make a change.
So the question is Practically, how do they can ensure durability?


texts:
I need to realize a software to manage some projects. Every project starts after a offer approvation.
A offer could have many revision, but only the last one offer is the parent of the project.

Example:

price_quotation_a => project

OR

price_quotation_a
price_quotation_b
price_quotation_c => project

My idea is to realize only one table, with a flag (type) to distinguish order from project. A second column (parent) to specify for every order wich offer is the parent.
The problem is that offer and project have the same data.

+----+------+--------+------+----------+--------------+--------------+
| id | type | parent | name | revision | project_name | project_desc |
+----+------+--------+------+----------+--------------+--------------+
|  1 |    0 |      0 | O001 | A        | Project01    | Project_desc |
|  2 |    0 |      0 | O001 | B        | Project01    | Project_desc |
|  3 |    1 |      2 | P001 | NULL     | Project01    | Project_desc |
|  4 |    0 |      0 | O002 | A        | Project02    | Project_desc |
|  5 |    1 |      4 | P002 | NULL     | Project02    | Project_desc |
+----+------+--------+------+----------+--------------+--------------+

Operatos must insert worked hours both for order or offer so i need a list with both entity. Something like:

+----+-------------+----------+----------------+-------+
| id |    date     | operator | order/offer ID | hours |
+----+-------------+----------+----------------+-------+
|  1 | 01-010-2002 |       12 |              1 |     5 |
|  2 | 01-010-2002 |       12 |              2 |     6 |
|  3 | 01-010-2002 |        5 |              1 |     4 |
+----+-------------+----------+----------------+-------+

Shout if I haven't bee clear. It's the right way?


texts:
If we use (+) notation we can have outer join. For example:

select *
from tab_a, tab_b
where tab_a.num = tab_b.num(+)

Is outer join.
If we use function around columns, for example:

select *
from tab_a, tab_b
where round(tab_a.num, 2) = round(tab_b.num(+), 2)

Is it still outer join? Or is it becomes inner join?


texts: Why MySQL pick a different index for the same query structure but different values?

texts:
Right now i have some issues understanding how MySQL picks the index to use on a query. I am executing a "COUNT QUERY", but everytime i use a different foreign key for the query, MySQL picks a different index to use.
Let me elaborate and explain my situation. On our DB table we have around 40 millions rows. I am using two "Count querys" with range filters to get the number of rows.  The two clients i use to test this behavior have this values:

Cliente_id 1 has around 8 millions rows on reports table.
Cliente_id 2 has around 1.5 millions rows on reports table.

Now i execute the following querys:
Count query of client with id 1 (finish in 5.6 seg)
Select count(*) From reports where cliente_id = 1 and (created_at  >= '2020-01-01T00:00:00' and created_at <= '2020-01-31T00:00:00')

Count query of client with id 2 (finish in 58 seg)
Select count(*) From reports where cliente_id = 2 and (created_at  >= '2020-01-01T00:00:00' and created_at <= '2020-01-31T00:00:00')

As you can see the second query of the client with less records, takes too long to execute. This seems odd, so when i try to use "EXPLAIN" to see which index is been use i got this:
EXPLAIN OF query from cliente_id 1 (Use created_at index.)
1   SIMPLE  reports range   index_reports_clienteid,index_reports_created_at    index_reports_created_at    63      5534206 Using index condition; Using where

EXPLAIN OF query from cliente_id 2 (Use cliente_id index.)
1   SIMPLE  reports range   index_reports_clienteid,index_reports_created_at    index_reports_clienteid 63      5534206 Using index condition; Using where

The second Count query is using a different INDEX. I been reading a lot of Mysql doc, but with not luck.  After a while i found a way to force the use of a specific index on a Query. I try this:
Selec Count with Use Index
SELECT COUNT(*) FROM reports USE INDEX(index_reports_created_at)  WHERE (company_id = '2' AND (created_at >= '2020-01-01T00:00:00' AND created_at <= '2020-01-31T0:0:00' ))

When i force the use of index on the query it only took around 6 seconds to finish. In theory the most optimal index to use is created_at, but on this situation MySQL picks  "cliente_id index".
What is the criteria here to pick cliente_id over created_at? I just don't understand this behavior.  I will really appreciated if someone can help me with this situation.
MYSQL VERSION is 5.7.32 and i am using innoDB engine
NOTE. I can hardcode the use index on my querys but i dont think is the right think to do. So i will really like to avoid use "USE INDEX" on my querys.
Note2. Sorry for my poor english. I try my best to explain.
Thanks for the help.


texts: Going from long/lat cords to Geometry or Geography type

texts:
When I make a query:

select *,
       concat(pickup_longitude,' ',pickup_latitude) as pickup_pt,
       concat(dropoff_longitude,' ',dropoff_latitude) as dropoff_pt

it usually defines the pickup_pt and the dropoff_pt as datatype text from default apparently.

Is there a way to define the column in another datatype?

Perhaps something like:

concat(pickup_longitude,' ',pickup_latitude) as pickup_pt of type geometry


texts: Getting the count and sum of distinct record from Postgres jsonb attribute

texts:
I have this Postgres 9.6 table def:

CREATE TABLE research (
   id int,
   data jsonb
);

and have the following sample data in it

 id |data
 ---|--------------------------------------------------------
 1  |{"name": "Sim Ltd", "sector": "business", "personnel": {"headcount": {"total": 100, "male": 50, "female": 50}}}
 2  |{"name": "EcoSmart", "sector": "business", "personnel": {"headcount": {"total": 500, "male": 460, "female": 40}}}
 3  |{"name": "HIDN", "sector": "government", "personnel": {"headcount": {"total": 431, "male": 121, "female": 310}}}
 4  |{"name": "RevDev", "sector": "government", "personnel": {"headcount": {"total": 15, "male": 10, "female": 5}}}
 5  |{"name": "NEFPAN", "sector": "non-profit", "personnel": {"headcount": {"total": 5, "male": 4, "female": 1}}}

I would like to get the COUNT of DISTINCT sectors and the SUM of total, male and female by DISTINCT sectors.I want the out like this:

Sector      |Count  |Total  |Male   |Female
------------|-------|-------|-------|--------
business    |2      |600    |510    |90
government  |2      |446    |131    |315
non-profit  |1      |5      |4      |1

Would greatly appreciate if someone can guide me with writing the SQL for it. I am use Postgres 9.6. Thanks


texts: Why is Clustered Index Scan Number of Executions so high?

texts:
I have two similar queries that generate the same query plan, except that one query plan executes a Clustered Index Scan 1316 times, while the other executes it 1 time.

The only difference between the two queries is different date criteria. The long running query actually narrower date criteria, and pulls back less data.

I have identified some indexes that will help with both queries, but I just want to understand why the Clustered Index Scan operator is executing 1316 times on a query that is virtually the same as the one that where it executes 1 time.

I checked the statistics on the PK that is being scanned, and they are relatively up to date.

Original query:

select distinct FIR_Incident.IncidentID
from FIR_Incident
left join (
    select incident_id as exported_incident_id
    from postnfirssummary
) exported_incidents on exported_incidents.exported_incident_id = fir_incident.incidentid
where FI_IncidentDate between '2011-06-01 00:00:00.000' and '2011-07-01 00:00:00.000'
    and exported_incidents.exported_incident_id is not null

Generates this plan:

After narrowing the date range criteria:

select distinct FIR_Incident.IncidentID
from FIR_Incident
left join (
    select incident_id as exported_incident_id
    from postnfirssummary
) exported_incidents on exported_incidents.exported_incident_id = fir_incident.incidentid
where FI_IncidentDate between '2011-07-01 00:00:00.000' and '2011-07-02 00:00:00.000'
    and exported_incidents.exported_incident_id is not null

Generates this plan:


texts: Workload group "Importance" vs. Resource pool IOPS throttling

texts:
Our SQL 2014 server has a blazing-fast tempdb drive (2800 IOPS) but a much slower data drive (500 IOPS). Our application runs a few long-running reporting queries that are I/O-intensive and we'd like to avoid them starving our server for I/O capacity when they run.

Ideally we'd be able to limit these queries to 50% of available I/O capacity. Unfortunately SQL Server Resource Pool's IOPS throttling is not percentage-baed nor volume-specific.  If I limit to 250 IOPS, then it will unnecessarily slow down performance of queries that make heavy demands on tempdb. Slowing down these long-running queries if the server is busy is OK, but slowing them down by 10x+ if they need lots of tempdb access is not OK.

So we're looking for workarounds that will defend other queries from these lower-priority, long-running queries, but without unnecessarily hurting performance of these long-running queries if they happen to use lots of tempdb.  It's not practical to change the queries themselves to reduce tempDB usage-- these queries are generated by a custom reporting feature that may sometimes generate really complex query plans that spill results to tempdb.

So far the best idea I have is to remove IOPS throttling and instead use the "Importance" of a workload group to defend the rest of the server's I/O capacity from these queries. Is this a good solution to the problem I'm trying to solve?  What are the pros and cons of using Importance?

Or is there a better way to achieve our goals?


texts:
I've got two tables that I need a count on both of them.

table1: id
table2: cid (table1.id = table2.cid) -- Join can be done by this.

I already got two queries that I'm using, and wanna put them in one single query.

Query related to table1:

WITH c_status AS (
select
CASE
  WHEN regdate = 1 THEN 'yes'
  WHEN regdate = 2 THEN 'no'
  from table1
  end as status_1
)

select status_1, count(*) AS c_status_count from c_status group by status_1

OUTPUT:

yes 548
no  2269

Query related to table2:

WITH u_status AS (
select
CASE
  WHEN regdate = 1 THEN 'yes'
  WHEN regdate = 2 THEN 'no'
  from table2
  end as status_2
)

select status_2, count(*) AS u_status_count from u_status group by status_2

OUTPUT:

yes 564256
no  31452345234

Question:

How can I put those two queries together? I want one single query reporting both counts.

UPDATE: Desired output:

u_status yes    548
u_status no     2269
c_status yes    564256
c_status no 31452345234


texts: giving users select-only permissions of database objects

texts:
I'm running SQL Server 2017 with a production database called Biz. I'd like to

Give three users the ability to run SQL queries against this database without altering the data, or maybe preferably,
Create a separate database (or schema) called BizView with some commonly-used queries, tools, etc. Then I could give BizView the appropriate read-only permissions on Biz. Then my users could just log into BizView and run their queries from there. . . right?

All of my users are on a domain. What combination of logins/roles/permissions would allow this basic read-only reporting on my production database? It's so simple, but I feel like I'm mis-applying Oracle concepts (I'm newly responsible for this single MS-SQL database) to this situation.


texts:
Say I have a hand-written script which performs a bunch of schema changes, eg: (plucked from air):

Adds 2 columns to an existing table
Creates a new stored procedure
Alters an existing stored procedure

And, after running this, we discover that we need to revert back to before this entire script was run.

Is this possible?

In the meantime, data will have been flowing in.  We can't throw away the new data (ie: we can't restore a backup of the whole database), but we don't care about any data from the 2 new columns.

I'm looking for a better solution than writing up an explicit "undo" script (with, for example, DROP COLUMN statements and old procedure definitions) every time we make schema changes.


texts:
I am finding what seems to be an inconsistency to the way a Write-Output line (which prints out a variable) should be formatted. I can't find any information on this, which makes me guess that I am misunderstanding what I am seeing.

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Statement:

For most simple variables, the below will work:

Write-Output " This $testValue will print just fine."

However, I need to add a $() to the Write-Output line if the variable is part of an object:

Write-Output $("Removing old software from directory: " + $site.INSTALLDIR)

=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

Question: Is the above statement correct?


texts: How to insert into 2 tables which are dependant on each other

texts:
I have this 2 tables (MySQL) that are dependent on each other and I don't know what to do when creating a new company as it needs a document type...and a document type needs the id of the company it belongs. I was thinking about asking (in a form) for the document type's info (it has only 2 fields) and then inserting it, but it will trigger a foreign key constraint fail as neither keys exists, right?

CREATE TABLE Company
(
    id long not null,
    document_type_id long not null
)

CREATE TABLE Document_Type
(
    id long not null,
    company_id long not null
)

Which approach should I take?


texts: How do I generate a date series in PostgreSQL?

texts:
If you're looking to generate a time series, see this question

Let's say that I want to generate a series of dates between two dates.
 I see the function generate_series provides only

Function                                    Argument Type                         Return Type                                                               Description
generate_series(start, stop, step interval) timestamp or timestamp with time zone setof timestamp or setof timestamp with time zone (same as argument type) Generate a series of values, from start to stop with a step size of step

So how would I go about doing this?


texts: How to UPDATE/DELETE an Oracle Foreign Table with Foreign Data Wrapper from PostgreSQL?

texts:
I need to transfer lots of row from my PostgreSQL database to a remote Oracle DB through the Oracle Foreign Data Wrapper (FDW). The required operation is so far INSERT to a Foreign Table (FT), but now am thinking to do DELETE/UPDATE as well. UPDATE is needed to flag the inserted rows based on some condition.

Originally I had a problem that INSERT to an Oracle FT got this error:

ORA-08177: can't serialize access for this transaction

And trying with different kind of Oracle Table showed the following pattern:

So then I choose the plain TABLE_4 (without any PK/index). But it turned out that DELETE/UPDATE does not like it, as stated in the error message:

ERROR:  no primary key column specified for foreign Oracle table
DETAIL:  For UPDATE or DELETE, at least one foreign table column must be marked as primary key column.
HINT:  Set the option "key" on the columns that belong to the primary key.

However when i use the TABLE_6 (with a PK), a DELETE/UPDATE command keeps on getting the same error that suggests to have a PK.

So how to UPDATE/DELETE an Oracle Foreign Table though Oracle FDW ?

The Oracle FDW docs here tells the following without any details/example that i need:

  If you want to UPDATE or DELETE, make sure that the key option is
  set on all columns that belong to the table's primary key.  Failure to
  do so will result in errors.

and

  For UPDATE and DELETE to work, the columns corresponding to the
  primary key columns of the Oracle table must have the key column
  option set. These columns are used to identify a foreign table row, so
  make sure that the option is set on all columns that belong to the
  primary key.

FYI, my playground:

PostgreSQL 9.6.11 64-bit with PostGIS 2.5.1
PostgreSQL 9.6.15 64-bit with PostGIS 2.5.3
each Postgres is on top of Windows Server 2008 R2 Datacenter, using Oracle FDW version 1.1, connected to different Oracle DB both with version 11.2.0.4.0 64-bit.
though PostGIS is vital in my PostgreSQL database, the data to be transfered to Oracle DB has no geometry at all


texts: Ruby on Rails cannot access production RDS on AWS

texts:
I have done rake db:create sucessfully. But rails db:migrate has access errors on the AWS RDS:
Mysql2::Error: Can't create table `llpdn9i88kz3y837`.`comments` (errno: 150 "Foreign key constraint is incorrectly formed")

and
SHOW ENGINE INNODB STATUS;

Error: Access denied; you need (at least one of) the PROCESS privilege(s) for this operation
SQLState:  42000
ErrorCode: 1227

and
GRANT PROCESS ON llpdn9i88kz3y837.* TO 'n8p1kzg1v9chfcny'@'localhost';

Error: Access denied for user 'n8p1kzg1v9chfcny'@'%' (using password: YES)
SQLState:  28000
ErrorCode: 1045


texts:
We have a database that is just over 200 gb in size, with around 430 million rows, a primary key and a spatial index. Every few weeks we get an update which involves around 3 million rows, deleting some old features and updating new ones. This is done offline.
We also have a history table, which stores the accumulated deletions from each update. This table is currently around 20 gb and 40 has million rows.
Essentially, we first insert into the history table anyting that is marked as to delete in the change table, then delete everything from the main table, that is either to delete or new, and then insert into the new table all the changed records (all offline, to be clear).

If I run this in a single procedure/function a table rewrite seems to be triggered, as I have come back after a few hours and found it failed due to using up the available disk space, around 100 gb. If I run it line by line in a psql console, no such problem. As there are no DDL statements and the total number of changed rows is vastly smaller than the total database size, I am puzzled as to what might be causing this amount of temporary disk usage, as the total space required to record the entire change as one transaction would seem to be much less.

Is there some rule of thumb for this or a simple diagnostic query that I can run to see what is happening?


texts: Detect multiple valid date periods for same id, interleaved with non-valid periods

texts:
My table structure and some sample data (test table), as well as my desired output (results) are available here.
When I do
SELECT distinct colony, min(date_check), max(date_check)
from test
where protection ='Y'
group by colony

I only detect 1 event by colony.
Should I use a function or is there another way ?
ps : if you think of a better title to help users, don't hestitate, I struggled.


texts:
I cannot find a detailed document on this anywhere, however, that being said, if anyone has an answer to my question, I would be extremely grateful.

In regards to Cassandra's standard node architecture, you are required as the administrator to divide the hash range by the number of nodes in the cluster, thus you have your tokens.

That being said, vnode tokens are generated automagically, and architecturally, I am curious as to how the system maintains these tokens.

To further specify my question for virtual nodes does the cluster re-evaluate tokens for all nodes just because a new host has been added?

To further this thought process, I am inclined to believe that it doesn't, as that would be a very expensive operation moving around any data that would then be in the wrong node due to the recalculation.  Unfortunately, I cannot find out what happens...

Also, if two nodes can hold the same token, doesn't that break the architecture, or at very least doesn't it make designating replication factor almost pointless, as it should be written to all nodes that have the specified vtoken/token?

How is a vnode chosen for a particular token are the tokens assigned serially or randomly?

Lots of difficult questions, however I am counting on any Cassandra hanging around.

Thank you,

Scott


texts:
I manage a MongoDB replica set for one of my customer and sometimes he send me a ticket (email) with only one argument :

~ WARNING Date/Time: ~Tue Jul 4 02:39:33 GMT 2017
Summary: ~WARNING - Queries / Sec: 9.674419

How understand this kind of message? I could guess that we have some slow query running. So the first thing I do it's to check the log. What i found in it's that we have some aggregate command that take 500-600ms to execute. Here an example :

2017-07-04T04:08:52.205Z I COMMAND  [conn2104435]
command databaseName.cards_PWCD900004
command: aggregate { aggregate: "cards_PWCD900004",
pipeline: [ { $unwind: "$virtualCards" },
{ $project: { cardProfileId: 1, virtualCards.state: 1,
virtualCards.device: 1,
virtualCards.primaryDpanId: { $ifNull: [ "$virtualCards.primaryDpanId", null ] } } },
{ $group: { _id: { cardProductId: "$cardProfileId",
virtualCardsState: "$virtualCards.state",
deviceId: "$virtualCards.device" }, tokensNumber: { $sum: 1 },
virtualCardsNumber: { $sum: { $cond: { if: { $eq: [ "$virtualCards.primaryDpanId", null ] },
then: 1, else: 0 } } } } }, { $group: { _id: { issuerName: "APLUS",
cardProductId: "$_id.cardProductId",
virtualCardsState: "$_id.virtualCardsState" },
devicesNumber: { $sum: 1 }, virtualCardsNumber: { $sum: "$virtualCardsNumber" },
tokensNumber: { $sum: "$tokensNumber" } } } ] }
keyUpdates:0 writeConflicts:0 numYields:219 reslen:30763
locks:{ Global: { acquireCount: { r: 460 } },
Database: { acquireCount: { r: 230 } },
Collection: { acquireCount: { r: 230 } } } protocol:op_query 523ms

What I would like to know is how troubleshoot such thing? I could launch a mongotop or mongostat but if the issue just finished I will see nothing. I also could use db.serverStatus() but I don't really know which variables is the most significant.

database size : 2GB
SSL : true

About mongo.conf all it's by default. Here more information about hardware:

RAM : 8GB
2 proc : 2,3 GHz each

What are best practices an administrator should have to check if all works on his database ?

If you have any question don't hesitate please.


texts: Apply same SQL SELECT query to different columns

texts:
If this question turns out to be very basic, I will delete it. I have an SQLite table with the following schema:
sqlite> .schema complexity
CREATE TABLE complexity (
  plan_id TEXT NOT NULL,
  arc INTEGER NOT NULL,
  coa REAL,
  em REAL,
  efs REAL,
  ltmean REAL,
  mcs REAL,
  mcsv REAL,
  mfa REAL,
  pi REAL,
  sas REAL,
  FOREIGN KEY (plan_id) REFERENCES plan (plan_id)
);
CREATE INDEX complexityindex ON complexity(plan_id);

And this is my table's contents:
plan_id arc coa     em      efs     ltmean  mcs     mcsv    mfa         pi      sas
112-19  1   0.317   0.26    57.631  0.561   0.212   0.212   6781.489    52.764  0.139
112-19  2   0.361   0.277   52.712  0.576   0.16    0.16    4991.226    48.033  0.167
443-18  1   0.334   0.25    76.283  0.407   0.175   0.175   7656.262    61.616  0.124
443-18  2   0.315   0.243   79.174  0.416   0.195   0.194   8130.702    61.332  0.098
231-18  1   0.326   0.264   53.622  0.602   0.216   0.215   6015.724    48.917  0.137
231-18  2   0.334   0.265   52.175  0.609   0.196   0.195   5288.368    45.981  0.144
236-20  1   0.368   0.271   27.209  0.775   0.229   0.228   2263.918    23.056  0.212

Given a plan with id plan_id, I'd like to calculate the percentile of that plan for the columns coa, em, efs, ..., sas. I've come up with the following code:
SELECT * FROM (
    SELECT plan_id, coa, PERCENT_RANK() OVER (
        ORDER BY coa)
        FROM complexity
    )
    WHERE plan_id = "112-19";

And I get:
plan_id coa     PERCENT_RANK() OVER ( ORDER BY coa)
112-19  0.317   0.166666666666667
112-19  0.361   0.833333333333333

How could I calculate the percent rank order by the rest of the columns, without copy/pasting the above SQL query and replacing 'coa' with 'efs', then 'em', etc.?


texts: Calculate percentage of false records to total records within a aggregate function

texts:
I'm looking for a solution to calculate a percentage rate of a boolean value:

Model:
Products 1 <-> n Components
Components.essential is a boolean attribute

What I want to calculate is something like:
SELECT products.*, count(comp_a.essential)/count(comp_b.essential) AS essential_percentage
From products
INNER JOIN components AS comp_a ON products.id = comp_a.product_id
INNER JOIN components AS comp_b ON products.id = comp_b.product_id
WHERE comp_a.essential = 1
GROUP BY products.id

Unfortunately this statement calculates way too many components as there are two INNER JOIN on the components model...
Any help on this?
Markus


texts:
I need some help in finding why the following T-SQL statement returns 1 (true):

SELECT IIF( 0 = '', 1, 0)

I guess someone has change an ANSI option like SET ANSI_NULLS or something else that is causing the behavior.

My issue is that I am joining some values and in the final row set I have values which are joined by 0 and '' values, which is not correct.


texts: PostgreSQL: how can queries possibly be "immutable" / functional?

texts:
I've read that PostgreSQL can have "functional" indexes.  For that you need the immutable keyword.

Seen that PostgreSQL is a CRUD DB I don't understand at all how it can possibly work in a functional way.

If there's an update or a delete to the DB, how can a past query guarantee that the result of the same query is always going to be the same?


texts: SQL performances of selecting recently modified records Solr

texts:
I have a SQL Server 2916 database, and Solr.
The Solr indexer runs often such queries:

select book_collection from books group by book_collection having max(updated_on) > '2017-04-04 09:50:05'

The column updated_on is updated by a trigger on insert/update; at every run of the Solr incremental indexer (every 10 minutes), many queries like the above get the latest modified records and reindexes them.
This table, for instance, has about one million rows and at every time the query would return 10-20 rows maximum.
These queries end up in the list of the most expensive queries run on the database, so I would like to optimize them.
My questions:

1) Would a timestamp column perform better than a datetime column?

2) If I changed the query like this, would it be more efficient?

select distinct book_collection from books where updated_on > '2017-04-04 09:50:05'

The first query plan is the original one, the second is modified by me. The fact that the second plan asks for an index, and the first one doesn't, really suggests that the modified query can use an index, if present, and is therefore better.


texts: How do I add an existing shared data source and dataset to my SSDT/Visual Studio SSRS Project?

texts:
I am trying to add a shared data source and data set to an SSRS project in SSDT/Visual Studio, also called Report Designer.  There are plenty of ways to add this into a Report Builder. I am looking on how to do it in Report Designer.

They are published to http://local//report/dataset.

Thank you in advance for your help.


texts: Run a count query for every row ID in results

texts:
I'm trying to count the number of cars at each parking lot in my app, along with some other info. But, I'm not too sure how to approach this.

Tables

I have 3 tables.

lot - this table contains information for a parking lot
manager - this table contains information for a parking lot's manager
car - this table has a list of cars that can be at a given parking lot

lot
 id | name | manager_id
----+------+------------
  1 | foo  | 1
  2 | bar  | 1

manager
 id | name
----+--------
  1 | alice
  2 | bob

car
 id | lot_id
----+--------
  1 | 1
  2 | 1
  3 | 2
  4 | 2

Desired output

Given a manager_id, I want a list of:

the name of the lot they manage
the name of the manager
the number of cars in that lot

This is what I want to end up with.

 id | lot_name | manager_id | manager_name | num_cars
----+----------+------------+--------------+----------
  1 | foo      | 1          | alice        | 2
  2 | bar      | 1          | alice        | 2

I've figured out how to get the manager name in the results, but I'm not sure how to get the number of cars for each lot.

This is what I have so far.

-- manager_id 1 is alice.

select
    id, name as lot_name, manager_id,
    (select name from manager where id = 1) as manager_name
from lot where manager_id = 1;

I'm thinking I need a join somewhere to bring in the car table and somehow do a count. Not exactly sure how though.


texts: Access denied for user 'myuser'@10.% to database 'mydb' - Mysql Import SQL Script

texts:
I have a database that I am trying to recreate at my web host. I used mysqldump to create a sql script. My web hosting company, IPage, has a hosted mysqladmin site where I am supposed to be able to import my script. When I run the script I get this error:

#1044 - Access denied for user 'myuser'@'10.%' to database 'mydatabase'

This user has full access to the database. Do I need to make some edits to my SQL script to get this working? Or is this a problem at my hosting site?

Thanks!


texts: Problem with new MySQL Workbench connection

texts:
I'm working on Debian 8 and wanted to create database using MySQL Workbench. I did everything in this tutorial, but  when I created a new MySQL Connection, the program returned:

  Failed to Connect to MySQL at 127.0.0.1:3306 with user root, Access denied for user 'root'@'localhost' (using password: YES).

What am I doing wrong? I typed the correct password.


texts: Extremely long import times from Excel File to SQL Server Management Studio using Import Wizard

texts:
I have been importing an excel file with approximately 250,000 rows of data and 40 columns to SSMS on a weekly basis through the Import Wizard. Unfortunately, the import times take around 45 minutes, which is a bit longer than I would like.

The main issue I am facing is that I am not able to access SSIS or use BulkInsert queries due to a client restriction. I am left with trying to import using the Import Wizard exclusively.

Are there any tricks I could try to maybe make the import run faster? The server I am on itself doesn't have much RAM so could that also be a limiting factor?

I have tried saving as a CSV and importing, but get a flow task error informing me that the import data did not match the target destination type. This is because I have a number of columns specified as datetime or int. Is there a way around this without having to edit each column in the advanced area for selecting my flat file source?


texts:
I understand that covering index will speed up read operation if all the columns in select list are covered in the index.

But what if I select and filter on columns in other tables as well? Would covering index still help in this case?

Here is my thought: Since the join would force the clustered key to be matched, so the clustered key look up couldn't be avoided. And by finding the clustered key we would have all the information we need thus covering index wouldn't help to speed up the read.


texts: Migrating 300 GB mysql database from one server to another cleanly with minimum downtime

texts:
I have a 300 GB mysql database which i want to migrate to another server in order to setup Master-Master replication between the two and my main aim is to achieve this with least possible downtime.

My database has only one table around 30GB where inserts are happening round the clock. All the other tables are history tables(static).

1) What will be the best way to go forward in this case?

Taking mysqldump of the whole database and transferring the dump to
other server and importing it to the new server will do the job i want to achieve but will take a lot of
downtime (maybe more than 14hrs) which is not permissible.
Or can i take individual table dumps for the static tables without stopping mysql  and import it to new server ,and after all this is over, take downtime for the single active table so that no new inserts will happen and both my databases will be in sync? First of all is it possible to do such a thing ? and if possible what issues it might present while setting up a master-master replication?
Or is there any other method to do this with very less downtime?


texts: SQL Server Error Log - Is It / Can It Be Encrypted?

texts:
Is it possible for the SQL Server Error Logs to be encrypted?

Our SQL Server recently had a memory dump which it threw into the Error Log.  While the dump was definitely useful to identify the issue, it got me wondering about the visibility of that dump to users who may have access to the server, but not necessarily SQL Server.

I know TDE protects Data, Log, and Backup files at rest, but potentially sensitive information can be exposed via a memory dump - especially if it is intentionally engineered by a malicious source.

The contents of the dump looks similar to this:

02f59f10  70b50b54 00000101 000000a2 00650073  T..p........s.e.
02f59f20  00760072 00720065 0027003d 006f006c  r.v.e.r.=.'.l.o.
02f59f30  00610063 0068006c 0073006f 00270074  c.a.l.h.o.s.t.'.
02f59f40  0054003b 00750072 00740073 00640065  ;.T.r.u.s.t.e.d.
02f59f50  0043005f 006e006f 0065006e 00740063  _.C.o.n.n.e.c.t.
02f59f60  006f0069 003d006e 00720074 00650075  i.o.n.=.t.r.u.e.
02f59f70  0041003b 00700070 0069006c 00610063  ;.A.p.p.l.i.c.a.
02f59f80  00690074 006e006f 004e0020 006d0061  t.i.o.n. .N.a.m.

I want to be proactive and protect against possible data breaches by encrypting the SQL Server Error Log if possible to reduce the chance of intentional memory dumps exposing sensitive data to non-authorized users.


texts: Remote access from domain and other pages on that domain

texts:
I know that you can access a database from a domain name with a user like this user@example.com but will this user also be able to access the database if they are trying to connect via another page on that domain i.e. example.com/page?

I am asking this as I am trying to connect from a domain name that will be establishing connections from different pages and sub folders i.e. example.com/admin/page, will setting it up with just example.com as the ending will all variables of that domain be accepted from the database connection?