texts: How to convert a Postgresql ltree structure to a nested set by using VIEW?

texts:
I would like to ask a question about a way for transforming a PostgreSQL ltree structure to a nested set structure with only one query by using only VIEWs.

For example, I have a table which has data with relation to each other as on the picture below:

So, the table daclaration is

KeywordLtree(id INT PRIMARY KEY, value TEXT, path ltree);

-- And the data is:

pk |  value  |  path  |
0  |   'A'   |   ''   |
0  |   'B'   |   '1'  |
0  |   'C'   |   '2'  |
0  |   'D'   |  '1.3' |
0  |   'E'   |  '1.4' |
0  |   'F'   |  '1.5' |
0  |   'G'   |  '2.6' |
0  |   'H'   |  '2.7' |

And I have to convert this table to such table:

KeywordSets(id INT PRIMARY KEY, value TEXT, lft INT, rgt INT);

where the rules for the left and the right borders are done according to a nested sets rules.
I found the way how to obtain vertices on each level

CREATE OR REPLACE RECURSIVE VIEW bfs (id, value, path, num_on_level, level) AS
    SELECT id, value, path, row_number() OVER (), 0 as level
    FROM KeywordLtreeSandbox WHERE path ~ '*{0}'

    UNION ALL

    SELECT C.id, C.value, C.path, row_number() OVER (PARTITION BY P.path), level + 1
    FROM KeywordLtreeSandbox C JOIN bfs P ON P.path @> C.path
    WHERE nlevel(C.path) = level + 1;

-- Data would be like below
id | value | path | num_on_level | level |
0  |  "A"  |  ""  |      1       |   0   |
1  |  "B"  | "1"  |      1       |   1   |
2  |  "C"  | "2"  |      2       |   1   |
3  |  "D"  |"1.3" |      1       |   2   |
4  |  "E"  |"1.4" |      2       |   2   |
5  |  "F"  |"1.5" |      3       |   2   |
6  |  "G"  |"2.6" |      1       |   2   |
7  |  "H"  |"2.7" |      2       |   2   |

But I have no idea how to enumerate them correctly then (So "A" left = 1, right = 16, "B" left = 2, right = 9 and so on...)

If I need to be more clear please let me know.

Could anyone give me an idea how it is possible to do?


texts:
I'm trying to execute an sql script file in a single transation. The file contains includes for some other scripts which in my example create some tables. It looks like this:

\include ../tables/table1.cre
\include ../tables/table2.cre
...
\include ../tables/table10.cre

I'm executing it using psql:

psql -X --set AUTOCOMMIT=off --set ON_ERROR_STOP=on -e --single-transaction -d my_db -f my_script.sql

The problem is that errors with the include meta command do not cause a transactiopn rollback. e.g. if some of tableX.cre files is missing, any changes before its include will be commited. However, if there's some SQL syntax error, everyting works as expected.

Is it possible to somehow handle include related errors and rollback the active transcation?

I'm using PostgreSQL 9.2.1.


texts: What are the canonical NoSQL resources for people who never used this technology?

texts:
I am getting more and more interested by the NoSQL technology and I can read several posts on SE about how it works and the different products available.

However, I wonder if there are some canonical references, books or articles, which we can site in a research paper for example and which we can read to have a good overview of:

What the benefits/disadvantages are?
How it works?


texts: Copy snapshot files from Publisher, Paste and Apply on subscriber in SQL Merge Replication

texts:
I read an excellent article about copying the snapshot files for Transactional replication from Publisher to Subscriber and Apply them on subscriber.

http://blogs.msdn.com/b/repltalk/archive/2010/03/07/tips-to-improve-performance-when-applying-snapshot-in-transactional-replication.aspx

But the focus of this article is directed to Transactional Replication.

I want to know how we can copy the snapshots from publisher, paste them on the subscriber and apply them on subscriber. I am using Merge Replication with push subscriber, Distributor and Publisher are on the same server.


texts: How to handle concurrency during an INSERT-SELECT cycle?

texts:
The application should limit each user's access to a certain resource to a maximum number of accesses every 24 hours. To keep track of those, we have the following table:

CREATE TABLE accesses (
    user_id         INTEGER NOT NULL,
    dt              TIMESTAMPZ
);

Each time the resource is accessed, we record it in the table. However, we also need to check if the access limit was reached. To that end, we need to execute this query:

SELECT count(*)
FROM accesses
WHERE dt >= timezone('utc', now()) - interval '24 hours' AND user_id = $1

and we need to check the result against the user limit.

Now, if two accesses are requested at the same time we may have problems:

if we first read, compare, and the insert we may grant more accesses than the limit;
if we first insert, then read and compare, we may disallow legitimate accesses.

The two ways that I know are to either use a SERIALIZABLE transaction or SELECT ... FOR UPDATE. I am not sure if the latter actually works in this case, because we will not update the rows that are read, rather we will insert new ones. So I think that it'll be a useless lock and the result will probably be still incorrect.

However, with a SERIALIZABLE transaction I have retry it in case it fails.

What should be done in this case, to ensure proper access limits?


texts:
My portal will be picking data every 2 hours from a file to a SQL Server table, lets say TableSource. As data is received in TableSource, it should also be copied into TableDestination, so we wrote a trigger for it.

My question is: what is the interval or rate of triggers getting fired in SQL Server? Will it be fired for each row (like MySQL), or after 500 inserts or after completion of all the inserts?

Data will be inserted in bulk, not manual entries.. maybe like 40k - 50k per hour. Can I run my trigger only once, after all my 40K are completely inserted into TableSource?


texts: What are object-relational databases, and why is this model necessary in spatial databases?

texts:
Over in GIS SE, many of us use ESRI geodatabases. ESRI describes geodatabases as being object-relational.

What are object-relational databases, and why is it necessary to use this model in spatial databases?

It seems like they've taken something simple, the relational database model, and made it into something complicated. I'd like to understand what the benefit is.

I'm not a DBA or a developer, so layman's terms would be appreciated.


texts: Update different columns on different clauses

texts:
I have a database table in Postgres 9.3 with the following layout:

id   SERIAL,
col1 INT,
col2 INT

Whenever a new row is inserted, I will have to update ALL rows col1 and/or col2 in different cases but want to do it in only one (faster) query to avoid performance problems (especially because I'll have to do a lock in the table to avoid corruption).

Right now it is done with two queries:

UPDATE tablename
SET col1 = col1 + 2
WHERE col1 > $VAR

and

UPDATE tablename
SET col2 = col2 + 2
WHERE col2 >= $VAR

note, $VAR is the same in both queries. It is the col1 value of the new row.

I can't find anything related to this question in the PostgreSQL manual.

EXAMPLE DB

ID | col1 | col2
1  |   1  |  12
2  |   2  |  5
3  |   3  |  4
4  |   6  |  11
5  |   7  |  8
6  |   9  |  10

If I added a new row with id 7 and col1 5 and col2 6, the table would become

ID | col1 | col2
1  |   1  |  14
2  |   2  |  7
3  |   3  |  4
4  |   8  |  13
5  |   9  |  11
6  |   11 |  12
7  |   5  |  6

In this case, all rows changed at least one column. But in a table with a thousand of rows, most would not even change, so I believe using CASE would not be good with performance.


texts: How do you test for race conditions in a database?

texts:
I try to write database code to make sure that it's not subject to race conditions, to make sure that I've locked the correct rows or tables. But I often wonder: Is my code correct? Is it possible to force any existing race conditions to manifest? I want to be sure that if they do happen in a production environment my application will do the right thing.

I generally know exactly which concurrent query is likely to cause a problem, but I've no idea how to force them to run concurrently to see if the correct behavior happens (e.g. I used the correct type of lock), that the right errors are thrown, etc.

Note: I use PostgreSQL and Perl, so if this can't be answered generically it should probably get retagged as such.

Update: I'd prefer it if the solution was programmatic. That way I can write automated tests to make sure there aren't regressions.


texts: MySQL nested groupby with count other field

texts:
I have a table like this:

option question     questionId
  A    question1        1
  A    question1        1
  A    question2        2
  B    question2        2

I want to (use query):

[
  {
    "question": "question1",
    "count": 2,
    "questionId": 1,
    "options": [
      { "value": "A", "count": 2 }
     ]
  },
  {
    "question": "question2",
    "count": 2,
    "questionId": 2,
    "options": [
      { "value": "A", "count": 1 },
      { "value": "B", "count": 1 }
     ]
  }
]


texts: How can we pass default value as null to Associative Array in Procedure?

texts:
How can we pass NULL as a default value to an associative array?

I have tried using

DEFAULT cast (null as t_test)

Here t_test is an associative array.


texts: Archive log backup error and backup completed with errors


texts: How to break down RELATIONAL data into HIERARCHY data structure?

texts:
I have a query that returns the following dataset:

RELATIONid MAPid  D1id   D2id  D3id
4999       4999   626    1250    7
5000       5000   626    1250    8

For the next step, I need to bind those datasets into treeview (hierarchy structure). I need to transform this dataset into the following:

Nodeid   ParentNodeid  Header
626       null           D1
1250      626            D2
7         1250           D3
8         1250           D3

How I can achieve those structure from the original dataset?

I have one more favor to ask, the data structure a (little bit) more complex than the previous one. Let's say I have sample dataset like this:

RELATIONid MAPid  D1id   D2id  D3id
4999       4999   626    1250    7
5000       5000   626    1250    8
5001       5001   627    1300    10
5002       5002   627    1300    12
5003       5003   628    1400    15

From the following dataset, we have 3 MainParent: 626, 627, 628 and the transformation (cross apply) output expectation will be like this:

Nodeid   ParentNodeid  Header
626       null           D1
1250      626            D2
7         1250           D3
8         1250           D3
627       null           D1
1300      627            D2
10        1300           D3
12        1300           D3
628       null           D1
1400      628            D2
15        1400           D3

Please note that the data are ordered per ParentNode followed by its node data.


texts: Wich is the most efficient/easy way for end-user to get data from Analysis database?

texts:
I know that you can quey a cube by connecting from excel to Analysis database and using formulas like cubevalue() or cubemember(). I also know that after converting the power pivot to formulas you can access the attribute and the value related only by writing a text.

For example: for Branch dimension, instead writing cubemember("connections";"[DimBranch].[Name].[All].[London])", you can write in the cell only "London". However, this won't work if you have a parent-child dimension and want to retrieve the amount for one of the intermediate levels.

Does anyone know how I can avoid writing these formulas directly by the end-user? Can I pass the text "London" as a parameter?


texts: PostgreSQL: hstore trgm search on fixed key

texts:
I have a table of products with custom properties for each product. So I have props::hstore column in my table. I want the user to be able to search products by key/value by first selecting the key and then entering the value with autocomplete feature. So I need two steps here:

Get all the keys in props field. SELECT DISTINCT with skeys should work here, but I don't understand how to create index.
Find most relavent values for fixed key (autocomplete feature). But seems like gin_trgm_ops can create index only on value not on value for fixed keys.

Also, maybe it can be a good idea to change the hstore with jsonb, but I don't see why it can be more perfomant.


texts:
When I change the content of VARCHAR(32) from 32 chars to 0, will it save some space?

The reason I'm asking it is because I store a hash of 32 chars to check something but after a while I'll not use it anymore, so if it takes some of DB space, I might change this field to empty ''.

According to the manual, it takes 1b plus the chars for strings with 255 chars or less, but what if we already have some data and change it to empty, does it save some space?

I'm currently using MySQL with InnoDB engine.

PS.: I'm a beginner yet, please bare with me.


texts: Create an "int" column forced between 0 and 3

texts:
I would like to create a table with one column that would be of type "integer", and locked between 0 and 3. Is this possible?
My goal would be to have this column so that I could just execute a increment or decrement command, to increment or decrement the value in the database.
Example
If value in DB = 0
I execute something like UPDATE xxx SET yyy = yyy-- WHERE zzz
Value would still be 0
If value in DB = 3
I execute something like UPDATE xxx SET yyy=yyy++ WHERE zzz
Value would still be 3
If value in DB = 3
I execute something like UPDATE xxx SET yyy=yyy-- WHERE zzz
Value would still be 2
If value in DB = 1
I execute something like UPDATE xxx SET yyy=yyy-- WHERE zzz
Value would still be 0


texts: postgres: what is the archive_command and when to use it

texts:
I have a master/slave postgresql configuration where the slave is a hot standby and is used as a readonly backup. Currently, the archive_command is set to "cd ." (a noop as I've read).  My understanding is that the archive_command can copy wal segments to a specific location; usually I see examples using the cp command to copy it somewhere else on disk.  I have wal_keep_segments set to 256, so if I understand correctly, the slave can fall behind by about 4GB (16MB * 256) worth of changes to the wal. Is this possibly why I've never needed to use the archive_command because I have enough wal segments to account for some lag?

So is the purpose of the optional archive_command to save more wal segments than the wal_keep_segments settings? So in my case, 256 wal segments would be in the database directory (pg_xlog), and postgres would automatically rotate out and clean up those wal segments as the limit was reached.

And, if I put an archive_command in place, would it just keep saving the wal segments to wherever the command copied the files to (and keep adding more and more archived wal segments unless I added my own cleanup process)? Is this thinking correct?

Then, assuming my understanding of the archive_command is correct, how does the slave take advantage of those wal files?  Does the slave first try to retrieve the 256 wal files from the master data directory, but then fall back to the recovery_command to retrieve wal files older than that?  Does the recovery_command run on the slave or somehow get proxied through the master; many of the examples of the recovery_command show it being used with cp, so I'm not sure how the files are being transferred over to a different server?

I've tried reading numerous guides on this, including the following.
When do I have to use archive_command and when not
https://www.postgresql.org/docs/current/static/continuous-archiving.html

I'm still a bit hazy on the concepts and am trying to reach some clarity. Can someone bring some clarity as to whether my thinking above is correct and why I might use the archive_command? Thanks for reading.

Here is my config:

Master: postgresql.conf

listen_addresses = '*'

wal_level = hot_standby
archive_mode = on
max_wal_senders = 2
archive_command = 'cd .'
wal_keep_segments = 256
hot_standby = on

Slave: postgresql.conf

listen_addresses = '*'

wal_level = hot_standby
archive_mode = on
max_wal_senders = 2
archive_command = 'cd .'
wal_keep_segments = 256
hot_standby = on

Slave: recovery.conf

standby_mode = 'on'
primary_conninfo = 'host=IP-of-master port=5432 user=rep password=****'
trigger_file = '/tmp/postgresql.trigger.5432'


texts: Optionally exclude certain partitions using OLA's scripts

texts:
I have been using OLA's scripts for a long time and love them.

I have a very large database - 20+ TB with hundreds of partitioned tables.

I purge old data by dropping (merging the old partitions).

It is partitioned by date with weekly partitions going back about 6 months.  I have to run daily index maintenance by partition to have any hope of keeping caught up (actually never catch up).

I am now on SQL 2017 and am trying ONLINE Rebuilds by Partition using the Resumable option.

What I find is every day I am rebuilding the current partition in almost every table only as it is always fragmented and likely will be fragmented tomorrow again due to massive # of inserts.

What I would like to do is exclude this partition.

This is the partition the current date falls in.

Ie, I would like to only rebuild past partitions.

Having this as an option would be very helpful.


texts: Appending backups while overwriting expired sets

texts:
I've been reading up on this, but the online documentation is a bit difficult to understand and seems awkwardly written.  For example the INIT, NOINIT. One says it will try to overwrite everything. The other says it will append regardless.

I want something in-between that. I want expired media sets to no longer be in the file, but for new backup jobs to append to the file along side any sets that have not yet expired.

Will the SQL below perform the desired backup?

BACKUP DATABASE [mydb]
TO  DISK = N'c:\Backups\mydb.bak'
WITH NOFORMAT, NOINIT,  NAME = N'mydb-Full Database Backup', NOSKIP,
NOREWIND, NOUNLOAD,  STATS = 10, RETAINDAYS = 60
GO

If I run the above, and there is a backup set older than 60 days, will it be removed from the file?

Edit:

In response to RichardTheKiwi's suggestion I have rewritten the code to create a new file each time.  I will then write a windows script to delete files older than a certain date

declare @filename varchar(max)
set @filename = 'c:\sqlbackups\pptd_' +  replace(replace(convert(varchar, getdate(), 120),':','_'),' ','_') + '.bak'
BACKUP DATABASE [Peter's pointless test database]
TO  DISK = @filename
WITH FORMAT, INIT,  NAME = N'pptd-Full Database Backup',
NOREWIND, NOUNLOAD,  STATS = 10
GO


texts: MySQL command "SHOW DATABASES" returns invalid databases

texts:
I have a MySQL server with a number of databases on it - 16 if I'm not mistaken.

When I run the SHOW DATABASES command I get 30 rows.

I would have expected 17 rows: The 16 databases and "information_schema".

I do however get 13 rows where the database name is prefixed and postfixed with a "?".
Does anybody know what that means?

When I look at the database folder I see my 16 databases, but also the 13 weird databases but here they are pre- and postfixed with @feff

Does anybody know what that means?

I have no idea how these databases got there - I just want to get rid of them.

Can I just delete the database folders?


texts: How to minimize access exclusive table locks while reducing bloat or ALTER (ing) a table?

texts:
In some circumstances, I've been told not to do a VACUUM FULL (or CLUSTER) of a table that is in production, because that will exclusively lock it for longer than wished. The same might apply to several ALTER TABLE operations (like changing the type of several columns).

The alternative presented is always to do something like:

 CREATE TABLE new_table AS SELECT * FROM old_table ;
 -- recreate all indices and constraints
 ALTER TABLE old_table RENAME TO going_to_drop_table ;
 ALTER TABLE new_table RENAME TO old_table ;
 DROP TABLE going_to_drop_table ;

This would work in an scenario where there are no dependencies to old_table (meaning there are not any views that depend on it, nor any foreign key constraints, functions, etc.), and that old_table does not get any inserts or updates. But that would be an exception, not the rule, in most databases.

Is there any way to make such a 'table swap' without losing dependencies?

[For the sake of completeness: I'm specially interested in how to do it for PostgreSQL 9.5 or 9.6]

Research until now (about the underlying reason):

Is it possible to run VACUUM FULL in Postgres asynchronously? => pg_repack. Caveats: might not be easy to implement on Windows, not tested with postgresql 9.6. Looks like the most promissing option.
pg_reorg: similar to pg_repack (was the basis for it) => Seems not to have been updated since postgresql 9.4, and it's mostly superseded by pg_repack.
Old bash-based tool from OmniTI, referenced by Reduce Bloat of table without long/exclusive Locks => looks not up-to-date.
Cleaning Up PostgreSQL Bloat => (for what I'm interested in), basically recommends using vacuumdb using --jobs=njobs. Looks like a way to speed-up vacuums, and an actual possibility. I don't like the sentence from the documentation that states "Note that using this mode together with the -f (FULL) option might cause deadlock failures if certain system catalogs are processed in parallel."
pgcompact from pgtoolkit  => don't know if this is up-to-date. It's based on Reducing bloat without locking, which looks safe.
Safe Operations For High Volume PostgreSQL: A good collection of workarounds to minimize table locking. Thanks to @hruske for the comment.

Which is the best way to avoid the exclusive locks associated with ALTER TABLE ALTER COLUMN ..., CLUSTER or VACUUM FULL?


texts: How to replace foreign reference in PostgreSQL?

texts: Suppose I have a foreign reference column which is pointing to an item A, how do I change it to item B? I would like to modify all records whose foreign reference column is A to B.


texts: Always On Availability Group ApplicationIntent=ReadOnly Not routing to Secondary

texts:
Version: SQL 2012 SP3
Two Node. 1 Primary 1 Secondary

This is how nodes are set up.

Using SSMS connection options when I set connection property ApplicationIntent=ReadOnly I am not getting routed to secondary node. Yes I am using listener name as Server name.

I did not create any read-only routing list because I only have 2 nodes.

Question 1

Am I wrong that even with only 2 nodes I need read-only routing list?

Question 2

What is the difference between these 2 settings about Readable Secondary? Secondaries can only serve read-only traffic, so why route other connections with selecting Yes?

As per books online:

  Read-intent only

  Only read-only connections are allowed to secondary databases of this
  replica. The secondary database(s) are all available for read access.

  Yes

  All connections are allowed to secondary databases of this replica,
  but only for read access. The secondary database(s) are all available
  for read access.


texts:
My schema 'test' has 3 tables: 'user', 'post' and 'revision'.

CREATE TABLE IF NOT EXISTS `test`.`user` (
  `iduser` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(45) NOT NULL,
   PRIMARY KEY (`iduser`))
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `test`.`post` (
  `idpost` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `iduser` INT UNSIGNED NOT NULL,
  `content` VARCHAR(4000) NOT NULL,
  `revision_count` INT NOT NULL DEFAULT 0,
   PRIMARY KEY (`idpost`),
   INDEX `fk_post_user1_idx` (`iduser` ASC),
   CONSTRAINT `fk_post_user1`
     FOREIGN KEY (`iduser`)
     REFERENCES `test`.`user` (`iduser`)
     ON DELETE NO ACTION
     ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS `test`.`revision` (
  `idrevision` INT UNSIGNED NOT NULL,
  `iduser` INT UNSIGNED NOT NULL,
  `idpost` INT UNSIGNED NOT NULL,
  `content` VARCHAR(4000) NOT NULL,
   UNIQUE INDEX `index1` (`idpost` ASC, `idrevision` ASC),
   PRIMARY KEY (`idrevision`, `iduser`),
   INDEX `fk_revision_user1_idx` (`iduser` ASC),
   CONSTRAINT `fk_revision_post`
     FOREIGN KEY (`idpost`)
     REFERENCES `test`.`post` (`idpost`)
     ON DELETE NO ACTION
     ON UPDATE NO ACTION,
   CONSTRAINT `fk_revision_user1`
     FOREIGN KEY (`iduser`)
     REFERENCES `test`.`user` (`iduser`)
     ON DELETE NO ACTION
     ON UPDATE NO ACTION)
ENGINE = InnoDB;

And there is the script creates data:

INSERT INTO `test`.`user` (`iduser`, `name`) VALUES (1, 'Ann');
INSERT INTO `test`.`user` (`iduser`, `name`) VALUES (2, 'Bob');

INSERT INTO `test`.`post` (`idpost`, `iduser`, `content`, `revision_count`) VALUES (1, 1, 'this is first post', 1);

INSERT INTO `test`.`revision` (`idrevision`, `iduser`, `idpost`, `content`) VALUES (1, 1, 1, 'this is old content of first post');

I create 2 sessions work concurrently. Session 1 works with query:

BEGIN;
INSERT INTO revision (idrevision, idpost, iduser, content) VALUES
  ((SELECT max_idrevision + 1
    FROM (SELECT max(idrevision) AS max_idrevision
          FROM revision WHERE idpost = 1) AS anon_1),
   1, 2, 'abc xyz');

Now session 2 works:

BEGIN;
UPDATE revision SET idrevision=3 WHERE idpost=1 AND iduser =1;
-- will hang by lock

-- but with this update
UPDATE revision SET content='nwe asdfs' WHERE idpost=1 AND iduser=1;
-- not hang

I don't know what's lock mysql used with query insert in session 1? Is it row lock or table lock or anything else ?


texts: Embedding vs foreign table in one-to-one relations?

texts:
I'm currently taking a university course about databases.

Consider the following modeling scenario where a car exists only in the context of its owning student:

Student (0,1) <---> (1,1) Car

As I'm reviewing the lecture material, there are two transformations available for this:

1) Put car into it's own entity type and create a student-car relation (store car's primary key in student).

2) "Embed" car's attributes into student entity type with a boolean flag has_car.

With 1) the student table and over all database size is smaller because only a NULL is saved to car_id. However, getting student with a car requires the car's attributes to be fetched from a foreign table.

With 2) there's no need for reaching into foreign tables. However, the database and student table is now bigger because we are saving all of the car's attributes even for student's with no car.

If there are very few students with a car, 1) makes sense.

If there are only very few attributes we store about car, 2) might make sense.

In a more general situation, how does one decide which transformation to use? Could car have a huge amount of attributes and 2) make still sense? Is it possible to calculate the correct solution by calculating the space the car's attributes take?


texts:
I have a backup of an unlogged table called potential_users, the relation has 58677237 records in it.

Steps:

pg_dump --format custom --verbose --file "potential_users.backup" --table "public.potential_users" productiondb

pg_restore -a -t potential_users potential_users.backup

I see the data prompted in the terminal and when it finishes shows:

PostgreSQL database dump complete

But after a SELECT count(*) FROM potential_users; 0 is returned.


texts: Converting CHAR to DATETIME so I can use DATEADD()

texts:
I have a question regarding using a DATEADD() function to a column that is identified as CHAR (6)

The time_stamp column holds values like 131329 as hhmmss.
Whenever I try to create a new column which will hold the value of the time_stamp column + 5 mins, I get an error.

  Msg 242, Level 16, State 3, Line 1
  The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.`

What I am trying to do is create a new column that is 5 mins ahead of the time_stamp. I am working on a report right now that shows 'completed orders' but I need the report to hold the order as NOT COMPLETED for at least 5 mins before it shows on the report as Completed.


texts:
I have a separate database per a client although data structure is same for all databases. Only difference is all clients have their own data on their on databases. Now problem is when I need a report that includes all clients, it is a big concern. As each client has millions of data and union will not work as it will take too much time. I have already applied indices on important columns. I have tried with triggers as well, but it creates lock many times.

I want to create a report database server, where I can create a flat table structure and can put all clients data into that with separate client id.

So is there any way I can modify bean logs so that I can create a flat table structure in a report database.
For Example,
Each client has Table A,B,C.
In Report I want column 1,2 of Table A, column 2,3 of Table B and column 5 of Table C of all clients.

Please note that I do not want to use trigger.

It can be resolved with ActiveMQ from application side. I am just curious about solution from database side.


texts:
Good morning all,
I have an on Always On SQL server configuration where transactional replication is configured
If I open SSMS I find that the distribution database is set to AlwaysOn on then I cannot find the synchronized word.

thanks


texts: Converting SQL SERVER Procedure to POSTGRESQL

texts:
I have procedure in SQL Server to print all players of given country.
I try to convert it into plpgsql, but I get errors.

SQL Server Procedure

CREATE PROCEDURE kraj_pilkarze @kraj varchar(30)
AS
DECLARE @idkraj INT
DECLARE @nazwa_zespolu varchar(30)
SELECT @idkraj = id_kraj FROM kraj WHERE @kraj=nazwa
DECLARE kursor_pilk CURSOR
FOR SELECT imie,nazwisko, id_zespol, id_kraj FROM pilkarz
DECLARE @imie varchar(20), @nazwisko varchar(30), @zespol int, @d_kraj int
OPEN kursor_pilk
FETCH NEXT FROM kursor_pilk INTO @imie, @nazwisko,@zespol, @d_kraj
WHILE @@FETCH_STATUS=0
    BEGIN
    IF @d_kraj=@idkraj
        BEGIN
            SELECT @nazwa_zespolu = nazwa FROM zespol WHERE @zespol=id_zespol
            PRINT @imie+' '+@nazwisko+', '+@nazwa_zespolu
            FETCH NEXT FROM kursor_pilk INTO @imie, @nazwisko,@zespol,@d_kraj
        END
    ELSE
        BEGIN
            FETCH NEXT FROM kursor_pilk INTO @imie, @nazwisko,@zespol,@d_kraj
        END
    END
CLOSE kursor_pilk
DEALLOCATE kursor_pilk

Doesn't work function in PostgreSql

CREATE FUNCTION kraj_pilkarze(p_kraj varchar(30))
RETURNS VOID AS $$
BEGIN
DECLARE p_idkraj INT;
DECLARE p_nazwa_zespolu varchar(30);
DECLARE p_imie varchar(20), p_nazwisko varchar(30), p_zespol int, d_kraj int;
SELECT INTO p_idkraj id_kraj FROM kraj WHERE p_kraj=nazwa;
DECLARE kursor_pilk CURSOR FOR
SELECT imie,nazwisko, id_zespol, id_kraj FROM pilkarz;
OPEN kursor_pilk;
FETCH NEXT FROM kursor_pilk INTO p_imie, p_nazwisko,p_zespol, d_kraj;
WHILE (FOUND) LOOP
    IF d_kraj=p_idkraj THEN
            SELECT INTO p_nazwa_zespolu nazwa FROM zespol WHERE @zespol=id_zespol;
            RAISE NOTICE '% %, %',p_imie,p_nazwisko,p_nazwa_zespolu;
            FETCH NEXT FROM kursor_pilk INTO p_imie, p_nazwisko,p_zespol,d_kraj;
    ELSE
            FETCH NEXT FROM kursor_pilk INTO p_imie, p_nazwisko,p_zespol,d_kraj;
    END IF;
END LOOP;
CLOSE kursor_pilk;
END;
$$ LANGUAGE PLPGSQL;


texts: Maintaining referential integrity in a booking system

texts:
I am developing a second version of a corporate training booking system on Microsoft SQL Server 2005.

I have 3 tables (simplified for this question).

Table 1- CourseSize table- this determines the maximum number of participants able to book into the session.
Table 2- Session- the course to be provided, the date/time and a reference to CourseSize record.
Table 3- Booking table- the person who made the booking with reference to the relevant Session record.

The SQL is as follows-

CREATE TABLE [dbo].[CourseSize]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CourseSizeMax] [tinyint] NOT NULL,
    CONSTRAINT [PK_CourseSize] PRIMARY KEY CLUSTERED ([ID] ASC)
);

CREATE TABLE [dbo].[Session]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CourseSizeID] [int] NOT NULL,
    CONSTRAINT [PK_Session] PRIMARY KEY CLUSTERED ([ID] ASC)
);
GO

ALTER TABLE [dbo].[Session] WITH CHECK
ADD CONSTRAINT [FK_Session_CourseSize]
FOREIGN KEY([CourseSizeID])
REFERENCES [dbo].[CourseSize] ([ID]);
GO

ALTER TABLE [dbo].[Session]
CHECK CONSTRAINT [FK_Session_CourseSize];
GO

CREATE TABLE [dbo].[Booking]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [SessionID] [int] NOT NULL,
    CONSTRAINT [PK_Booking] PRIMARY KEY CLUSTERED ([ID] ASC)
);
GO

ALTER TABLE [dbo].[Booking] WITH CHECK
ADD CONSTRAINT [FK_Booking_Session]
FOREIGN KEY([SessionID])
REFERENCES [dbo].[Session] ([ID]);
GO

ALTER TABLE [dbo].[Booking]
CHECK CONSTRAINT [FK_Booking_Session];
GO

Problem- I need to ensure that inserts into the Booking table are stopped if the number of bookings against a session has reached the maximum course size.  In the past I have used something like this:

INSERT INTO Booking
SELECT 1 AS SessionID
WHERE
    (
        SELECT COUNT(*)
        FROM Booking
        WHERE SessionID = 1
    ) <=
    (
        SELECT CourseSizeMax
        FROM CourseSize
            INNER JOIN Session ON CourseSize.ID = Session.CourseSizeID
        WHERE Session.ID = 1
    );

This fails when multiple users make bookings at the same time.

I tested the above query by setting the CourseSizeMax to 2, and using WAITFOR time. I scheduled the query to run 3 times; 2 at the same time and the last 1 ms before the other two. They all were able to insert a row, thereby exceeding the session size limit.

I would really like to avoid using a trigger to handle this; my preference is to use constraints.  I have total control of the design of the database so there is no problem with changing the structure if need be.

How do I prevent simultaneous updates from breaking the course size limitation?


texts: Postgres-XL adding GTM Proxy seems to do nothing

texts:
I've set up a Postgres-XL cluster using this recipe:

GTM:
hostname=host1
nodename=gtm

Coordinator：
hostname=host2
nodename=coord1

Datanode1：
hostname=host3
nodename=datanode1

Datanode2：
hostname=host4
nodename=datanode2

When I ran a load test against it, the GTM would fallover. I tweak settings until the GTM didn't fall over but only reported errors - thus kept on working after the load test.

I the added a GTM Proxy. I did not do init all but rather only init the proxy.  When I restarted the cluster, the GTM reported that the GTM proxy was up and running.  When I looked at the GTM proxy's log, it looked like it started up and was connected.

But when I ran the load test again, I got the same result with no log entries for the GTM proxy.  Thus it seems like the GTM Proxy didn't pick up the load processing as I expected it to do.

I don't know how to trouble shoot this. Any pointers on where to look next?

(I don't know what extra info to post here)


texts: How to return resultset from MySQL Stored Procedure using prepared statement?

texts:
DELIMITER $$

CREATE PROCEDURE List_IL()

BEGIN

  DECLARE Project_Number_val VARCHAR( 255 );
  DECLARE Temp_List_val VARCHAR(255);
  DECLARE Project_List_val VARCHAR(255);
  DECLARE FoundCount INT;
  DECLARE Project_Number INT;
  DECLARE db_Name VARCHAR(255);

  DECLARE no_more_rows BOOLEAN;
  DECLARE loop_cntr INT DEFAULT 0;
  DECLARE num_rows INT DEFAULT 0;

  DECLARE   projects_cur    CURSOR FOR
    SELECT  Project_Id
    FROM    Project_Details;

  DECLARE CONTINUE HANDLER FOR NOT FOUND
    SET no_more_rows = TRUE;

  OPEN projects_cur;
  select FOUND_ROWS() into num_rows;

  the_loop: LOOP

    FETCH  projects_cur
    INTO   Project_Number_val;

    IF no_more_rows THEN
        CLOSE projects_cur;
        LEAVE the_loop;
    END IF;

SET Project_List_val = CONCAT(Project_Number_val, '_List');
SET db_Name='panel';

SELECT COUNT(1) INTO  FoundCount  FROM information_schema.tables WHERE table_schema = `db_Name`  AND table_name = `Project_List_val`;

 IF FoundCount = 1 THEN
SET @Project_Number=Project_Number_val;

SET @sql = CONCAT(' SELECT Panel_Id,', Project_Number_val,'
                    FROM ', @Project_List_val,' Where status=1');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END IF;

    SET loop_cntr = loop_cntr + 1;
  END LOOP the_loop;

END $$

In the above stored procedure How can I get the all the rows selected during execution of prepared statement and after the loop terminates I want to return the entire result set whichever calls the stored procedure. Can you please help me how to do this?


texts: Why seq-scan can be much faster than index-scan and index-only-scan in this simple query?

texts:
I am using PostgreSQL 9.4.4. I have a query like this:

 SELECT COUNT(*) FROM A,B WHERE A.a = B.b

a and b are the Primary Keys of tables A and  B, so there are B-indexes on a & b

By default, PostgreSQL will use seq-scan on AB and use hash join, I force it to do the index-scan and index-only-scan.

The result showed that, seq scan is much faster than the other two, it takes more time to do the full scan on a,b for index-scan and index-only-scan.

EXPLAIN ANALYZE SELECT COUNT(*) FROM journal,paper WHERE journal.paper_id = paper.paper_id;

Can someone explain it?

Thank you so much!


texts: How to remove string quotes in MySQL 5.7 for function JSON_EXTRACT?

texts:
Other than doing string manipulation after each JSON_EXTRACT, is there a simpler or more correct way to have the JSON_EXTRACT return the string WITHOUT the enclosing quotes?
Or should this question be on StachExchange?


texts: SQL Server - Database per company. How to query across databases?

texts:
We are designing a product that, as per the client's request, must utilize a database-per-company design approach with one shared database holding common data.

We are using SQL Server 2012 for the job.

One of the features is a news feed with an ability to post messages, comments, likes etc.

The news feed also has the ability to post to "community."  These "community" posts should be visible to all the companies.  We will have to store those posts at the common level.

My question is - how would you go about designing this?

I was thinking of having duplicate tables in the common database and each of the company databases and just joining the tables for the company feed that appears in the UI.

This brings up another question; how do we manage user IDs for example?  Each post or comment is associated with a user ID.  Would we have to make user IDs unique for all the databases to do that?

Is there a better way to design this?


texts: Why do sys.columns and INFORMATION_SCHEMA.COLUMNS display different number of columns

texts:
I am trying two methods to display the columns with a particular name:

INFORMATION_SCHEMA.COLUMNS

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME='SUPPLIER_NAME';

SYS.COLUMNS

SELECT *
FROM SYS.COLUMNS
WHERE NAME='SUPPLIER_NAME'

Why do the queries display different output?


texts: Social network comments and posts model in PostgreSQL 9.6

texts:
I'm trying to figure out a data structure for migrating from MongoDB to PostgreSQL. Within my posts table in Postgres, I have a JSONB[] array holding comments for the post.

The thing is that each element in the array only holds an id of the comment author, not the actual user info of the author. What would be the best way to perform a join on every element of that array to replace the author id for their data, or if not what would be a better way to do this?

For example, one of my post table rows might look like:

id: 5
author: 1
body: "Hello word"
comments: [{author:0, body: "It's me"}]

Where the comments column is a JSONB array.

My users table might look like:

id: 0
username: Dux

I do need threaded comments.


texts: Find which databases have a particular service in SQL Server Service Broker

texts:
From what I currently understand, the names of services are global to the instance and you can refer to them without knowing which database contains the service and associated queue etc.  In addition, you can have the same service in different databases on the same instance and in that case, you should use routing and can also use the service_broker_guid to distinguish them when you BEGIN DIALOG CONVERSATION.

Is there a better way than looking in all databases to see what databases the service name exists in?

The sys.services catalog view is restricted to within a particular database context only, either the current database or it can be called with a fully qualified database_name.sys.services.

In particular, I have some triggers that need to send messages to a service in another database, but I don't want them to fail if the service is not there, or if there are two databases with the service, or the database name is different etc.

If performance is too slow to do all these safety checks in the trigger every time, I can do regular maintenance checks to drop them, but whether the trigger is there all the time or not, I still would like the best way to know if the service is defined in the instance at all in one or more databases.

I did manage to come up with this dynamic SQL, but that would probably have to go in a proc or something:

USE master;
DECLARE @DatabasePattern AS varchar(max) = '%';
DECLARE @ServicePattern AS varchar(max) = '%';
DECLARE @SQLTemplate AS varchar(max) = 'UNION ALL
SELECT database_id = {database_id}
    , database_name = ''{database_name}''
    , service_name = s.[name]
    , service_broker_guid = ''{service_broker_guid}''
FROM {database_name}.sys.services s
WHERE s.[name] LIKE ''' + REPLACE(@ServicePattern, '''', '''''') + '''
';
DECLARE @SQL AS varchar(max);

WITH Statements AS (
    SELECT SQL = REPLACE(REPLACE(REPLACE(@SQLTemplate
                    ,'{database_id}', db.[database_id])
                    ,'{database_name}', REPLACE(db.[name], '''', ''''''))
                    ,'{service_broker_guid}', db.[service_broker_guid])
    FROM sys.databases db
    WHERE db.[is_broker_enabled] = 1
        AND db.[name] LIKE @DatabasePattern
)
SELECT @SQL = STUFF((SELECT SQL
FROM Statements
FOR XML PATH (''), TYPE).value('.', 'varchar(max)'), 1, LEN('UNION ALL'), '');

EXEC (@SQL);


texts:
I have MySQL Master-Master replication on 2 servers.

I want to replicate to another slave server.

When I configure the slave to replicate from master1, only data added on master1 is replicated.   When I added a record on master2, the record is replicated to master1 but not replicated to the slave.

Any idea how to solve this?

m1

server_id = 1
log_slave_updates = OFF

m2

server_id = 23
log_slave_updates = OFF

s3

server_id = 21
log_slave_updates = OFF


texts:
I am trying to execute a table from a MySQL database using a for loop statement, in order to get the field names as the column header.

Here is my code:

$result = mysql_query("SELECT * FROM bom_commonmaterials where description like '%CAP%'");
        if (!$result) {
            die("Query to show fields from table failed");
        }

        $fields_num = mysql_num_fields($result);

        echo "

";
        // printing table headers
        for($i=0; $i<$fields_num; $i++)
        {
            $field = mysql_fetch_field($result);
            echo "{$field->name}	";
            if ($field ="WATTAGE")
            {
                CONTINUE;
            }
        }
        echo "

\n";
        // printing table rows
        while($row = mysql_fetch_row($result))
        {
            echo "
";

            // $row is array... foreach( .. ) puts every element
            // of $row to $cell variable
            foreach($row as $cell)
                echo "$cell	";

            echo "
\n";

        }
        mysql_free_result($result);
        echo "
";

As you can see, I have an if statement $field ="WATTAGE" inside the for loop statement. I am trying to execute this code so that the field name WATTAGE will be not included as a table header, but I think my code is wrong.

In this case I need help on how to skip a specific field name, and also the field rows of this field name.

Note: I used loop statement in this because the table field name from the database is not fixed. For example the user wants to add new columns. So I can't just code it manually because the table doesn't have a fixed data.


texts: Index on a persisted computed column not seekable

texts:
I have table, called Address, that has a persisted computed column called Hashkey. The column is deterministic but not precise.  It has a unique index on it that is not seekable. If I run this query, returning the primary key:

SELECT @ADDRESSID= ISNULL(AddressId,0)
FROM dbo.[Address]
WHERE HashKey = @HashKey

I get this plan:

If I force the index I get this even worse plan:

If I try and force both the index and a seek, I get an error:

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

Is this just because it's not precise?  I thought that didn't matter if it was persisted?

Is there a way to make this index seekable without making this a non-computed column?

Does anyone have any links to information on this?

I can't post the actual table creation, but here is a test table that has the same issue:

drop TABLE [dbo].[Test]

CREATE TABLE [dbo].[Test]
  (
     [test]        [VARCHAR](100) NULL,
     [TestGeocode] [geography] NULL,
     [Hashkey] AS CAST(
                        ( hashbytes
                            ('SHA',
                                ( RIGHT(REPLICATE(' ', (100)) + isnull([test], ''), ( 100 )) )
                                + RIGHT(REPLICATE(' ', (100)) + isnull([TestGeocode].[ToString](), ''), ( 100 ))
                            )
                        ) AS BINARY(20)
                      ) PERSISTED
    CONSTRAINT [UK_Test_HashKey] UNIQUE NONCLUSTERED([Hashkey])
  )
GO
DECLARE @Hashkey BINARY(20)

SELECT [Hashkey]
FROM   [dbo].[Test] WITH (FORCESEEK) /*Query processor could not produce a query plan*/
WHERE  [Hashkey] = @Hashkey


texts: How to insert '1001-01-01 00:00:00' into MariaDB when altering a table?

texts:
I created a table in HeidiSQL:
CREATE TABLE `publish_dt` (
    `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `TS` DATETIME NULL DEFAULT NULL,
    `Text` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_unicode_ci',
    PRIMARY KEY (`ID`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=2
;

Then I insert some data into the table, e.g. with
INSERT INTO publish_dt (`Text`) VALUES ('Hello')

Then I want to change TS column to non-NULL with a default of '1001-01-01 00:00:00'. I tried to do this in HeidiSQL, it produces the following ALTER TABLE query:
ALTER TABLE `publish_dt`
    CHANGE COLUMN `TS` `TS` DATETIME NOT NULL DEFAULT '1001-01-01 00:00:00' AFTER `ID`;

However, when I try to save changes in HeidiSQL, I receive the following error from the DB:
SQL Error (1265): Data truncated for column 'TS' at row 1

Could you, please, clarify why this error happens (I couldn't find the same case with DATETIME on the internet) and how to work it around?
Reason for asking: MariaDB documentation says

MariaDB stores values that use the DATETIME data type in a format that
supports values between 1000-01-01 00:00:00.000000 and 9999-12-31
23:59:59.999999.


texts: How to improve performance to 12million rows table?

texts:
I have performance issues on a query in a big table (12 million records) based on Geonames, that's a read-only database so NO DELETE, UPDATE or INSERT only SELECT.
There are queries I make every now and then filtering by different columns that are not keys (latitude and longitude, fcode and country, only name, etc..), the thing is that with my server resources it takes more than 30 seconds to complete them.
I have made views and small tables (clone of the big table but with only data from one country) to check how to improve it.
With the views, I get similar results than in the big table and using explain I have seen that views check the same amount of rows as the big table (12million rows)
In one of the small tables I get less than 200 milliseconds, more or less depending on the table size.
I'm not a database expert but duplicating data in small tables feels awkward. I'm not sure if that's the best approach that can be done there.
All queries are being sent from my backend, so no stored procedures.
The queries done filtering by primary keys works blazing fast though!.
Thanks in advance for any advice!

UPDATE FOR COMMENTS

Table definition

CREATE TABLE `geoname` (
  `geonameid` INT,
  `name` VARCHAR(200),
  `asciiname` VARCHAR(200),
  `alternatenames` VARCHAR(4000),
  `latitude` DECIMAL(10,7),
  `longitude` DECIMAL(10,7),
  `fclass` VARCHAR(1),
  `fcode` VARCHAR(10),
  `country` VARCHAR(2),
  `cc2` VARCHAR(60),
  `admin1` VARCHAR(20),
  `admin2` VARCHAR(80),
  `admin3` VARCHAR(20),
  `admin4` VARCHAR(20),
  `population` INT,
  `elevation` INT,
  `gtopo30` INT,
  `timezone` VARCHAR(40),
  `moddate` DATE,
  PRIMARY KEY (geonameid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

What kind of indexes could I add there?


texts: High Unrestored log on secondary database mirror server

texts:
I need your help in understanding/resolving one of the issues we are witnessing today.

We have a high value of unrestored log on secondary side where value keep ranging from 5-10 GB as monitored.

Simultaneously we have a job to create snapshot on this mirror [for reporting] server every 30 minutes which completes within a minute when unrestored is within MB's but keep running for 2-3 hours with high value of unrestored log

The database here is 10 TB and OLTP with DB in high PERformance mode.

Could someone explain why the snapshot job is running that long or how to find it and fix high unrestored value?

Or could it be Vice-versa and i am thinking wrong as mirror causing snapshot to run that long?


texts: What is the difference between concurrency control in operating systems and in trasactional databases?

texts:
Recently I'm learning concurrency control techniques in transactional databases. However, I am so confused about the differences between concurrency control in operating systems and in transactional databases.
Within my understanding, the concurrency control techniques introduced in database literature can be used in a multithreading program, whose threads share some variables with each other, and vice versa. The techniques used in multithreading programs for sharing variables between threads can also be used in databases for concurrency control.
Why do we bother to introduce this differently in database literature and in operating systems?


texts: How to get last inserted rows where a condition is satisfied


texts: SQL Server 2005 query erroring with message 7105, slot for LOB data type node does not exist

texts:
SQL Server 2005 sp3 Standard on 64-bit Windows Server 2008

inconsistently receiving an error that seems to indicate corruption

select *
from some_table
where text_field like ‘%the fox did not go into the hen-house %’

sometimes, but not always, this and similar queries will generate the following error

Msg 7105, Level 22, State 6, Line 1
The Database ID 11, Page (1:990063), slot 4 for LOB data type
node does not exist. This is usually caused by transactions
that can read uncommitted data on a data page. Run DBCC CHECKTABLE.

the severity level of 22 causes the connection to be broken

the data has long ago been committed

the table has only two indexes, neither of which list the text field

the database is in isolation level read committed snapshot

the database is being mirrored

the error is being generated by simple select queries, not by update queries

the error showed up for the first time about a month ago, one occurrence; second occurrence was twelve days ago; third and fourth occurrences were yesterday; today there have been two new occurrences

the queries are being run without hints, no use of NOLOCK

I have repeatedly run dbcc checkdb and dbcc checktable, and so far in a half dozen tries, no errors noted by either command; these commands take much longer to run than expected .. on similarly sized databases, dbcc checkdb will run in under a minute, but for this one, it takes almost ten minutes

the error comes and goes, meaning sometimes I run the query and it returns quickly with all expected information, and other times running the query returns the error

the network/server engineers were asked to review the associated SAN disks, and report that there are no issues

what could be the root cause of this issue? could it be hardware? if so, how can I guide the engineers? if not hardware, what else could it be?


texts: Set all images name of a product to the same field in other table separated by comma in MySQL

texts:
I'm using MariaDB v10.1.31. I have 2 tables:

Product_images (created from a .csv import).

product_reference | name

REF.001           | example_image_1.jpg
REF.001           | example_image_2.jpg

Importer* (created to store all the data I need from others tables).

sku     | images

REF.001 | null

How could I update Importer to show the following?

sku     | images

REF.001 | example_image_1.jpg,example_image_2.jpg

*Data in Importer would be to export in .csv and be able to import in WooComerce, this is why I need to import the images by this way.


texts:
I have a database currently running on EC2. As I have to move it to a bigger machine the question of using RDS came up.

Pricing:

I can get an EC2 c3.large instance with two SSDs (16GB each), two vCPUs and 4GB of memory on demand for $0.132 per hour [1].

The closest (considering price) RDS machine would be db.m3.medium for $0.125 per hour (Single-AZ) [2]. This machine has the same amount of memory but only one vCPU [3]. Additionally I would have to pay for storage and io [2].

So the prices would be very similar for these two.

The advantages for EC2:

As mentioned already, one more vCPU.
I can put the Write-Ahead-Log on the second disc when using EC2 (great performance improvement when writing a lot to the db).
I can run pgbouncer on my EC2 instance (more performance because I can keep connections open).
I can edit the configuration file (will eventually improve performance)

The advantages for RDS:

Automatically does daily backups.

Can RDS cover the advantages that EC2 has (especially 2.)? Is there any other advantages?


texts:
I have a large table (TableA) with approx. 120 mio. datasets. This table contains a single primary key column (type: int).

Another large table (TableB) contains the same ID column with some other fields. Currently there is no foreign key constraint or unique index defined on this table (although I'm thinking about adding a primary key constraint here in any case).

The key task is to determine if any of the ID values in TableA do NOT appear in TableB.

SELECT * FROM TableA WHERE Id NOT IN (SELECT Id FROM TableB)

I timed this task on our database server to take approx. 4 minutes. Now I was wondering if there was a way to improve this.

From what I understand the lists may not be ordered BUT it's guaranteed that the Id is Unique for both tables. Is there a method I can first sort these fields (or even better have a sorted index of some kind) and then just compare those sorted lists or is there another approach to this?


texts: Changing Table Ownership in Postgres from Postgres User

texts:
I have a database with some tables that was created by a certain user. All tables and sequences in that DB were owned by that user. I needed to change the owner of those tables and sequences to be able to run certain operations (Django Migrations).
In doing so, I changed it to postgres by mistake:
REASSIGN OWNED BY delme TO postgres;

Now, when I attempt to correct the oversight, I get the following:
db_name=# REASSIGN OWNED BY postgres TO django_development;
ERROR:  cannot reassign ownership of objects owned by role postgres because they are required by the database system

How can I change ownership of that entire db's contents to django_development user?


texts: Database throughful design vs Quick and dirty design comparison

texts:
Take the representation of an Address, here below is a complete and very detailed implementation:

here instead is a quick implementation (which more or less contains the same fields, imagine all fields in the former are contained also in the second)

If I were to decide which is the closer to a normalized and academically correct one I'd say the first one, however if I were to start a project I would go with the second.

Do you agree with this consideration? And if yes, how does one deal with this fact?

start with an easy database and as soon as it is time improve it into a more normalized/academical database.
start with something as close as possible to the academical database
stick with the quick and dirty solution


texts: Where is my ORACLE_HOME Windows directory?

texts:
I'm trying to find my listener.ora file to edit it, but the docs say it's in the ORACLE_HOME directory, but where is that?

I'm running 12.1.0 on Windows Server 2008


texts: How can I check my config files my.cnf for example

texts:
I have 2 config files, master.cnf and slave.cnf, each containing a group [mysdql0] or [mysqld1] respectively.
I have an almost identical setup across different servers and all but one work correctly with a single my.cnf file which has the following lines;
!include /etc/mysql/master.cnf
!include /etc/mysal/slave.cnf

and when I run mysqld_multi report I get the output I expect, relating to mysqld0 & mysqld1
However, there is one box that simply returns no instance groups and in an attempt to diagnose this issue, I moved the configuration for master.cnf into my.cnf and to my surprise I am then able to run mysqld_multi report and also start and stop that instance without issue.
For whatever reason, when I split this out into the two files as mentioned above and a single my.cnf file, it just doesnt see any instance groups.
My question is twofold reallyl
1) What could be causing this? given the same configuration is up and running on a few other servers.
2) Secondly, how can I diagnose this issue? (is there a way to see if the my.cnf file is being parsed and more importantly getting to the !include lines etc?
I've tried running mysqld --validate-config on newer versions but this just gives an error unknown option --validate-config
I'm running on a mixture of Debian boxes and version range including;
5.5.55-0+deb8u1 and 10.3.27-MariaDB-0+deb10u1
UPDATE 21-JAN-2021
I've updated my post based on comments received and focusing on the prolbem box only. the above can be treated as merely background information.
Ok, so I have amended things slightly in an attempt to put across what the current problem is.
So currently, I have a file /etc/mysql/my.cnf which is as follows;
[client-server]

!include /etc/mysql/my_master.cnf
!include /etc/mysql/my_slave.cnf

I also have two other cnf files, surprisingly my_master.cnf and also my_slave.cnf and these contain sections as below;
my_master.cnf contains [mysqld0]
my_slave.cnf contains [mysqld1]
both of the above have configurations that work.
If I run mysqld_multi report I get;
Reporting MySQL servers
No groups to be reported (check your GNRs)

If I run my_print_defaults --config-file=/etc/mysql/my.cnf mysqld0
I get the output I'm expecting for [mysqld0]
and similarly, if I run my_print_defaults --config-file=/etc/mysql/my.cnf mysqld1
I get the output I'm expecting for [mysqld1]
but still can neither report, nor start either of my instances with mysqld_multi
If I simply rename my_master.cnf or my_slave.cnf to my.cnf then mysqld_report reports as expected and I can also start and stop that instance as expected.


texts: MS SQL Server CPU load goes up dramatically when turning on 2014 features by setting compatibility level

texts:
I'm running some Microsoft SQL Server 2014 load testing on a decently spec'ed machine. I'm certainly not an expert on the SQL Server tuning specifics, but with the bits an pieces I've gathered from various places I think the setup is quite OK.

One element in the performance does puzzle, hence I'm reaching out here. The database that I'm testing against was built up historically and was originally created against 2008r2. The compatibility setting was carried over from that version. I've raised that setting last week in two stages, but when I raised it from 2012 to 2014 compatibility, all of sudden the CPU load went through the roof.

When changing this setting back an forth it seems to be systemic. I can run X simulated users (web app in front of the DB) on an 8 core database with the 2012 setting. If I raise it to 2014 I only get away with 60% of X simulated users. And the other way around for X users the batch requests number is 40% lower for the 2014 setting.

As I understand, switching compatibility level back doesn't revert the DB but only turns off features. This situation seems to point to a feature that eats CPU with no apparent gain, so I was wondering which feature that could be and if there's a way to turn of that specific feature separately?

Other posts on serverfault have pointed towards rebuilding the indexes after an upgrade, I've done so, but that doesn't really seem to make a difference.

It's probably important to note that the application at hand generates a lot of queries that are not prepared statements. This is probably leading to the high number of compilations I see in perfmon (compilations/batch requests : 0.25). I can't do too much about that, so telling me to do something about that first won't really help me forward.


texts: SSIS Package execution fails when started by user logging in with FreeTDS

texts:
I am attempting to let a login kick off an SSIS package via the built-in SSISDB stored procedures instead of doing so through SQL Agent.

The login will be connecting through an Airflow DAG on a Ubuntu box, so we're utilizing FreeTDS / pyodbc to allow it to authenticate as a Windows Login, which is working fine.

However the following happens when launching the package:

Package is launched using [SSISDB].[catalog].[start_execution] @execution_id. Tried running both synchronously and asynchronously.
An execution ID is generated, along with an entry in [SSISDB].[catalog].[executions] with a status of 5 ("Pending")
~1 minute later the the execution "completes" and the execution ID is wiped from all SSISDB tables and does not show up in any of the built-in reports.

Even when adding exec parameters to create dump files, none are created during the package execution.

Bonus problem: During the ~1 minute timeframe, other packages being launched from SQL Agent that generally work fine often fail with this error message:

  The operation failed because the execution timed out.

Notes:

The SQL Server installation is on Windows and is SQL Server 2016 Ent.
This process works fine when using my Windows Authentication login from a Windows box.
The Windows account being used by the DAG is the agent service account, so has all necessary permissions.
The two SID fields in [SSISDB].[catalog].[executions] for the pending execution match the SIDs of other working executions being handled by SQL Agent for the same service account.
We are using TDS version 7.2 and the latest FreeTDS version.
No difference when using autocommit True/False
No blocking seen during execution

I am guessing the login is being re-authenticated when it is used to launch the external SSIS process, but am hoping there may be some way to make this work still.

Calling via CLR is an undesirable option due to the added complexity and security implications.

I am also currently testing launching them via dtexec via xp_cmdshell (looking promising) but it is unclear to me how/why that would work and using the stored procedures does not.


texts: Force a Member to Become Primary when Primary fails in mobgodb

texts:
I have mongodb 2.6.11 cluster of two members: nms02 and nms01m. I know how to force secondary to become primary when primary is avail. But now my primary nms02 failed..and nms01m is secondary...How I force secondary to become primary now ?

nms:SECONDARY> rs.status()
{
        "set" : "nms",
        "date" : ISODate("2015-12-19T14:52:41Z"),
        "myState" : 2,
        "members" : [
                {
                        "_id" : 0,
                        "name" : "nms02:27017",
                        "health" : 0,
                        "state" : 8,
                        "stateStr" : "(not reachable/healthy)",
                        "uptime" : 0,
                        "optime" : Timestamp(1450445883, 1),
                        "optimeDate" : ISODate("2015-12-18T13:38:03Z"),
                        "lastHeartbeat" : ISODate("2015-12-19T14:52:37Z"),
                        "lastHeartbeatRecv" : ISODate("2015-12-18T13:38:03Z"),
                        "pingMs" : 0,
                        "syncingTo" : "nms01m:27017"
                },
                {
                        "_id" : 1,
                        "name" : "nms01m:27017",
                        "health" : 1,
                        "state" : 2,
                        "stateStr" : "SECONDARY",
                        "uptime" : 180204,
                        "optime" : Timestamp(1450445886, 1),
                        "optimeDate" : ISODate("2015-12-18T13:38:06Z"),
                        "self" : true
                }
        ],
        "ok" : 1
}

I can't do much on secondary now

nms:SECONDARY> cfg = rs.conf()
2015-12-19T10:20:51.821-0500 error: { "$err" : "not authorized for query on local.system.replset", "code" : 13 } at src/mongo/shell/query.js:131


texts: Display the choices even if no rows found in associated table

texts:
I have the following 3 tables for a small voting/polling feature:

CREATE TABLE IF NOT EXISTS `options` (
  `option_id` tinyint(4) unsigned NOT NULL,
  `poll_id` mediumint(8) unsigned NOT NULL,
  `option` tinytext NOT NULL,
  UNIQUE KEY `option_id_poll_id` (`option_id`,`poll_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Storing poll options/choices';

CREATE TABLE IF NOT EXISTS `questions` (
  `poll_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `deleted` bit(1) NOT NULL DEFAULT b'0',
  `question` tinytext NOT NULL,
  `nick` varchar(32) NOT NULL,
  `dated` datetime NOT NULL,
  PRIMARY KEY (`poll_id`),
  KEY `deleted` (`deleted`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Table to store poll questions/titles';

CREATE TABLE IF NOT EXISTS `votes` (
  `poll_id` mediumint(8) unsigned NOT NULL,
  `option_id` tinyint(4) unsigned NOT NULL,
  `nick` varchar(32) NOT NULL,
  `dated` datetime NOT NULL,
  UNIQUE KEY `poll_id_nick` (`poll_id`,`nick`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

and I'm fetching the results of a certain poll_id (represented with %d below) using the following query:

SELECT
    o.option_id AS option_id,
    o.`option` AS `option`,
    COUNT(v.nick) AS total
FROM options o
LEFT JOIN votes v
    USING (poll_id)
WHERE o.poll_id = %d
    AND v.option_id = o.option_id
ORDER BY total DESC, o.option_id ASC

A sample can be seen on sqlfiddle. I'd like to retrieve all the options/choices available for any poll_id and display the COUNT as 0, or NULL; if no user chose that particular option. Thus, for the fiddle example, the result would be:

option_id | option | total
1         |   A    |   3
2         |   B    |   1
3         |   C    |   0


texts: Combine column from multiple rows into one row in SQLITE

texts:
I have a sqlite-database with the schema

Articles
 Id
 Name

Tags
 Id
 Name

ArticlesTags
 Id
 ArticleId
 TagId

I´m trying to query for Articles.Name and a LIST of tags. Basically what I would want is:

Name1, (tag1,tag2,tag3)
Name2, (tag3,tag2)

Is this possible?
I´m lacking the language to explain what I actually want to do, what is it called?

I found Combine column from multiple rows into single row but it seems limited to SQLServer and the usage of builtin xml-features of that.


texts: stored proc to handle XML as input param for multiple record inserts

texts:
i have updated my proc from taking typical data types to take a single XML param so that i can call it once instead of multiple times for inserting multiple records.

here is a sample of my proc that works well...

ALTER PROCEDURE [dbo].[ins_Attachment_xml]
    @XML XML
AS
BEGIN

        DECLARE @messageId INT
        SET @messageId = (SELECT n.c.value('@MessageId','int') FROM @XML.nodes('/Attachment') n(c))

        SELECT
            rr.item.value('@FileboxId','varchar(MAX)') AS FileboxId,
            rr.item.value('@FileName','varchar(MAX)') AS FileName
        INTO #tmp
        FROM @XML.nodes('/Attachment/item') AS rr(item)

        INSERT INTO Attachment (MessageId,FileboxId,FileName)
        SELECT @messageId, T.FileboxId, T.FileName
        FROM #tmp T
END

exec ins_Attachment_xml
@XML =
'<Attachment MessageId="2">
  <item FileboxId="FB1"  FileName="notes.doc" />
  <item FileboxId="FB2"  FileName="W2.jpeg" />
  <item FileboxId="FB3"  FileName="2011 Taxes.pdf" />
</Attachment>'

now i need to change the XML param being passed in to this but dont know how to update the proc to make it work with the new XML structure. this is the new xml:

<MessageRecipient>
<MessageId>52</MessageId>

<Recipient>
        <RecipientTypeId>2</RecipientTypeId>
        <EmailAddress>dig.s@mail.com</EmailAddress>
        <FriendlyName>dig s</FriendlyName>
        <UFID>dig123</UFID>
</Recipient>

<Recipient>
    <RecipientTypeId>3</RecipientTypeId>
    <EmailAddress>ash.k@mail.com</EmailAddress>
    <FriendlyName>ash k</FriendlyName><UFID>ashish</UFID>
</Recipient>

<Recipient>
    <RecipientTypeId>4</RecipientTypeId>
    <EmailAddress>dee.v@mail.com</EmailAddress>
    <FriendlyName>dee v</FriendlyName>
    <UFID>dee123</UFID>
</Recipient>
</MessageRecipient>


texts: Copy all agent jobs to separate files based on date and keep five copies

texts:
Looking for a crazy script to run after database backup ...

Want to look at all agent jobs and if modified date is equal to today save it to a shared drive, if not skip. Also want to delete the oldest saved file if there are more than five.  Any help on any step would be greatly appreciated!


texts: Is it possible to limit connecting users to a schema from specific terminal or program?

texts:
I read about Oracle ACL, but on first sight, it doesn't look like it limits the access to a schema on terminal, program level. Is it even possible? I did experience such limitation once. I was connecting by sqlplus from unix box, the same box the application scripts were connecting from. Is it, perhaps, related to Oracle Wallet?

Goal:

I want to limit direct access to an application schema. I already enabled proxy access. However web application on localhost does access application schema, from the same host that I want to limit direct access through any IDE. Is it possible to limit connectivity to schema from the same machine, depending on what terminal, or program wants to connect?


texts:
I have a range of tabular data with two columns for dates Start Date and End Date and I want to pick up everything either Starts/Ends on coming Monday 03/05/21 or Starts/Ends on Sunday 09/05/21 and everything in between so typically a week of workload.
e.g.
Derived Monday Start Date:  03/05/2021
Derived Sunday End Date:    09/05/2021
Derived Monday End Date:    10/05/2021
I initially wrote below syntax to work around to choose every Monday since Report will run on every Thursday but the query not producing correct results, I understand it might be due to excessive use of ANDs & ORs -- I am pretty much new in SQL world so any help on this would be really appreciated.
WHERE
   StartDate >= CAST(GETDATE()+4 AS date) AND EndDate <= CAST(GETDATE()+14 AS date)
OR StartDate <= CAST(GETDATE()+4 AS date) AND EndDate >= CAST(GETDATE()+4 AS date)
OR StartDate >= CAST(GETDATE()+14 AS date) AND EndDate <= CAST(GETDATE()+14 AS date)

I also tried below but this formula doesn't up pick on-going work:
StartDate BETWEEN CAST(GETDATE()+4 AS Date) AND DATEADD(week, 2, CAST(GETDATE() AS Date))

ID  Start Date  End Date
1   20/04/2021  07/05/2021
2   05/06/2021  06/06/2021
3   04/05/2021  30/06/2021
4   20/05/2021  30/05/2021
5   05/04/2019  06/06/2021
6   24/06/2018  31/10/2023
7   05/05/2021  06/05/2021
8   07/05/2021  08/05/2021
9   27/11/2020  05/05/2021
10  09/05/2021  10/05/2021

I want to pick everything that is either starts or End on Monday OR  starts or End on Sunday and everything in between a week of range for example.
I want data for next week between 03/05/21 - 10/05/21 so the query should pick ID: 1, 3, 7, 8, 9, 10. I sadly can't use any fixed date as the data I want, is between Every Monday and Sunday.


texts: SQL Server Management Studio 2012 - horrible blue background

texts:
I've just installed SSMS 2012 and when I loaded it up I was presented with a nasty blue background - it looks so bad that I honestly thought something was broken, but apparently it's supposed to look like that!

Short of sending the SQL Server Design Team a set of vouchers to have their eyesight tested, is there anything that I can do to change this?  I've looked around and found a couple of other people whose sight has been similarly assaulted, but I cannot find a solution.


texts: calculate duration between intervals of 15 minute

texts:
so i've been trying to find an answer for a problem i have.
i have a client with call information table.
the table look  like this:

and he want me to create a new table that splits every record to hour quarters (0-15,15-30,30-45,45-60) and using the login time and duration of the call calculate for each row how many seconds in each hour quarter he was in the agentsate. so if he was in a talking state for 120 seconds(duration column) and he started talking on logindatetime of 14.3.18 11:44 i should add two columns to the new table for each quarter in the hour and what was the duration for it.
something like this

does anyone have an idea?
i thought about a cursor but i dont understand how to do it and it sounds like its going to have a big impact on performance.
the duration in seconds can be up to a day, not only minutes or seconds.

thanks in advanced.


texts:
I've been trying to optimize my table, but am yet unable to get the desired results.

CREATE TABLE IF NOT EXISTS `puzzles` (
  `puzzleID` int(7) NOT NULL,
  `puzzleMask` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `puzzleScore` int(7) NOT NULL,
  `time` int(11) NOT NULL DEFAULT '0',
  `hour` int(11) NOT NULL DEFAULT '0',
  `ip` varchar(20) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `Iip` int(11) unsigned NOT NULL DEFAULT '1',
  PRIMARY KEY (`puzzleID`, `puzzleMask`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1;

The combination of ID and Mask must be unique, whereas ID is a random integer and Mask a combination of numbers with a max length of 30. Time is the unix timestamp and hour is "time - (time % 3600)" (the hour in which the row was submitted). ip is the IP address of a server (about 20 servers) and Iip is INET_ATON( ip ).

These 20 servers submit puzzle information to this database. I would like to show live statistics of the submitted information (5-10 records per second).

For that, I use two main queries which need to be as optimal as possible:

Obtain total statistics

SELECT COUNT(*) AS total, MAX(puzzleScore) AS maxScore, hour FROM puzzles WHERE hour => #startHour# GROUP BY hour

Obtain server dependant statistics

SELECT COUNT(*) AS total, MAX(puzzleScore) AS maxScore, hour FROM puzzles WHERE hour => #startHour# AND Iip = INET_ATON("#IPAddress#") GROUP BY hour

My question is, what indexes to use? I've tried many things now, but with the database rapidly increasing the time to trial and error is over...
for example, do I need a seperate index for puzzleScore, since I want the max? or do I need to combine it in another index?

Also, is it true that using a primary key (puzzleID and puzzleMask) in combination with INSERT IGNORE INTO... is better for the performance, than using an id with autoincrement and first perform a SELECT to see if the record exists and than insert?

Thank you


texts: Is Oracle 12g free to download for learning purposes?

texts:
I am a software developer that supports and develops an application that connects to both Oracle databases and SQL Server databases.

I want to learn more about database administration.  Therefore I am planning to purchase SQL Server Developer edition and install it on my PC at home (it costs around: £60).

I am also planning to download and install Oracle 12C: http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html.  I believe it is completely free to do this after reading the license agreement (assuming that I do not use if for commercial purposes i.e. I use it for learning purposes only).  Is that correct?

I have read many questions on here stating that previous versions are free e.g. this one: Is Oracle free to download?.  However, I want to make sure that it is free? - the questions I have read are a few years old at least.

Also when I register on the Oracle website; what do I put as my job title etc.  Is this the company I am employed by? - bear in mind that I am not downloading it for them - I am downloading it for me.


texts:
I have a VIEW where I'm attempting to create an evolution script for, so I can add a column to it. That part works fine; column added just fine. However, the reverse does not work; remove that last added column fails with a ERROR:  cannot drop columns from view message. The problem is that this particular view has many references, both from and to, therefore I cannot just DROP CASCADE the darn thing!

Is there a reason why I cannot remove a newly added column from a given VIEW? Then, what can I do to accomplish this task?

(Note: the circumstances, here, are what they are, but I can very well see a similar situation, aka dropping a column from a view, in many other cases.)


texts: Sql Server consuming lot of memory Creating Bottleneck for my web application Hosted in the same server

texts:
HI I have a server with 64 GB Ram and I have assigned around 52GB to the SQl Server itself. However Even this has effected the  performance of the server and the web application hosted in the same server is performing poorly because of this, Can anyone suggest me a way to handle this situation
I can give you some of the major activities that can effect server
A)I Constantly import(dump) 5million plus records 3 hour , Compare it to the current 7 Million records and process the new ones.  I use lot of temporary tables for this process ... are these reasons for my bottle neck if so what can i do?
b) My web applications and database server are on the same server is this a bad practice?


texts: Inserting into three different tables, using foreign keys -- 500000x each

texts:
We're working on refactoring many of our offline (python) scripts.  I'm very new to any kind of SQL, but have been trying to learn as much as I can, especially in the performance field.

We have a script that loads a CSV from a client, that has user-defined fields in it, that must be matched to unique names (and eventually keys) in a separate table.

We write each row of the CSV to our DB in 4 different queries, to 4 different tables.  All four queries relate to the same row of data, and use the first's primary key in each.

The latter 3 use the primary key from the first insert, so I assume that's a constraint.

We're getting around 100-150 IOPs on our Amazon RDS instance, so it's painfully slow trying to do something like this.  We proved (well, somewhat), that the script is not the bottleneck.  I created another script that just ran a loop to 'insert' into a dummy table with no indexes, and it ran about as fast.

Is there anything we can do to optimize our current method?  I think there has to be, I couldn't imagine we really have to perform 2 million queries for our theoretical max-size CSV file of 500,000 rows.

I've ruled out (or think I have, I could be very, very wrong) a few options:

batch-inserts: Since each insert goes to a separate table, can we really batch insert?
stored procedure/functions: The headers are dynamic, so I don't think there's anything we can really do in that department.
Building a query through string concatenation: Mostly because I don't know even where to start with this, but I also think it would be impossible since we need the keys from the first insert.


texts:
heyy all, we want to move our current mysql 5.6 server to mysql-8 innodb cluster. for this, we are setting up a replication like 5.6 -> temporary 5.7 -> mysql-8 cluster. We already have the mysql-8 cluster ready, and is also receiving app traffic for other DBs which are present in it. We have replication setup from 5.6 -> 5.7. But, when I try to restore the mysqldump from mysql-5.7 to primary node of the cluster, it gives below error >>

ERROR 3911 (HY000) at line 24: Cannot update GTID_PURGED with the
Group Replication plugin running

some more background >>
mysql-5.6
It has 4 databases in it, say - DB1, DB2 , DB3, DB4 (All the DBs are getting app traffic, but we only want to migrate DB3 to mysql-8 cluster )
mysql-5.7
We enabled replication with mysql-5.6 and also set binlog_do_db=DB3, such that binlogs have only DB3 related entries and we can then setup replication from 5.7 to cluster for DB3
mysql-8 innodb cluster
It already has 3 DBs in it, which is receiving app traffic, say, DB10,DB11,DB12. We need to setup a replication from 5.7 for DB3
Any idea what can be done here? Please let me know if you need any more info on this. Thanks


texts: What would be the best way to design a table in Mysql that handles read items in an RSS feed reader?

texts:
I'm working on an RSS feed reader, it is mostly functionnal. But after some time beta testing it, I noticed that its very slow running some queries on a table that handles all the items that have been read. A user can read items, and then they don't appear anymore.

I have a table that handles each item that is read by a user.
Previously it was like this:

id (INT) PRIMARY
user_id (INT)
item_sid (VARCHAR)

My first beginner mistake was to use an id, instead of putting an index on item_sid. id is completly useless, and should not be used.
Also for some queries, to avoid joins, I think it is better if I put in a feed_id column, to grab every item from a specific feed.
The new table should look like that:

user_id (INT)
item_sid (VARCHAR)
feed_id (INT)

Here are some example of queries running on this table:

Get amount of items a user has read from a feed (count(item_sid) WHERE user_id = x and feed_id = x)
Check if an item has been read (WHERE user_id = x AND item_sid = x)
Delete an item that has been read
List all items that have been read by the user (SELECT item_sid WHERE user_id = x)

I have two questions about this:
1) What type of indexes should I put on this table, I have not a very good understand yet of how every indexes work, I'm not sure if I should use fulltext or not on the item_sid
2) Am I doing it the correct way, or is there something better I could do ?

Thank you


texts: On which fields to index? Fields queried (WHERE) or fields returned (SELECT)

texts:
I have a large MySQL database table (~1 million rows and growing) on an AWS RDS Medium instance:

mysql> describe clients;
+-----------------+---------------+------+-----+---------+----------------+
| Field           | Type          | Null | Key | Default | Extra          |
+-----------------+---------------+------+-----+---------+----------------+
| id              | int(11)       | NO   | PRI | NULL    | auto_increment |
| name            | varchar(500)  | YES  |     | NULL    |                |
| address         | varchar(500)  | YES  |     | NULL    |                |
| city            | varchar(200)  | YES  |     | NULL    |                |
| state           | varchar(100)  | YES  |     | NULL    |                |
| zip             | varchar(50)   | YES  |     | NULL    |                |
| country         | varchar(50)   | YES  |     | NULL    |                |
| phone           | varchar(20)   | YES  | UNI | NULL    |                |
| source          | varchar(20)   | YES  | MUL | NULL    |                |
| campaign        | varchar(200)  | YES  |     | NULL    |                |
| search_term     | varchar(200)  | YES  |     | NULL    |                |
| search_location | varchar(200)  | YES  |     | NULL    |                |
| added           | datetime      | YES  |     | NULL    |                |
| email           | varchar(150)  | YES  |     | NULL    |                |
| website         | varchar(150)  | YES  |     | NULL    |                |
| full_output     | varchar(5000) | YES  |     | NULL    |                |
| client          | varchar(50)   | YES  |     | NULL    |                |
| is_deleted      | int(2)        | YES  |     | 0       |                |
| is_valid        | int(2)        | YES  |     | 1       |                |
+-----------------+---------------+------+-----+---------+----------------+
19 rows in set (0.00 sec)

I often need to perform a variant of the following query:

SELECT name, zip FROM clients WHERE source IN ('Foo','foo','Bar','bar') AND added>'2013-11-25 13:00:00' limit 150000, 150000;

And the relevant EXPLAIN:

mysql> EXPLAIN SELECT name, zip FROM clients WHERE source IN ('Foo','foo','Bar','bar') AND added>'2013-11-25 13:00:00' limit 150000, 150000;
+----+-------------+------------+-------+---------------+--------+---------+------+---------+-------------+
| id | select_type | table      | type  | possible_keys | key    | key_len | ref  | rows    | Extra       |
+----+-------------+------------+-------+---------------+--------+---------+------+---------+-------------+
|  1 | SIMPLE      | clients    | range | source        | source | 63      | NULL | 1168144 | Using where |
+----+-------------+------------+-------+---------------+--------+---------+------+---------+-------------+
1 row in set (0.03 sec)

What optimizations should I be making? Should I add indexes on the name and zip fields, or on the added and source fields?


texts: SQL Server 2008 CPU Troubleshooting on 6 identical servers

texts:
We offer an API service that rotates between 6 identical SQL Servers on a "round robin" rotation. I'm trying to troubleshoot some high CPU usage on one of the 6 servers but have hit a wall.

All of the servers use the exact same Stored Proc query plans and are rotated evenly. One server ("Server 1") is hitting around 80% CPU whereas the rest are sitting at around 40% CPU. 3 of the servers are waiting on 96 major windows updates and the other 3 are waiting on 30. The server in question is one of the servers waiting on 96 windows updates.

However, a few weeks ago one of the servers that was waiting on 30 windows updates had the same problem. This server was again sitting at around 80% CPU and the rest at 40%. It's as if the high CPU usage is shifted between servers at random.

I've run multiple traces on Server 1 and it appears to behave in the exact same way as the other servers. Reindexing and Update Stats are run at the same time on every server and produce the same query plan each time across all servers. I'm running out of ideas on where to look, can anyone possibly point me in the right directly?


texts: MySQL deleting lots of rows by id - where in or loop over each row in transaction

texts:
I have a delete query which is potentially going to delete tens of thousands of rows.

Currently it's taking quite a long time in the more extreme case (over 100000 rows to delete) using this:

DELETE FROM tablename WHERE id IN (?, ?, ?...)

However, I was wondering whether it might be better to create a transaction and use php to loop through each of the deletes.

Something like...

START TRANSACTION;
DELETE FROM tablename WHERE id=?;
DELETE FROM tablename WHERE id=?;
DELETE FROM tablename WHERE id=?;
.....
COMMIT;

I hope this makes sense... I'm not the best at using the correct terminology.

Thanks


texts: Can SQL Server use multiple indexes for the same query?

texts:
This question might have been asked before, but I tried searching with this term "can SQL Server use two indexes for same query" and it yielded no results.

Assume you have the below query:

select col1a,colb
from #ab
where col1a in (
Select col1a from #ab
group by col1a
having count (distinct colb)>1)

And you have the below index:

create index nci on #ab(colb)
include(col1a)

This is the execution plan, and Paste The Plan link as well.

Bottom part of plan is for below query:

Select col1a from #ab
group by col1a
having count (distinct colb)>1

Table scan/top part of plan is for below query:

select col1a,colb
from #ab
where col1a in (

Question:

If I have an index like below:

create index nic_toppart on #ab(Col1a,colb)

Will it be chosen for top part of plan?

In summary I meant:

Can SQL Server use index nci for grouping part/bottom part of plan  and use index nic_toppart for top part of the plan

Is this possible?

My tests show it can only choose one.

Below is test data. Please let me know if I am unclear.

create table #ab
(
col1a int,
colb char(2)
)

insert into #ab
values
(1,'a'),
(1,'a'),
(1,'a'),
(2,'b'),
(2,'c'),
(2,'c')

select col1a,colb
from #ab
where col1a in (
Select col1a from #ab
group by col1a
having count (distinct colb)>1)

create index nci on #ab(colb)
include(col1a)

create index nci_p on #ab(col1a,colb)


texts: Make a boolean false after every n number of minutes in MySQL

texts: I have a table with a boolean value, if that value is not explicitly set to anything in the database for a few minutes by the back-end (django) then it must automatically be set to false. How do I do that in MySQL efficiently?


texts: How to programatically change the login audit settings?

texts:
Is it possible to change the login auditing settings of a SQL Server database by script, rather than through the Server Properties Security setting in the SSMS UI?

I need to programatically change the setting to "None", and then back to "Failed logins only"


texts:
In the following error message, the reason given is "Invalid Protection Option".  What does that indicate?

  SQL Server detected a logical consistency-based I/O error: invalid protection option

For the purposes of this question, I don't need to know anything about "how to run DBCC", or "check for corruption".  I got that.  I'm just curious about the "root cause" piece, and what could possibly cause this flavor of the logical consistency-based I/O error.


texts: Mongodb replica set primary and secondary node conf fail when one slave is down

texts:
In my env, I had created MongoDB replica set conf for high availability so one server fails should be handled by other also in my conf I have 3 DB server and 1 arbiter too but,

my slave's server fails due to disk utilization by this, my master node also fails to provide data to api's which create disrupts my whole system.

So, is this event occurs due to configuration fail or its mongo property? Or some conf other than replica set creation is required to prevent this??

currently, I am using MongoDB shell version v4.0.3

Followed this doc some of this command used in the replica set setup

https://docs.mongodb.com/manual/tutorial/deploy-replica-set/


texts: how to find out the log backups that I need to restore in order to bring log shipping from standby to online?

texts:
I have log shipping set up from server1 to server2.

on server1 transaction logs backups are taken every 15 min to a network share folder.

these transaction log backups are then copied (afte 1 hour) to server2 where they are restored.

question:
in the event that I need to bring any of these databases from standby to online, how do I find out what transaction logs need to be restored for each database?

I found these 2 good links:

Your poor man’s SQL Server log shipping-PowerShell version

Automate Restoration of Log Shipping Databases for Failover in SQL Server

this second one did not work for me:

on msdb:

 SELECT destination_dir, destination_database, last_file_loaded
 FROM log_shipping_plans a INNER JOIN log_shipping_plan_databases b
 ON a.plan_id=b.plan_id


texts: Getting repetition of same values multiple times while combining two queries

texts:
I wrote two queries in MySQL Workbench and combined them into a single query:

Select *
from
  (
    (select division.name,
            date(meter_data.reading_date),
            sum(meter_data.meter_delta) 'Gas Consumption (m3)'
     from meter, meter_data, division
     where meter.meter_id = meter_data.meter_id
       and meter.division_id = division.division_id
       and Date(meter_data.reading_date) between '2018-04-01' and '2018-05-03'
     group by date(meter_data.reading_date)
    ) as table1,

    (Select Date(receipt.receipt_date),
            sum(case receipt_item.item_name
                    when 'Gas' then receipt_item.item_amount else 0
                end) 'Gas Purchase'
     from receipt_item, receipt
     where receipt.receipt_id = receipt_item.receipt_id
       and Date(receipt.receipt_date) between '2018-04-01' and '2018-05-03'
     group by Date(receipt.receipt_date)
    ) as table2
  );

When I execute the queries separately, each query returns the exactly 33 rows, but combined it returns 1089 rows. Why?


texts: SQL 2008 R2 Merge Replication - Clearing Skipped Schema Changes

texts:
A developer made a change to our production database that is using merge replication to a table (Activity).  That table has a Foreign constraint that points to a table that is not part of replication and does not exist at the replicated database. This broke replication.  To get replication up and running, I used the sp_markpendingschemachange procedure to skip the schema changes.  This worked, but now I am left with the records still in the sysmergeschemaarticles table, just marked skipped.  At this point, I want to get rid of the records so they get executed in the future.

My solution is to perform a snapshot (at a less busy time) assuming that the this will clear the sysmergeschemaarticles table of any ddl changes but have not found any supporting documentation to support this.  Anyone have an experience with this type of situation that can support this solution or propose a new better one?

Thanks!


texts: How do I use 'N' in my prepared SELECT statement

texts:
I'm connecting to an SQL Server 2008 database using pdo and the ODBC Driver 13 for SQL Server.

I'm trying to run the following query which searches the database for Chinese characters:

SELECT TOP (10) ... WHERE (sItemName LIKE N:term1)

        $text = "%简况%";
            $query = $this->DBH->prepare($sql);
            $query->bindParam(':term1', $text, PDO::PARAM_STR );
        $query->execute();

This query returns results with random characters so it looks like the Character encoding is somehow incorrect.

I was able to return the same results by running this query directly on the DB using Heidi:

SELECT TOP (10) ... WHERE (sItemName LIKE '%简况%')

Changing the query to the following by adding an 'N' before the string returned the correct results:

SELECT TOP (10) ... WHERE (sItemName LIKE N'%简况%')

But when I try to do the same with my prepared statement as shown below I get a syntax error:

SELECT TOP (10) ... WHERE (CI.sItemName LIKE N:term1)

        $text = "%简况%";
            $query = $this->DBH->prepare($sql);
            $query->bindParam(':term1', $text, PDO::PARAM_STR );
        $query->execute();

Here's the error:

  Syntax error or access violation: 102 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near 'N:'.

So my question is how do I use 'N' in my prepared statement without getting an error. I'm not a DBO by trade so apologies if I've left anything out!

This returns the expected result:

$seach_term = (string)'简况';
$text = '%'.$seach_term.'%';
$query = $this->DBH->prepare("SELECT * FROM Articles WHERE sHeadline LIKE :term");
$query->bindParam(':term', $text, PDO::PARAM_STR);

But using the PARAM_STR_NATL constant doesn't return anything:

$query->bindParam(':term', $text, PDO::PARAM_STR_NATL);


texts: insert/update from web server or database

texts:
I have a data-model on the front-end that I want to insert/update in the database which of the following approaches (or neither!) is considered best practice:

# in webserver

some_model = {json}

some_url_executed('/', db_exec("select update_model(some_model);")

-- in database
create function update_model(json)

/* multiple tables to update for the model */
insert/update tables from json data

or option 2:

# in webserver

some_model = {json};

db_exec("begin transaction");
foreach field in some_model {
    db_exec("update some_table set value = some_model.value
}
dbexec("commit or rollback");

My data-model, if it matters, looks like:

{ fld1: {},
  ...
  fld5: { 'sub-fields' : { 'nested':'values' } }
  fld6: [ {...}, {...} ]
}

I like the idea of letting the database handle this because I can grant permissions on that function to the user/group in the database (they shouldn't be allowed to navigate to the url anyway) but, yea - could use some guidance.

Back to the question, is it a bad idea to loop through a json object and insert/update from a stored procedure? Where does this logic belong? I'm using postgresql 9.5.


texts: How to Find Pages/Sec from sys.dm_os_performance_counters

texts:
How do we find pages/sec from dm_os_performance_counters DMV, I do not see it here:

SELECT * FROM sys.dm_os_performance_counters
where counter_name like '%page%'

Monitor Memory Usage

  Pages/sec Definition: The Pages/sec counter indicates the number of
  pages that either were retrieved from disk due to hard page faults or
  written to disk to free space in the working set due to page faults.


texts: Is this considered a "light load" trace to run on SQL Server Profiler

texts:
This is my first experience of using SQL Server (2012 Enterprise Edition) Profiler. I've set up a trace to log when views are being accessed on a particular database.

I'm running Profiler on my personal PC, connecting to our Live/Production Server and letting the trace run.

It's only monitoring a single database, and writes events when views are accessed (of which there are approx. 300 potential Views in total).

I have reservations that leaving Profiler to run for long periods of time may affect server performance (especially as I'm querying a Live/Production server).

Is what I've proposed considered a fairly "light load" to be running?