texts: How can filesystem storage (for blobs, images, etc.) be integrated with (traditional) database storage (e.g. MySQL/Postgres)?

texts:
If I use my database for relational data and opt to store large binary blobs in the filesystem, how can I integrate the two? How does this process work?

I know that the database will need to store a directory path/link to the corresponding blob, but can someone please enlighten me on the specifics?  For example, I think I should have some shell scripts to first perform a virus check on the binary in a secure /tmp location and then move it to the actual storage location and update the path in the db. But to do this from the operating system will require a script to check periodically if there has been a change in a directory and then fire off actions. Is it possible to run a shell script from within a DB (I know the reverse is possible)?

Or are there better tools to do this sort of thing (integrate with filesystem storage) in MySQL/Postgres?


texts:
On my server I had files from 2012, 2013, ... 2016 which were .frm, .MYD, .MYI. I deleted files which were older than 180 days. I left the files from this year only.

Three days later database was gone and phpMyAdmin showed:

  #2002 - The server is not responding (or the local MySQL server's socket is not correctly configured)

Could deletion of these old MySQL files have been the problem here?


texts: Inventory database maintaining remaining qty of an item

texts:
In my inventory database there is a sale table.

This table contains fields sold qty and balance qty of an item after each sale.

While implementing 'edit sale' feature, I need to update the sold qty field of a sale done in the past. This will affect the balance qty field of all the records past that sale of that particular item. I would need to run a query to update all the sale records past this sale. Assuming the total no. of records in this table < 10 K, is this a good design?

Should I calculate balance qty on the fly when needed for report/visualization and not storing it in the sale table?

Sale table structure
====================
Sale Id  | Item ID |  Sold QTY | Balance QTY    | Date
1          1            10            1000        2018-01-19
2          1            50             950        2018-01-20
3          1           100             850        2018-01-21
4          1           200             650        2018-01-22
...

This kind of table is useful for a report where I need to show how the items sold over a period.
Now I need to give edit feature where a user can edit a past sale for example (sale id = 2) He can change the sold qty to 100 from 50.


texts: Want to delete then insert with single statement using a CTE in Postgres

texts:
I want to delete then insert with single statement using a CTE in Postgres.  We could use an explicit transaction and 2 statements, but want to avoid that if possible.  I don't know if there will be zero or 1+ rows deleted, so I don't think I can use a single WHERE EXISTS or WHERE NOT EXISTS to ensure that the delete statement in the CTE runs first.  Here's something like what I envision:

WITH deletions AS (
   DELETE FROM foo WHERE a = 'abc' and b = 2
)
INSERT INTO FOO (a, b, c) VALUES ('abc', 2, 'new value')

but I need a way to force the CTE to run first.  There's no pk on the table that would create issues with attempting to update the same record twice in the same transaction.


texts: What does Reinitialize All Subscriptions actually do to the databases

texts:
I tried to find the answer and came across these MSDN pages but they don't actually explain what happens really:

http://msdn.microsoft.com/en-us/library/ms152771.aspx
http://msdn.microsoft.com/en-us/library/ms189701.aspx

Step by step what does the Reinitialize All Subscriptions option actually do to the database in SQL Server Management Studio 2008?


texts: Can connect to SQL Server through tsql but not from the bsqldb

texts:
After installing FreeTDS I was able to connect to our SQL Server using tsql but not yet through bsqldb.
With bsqldb it's trying to connect to the master even though the db was specified to  testDB.

serverName is setup in /etc/freetds.conf

[serverName]
        host = database.windows.net
        port = 1433
        tds version = 8.0

Using tsql:

[gpadmin@mdw ~]$ /usr/bin/tsql -S serverName -U user -D testDB -P password

works fine

With bsqldb it's trying to connect to the master even though the db was specified to  testDB.

[gpadmin@mdw ~]$ /usr/bin/bsqldb -U user -P password -S serverName \
                                 -D testDB -i tag.sql -o tag.csv

Msg 4060, Level 11, State 1
Server 'fj5j2jtt5k', Line 1
        Cannot open database "master" requested by the login. The login failed.
bsqldb: error: severity 11 > 10, exiting


texts: Unable to trace the transaction log space

texts:
im new to sql server. One of the instance encounters a scenario it shows the transaction log full, and the database need to perform backup in order to solve the issue.

I want to replicate the scenario again to show there is potential that the transaction log would go full, and suggest my client to increase the transaction log space and grow factor. (The database is using Full recovery mode)

However, the scenario is unable to replicate whatever how demanding query executed (40000 deletion and then 40000 insertion, repeatedly), i found that the transaction log occupied space is just trembling by querying it

DBCC SQLPERF(LOGSPACE)

I expects the transaction log will grow continuously when executing bulk insertion and deletion (no backup or checkpoint is done between the query). But the fact is the log space isn't really behaved as expected.

I would like to know

when the db throw exception transaction log is full - is it due to the out of memory of harddisk storage? or is it due to log size is over the configured size?
why the operations of bulk insertion & deletion didn't reduce the log space, but its size behaves trembling(up and down in stead of continuous increase)?


texts: Aggregate discarding values in one column that haven't a match in another column

texts:
Say, I have a table representing colored and labeled items inside numbered boxes.
Each box can not contain more than one item with a particular label, but items with the same label (and the same or a different color) may be unique in other boxes.

Oversimplifying, and using PostgreSQL, we can take the following table:

CREATE TABLE items (
    label character varying,
    color character varying,
    box_number integer
);
INSERT INTO items VALUES
  ('a','red',1),
  ('b','blue',1),
  ('c','blue',1),
  ('a','red',2),
  ('c','green',2),
  ('d','blue',2),
  ('b','red',3),
  ('d','green',3);

I want to know the label and the color of all the items inside the box number 3, but also all the box numbers where an item with the same label can be found. In other words, I'm trying to:

SELECT label, boxes
FROM (
  SELECT label, array_agg(DISTINCT box_number) AS boxes
  FROM items
  GROUP BY label
) AS sub1
WHERE 3 = ANY(boxes);

But I also need to return the color column, showing only the color of the item inside the box number 3.

For the example data, the output should be this:

label | color | boxes
------+-------+------
b     | red   | 1,3
d     | green | 2,3

Here's an SQL Fiddle for the example.


texts: Is it necessary for rownum = 1 with Query.createQuery().getSingleResult()?

texts:
I'm a newie and I'm not really sure if MIN(My_Date) returns just one row if 2 or more dates are identical.

Reference is a FOREIGN KEY.

so to limit query:

SELECT MIN(Shipping_Date)
FROM Com_Order
WHERE Reference = 'X' and rownum = 1
GROUP BY Reference;

This second query seems to have same results just one value..

SELECT MIN(Shipping_Date)
FROM Com_Order
WHERE Reference = 'X';

I'm using JPA, so is it redundant to use rownum = 1 and GROUP BY Reference if I use Query.creatQuery().getSingleResult()? Are the 2 queries the same?

Ty.


texts: INSERT-ing to a table if the the data does not already exits - how do indexes peform in this scenario?

texts:
When you are in the position of inserting data into a table if the data does not already exist ...what is the best way to go with indexes?
I'm specifically thinking of SQL Server here but I assume the situation would be the same on other platforms.
On the one hand you are inserting new data into the table that will need to be added to the index at some performance cost.
On the other hand you are reading the table to see if the data already exists, so the index will help performance.
My theory is that having an index in this situation would probably be better than to not have one as (from my limited experience) I have always found un-indexed tables to perform terribly and insertions to indexed tables to be 'a bit slower' (obviously the type of index will impact on this).
My simple testing also suggests that indexing the table (a clustered index on 2 of the 7 columns) does improve performance, but then the table I tested on already contained 30M rows to start with (40-50M more are added by the insert).
I was just wondering if anyone can offer any insight into this scenario to help my learning.
Thanks


texts: Postgres \copy from CSV: how to format range variables?

texts:
Consider a Postgres table containing a range variable:

CREATE TABLE my_table (t_range tsrange, field1 text);

If I want to manually insert a row via psql, I can run:

INSERT INTO my_table (t_range, field1) VALUES ('[2014-06-02 07:00:00,infinity)'::tsrange, 'sometext');

Instead of inserting rows manually, I want to perform a bulk insert from a CSV file. I created a CSV file called my.csv using a similar format (using pipe as the delimiter to avoid having to escape the comma in the tsrange variable, and removing the explicit quote characters) with some content:

t_range|field1
'[2014-06-02 07:00:00,infinity)'::tsrange|'sometext1'
'[2014-06-02 07:00:00,infinity)'::tsrange|'sometext2'

Then from psql I ran

\copy my_table FROM 'my.csv' DELIMITER '|' QUOTE '''' CSV HEADER;

but this failed with the error

ERROR:  malformed range literal: "[2014-06-02 07:00:00,infinity)::tsrange"
DETAIL:  Junk after right parenthesis or bracket.
CONTEXT:  COPY my_table, line 2, column t_range: "[2014-06-02 07:00:00,infinity)::tsrange"

How should I format a CSV file with range variables for use with \copy?


texts: How to write a complex logic in SQL that should work two main cases (unfiltered and filtered by one dimension)?

texts:
This question is slightly related to this one.
I am dealing with a legacy business logic that computes several DocumentXUser security tables. This logic is very complex (a single query with lots of inner queries). I have managed to break it down to avoid a large and hard to manage query.
Currently, there are two issues I am trying to solve:

maintainability - any security-related requirement is hard to implement because of the complexity.
performance - the last change made the computation to take longer by about 50%

Until the security is rewritten (this requires more time than the team has right now), I have to support the following scenarios:

compute the security for all documents
compute the security for all users and a single document
compute the security for all documents and a single user

One way to achieve this is to provide two parameters (@UserId INT = NULL and @DocumentId INT = NULL) for the stored procedure and use them in WHERE clauses. Queries would look like the following:
SELECT ...
FROM SomeTable X
WHERE (@UserId IS NULL OR X.UserId = @UserId)
    AND (@DocumentId IS NULL OR X.DocumentId = @DocumentId)

Another way is to use dynamic queries (sp_executesql) which allows me to append the WHERE clause only when necessary. This allows to execute exactly what I want (no ORs), but makes the stored procedure look messier and I loose all the intellisense and query syntax checking.
Which is the recommended way if performance is what I want to optimize for?


texts: Are there differences between which AlwaysOn AG server (Primary or Secondary Replica) I store my backups and retention maintenance plans on?

texts:
I have an AlwaysOn AG system in place between two servers. Server A hosts the primary replica, Server B hosts the secondary replica.

Are there any differences if my Full & Transaction Log Backups are stored on Server A vs Server B?

Are there any differences if my backup retention maintenance plan and job is ran on Server A vs Server B (regardless of where the backups live)?

My current setup is Server A has a SQL Agent job that runs a maintenance plan on Server B to back up my databases to Server B. In that same job on Server A there's a subsequent step that runs a maintenance plan on Server A that cleans up old backups from Server B.


texts: Transfer of large database from 1 server to another

texts:
I am in the process of setting up a test environment on a server in a different location to the LIVE server.

I need to make a copy of the SQL Server database and move it across.

I want to know, if I were to Zip the Data and Log files and then split the zip file into smaller files for faster FTP. Would this damage or alter the data alignment in any way?


texts: Is it possible to clean up duplicate entries with just SQL?

texts:
I accidentally introduced a bug in my software that caused it to create duplicate entries. Now I have a lot of duplicate entries, the total rows of that table is around 111 million.

This is my table structure for that table.

CREATE TABLE `graph_player_ranked` (
  `player_id` int(11) NOT NULL,
  `season_id` int(11) NOT NULL,
  `playlist_id` int(11) NOT NULL,
  `timestamp` datetime NOT NULL,
  `matches_played` int(11) DEFAULT NULL,
  `rank_points` int(11) DEFAULT NULL,
  `tier` int(11) DEFAULT NULL,
  `division` int(11) DEFAULT NULL,
  `no_update_at` datetime NOT NULL,
  PRIMARY KEY (`player_id`,`season_id`,`playlist_id`,`timestamp`),
  KEY `player_id` (`player_id`,`playlist_id`,`timestamp`),
  KEY `idx_timestamp` (`timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Please don't comment about the possibly bad structure, I am switching to a new structure but I have to fix this issue first. However, should I remove timestamp from the PK?

The idea was to only add a new row when "rank_points" OR "tier" OR "division" changed OR "matches_played" increased. At some time I accidentally made it create a new row when "matches_played" was the same as the previous row or larger.

Example of duplicate data:

I want to remove all duplicate entries with just one row. So for the above image that would be:

Remove all of the rows in a red rectangle
Grab "timestamp" from the first row in the rectangle.
Grab "no_update_at" timestamp from the last row in the rectangle.
Place one row back with all previous data and the above two timestamps.

I have no idea how to achieve this, the only solution I can think of is creating an external tool to do this. But that would probably take very long to process all rows.

Edit: Sample data for two players can be found here https://dl.dropboxusercontent.com/u/184110260/sample_data/graph_player_ranked.sql


texts:
I need to add another column from another table i.e. CusSorMaster+.SalesOrder.The join is on where dbo.SorMaster.SalesOrder = dbo.CusSorMaster+.SalesOrder. This wasn't written by me and I need to manipulate it to include the other column.When I join the table with the column I don't get the same rows as my original query which I want. Please assist and feel free to ask any questions.
SELECT RIGHT(dbo.SorMaster.SalesOrder,6) AS SalesOrder,
       dbo.SorMaster.CustomerPoNumber,
       dbo.SorDetail.SalesOrderLine,
       dbo.SorDetail.MStockCode,
       dbo.SorDetail.MStockDes,
       dbo.SorDetail.MOrderQty,
       dbo.SorDetail.MCusSupStkCode,
       dbo.SorDetail.LineType,
       dbo.SorMaster.OrderStatus,
       dbo.[CusSorDetailMerch+].DuparJobNumber,
       dbo.SorMaster.Customer,
       dbo.SorDetail.MShipQty,
       dbo.SorDetail.MBackOrderQty,
       CASE
           WHEN
       RIGHT(dbo.SorMaster.SalesOrder,6) = '211125'
           THEN dbo.SorDetail.MOrderQty
           ELSE dbo.SorDetail.MShipQty + dbo.SorDetail.MBackOrderQty
       END AS ShipPlusBackorderQty,
       dbo.InvAltStock.AltStockCode,
       RIGHT(STUFF(DATEPART(year,GETDATE()),1,0,'0'),2) + RIGHT(STUFF(DATEPART(week,GETDATE()),1,0,'0'),2) AS YearWeekNo,
       'B00ABD' + RIGHT(STUFF(DATEPART(year,GETDATE()),1,0,'0'),2) + RIGHT(STUFF(DATEPART(week,GETDATE()),1,0,'0'),2) + '' AS SerialPrefix,
       dbo.[CusSorDetailMerch+].InvoiceNumber
  FROM dbo.SorMaster WITH(NOLOCK)
 INNER JOIN dbo.SorDetail WITH(NOLOCK) ON dbo.SorMaster.SalesOrder = dbo.SorDetail.SalesOrder
 LEFT OUTER JOIN dbo.InvAltStock ON dbo.SorDetail.MStockCode = dbo.InvAltStock.StockCode
 LEFT OUTER JOIN dbo.[CusSorDetailMerch+] ON dbo.SorDetail.SalesOrder = dbo.[CusSorDetailMerch+].SalesOrder
                                                      AND dbo.SorDetail.SalesOrderLine = dbo.[CusSorDetailMerch+].SalesOrderInitLine
 WHERE(
       dbo.SorDetail.LineType = '1'
       OR
       dbo.SorDetail.LineType = '7')
      AND (
       dbo.SorMaster.OrderStatus <> '9')
      AND (
       dbo.SorMaster.OrderStatus <> '\')
      AND (
       dbo.SorMaster.OrderStatus <> '*')
      AND (
       dbo.[CusSorDetailMerch+].InvoiceNumber = ''
       OR
       dbo.[CusSorDetailMerch+].InvoiceNumber = '000000000000000'
       OR dbo.[CusSorDetailMerch+].InvoiceNumber IS NULL);


texts: I have a MongoDB database with ~100GB data size. I ran a test with 300 threads and a total 10,000 'read' queries. I notice on the MMS lock% it is showing 0.6 ~ 1% of write lock. My question is, the queries are purely 'read', why is it showing write lock?


texts:
Im looking for a tool that could help me and my team monitor our MySQL Replication.

I would love for a tool that could alert me if

Seconds_Behind_Master passes a threshold
Slave_SQL_running is false
Slave_IO_running is false

If not alerting, then maybe just get a quick view of these parameters.

I am also ready for paid solutions.

Any ideas on such service/tool?


texts: Can the Chase test algorithm only modify attributes on the right side of a functional dependency?

texts:
I'm attempting to apply the Chase Algorithm to determine if a decomposition is a lossless join decomposition.

Functional Dependencies = {A → C, B → D, C → D, DE → C, CE → A}

Here is my initial matrix:

Can I apply DE → C to remove the subscript from e on the first two rows?  Since all c1's are the same, then this means that e1 and e2 must be equivalent to e, correct?

At this point, I would be able to set all c1's equal to c, correct?  (Since, again, DE → C.)


texts: PostgreSQL: Replace schema in db1 with schema from db2

texts:
Is there a way to replace data in a schema in db1 with data in a schema in db2, given that the schemas are identical (only the data differs)? An alternative would be to replace the whole schema? I would like to keep the name of the schema in db1.

I've searched the documentation but can't find any solution. Any ideas?


texts: How to suppress an informational raiserror message

texts:
I have a setup script that calls sp_configure a few times to set up some system variables, it works fine but I have a few of the following statement

  Configuration option '%ls' changed from %ld to %ld. Run the RECONFIGURE statement to install.

with the variables filled in accordingly. Internally I can see sp_configure is calling

raiserror(15457,-1,-1, @configname, @prevvalue, @configvalue) with log

Is there anything I can do to prevent those informational messages (which are useless to the script, I call RECONFIGURE from inside the script) from displaying in the messages window in MSSMS when a user runs the script?


texts: Any way to speed up my queries in MariaDB with tables less than a 4 GB in size?

texts:
I'm doing some text mining work and a bit frustrated that queries are taking 6 to 10 minutes to run.

My workstation has a 3.5ghz Xeon, 32GB of RAM, and 2 SSD as RAID 0.

Is it possible to do something like load the table into memory and then run the queries? For example, is it possible to insert a table into the MEMORY storage engine and run queries against that, or is that stupid/crazy/worthless?

UPDATE: With a bit more additional searching and reading, it looks like there is no need to use the MEMORY story engine because InnoDB can load the tables into memory.

I changed the following in my.cnf:

increasing the innodb_buffer_pool_size to 16G
adding innodb_flush_method=O_DIRECT

This reduced a query that took several minutes to 38 seconds.

Also, compressing the tables appears to help, and adding the following to my.cnf:

innodb_file_per_table=1
innodb_file_format=Barracuda

This reduced the query time to 31 seconds.

I'm still open to additional advice.


texts: LibreOffice Base Brings in all visible databases as list of tables

texts:
I'm trying to connect to a singer database through LibreOffice Base.  The connection goes fine and successful.  However, when selecting tables, it's showing a list of all the visible Databases in the list.

The list includes mysql, performance_scheme, phpmyadmin, and others which are unrelated to the database in question could be dangerous in the wrong environment.

I have recreated deleted and recreated the database using LibreOffice Base' Database Wizard numerous times.  I'm performing these steps to do it again.

Steps for Connecting to the Mysql Database

From LibreOffice -> File -> New -> (Database Wizard) -> (tick) Connect to an exiting database -> (select) MySOL -> (click) Next -> (tick) Connect directly

Then I fill out the form with:

Database Names: test
Server: localhost

Then I click Next -> (I enter username and check Password required and click) Test Connection -> (The results is successful). -> OK

(click) Next -> (yes register, open for editing , click) Finish (click) Save

At this point, I'm in configuration with a list of all the visible databases listed as tables with dropdowns.  The tables for each of the databases on the service can be seen by clicking to expand the dropdowns associated with each database.

Can someone tell me how to access a single database, not all of them, whereas the tables will include only the list of tables that are in the specific database?

Is this a bug with the new LibreOffice?

My Operating system and version:

OS: Ubuntu 18.04.2 LTS
LibreOffiice: Version: 6.0.7.3
MySQL Connector: Built-in - version 1.0.2


texts: Mongoexport and mongodump disk usage rationale

texts:
I just had an interesting experience with the mongoexport tool and came out with a doubt about the exporting process' disk usage.

I was trying to dump a 50 MB collection to a disk that had 3 GB free disk space. When the dump reached 101 records, it halted and eventually the connection timed out. You can read more about it here.

User @priyanka-kariya suggested I checked disk space, then I freed a total of 13 GB and the dump ran ok again.

So I wondered, how is disk space used in order to complete a dump process?

*Both mongoexport and mongod are version 4.0.2.

EDIT

@Stennie suggested I checked uncompressed data size, so db.requests_PRD.stats() returns:

{
    "ns" : "bubeIO.requests_PRD",
    "size" : 52721003,
    "count" : 60196,
    "avgObjSize" : 875,
    "storageSize" : 15486976,
    "capped" : false,
    [...]
}

The size is actually 50.3 MiB, which is less than I initially thought.


texts: If I have a plain-text field on a web form (not a file upload) inserting its content into my table, would it be wrong for me to set a mime type 'text' to it, or would it be safer?


texts: Workflow for SQL Server + Azure SQL development?

texts:
Lets assume the following:

5 devs all working on laptops
Each develops/debugs on Azure compute emulator (code) and SQL Server (db) - both running locally on their laptops.

I'd like to have

These 5 SQL 'Servers' need to sync with each other
BONUS: If the 5 SQL servers can also sync with (non-production) Azure SQL database!

How can I set this up? The way we're setup, all dev's eventually diverge from the 'reference' database (both schema and data) and pruning it is a PITA. I'm hoping there are some tools to alleviate this issue.


texts: AlwaysOn High Availability doesn't work , can't activate it

texts:
I am trying to activate the high availability
on my SQL server and getting below message:

SQL server version: Microsoft SQL Server 2017 (RTM) - 14.0.1000.169
(X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation
Express Edition (64-bit) on Windows Server 2012 R2 Datacenter 6.3
 (Build 9600: ) (Hypervisor)
Operating System: windows server 2012.

Appreciate any help.


texts: MySQL 5.5.58 Installing Multiple Instances on RHEL7

texts:
I am Avinash.

I am willing to install an additional MySQL instance on a UAT server for testing purposes. I am using MySQL Community Server 5.5.58 installed on RHEL7.2.

I am going through the MySQL Docs https://dev.mysql.com/doc/refman/5.5/en/multiple-servers.html , which tells me that I would require certain unique operating parameters such port, socket, pid, datadir and log files related parameters. To achieve better performance, it also recommends to use different tmpdir. This is well understood.

I can either use different binaries or the same binary for this purpose.

If I am using same binary, I'll have to use mysqld_multi and use the same config file. If I use mysqld_multi, I'll have to put different sections for mysqld daemon such as mysqld1, mysqld2 and so on. All using unique values for above mentioned operating parameters.

Please help me out here. Am I correct about mysqld_multi?

My second question is about bind-address variable.
https://dev.mysql.com/doc/refman/5.5/en/server-options.html#option_mysqld_bind-address
Here, it explains that "The MySQL server listens on a single network socket for TCP/IP connections. This socket is bound to a single address, but it is possible for an address to map onto multiple network interfaces."

My question is that will I need an additional NIC and an additional IP address for the second MySQL instance?

For the existing instance, I have disabled the bind-address option.
Do I need to add the bind-addresses for both the instances.

Also, the second instance will be used only by me (for now).

Thanking you.


texts: Replicating PostgreSQL data into Citus/Greenplum?

texts:
I need to integrate data from 3 different PostgreSQL databases (OLTP application backends) in a data warehouse. For the data warehouse itself I consider using Citus or Greenplum. There is a requirement that the data from applications has to be synced with the data warehouse as close to real time as possible (everything above 3-5 minutes delay is unacceptable, real time replication would be the best). In this regard I have the following questions:

Will Postgres logical replication work with Citus? Citus is a Postgres extension, can you treat a Citus cluster as an ordinary Postgres database? If yes, then logical replication should theoretically work, but how does it deal with distributed tables?
Greenplum is a Postgres fork, so will Postgres logical replication work with it at all? I have also read that Greenplum is not optimized for OLTP workloads, does that mean it will break when I try to ingest OLTP data into it?
If logical replication does not work with Citus/Greenplum, then how to stream data from Postgres? Do I need to stream logical-level WAL into Kafka and then write custom logic for translating it into SQL statements on the target database? Are there any tools for that?

Bonus question: does anyone have experience with both Citus and Greenplum, especially with their SQL limitations? I know that Citus does not fully support correlated subqueries and recursive CTEs, does Greenplum have any similar limitations?
I would appreciate any help with these questions, I tried googling but there is little or no info on the subject, could you please give at least some direction?


texts: Combine Multiple Rows Results From Left Join in a unique cell


texts: "Tuples in a weak entity set are partitioned according to their relationship with tuples in a strong entity" - meaning?

texts:
http://web.cs.ucla.edu/classes/fall04/cs143/solutions/chap2.pdf
In the above link, there is the following statement - "Tuples in a weak entity set are partitioned according to their relationship with tuples in a strong entity". I have searched a lot, but I can't find anywhere what partitioning is and how (also why) exactly the tuples in a weak entity set are partitioned. If someone can illustrate it with an example it would be very helpful


texts: Does PostgreSQL 9.1 Streaming Replication catch up after a lag without WAL archiving?

texts:
Context:

Let's say that, while using Streaming Replication/Hot Standby on a Postgres 9.1 cluster, a standby node goes down. It stays down for an day, during which time a lot of DML occurs on the master. The standby's recovery.conf doesn't contain a 'restore_command' entry (for restoring from WAL journal files), but does contain a 'primary_conninfo' string (for Streaming Replication).

Question:

If I start the standby again after a day of changes on the master. Will it "catch up" (eventually come into a state which mirrors the master) using only Streaming Replication? Or do I have to enable WAL file archiving and let it apply files archived during the outage to ensure currency?

I've checked the WAL archiving/streaming replication doc here, and it says that you don't have to enable both WAL archiving and Streaming Replication, but it is unclear whether or not catch-up will happen without WAL file archiving being enabled.

Thanks!


texts:
Given the following type of data structure.

    ID  NAME  TREELEFT  TREERIGHT  PARENT
    1   XYZ       1         2         0
    2   ABC       3         6         0
    3   DEF       4         5         2
    4   HIJ       7         10        0
    5   KLM       8         9         4
    6   NOP       11        16        0
    7   QRS       12        15        6
    8   TUV       13        14        7

The methodology is more or less identical to the nested set model except I've included a parent, just to make my life a little easier. (https://en.wikipedia.org/wiki/Nested_set_model#Example) I need to come up with a way of detecting gaps. For example if ID=6 had a different treeright say 17. There would technically be a gap of 1.

For the moment I've done this in a series of different queries.

One to detect if treeright doesn't exceed 2n, another to detect if treeright is always > than treeleft. Another couple to ensure that no treelefts or treerights are identical to each other.

However if I could add just check to ensure that the tree had no gaps I'm sure a couple of the other tests would become redundant. Can anyone think of a good way to do this. I've been racking my brain on this one for quite a long period of time.


texts: Why does the varchar datatype allow unicode values?

texts:
I have a table with a varchar column. It is allowing Trademark(™), copyright(©) and other Unicode characters as shown below.

Create table VarcharUnicodeCheck
(
col1 varchar(100)
)

insert into VarcharUnicodeCheck (col1) values ('MyCompany')
insert into VarcharUnicodeCheck (col1) values ('MyCompany™')
insert into VarcharUnicodeCheck (col1) values ('MyCompany░')
insert into VarcharUnicodeCheck (col1) values ('MyCompanyï')
insert into VarcharUnicodeCheck (col1) values ('MyCompany')

select * from VarcharUnicodeCheck

But the definition of varchar says, it allows non-unicode string data. But the Trademark(™) and Registered(®) symbols are Unicode characters. Does the definition contradicts the property of varchar datatype? I read couple of links like first one and second one. But still I could not understand why it allows unicode string when the definition says that it allows only non-unicode string values.


texts: MongoDump Failed: error writing data for collection `collection` to disk: error reading collection: EOF

texts:
[BACKUP COMMAND] mongodump --host 127.0.0.1 -d database --port 27017 --username username --password password --out /output/path/mongodump_DATE_12_00_AM
[ISSUE] Failed: error writing data for collection collection1 to disk: error reading collection: EOF
MongoDB v3.6.5
The Mongo Database and the Node JS application is running in the docker container, Linux Machine. Mongo DB total backup size is 41GB. We are running the backup command on daily basis it is working fine for 10 to 15 days but after that getting above mentioned error intermittently.
[WORKAROUND] = As a workaround, if I stop and start all the NODE application services, application container and MongoDB container then the issue is getting resolved.
For Testing purpose before doing workaround(stop & start container), I ran the mongodump command only for that error collection mongodump --host 127.0.0.1 -d database --port 27017 --username username --password password --collection collection1 --out /output/path/mongodump_DATE_12_00_AM then it is working fine.
So my questions are:

Is this application memory leak issue?
Or, Is System low memory(RAM) issue? Linux server TOTAL RAM SIZE is: 12GB.

If system low memory issue means it should failed on every time but here it failed after a few days.


texts:
Scenario:
Find out any customers in each factory that have three or more orders which contain new items they have never purchased before. For example, John has:

Order A: Item 1, Item 2
Order B: Item 2, Item 3, Item 4
Order C: Item 3

Order B has items John has never purchased before, while Order C does not.

To put it another way: Starting with each customer's second order, we want to know how many orders they have with at least one item that's never been on one of their orders before.

Table schema: see SQL Fiddle. RelatedRecordID binds multiple records into a single order.

My approach: see also SQL Fiddle above. This approach is flawed and not robust. In the previous John example, this method will give out 2 increments, but because Order C is placed after B, there is actually only one increment. And if Order B does not contain Item 3, its precedence won't affect the result.

Can anybody think of a fix or a new way to do this?


texts: Is a relation R(A,B,C) with functional dependencies {AB->C, C->B} in BCNF?

texts:
If not, then how do I make it BCNF while being lossless and dependency preserving?

I can see that the primary key of R is AB. And in BCNF every functional dependency should be of the form [Key] -> [Key or Non-key]. But C->B doesn't satisfy that.

I know I will lose the AB->C dependency if I do any decomposition. But I am unable to figure out which decomposition would be lossless. I would be grateful if someone could help me out.


texts: MySQL - reformat a column value dependant on existing value

texts:
Am needing to provide a concatenated field from a select query which is dependant on existing values.

SELECT `delegate`.`FirstName` AS `First Name`,
   `delegate`.`LastName` AS `Last Name`,
   `organisation`.`Name` AS `Organisation`,
    IF (`delegate`.`PhoneWork`  IS NOT NULL, 'Yes','No') AS `WorkPhone`),
   `delegate`.`EmailAddress` AS `Email Address`
FROM `delegate`
JOIN `organisation` on((`delegate`.`OrganisationID` = `organisation`.`ID`))

Can anyone point me in the right direction?


texts: Postgres permissions - schema not showing using \dt

texts:
I have no experience in postgres, and am trying to get a staging site correctly configured.

The database has a schema data in the database drupal.
I imported the database from development using (as postgres user):

psql drupal <drupal.dump.sql

When I connect as the drupal user, and execute \dt, it only shows the regular tables and not the tables in the schema "data".

I see those "missing" tables when I execute \dt *.*

I am probably missing some permissions to be added to the drupal user. Any help greatly appreciated!

Postgres version: 9.3.17


texts: sp_blitzCache against one stored procedure

texts:
Recently we started using sp_BlitzCache to help us tune our queries. We found one SP that had some issues with it and we were able to refactor it successfully.

I have a copy of the output of sp_BlitzCache taken during the week before we tuned the query, it is available here

https://drive.google.com/open?id=1oiAEl62ZT51qcCEhVzkg8oDA3HB-NCsv

The command run to generate this output was

exec sp_BlitzCache @StoredProcName = 'hs_UpdateShipmentPackagesAndWeightSp'

Few questions

1) I noticed that we have multiple rows for the same plan handle, why is that?

2) You'll notice that the highest cost query (1451) has a query text of the entire create procedure statement, what does that mean? Are the metrics for this row just for the creation of the stored procedure?

3) For the highest cost row, the one referenced above, it has a Min/Max grant of almost 2GB. Does that mean that when we run the create procedure statement it asked for 2gb of memory? Or is this plan being used when the proc is called ? I suppose this ties into the answer from question 2

4) Why in some cases we have Min/Max grant KB > 0 and Min/Max used grant kb > 0, but percent memory grant used is null ?

We are running SQL Server 2012.

Thanks,
Kevin


texts:
I am using Oracle SQL Developer and am trying to concatenate two columns into one for each row of students.  Under pivot below, I have "1" and "1A" separately but would like to show both into one column.

SELECT
  *
FROM (
    SELECT
      students.student_number   id
     ,students.last_name ||',' ||students.first_name student
     ,students.grade_level      gr
     ,sections.external_expression
     ,teachers.last_name        class
    FROM cc
      JOIN sections ON cc.sectionid   = sections.id
      JOIN courses ON sections.course_number   = courses.course_number
      JOIN teachers ON sections.teacher   = teachers.id
      JOIN students ON cc.studentid   = students.id
    WHERE
      SYSDATE BETWEEN cc.dateenrolled AND cc.dateleft
      AND cc.schoolid         = sections.schoolid
      AND sections.schoolid   = '300'
  )
    PIVOT (
      LISTAGG ( class,'/' ) WITHIN GROUP ( ORDER BY class )
      FOR external_expression
      IN ( '1A(A) 1B(A)' "1",'1A-2A(A)' "1A",'2A(A) 2B(A)' "2"
          ,'1B-2B(A)' "2A",'3A(A) 3B(A)' "3",'4A(A) 4B(A)' "4"
          ,'5A-5B(A)' "5",'5A(A) 7A(A)' "5A",'7A-7B(A)' "7"
          ,'5B(A) 7B(A)' "7A",'8A(A)' "8",'9A(A)' "9",'10A(A)' "10" )
    )
ORDER BY student


texts: Why would someone put `where 1=1` in a query?

texts: I came across a view in our database today where the first statement in the where clause was where 1 = 1. Shouldn't this return true for every record? Why would someone write this if it isn't filtering any records?


texts: assignment in trigger function does not work

texts:
For the table, an AFTER trigger is supposed to update the tsvector column on insert and update. The assignment works outside the function using an update statement. Also, the notice in the BEGIN block appears in the log as desired. However, the assignment is not made when the trigger fires. Columns in the update or insert are updated; only the fts_vector column is empty.

Logged on user is part of a group that has execute privileges on the function and insert/update privileges on the table.

What might be preventing this from working? All answers and suggestions are welcome.

CREATE TABLE customer
(
  name character varying(1000) NOT NULL,
  address character varying(1000),
  customer_uid serial NOT NULL,
  fts_vector tsvector,
  CONSTRAINT customer_pkey PRIMARY KEY (customer_uid);

CREATE OR REPLACE FUNCTION vector_update_customer()
  RETURNS trigger AS
$BODY$
BEGIN
    RAISE NOTICE 'vector_update_customer invoked';
    NEW.fts_vector = to_tsvector('english', coalesce(NEW.name,'')  || ' ' || coalesce(NEW.address,''));
    RETURN NEW;
EXCEPTION
    WHEN OTHERS THEN
      RAISE NOTICE 'vector_update_customer failed';
END
$BODY$
  LANGUAGE plpgsql VOLATILE

CREATE TRIGGER trigger_vector_update_customer
  AFTER INSERT OR UPDATE
  ON customer
  FOR EACH ROW
  EXECUTE PROCEDURE vector_update_customer();


texts:
I have a Oracle XE 11g on Windows 7. I try to backup my database and restore it.

First, i backup my database with running backup.bat script which is in C:\oraclexe\app\oracle\product\11.2.0\server\bin which ended with no error.

Then, for testing i drop a table with this command drop table karzarar; and then committed.

I restore my backup by clicking Restore Database from Startup menu. It restored with no error.

But when i connected to the database and try to select the table that i dropped it gives me error.

SQL> select * from karzarar;
select * from karzarar
              *
ERROR at line 1:
ORA-00942: table or view does not exist

I fallowed the instructions from there to backup the database.

Did i miss anything?


texts:
I have a job which each day (at 6:01pm) copies 516 rows from a table and seeds them with tomorrow's date

INSERT MYDB.MARKET_DATA(
            MARKET_DATE,
            CURRENCY_BASE,
            CURRENCY_TERM,
            PERIOD,
            MID)
             (
               SELECT
                  DATEADD(hh, 6, GETDATE()),
                  MARKET_DATA.CURRENCY_BASE,
                  MARKET_DATA.CURRENCY_TERM,
                  MARKET_DATA.PERIOD,
                  MARKET_DATA.MID
               FROM MYDB.MARKET_DATA with (nolock)
               WHERE cast(MARKET_DATE as date)=cast (getDate() as date)
             )

We also check that the right number of rows are copied by looking at the number of rows for the last 7 days

SELECT count(*)
,CAST(market_date AS DATE)
FROM mydb.market_data
WHERE market_date > dateadd(day, - 7, getdate())
GROUP BY CAST(market_date AS DATE)
ORDER BY CAST(market_date AS DATE) DESC

We ran this SELECT query twice today. At 10am in the morning it returned 517 rows for today, and at 1pm it returns 516 rows for today.

What could possibly account for this discrepancy?

There was no user interaction with this table today (other than running the two SELECT's)
There is no with (nolock) on the SELECT query
Note that there is a with (nolock) on the INSERT/SELECT, because the rows are updated throughout the day with latest information, and I don't want the INSERT/SELECT to fail on any rows that are being updated at the precise moment that it is being executed
There is no stored procedure or job to delete any rows


texts:
My Database server is consuming 71% - 75% percent of RAM memory, I can see from perfMonitor logs that SQL_Service is eating up 65% - 69% of RAM memory.

We carried out different operations to lower the memory consumption like:

We purged most of the databases to lower the size on disk.
We restart different application services that were in active state
with SQL Server.

Server RAM memory is 65 GB in total.

Looking forward for an expert opinion. How to Lower the memory consumption ?


texts: Security for SSIS packages in SQL Server - use a domain account?

texts:
I have an SSIS package that I want to deploy to SQL Server A. This package needs to access tables on SQL Server A, and another SQL Server, B. What is the best way to set this up? Should I create a domain account, and create a matching login on each machine and grant the appropriate permissions? If I did this, how do I run the job that runs the SSIS package completely under that domain account context? How should I be saving connection info within the SSIS package?

Do I really need to use a proxy account? It seems that if I have connection information saved in the SSIS package, it shouldn't matter what account is running the SQL Server Agent or anything like that.

Would just using a linked server using a SQL Server login make the most sense?


texts: MySQL vs PostgreSQL: Benchmarking COUNT(*) execution speed

texts:
I benchmark DBs to find out the best for my project and I found that count(*) is extremely slow in PostgeSQL. And I don't understand is it a normal behaviour of PostgeSQL or I do something wrong.

I have a table with ~200M records. MySQL table definition:

CREATE TABLE t1 (
  id int(11) NOT NULL AUTO_INCREMENT,
  t2_id int(11) NOT NULL,
....
  PRIMARY KEY (id),
  KEY index_t2 (t2_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Request (returns ~30M):

SELECT COUNT(*) FROM t1 WHERE t2_id = 7;

runs:

25,797ms MySQL (v5.7.11)

1,222,168ms PostgeSQL (v9.5)

Explain:

MySQL:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
   partitions: NULL
         type: ref
possible_keys: index_t2
          key: index_t2
      key_len: 4
          ref: const
         rows: 59438630
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

PostgreSQL

Aggregate  (cost=4469365.02..4469365.03 rows=1 width=0)
 ->  Bitmap Heap Scan on t1  (cost=715817.34..4382635.74 rows=34691712 width=0)
       Recheck Cond: (t2_id = 7)
       ->  Bitmap Index Scan on index_t2  (cost=0.00..707144.41 rows=34691712 width=0)
             Index Cond: (t2_id = 7)

Server: AWS RDS (db.r3.xlarge) vCPU:4 Memory:30Gb

Updated (2016-09-20):

> explain (analyze, buffers) SELECT COUNT(*) FROM t1 WHERE t2_id = 7;

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=4469365.02..4469365.03 rows=1 width=4) (actual time=1213456.539..1213456.539 rows=1 loops=1)
   Buffers: shared read=2734808
   ->  Bitmap Heap Scan on t1  (cost=715817.34..4382635.74 rows=34691712 width=4) (actual time=64015.828..1205542.421 rows=31383566 loops=1)
         Recheck Cond: (t2_id = 7)
         Rows Removed by Index Recheck: 108582028
         Heap Blocks: exact=19929 lossy=2606242
         Buffers: shared read=2734808
         ->  Bitmap Index Scan on index_t2  (cost=0.00..707144.41 rows=34691712 width=0) (actual time=64009.598..64009.598 rows=31383566 loops=1)
               Index Cond: (t2_id = 7)
               Buffers: shared read=108637
 Planning time: 0.080 ms
 Execution time: 1213456.891 ms
(12 rows)

Time: 1213484.579 ms

Updated (2016-09-21):

> explain (analyze, buffers) SELECT t2_id FROM t1 WHERE t2_id = 7;
                                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t1  (cost=715817.34..4382635.74 rows=34691712 width=114) (actual time=59954.834..1234070.436 rows=31383566 loops=1)
   Recheck Cond: (t2_id = 7)
   Rows Removed by Index Recheck: 108582028
   Heap Blocks: exact=19929 lossy=2606242
   Buffers: shared hit=4824 read=2729984
   ->  Bitmap Index Scan on index_t2  (cost=0.00..707144.41 rows=34691712 width=0) (actual time=59948.598..59948.598 rows=31383566 loops=1)
         Index Cond: (t2_id = 7)
         Buffers: shared hit=4824 read=103813
 Planning time: 0.086 ms
 Execution time: 1239826.408 ms
(10 rows)

Time: 1239827.053 ms


texts: Query with join and concatenation of one joined table's column

texts:
I have the following structure:

Table R     Table RZ (n-m)    Table Z
-------     -----------------  ---------
R1          R1 | Z1           Z1 | "A"
R2          R1 | Z2           Z2 | "B"
R3          R2 | Z1           Z3 | "C"
R4          R2 | Z3           Z4 | "D"
...         R3 | Z1             ...
...         R3 | Z2             ...
...         R3 | Z4             ...

(I left out some additional complications and further joins to simplify)

I need to query all rows from "R" with some of the columns or "R" including a column in the result that has the concatenated "Name" column of table "Z".

Here is a sample following the given table contents above:

ID of R | Name of Z
--------------------
R1      | "A,B"
R2      | "A,C"
R3      | "A,B,D"

So far a scalar function was in place, used by every row of R, re-querying the R- RZ-Z join again, concatenating and returning the names, but I figured this is very slow.

I tried FOR XML, but I can't manage to combine the XML result delivering the string I want with the rest of the columns required from table R.

What is the best way to solve this?


texts: Relationship from Orders to Shop and Supplier, or to general User?

texts:
I have a platform where

a User can have a Shop, which can place Orders
a User can be a Supplier, which can see Orders from Shops coming in.

I have created the following diagram depicting the structure I have modeled so far:

Is that a good schema? Or, would it make more sense to couple the relationships to the User straight away?

I'm guessing that will cause more overhead in my backend? (Laravel - PHP)

Thank you!


texts: Different runtimes using parameter vs. constant

texts:
A strange thing is happening to me during execution a specific query.

I have this query:

DECLARE @P0 as varchar(4000)
DECLARE @P1 as varchar(4000)

SET @P0 ='P0'
SET @P1 ='2015-02-21 00:00:00'

Select DISTINCT  top 10 USER_ID, MODULE_TYPE_CLASS, ADDRESS
From VIA_MESSAGE_AGGREGATOR ma
where state = @P0
and DATE_CREATED <= @P1

This take a long to execute (about 23 seconds).

When I substitute the @P0 for 'AA' value the same query is executed in 3 secs.

This is strange because I have an index with the same columns (state and date_created) and when I execute with parameters the index is not used, but when I execute with the 'AA' value instead of parameter then SQL Server uses the index.

Anyone have any idea why its behaves like this? I'm using JPA to execute this queries, the code is just an example of the same behavior.


texts: Merge and formatting of data from two tables


texts:
I have this stored procedure that is taking over 10 seconds to run. It returns a data set (list of files) to the application. Any suggestions on how to speed it up?

Details about the plan. It looks like query 14 in the plan takes 98% relative to the batch. Inside query 14 is a sort that is 62% of query 14.

I'm using SQL Server 2016. Estimated query plan.

Here is the actual plan. Took 5 min. and 21 seconds to run.


texts:
We need to configure a database for our NGO. We have to save information about the children and several pictures and documents for each of them.
A friend is currently saving the data of over 70 kids in folders and subfolders of her device and it's becoming a mess. She asked me if I could digitalize it and I am not sure how I want to implement it.
We need the database and some kind of frontend where my friend can easily add, change or display information in the database without any IT knowledge. It would also be beneficial if we could connect our website to that database to display some pictures of the kids.
I've read about Wordpress and their database, is that a good solution? Or should I create our own MySQL database and a simple webpage where my friends has access to it?
I'm kinda new to databases but I really want to implement something that is better than our current storage situation. We are currently using a simple PHP, HTML, CSS, JavaScript website: www.kencef.de. A friend and I are the only developers and aren't that experienced yet. Should we switch to Wordpress?


texts: Benefit of using WITH (TABLOCK, XLOCK) when inserting one line in a table?

texts:
I found in some legacy code (originally SQLServer 2008) INSERT hints that I'm not sure to understand.

I have a table (storing many rows) and some procedures to fill this table.
We insert rows one by one. One procedure call = one line inserted.
Fine.

Sometimes (not always) SQL code looks like:

insert into my_table with(tablock, xlock)
values(......)

Why tablock / xlock have been put here ?

What could be the benefit of locking a table when we insert only one line ?

Note this insert is embedded within a transaction with isolation serializable.

(I can imagine the benefit if we want to insert a bunch of records, ie. better performance).


texts: slow autovacuum on table with binary data

texts:
I have a table with about 4M rows, but each row has some binary data that may consume up to 1Mb.

The autovacuum process on this table is taking over 1.5 hours to complete, which seems way to long to me.

I understand that when Postgres updates or deletes a row it keeps a copy of the old row and then autovacuum cleans this up later.

If my rows have a lot of binary data and they are getting updated regularly, is this the cause of my very slow autovacuum? And is it trying to load all of that data into memory?


texts: Oracle procedure with input parameter that performs multiple SUBSTR

texts:
I have a table Employee with the columns employee_id as type number, and  employee_name as type varchar.

I need a procedure that takes an input string to insert employees into the table. The procedure should read and analyze the string which will include multiple names separated by commas.

Example:

'adam,alex,hales,jordan,jackob'

Each name should be extracted up until the comma and inserted into the employees table. So basically using the input string above I should have the names inserted as in the following result:

 employee_id | employee_name
-------------+--------------
 1           | adam
 2           | alex
 3           | hales
 4           | jordan
 5           | jackob

Any solution is appreciated.


texts: SQL update multiple rows in destination table with same id but different values from other table

texts:
creditNote table

id      invoice_id     generated_at(timestamp)     status
10101      111          2018-02-28 14:42:39.247         generated
23982      111          2018-03-30 11:11:11.247          generated

paymentModeAmount table

id      invoice_id     paid_on(timestamp)     payment_mode
98236      111                                 creditNote
63725      111                                 creditNote

After execution of below query OUTPUT is

paymentModeAmount table

id        invoice_id     paid_on(timestamp)       payment_mode
98236        111         2018-02-28 14:42:39.247   creditNote
63725        111         2018-02-28 14:42:39.247   creditNote

Here PaidOn Column has same data, but we need to get two different timestamps

Expected

id      invoice_id     paid_on(timestamp)       payment_mode
98236      111         2018-02-28 14:42:39.247   creditNote
63725      111         2018-03-30 11:11:11.247   creditNote

paidOn timestamp's should be different as expected

PL/SQL Function

CREATE OR REPLACE FUNCTION updatePaidOn()
RETURNS TABLE(
invoiceid BIGINT,
generatedat TIMESTAMP)
AS $$
BEGIN
RETURN QUERY SELECT
generated_invoice_id as invoiceid, generated_at as generatedat
FROM
credit_note
WHERE
status='generated';
END; $$

LANGUAGE 'plpgsql';

Execute the below function

UPDATE payment_mode_amount pm
SET paid_on = sq.generatedat
from updatePaidOn() as sq
where pm.invoice_id = sq.invoiceid
and pm.paid_on is null
and pm.payment_mode='creditNote';

Update accordingly timestamps..


texts: Encrypt and decrypt text using the same key

texts:
I need to write a stored procedure that will accept two input string parameters and will encrypt the data as explained below.

First parameter should be the message to encrypt and second parameter should be the key for encryption. The encryption key must be min. 6 to 8 characters mix of alphabets & numbers. Procedure should perform encryption by adding the key to message. In addition to this, the procedure should also decrypt back the message using same key.

I am able to encrypt input text using a key, but not able to decrypt text using same key.

DELIMITER $$

DROP PROCEDURE IF EXISTS sp_EncryptWithKey;
CREATE PROCEDURE `sp_EncryptWithKey`(IN `entext` varchar(50), IN `keyval` varchar(8))

begin
declare des varchar(500);
declare len int;
set len=length(keyval);
if keyval regexp '[A-Za-z]' && keyval regexp '[0-9]' && length(keyval)>=6 && length(keyval)<9

then

    begin

    SELECT entext,keyval INTO @A,@B;
    SET @C=AES_ENCRYPT(@A,@B);
    SELECT @C as encrypt_value;

    SELECT AES_DECRYPT(@C,@B) as decrypt_value;
    end;
else
     select 'key length must be between 6-8' as error;

      end if;
end
$$

DELIMITER ;

In the stored procedure, if my input text is ABCD and key is pqrs123 then the output is encrypted text e.g. Ÿå4¸Wòm. If I give input text as Ÿå4¸Wòm with same key I should get ABCD as decrypted text.


texts: Trying to connect to PostgreSQL with Entity Framework via Npgsql and C#

texts:
So, I'm new to databases and only have done a little with PostgreSQL before. I have C# (visual studio 2012) and have downloaded the Entity framework (6). I have also downloaded the latest Npgsql data connection driver and used this tutorial:

Using Entity Framework 6 with Npgsql 2.1.0

Now I have of course installed PostgreSQL and referenced the two dll's in my C# project:

npgsql.dll and npgsql.entityframework.dll

Now I need to know how to add, view, delete stuff via C# but I can't find any material on using C#, Npgsql and Entity Framework together.

With no material on using Npqsql and Entity Framework 6, I have instead found this tutorial for version 5:

Create Entity Data Model in Entity Framework 5.0

However, under choose your data connection, when I add a new connection, there is no option to use Npqsql. I really am confused and have been trying to control PostgreSQL via C# for about a week now, I have tried almost every link I can find on Google and am close to giving up. A lot of tutorials show how to drive PostgreSQL directly via Npgsql without using Entity Framework, but I really wanted to use the Entity Framework because I've been told it makes manipulating the database much easier.


texts: Quite large (400k) mysql database design for multiple users

texts:
I'm seeking advice of experienced admins.

I'm working on a website, where you solve word anagrams. If it solved it should never be displayed again.

Wordbase contains ~400k entries. What would be the most effective solution to storing such data?

One way could be:

+---------+------------------------+
| word_id | user1 | user2 | user...|
+---------+------------------------+
|  1      | null  |  null |  1     |
|  2      | 1     |  null |  null  |
|  ...    |       |       |        |
|  400000 | null  |  1    |  null  |
+---------+------------------------+

Where let's say 1 = solved.

But wouldn't it become a monster quite quickly?
(+even a simple query of extending it by a new user takes forever)

Other solution is to store every solved word_id for all users, but then, it can be 6-digits for every entry and growing massively and rapidly aswell.

Also which engine would be more effective in this example? MyISAM or    InnoDB?


texts:
I’m looking at 3 tables here with:

1 called Users containing a UserID.
UserID is linked by a foreign key to UserID in a table called Orders.
Within Orders there’s a PassengerID linked by a foreign key to a table called Passengers.

Is there anyway I can link these tables to identify the UserID it’s associated OrderID and that OrderIDs associated passengerID.


texts: Migration path for IDENTIFIED BY PASSWORD 'password_hash' when removed in MySQL 8

texts:
We have a solution where the management of users to a MySQL database is automated. Today we use MySQL 5.6 but we want to move to MySQL 8.

According to https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/mysql-nutshell.html

IDENTIFIED BY PASSWORD

was removed. Unfortunately, this is something that we make use of. We store the hash of the password outside the database, and then whenever a user needs to be created we can use it to directly create the account without actually knowing the password.

Why not create user immediately and remove the need for external storage?

We also make use of MySQL's host concept to limit access from only certain IP addresses. However, these may change. Use case:

A new user 'myUser' should be given access from 1.2.3.4 with password 'myPassword'
The hash for 'myPassword' is calculated and stored together with other metadata in an external storage
A MySQL user myUser@1.2.3.4 is created with the given hash
Later, user should also be given access from IP 5.6.7.8
No problem, a new MySQL user myUser@5.6.7.8 is created using same hash as before

The end result is a solution that is restrictive and user isn't aware of that technically two accounts exist. We also don't know the user's password.

As far as I can understand, this solution will not work going forward with MySQL 8 so I'm looking at possible migration paths. One possible solution would be if the following was possible:

Is it possible to create a new user in MySQL and set its password and authentication plugin to exactly the same as another user, without actually knowing the underlying password with MySQL 8? If yes, we could use that with some trickery to create any additional users by simply copying the information we have. We would only need to know the password while the first initial user is created, but whenever a new IP adress is given access we can perform a "copy" logic instead.

Any ideas? Thanks in advance!


texts: Update badly formed XML to allow parsing

texts:
I have inherited a production system that needs some fine tuning to enable easy extract of the result set.  A record gets added to a table which contains an XML field, with contents similar to the below:

<PROG>PROGX_</PROG><STNTYPE>LEAKTEST</STNTYPE><STNID>S1</STNID><TRCODE>SN_REQUEST</TRCODE><OPERATORID>222</OPERATORID><PARTID>PARTID</PARTID><TRSTATUS>0000</TRSTATUS><TRTEXT>TEST OK</TRTEXT><T1_USL>0.45</T1_USL><T1_LSL>-0.45</T1_LSL><T1_RESULT>05,CC/M</T1_RESULT><T1_STATUS>OK</T1_STATUS><OVERALL_RESULT>1</OVERALL_RESULT><OVERALL_STATUS>OK</OVERALL_STATUS>

However, I believe the formatting of the XML is invalid and I want to be able to clean this up a bit, by adding a root and grouping this a bit better.  Something similar to along the lines of:

<RESULTSET>
    <PROG>PROGX_</PROG>
    <STNTYPE>LEAKTEST</STNTYPE>
    <STNID>S1</STNID>
    ...etc...
    <OVERALL_STATUS>OK</OVERALL_STATUS>
</RESULTSET>

(The idea being then that I can use SQL views to then properly split this).

Any ideas if this is possible?  I'm new to XML on SQL.


texts:
I'm working in a project with MySQL and using stored procedures to make my SELECT queries,
so all of them have this same structure:

DELIMITER $$
CREATE PROCEDURE `case_in_where`(IN `column_selector` INT, IN `value` VARCHAR(255))
    BEGIN

    SELECT * FROM `foo`
    WHERE
        CASE
            WHEN `column_selector` IS NULL THEN 1
            WHEN `column_selector` = 1 THEN `foo`.`column_1` = `value`
            WHEN `column_selector` = 2 THEN `foo`.`column_2` = `value`
        END
    ;
END $$
DELIMITER ;

But I have seen that when people ask for help using this approach, usually the answers are that it should use AND, OR instead or Dynamic SQL. So, this way is not correct? It's a bad practice or have any difference in performance?

I should use AND, OR or Dynamic SQL or make different queries for every case?

Thanks beforehand.


texts:
I have two database with a table with utf8 table, I wanted to change the encoding of a column, I used two scenarios but a problem happened to me.
at first the table was like this :

CREATE TABLE `spool` (
  `username` varchar(250) NOT NULL,
  `xml` text  NOT NULL,
  `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `seq` (`seq`),
  KEY `i_despool` (`username`) USING BTREE,
  KEY `i_spool_created_at` (`created_at`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

First scenario

in the first one I change the row encoding with bellow command :

ALTER TABLE spool MODIFY xml TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

and then I use show create table spool; like this one :

 CREATE TABLE `spool` (
  `username` varchar(250) NOT NULL,
  `xml` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `seq` (`seq`),
  KEY `i_despool` (`username`) USING BTREE,
  KEY `i_spool_created_at` (`created_at`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

second scenario

In the second table I first changed the table encoding and then I changed the xml column encoding like this :

ALTER TABLE spool
CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

and then I changed column encoding like below :

ALTER TABLE spool MODIFY xml TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL;

and then we can see table like this :

CREATE TABLE `spool` (
  `username` varchar(250) COLLATE utf8mb4_unicode_ci NOT NULL,
  `xml` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `seq` (`seq`),
  KEY `i_despool` (`username`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=30849368 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

as you can see in the second scenario in front of the "`xml` text" we don't see the CHARACTER SET utf8mb4 and I think it causes error for me because when Ejabberd wants to insert a query in the second one I see bellow error but in the other table I don't have this error:

HY000Incorrect string value: '\\xF0\\x9F\\x98\\x8F\\xF0\\x9F...' for column 'xml' at row 1"
** Stacktrace: [{ejabberd_odbc,sql_query_t,1,[{file,"src/ejabberd_odbc.erl"},{line,173}]},{lists,foreach,2,[{file,"lists.erl"},{line,1336}]},{ejabberd_odbc,outer_transaction,3,[{file,"src/ejabberd_odbc.erl"},{line,443}]},{ejabberd_odbc,run_sql_cmd,4,[{file,"src/ejabberd_odbc.erl"},{line,380}]},{p1_fsm,handle_msg,10,[{file,"src/p1_fsm.erl"},{line,582}]},{proc_lib,init_p_do_apply,3,[{file,"proc_lib.erl"},{line,237}]}]

in the first scenario we don't see this error but in the second one the error exists. how to solve this problem?


texts: UTL_RAW "invalid identifier" error message

texts:
When I execute the following:
select utl_raw.cast_to_raw('8') from dual;

I receive the error message below:

invalid identifier

...while in other PC with the same Oracle version, i.e., 11g, the query is ok.
My questions

What's the origin of this error message?

How can I fix it?

I found no related docs in the web.

Comment interactions

Is "other PC" connecting to the same Database Instance? – @Michael Kutz

I installed the same version of Oracle in two PCs, but only in one of them I received this error when I tried to make a backup.


texts: Intellisense is not working since upgrade to SQL Server 2014

texts:
We recently upgraded our production server to SQL Server 2014. This server hosts ~2200 databases. When I connect to it as sa with SQL Server Management Studio 2014 and I type queries, Intellisense does not work (nothing show up). Before the upgrade, we ran SQL Server 2008 and SSMS 2008 and Intellisense was working perfectly in the same conditions. Also, Intellisense works when connected to an other server (our development server, SQL 2014 too) which holds ~10 databases.

I already made sure Intellisense is enabled
I already tried to Refresh the local cache (CTRL-Shift-R)

Am I missing something else? Or does it have something to do with the number of databases on the server? If so, why was that not happening before, with SQL Server 2008?


texts: I have multiple different objects which share most attributes, but vary on a few, what methods could I use to model them?

texts:
I have a few different items which mostly share the same attributes, but then have a few extra/less. In OOP they could be modeled well with inheritance, but postgres' inheritance is limiting and doesn't seem designed for this. An example of the types might be (this isn't the actual example, so don't take it too serious):

CREATE TABLE document (
    id serial not null PRIMARY KEY,
    name text,
    created_date date,
    modified_date date
);
CREATE TABLE document_list (
    id serial not null PRIMARY KEY,
    name text,
    created_date date,
    modified_date date,
    list_length int
);
CREATE TABLE document_index (
    id serial not null PRIMARY KEY,
    name text,
    created_date date,
    modified_date date,
    num_index int,
    index_header text
);

As you can see they share most values except for a few unique ones. This would fit well with inheritance patterns, but I'm not sure of the best way to implement them in postgres.

I've tried creating document, then inheriting the others from it, but this causes problems with foreign keys, as inherit doesn't support them in postgres.

An alternative way I was thinking about was doing:

CREATE TABLE document (
    id serial not null PRIMARY KEY,
    name text,
    created_date date,
    modified_date date
);
CREATE TABLE document_list (
    document_id int not null references document(id) PRIMARY KEY,
    list_length int
);
CREATE TABLE document_index (
    document_id int not null references document(id) PRIMARY KEY,
    num_index int,
    index_header text
);

Is this a good and/or acceptable way of doing it? Or are there better methods I'm missing?


texts:
I want to migrate the database.
When I disable the constraints before adding the data to the output file I get extra lines:

SQL> spool disable_constraints.sql;
SQL> select 'alter table '||table_name||' disable constraint '||constraint_name||';'
from all_constraints where owner = 'THE_OWNER' and constraint_name not like 'BIN%';
SQL> spool off;

Part of disable_constraints.sql:

SQL> select 'alter table '||table_name||' disable constraint '||constraint_name||';' from all_constraints where owner = 'THE_OWNER' and constraint_name not like 'BIN%';

    'ALTERTABLE'||TABLE_NAME||'DISABLECONSTRAINT'||CONSTRAINT_NAME||';'
    --------------------------------------------------------------------------------
    alter table DATABASECHANGELOGLOCK disable constraint SYS_C0012929;
    alter table DATABASECHANGELOGLOCK disable constraint SYS_C0012930;
    alter table DATABASECHANGELOGLOCK disable constraint PK_DATABASECHANGELOGLOCK;
    alter table DATABASECHANGELOG disable constraint SYS_C0012932;
    alter table DATABASECHANGELOG disable constraint SYS_C0012933;
    alter table DATABASECHANGELOG disable constraint SYS_C0012934;
    alter table DATABASECHANGELOG disable constraint SYS_C0012935;
    alter table DATABASECHANGELOG disable constraint SYS_C0012936;
    alter table DATABASECHANGELOG disable constraint SYS_C0012937;
    alter table ARCH_TA1_TOTAL_ENTECONOMICS disable constraint SYS_C0012938;
    alter table ARCH_TA1_TOTAL_ENTECONOMICS disable constraint SYS_C0012939;

    'ALTERTABLE'||TABLE_NAME||'DISABLECONSTRAINT'||CONSTRAINT_NAME||';'
    --------------------------------------------------------------------------------
    alter table ARCH_TA1_TOTAL_ENTECONOMICS disable constraint SYS_C0012940;
    alter table ARCH_TA1_TOTAL_ENTECONOMICS disable constraint SYS_C0012941;
    ....

For example, an extra line in the file:

'ALTERTABLE'||TABLE_NAME||'DISABLECONSTRAINT'||CONSTRAINT_NAME||';'
--------------------------------------------------------------------------------

How to filter out the extra lines?
I would be very grateful for the information. Thanks to all.


texts: How to analyse the memory usage of hash table in redis

texts:
I am running a redis instance with maxmemory=2GiB. After inserting items with 10-byte value using YCSB bechmark until the instance is full, I use the INFO MEMORY and MEMORY STATS to analyse its memory usage.
MEMORY STATS returns overhead.hashtable.main=459742168, while MEMORY USAGE _indices returns 1292617326. The data type of _indices is zset, which is the index for finding keys I think. Based on total.allocated=2147442672 and dataset.bytes=1686835232 returned by MEMORY STATS, the memroy used by hash table is 459742168 bytes. But I am confused about the MEMORY USAGE _indices. There are always one zset instance, the _indices. Isn't it the hash table?
Also the ZCARD _indices is 10030238. However, the source code indicates it should be 2^n. How can I get the current hash table capacity?


texts:
We have a MySQL database that was restarted a few hours ago

When it restarted, the views were missing. The tables all seem intact, and a check on the mysql tables seems all good.

Any ideas what could have happened?


texts:
Are shards distributed randomly across machines in MongoDB or are they in order?

For example, if I had a MongoDB cluster that was sharded across two machines and my shard key was a person's name, would names starting with 'a-m' be on the first machine (or replica set) and names starting with 'n-z' be on the second machine?

Thanks so much!


texts: How can I truly DELETE data from a SQL Server table - still shows up in notepad in backup file?

texts:
SQL Server DELETE only marks as DELETED though leaves as data in the data pages.
Data pages are 1024 KB in size and this problem can be eliminated by adding a FILLFACTOR that ensures only 1 record per page. This will however increase the size of your database substantially.

So far I have tried the following without complete success, whereby they will fix the problem a little, reduce some of the DELETED record data from within the pages, though not all.

ALTER INDEX...WITH REBUILD
DBCC SHRINKFILE using NOTRUNCATE can compact pages into
dbcc shrinkdatabase (0,0,NOTRUNCATE)
sp_clean_db_free_space 'DBNAME'

This does not work and the only way I have found to fix this problem is to:

Insert all data into a temp table
DROP the table Reinsert the data back and recreate all indexes,
triggers, constraints etc etc
drop Temp table

Any better ideas out there?


texts: SSIS - Best way to insert large number of rows

texts:
So here's the scenario: I have an XML file, which is in size of 500GB, and with data of around 600 million rows (once on a database table). I'm using SSIS for the operation, and since it consumes a REALLY large amount of memory if I am to use an SSIS component (i.e.: XML Source), it might cause a timeout (correct me if I'm wrong, but as far as I know, using the components on SSIS loads the content of the XML into memory - with a file that big it will surely cause errors). My approach then is:

Use a Script Task to parse the XML data using XML Reader (XML Reader
by far is the best approach, since it parses the XML on a forward,
non-cached approach)
Insert the data on a DataTable
Every 500,000 rows on the DataTable, insert the contents to the
database using SqlBulkCopy, then clear the contents of the DataTable

My problem is, currently, I tried it to parse another file with the size of 200GB, and it's running on around 13.5M / 1 hour - and I don't know if it's still fine with that run time. It sure solves my problem - but it's not too elegant, I mean, there should be other ways.

I'm looking on other approaches, like:

Dividing the large XML files into small pieces of CSVs (around 20GB)
then use an SSIS Data Flow task
Use INSERT script every new rows

Can you help me do decide which is best? Or suggest any other solutions.

Every answer will be very much appreciated.

EDIT

I forgot to mention - my approach will be dynamic. I mean, there are many tables that will be populated with large sized XML files. So, using a Script Component as source might be not so useful, since I still need to define the output columns. But still, will give it a try.


texts: How to migrate from MySQL to MariaDB or Percona with minimum downtime?

texts:
I want to migrate my MySQL 5.1 database to one of these databases: MariaDB, PerconaDB or MySQL 5.6

Is there any way to do this operation with no downtime or with minimum downtime possible? How?

The database is 40GB size, has does hundreds of writes and reads / minute. There are 10 servers connecting to it and it is a sing db server right now.


texts: Concatenation of setof type or setof record

texts:
I use Postgresql 9.1 with Ubuntu 12.04.

In a plpgsql function I try to concatenate setof type returned from another function.

the type pair_id_value in question is created with create type pair_id_value as (id bigint, value integer);

the function that returns elementary setof pair_id_value (those that will be concatenated later) is this one:

create or replace function compute_pair_id_value(id bigint, value integer)
    returns setof pair_id_value
as $$
    listResults = []
    for x in range(0,value+1):
        listResults.append({ "id": id, "value": x})
    return listResults
$$
language plpython3u;

this straigth-forward plpython code should be good, for exemple the query: select * from compute_pair_id_value(1712437,2); returns nicely:

  id            | value
 ---------------+-----------
        1712437 |         0
        1712437 |         1
        1712437 |         2
 (3 rows)

this python function is fairly simple for now, for this example, but above all for my proof of concept. It will be more complex in the near future.

The problem arises when I try to concatenate all the result tables from multiples id.

create or replace function compute_all_pair_id_value(id_obj bigint)
    returns setof pair_id_value as $$
declare
    pair pair_id_value;
begin
    for pair in (select compute_pair_id_value(t.id, t.obj_value) from my_obj as t where t.id = id_obj)
    loop
            return next pair;
    end loop;
    return;
end; $$ language plpgsql;

I receive the error: invalid input syntax for integer "(1712437,0)" as if it is no longer seen as a pair_id_value with two columns but as a tuple (1712437,0).

So I changed the output type of the function from setof pair_id_value to setof record... and if I execute this similar concatenation function:

create or replace function compute_all_pair_id_value(id_obj bigint)
    returns setof record as $$
declare
    pair record;
begin
    for pair in (select compute_pair_id_value(t.id, t.obj_value)  from my_obj as t where t.id = id_obj)
    loop
            return next pair;
    end loop;
    return;
end; $$ language plpgsql;

I get the error: a column definition list is required for functions returning "record"

Trying to follow the answer to this SO question:  I have tried defining the column definition in the select this way select compute_pair_id_value(t.id, t.obj_value) as f(id bigint, value integer), the complete code is here:

create or replace function compute_all_pair_id_value(id_obj bigint)
    returns setof record as $$
declare
    pair record;
begin
    for pair in (select compute_pair_id_value(t.id, t.obj_value) as f(id bigint, value integer) from my_obj as t where t.id = id_obj)
    loop
            return next pair;
    end loop;
    return;
end; $$ language plpgsql;

But when launching the sql script, psql doesn't accept to create the function:
syntax error at or near "("  select compute_pair_id_value(t.id, t.obj_value) as f(id bigint, value integer) ... pointing the finger at the f(

Any idea how to do it properly ?

Should I consider to create temporary table to do the job ?


texts:
I'm trying to write a recursive CTE to explore a workflow system. Unfortunately, I'm getting maximum recursion errors due to loops:

with cteActs as
 (
  select a.id as [id], aa.TASKNEXTID as [childid]
  from TASK a
  inner join TASKNEXT aa on a.id = aa.TASKPARENTID
  where a.id != aa.TASKNEXTID
  ),
 cteNext as
(
    select a.*
    from cteActs as a
    where a.id=42
    union all
    select a.*
    from cteActs as a
    inner join cteNext as n on a.id = n.childid
    )
select *
from cteNext

The table TASK is a list of tasks whereby 42 is "Start job" for example. TASKNEXT links 42 to possible subtasks back in the TASK table. e.g. it may link 42 to 43 which may be "Find materials" e.g.

ID, name, childID
42, Start job, 43
42, Start job, 44
43, Find materials, 200
44, Report to boss, 201
201, Discuss with boss, 202
202, Receive payment, 44

I think the recursion is dying because 44>201>202>44 creates a loop the query doesn't escape from. How can I allow for this? Most of the examples/tutorials I read assume a strict parent>child relationship, where a child can never be the parent of something in its own higher tree.

What I'm trying to get to is a distinct list of TASKS that originate from flows starting at task 42, or wherever I choose.

This is iteration 2 which may work, but runs so slow:

  select a.id as [id], aa.ACTIONACTIVITYID as [childid]
   into #temp
  from TASK a
  inner joinTASKNEXT aa on a.id = aa.TASKPARENTID
  where a.id != aa.TASKNEXTID
  create clustered index [hello] on #temp (ID ASC)
  create nonclustered index [hello2] on #temp (childid ASC)
  ;
with cteNext as
(
    select a.*,
    cast(',' + cast(a.ID as varchar(10)) + ',' as varchar(max)) as Path,
    0 as [cyc]
    from #temp as a
    where a.id=42
    union all
    select a.*,
    n.Path + cast(a.ID as varchar(10)) + ',',
         case when n.Path like '%,'+cast(a.ID as varchar(10))+',%'
           then 1
           else 0
         end as [cyc]
    from #temp as a
    inner join cteNext as n on a.id = n.childid
    where n.cyc = 0
    )

select   id, childid
from cteNext
where cyc =0

Execution plan


texts: Who and when were changed Database default location settings?

texts: I need to know when and who changed SQL Server Database default location settings. How can I do that?


texts:
Let's say i have created a user as 'TEST_USER', and then i assigned a profile to it.

Now how can i see for TEST_USER which profile has been assigned?

I want some query that will check what profile a user is assigned with.


texts: Can't set root password after fresh installation of mariadb using ansible

texts:
Problem:
After a fresh installation of mariadb 10.4 the root password could not be set using ansible.
When installing mariadb from the debian packages by mariadb (not the official debian packages), the password of root is empty and a passwordless login is possible from localhost.
Still, ansible mysql_user threw the following error:
failed: [XXX] (item=localhost) => {"ansible_loop_var": "item", "changed": false, "item": "localhost", "msg": "unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (1698, \"Access denied for user 'root'@'localhost'\")"}


texts:
I have a process that has a last_wait_type as SOS_Scheduler_Yield. It is inserting 100 records to a table. This process has been with this wait type forever with low CPU utilization %.

There are other processes executing the same query simultaneously without this wait type and executing in minutes. What could be the reason for this process hanging? It has been in runnable state with Last_wait_type as SOS_Scheduler_Yield for that long.


texts: Debate on which id to use as reference when quering a one to many table

texts:
I have a database that has a One to Many relationship. A few changes are in order and now when I query the output I need to provide just one ID field. The output contains all iterations of the one to many relationship so that if the parent has many child, the parent and each child will show up as one row in the output.

I'm debating how to handle giving the output an id, (or reference number) and I see that I have three options.

Option one

I can make a new table, or alter one I already have, which would give each Parent/Child pair it's own unique ID.

Option Two

Since it is a one to many relationship I could always just reference the child id, since that would be unique in it's own right.

Option Three

I can 'generate' an id on the fly which would Concat the parentid, and the childid, padding the childid with 4 leading zeros for later expansion.


texts: How can I create a MySQL database based on a SQLite schema?

texts:
I have MySQL on my server and different SQLite databases on a client. I'm trying to find a way to create the same databases on the server.

Since a SQLite schema is actually a CREATE query, one way would be to write a script that extracts every critical part from the create query and converts them into a corresponding MySQL query, finally concatenate them all.

This seems quite a complex and hard-to-cover-all-situations method. I am wondering if there is any alternatives?


texts: How to restore data after MySQL Upgrade?

texts:
I upgraded from MySQL 5.5 to 5.6 by uninstalling 5.5 and then installing MySQL 5.6. Now that MySQL 5.6 is running, none of my DBs or data is present/visible in MySQL Workbench.

I also see that I have two data directories now:

C:\ProgramData\MySQL\MySQL Server 5.5\data
C:\ProgramData\MySQL\MySQL Server 5.6\data

I've tried various things with no luck. I'd really appreciate someone pointing me in the right direction.


texts:
I have this query

    SELECT  dbo.f_DateWithoutHour (0,DATEADD (DAY, 3, TheDate)) AS Date
    FROM (
        SELECT TheDate
        FROM xxxxxxx.dbo.dim_date (NOLOCK)
        WHERE  TheDate >= '2019-02-09 00:00:00' AND TheDate < '2019-04-07 00:00:00'
    ) AS dim_date
    GROUP BY dbo.f_DateWithoutHour (0,DATEADD (DAY,  3, dim_date.TheDate))
    ORDER BY Date

which return this

But the thing is that i want to GROUP BY every 3 days so i get in return

       DATE
2019-02-12 00:00:00.000
2019-02-15 00:00:00.000
2019-02-18 00:00:00.000
2019-02-21 00:00:00.000
2019-02-24 00:00:00.000
          .
          .

Thanks


texts:
I want to insert data from mysql into a table in postgresql. I want to perform a WHERE NOT EXISTS statement for a particular id.

   # Source data: MySQL
   curr_msql.execute(''' SELECT code, subjectname
                          FROM test_subj ''')

    # Target data: PostgreSQL
    for row in curr_msql:
            curr_psql.execute(''' INSERT INTO subs (
                                                    created, modified,
                                                    subjcode, subjname,
                                                    is_pe_or_nstp)

                                  VALUES (current_timestamp, current_timestamp,
                                          %s, %s,
                                          %s) ''', (row['code'], row['subjectname'],
False))

I want to know how can I replace the "False" to a case statement. Something like

CASE
    WHEN code like '%PE%' or code like '%NSTP%'
    THEN True
    ELSE False
END

I tried to run this code:

for row in curr_msql:
            curr_psql.execute(''' INSERT INTO subs (
                                                    created, modified,
                                                    subjcode, subjname,
                                                    is_pe_or_nstp)

                                  VALUES (current_timestamp, current_timestamp,
                                          %s, %s,
                                          CASE
        WHEN code like '%PE%' or code like '%NSTP%'
        THEN True
        ELSE False
    END) ''', (row['code'], row['subjectname']))

I'm getting IndexError: tuple out of index error message
Any advice pls?

The destination DB is PostgreSQL, source data is from MySQL.


texts:
I have a table with a foreign key column to another table with the value on most rows being null. The table is around 10 million rows.
I (accidentally) ran a query that would have updated the rows where the foreign key was null to be x.
I stopped the query with
SELECT pg_cancel_backend(procpid);

as instructed here https://stackoverflow.com/questions/3508627/stop-long-running-sql-query-in-postgresql-when-session-or-requests-no-longer-e
The query did run for some time before I stopped it and disk usage went up while it ran.
None of the rows was actually updated and the disk usage didn't go down after the query had stopped.
Googling suggested VACUUM, which I ran on the table and it finished just fine but didn't free the used disk space. Tried running VACUUM FULL, which finished with
HINT:  Check free disk space.

What is taking the disk space and what can I do to free it?
I don't know what else I should tell so please ask for any other information that might help


texts: Do you know if this are examples of non-identifying or identifying relationships?

texts:
Im using MySQLWorkbench to model a diagram. But Im with some doubts about identifying and non-identifying relationships. It seems that all relationships of the project are identifying.

For example, a conference has one user that created the conference.  So the conference table have a column "user_id" to store the id of the user that created the conference. A user can create many conferences so there is a 1 to many relationship between the users and conferences table. But Im not understanding if its a identifying relationship or non identifying relationship. Maybe its an identifying relationship because the conference table, the sabe in the many side, has the column user_id, so because of that is identifying? But so it seems that almost all relationships are identifying.

For example, a conference can have many ticket types so there is a 1 to many relationship between the conferences and ticket types table. A ticket type also have a column "conference_id" so it should be also identifying?

Also a conference can have many registrations so there is a 1 to many between conference and registrations, so there is a column "conference_id" in the registrations table. Its also identifying right?

Also a registration can have many participants associated, so there is 1 to many between registrations and participants tables, so the participants table has a column "registration_id", its also identifying right?


texts:
I have 2 query results that need merged together where periods are alike, adding a the column 'seats', using a '0' the period doesn't exist. Before:

tdate      period   staff                      tdate     period   seats
-------------------------                    ---------------------------
20141001     9       1                       20141001      9        2
20141001     10      2                       20141001      10       4
20141001     11      7                       20141001      11       2
20141001     12      20                      20141001      14       16
20141001     13      22                      20141002      9        3
20141001     14      15                      20141002      10       7
20141002     8       1                       20141002      11       9
20141002     9       4                       20141002      12       20
20141002     10      9
20141002     11      12
20141002     12      18
20141002     13      14

With this:

DECLARE @max_hr INT =(SELECT Max(CAST(LEFT(StartingTime, 2) AS INT))
  FROM   view_TimeRecords_COMBINED);

WITH cte
     AS (SELECT Min(CAST(LEFT(EndingTime, 2) AS INT)) [period]
         FROM   view_TimeRecords_COMBINED a
         UNION ALL
         SELECT [period] + 1
         FROM   cte
         WHERE  [period] < @max_hr)

SELECT convert(char(8), StartingDate, 112) AS tDate, [period], Count(t.TimeKeeperStaffCode) AS Staff
        FROM cte a
        JOIN view_TimeRecords_COMBINED t
        ON a.[period] BETWEEN CAST(LEFT(StartingTime, 2) AS INT) AND CAST(LEFT(EndingTime, 2) AS INT)
        WHERE convert(char(8), StartingDate, 112) BETWEEN 20141002 AND 20141002 AND TimeKeeperJobFunctionCode NOT IN ('offic', 'maint', 'mgr', 'laun')
        GROUP  BY [period], t.StartingDate

SELECT convert(char(8), POSChitDate, 112) AS tDate, LEFT(POSChitTime, 2) AS period, SUM(Covers) AS seats
        FROM tblPOSChits s
        WHERE convert(char(8), POSChitDate, 112) BETWEEN 20141002 AND 20141002
        GROUP BY LEFT(POSChitTime, 2), POSChitDate

I need to get to this:

tdate      period   staff  seats
----------------------------------
20141001     9       1       2
20141001     10      2       4
20141001     11      7       2
20141001     12      20      0
20141001     13      22      0
20141001     14      15      16
20141002     8       1       0
20141002     9       4       3
20141002     10      9       7
20141002     11      12      9
20141002     12      18      20
20141002     13      14      0

I eventually will also need to group by the day,week & month if you need to take that in consideration as well. It tracks the number of people working -vs- people served by hour.
Using MSSQL 2014.


texts: Report on data in SQL database that does not meet certain rules

texts: I need to generate reports in a web app for rows in my tables that do not meet specific rules. I want to allow the data to be inserted into the tables, but I want to be able to flag it so that a user can return later and correct it. An example of some of the rules I'm checking against are that strings are in certain formats and lengths (like a license plate) and I'm also checking for validity on dates. I expect to have more of these rules come up in the future, so I'm looking for a scalable solution. My application is in .NET, but I've been told it would be better to do this on the DB side rather than in code. Should I be looking into implementing some type of rules engine with stored procedures?


texts:
I'd like to integrate the following query:

SELECT
    parent.id
  , COUNT(child.id) AS child_count
FROM
             messages parent
  INNER JOIN messages child
    ON child.parent_id = parent.id
WHERE parent.parent_id = 0
GROUP BY parent.id;

into this query:

SELECT m.id,m.seen, m.uid, m.hash, m.date_created,m.subject,
m.textplain,m.texthtml, CONCAT_WS('@', a.localpart, a.domain )
AS address
FROM messages m
LEFT JOIN message_address_fields maf ON maf.message_id = m.id
LEFT JOIN addresses a ON a.id = maf.address_id
WHERE maf.field_id =4 AND m.user_id =1

Here's a sql fiddle: http://sqlfiddle.com/#!2/bef27/1

Here's my schema:

 CREATE TABLE IF NOT EXISTS `addresses` (
   `id` int(10) NOT NULL AUTO_INCREMENT,
   `user_id` int(10) NOT NULL,
   `name` text NOT NULL,
   `localpart` text NOT NULL,
   `domain` text NOT NULL,
   PRIMARY KEY (`id`)
  ) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `fields` (
   `id` int(10) NOT NULL AUTO_INCREMENT,
   `name` text,
  PRIMARY KEY (`id`)
 ) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;

CREATE TABLE IF NOT EXISTS `messages` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) NOT NULL,
  `account_folder_id` int(10) NOT NULL,
  `hash` varchar(255) NOT NULL,
  `subject` varchar(255) NOT NULL,
  `texthtml` text NOT NULL,
  `textplain` text NOT NULL,
  `uid` int(10) NOT NULL,
  `seen` tinyint(1) NOT NULL,
  `flagged` tinyint(1) NOT NULL,
  `date_created` int(11) NOT NULL DEFAULT '0',
  `last_modified` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `hash` (`hash`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS `message_address_fields` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `message_id` int(10) NOT NULL,
  `field_id` int(10) NOT NULL,
  `address_id` int(10) NOT NULL,
  `date_created` int(11) NOT NULL DEFAULT '0',
  `last_modified` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

Thanks for the help!

QUESTION: This query limits the data to only messages that contain an address with field id of 4. How would I make it optional to have a correlated address with a field id of 4 or not


texts: Convert JSON array to native integer array

texts:
My function is returning json data which is containing two integer array.
Now I need to get this two integer array list  and passed it to another function.

CREATE OR REPLACE FUNCTION method1(id integer)
RETURNS JSON
LANGUAGE 'plpgsql'
VOLATILE
PARALLEL UNSAFE
COST 100
AS $BODY$DECLARE

RETURN json_build_object(foo:'{1,2,3}',bar:'{10,20,30}');
END;
$BODY$;

I am calling this function to another function using

select * into json_data from
method1(1);

Now I need to pass json_data->foo as a integer list to another function.
How Can i do that?


texts: Remove/suppress dashes from "Results to Text" output in SSMS?


texts: Why does PostgreSQL allow querying for array[0] even though it uses 1-based arrays?

texts:
I have been playing with arrays in one of my PostgreSQL databases.

I have created a table with a geometry array with at least one element:

CREATE TABLE test_arrays (
    polygons geometry(Polygon,4326)[],
    CONSTRAINT non_empty_polygons_chk
        CHECK ((COALESCE(array_length(polygons, 1), 0) <> 0))
);

I have added a few rows, and queried the table for the first element in each geometry array:

SELECT polygons[0] FROM test_arrays;

To my surprise, I got a list of empty rows!

After some research, it turns out that PostgreSQL arrays are one-based:

  The array subscript numbers are written within square brackets. By default PostgreSQL uses a one-based numbering convention for arrays, that is, an array of n elements starts with array[1] and ends with array[n].

So SELECT polygons[0] FROM test_arrays; worked and returned the first polygon of each row.

If PostgreSQL uses a one-based numbering convention, why is querying the 0-th element allowed, and does the result have any meaning?


texts: Mariadb 10.1 / Galera - bootstrap node always doesn't lose quorum

texts:
I'm using mariadb10.1.37, wsrep_provider_version 25.3.24(r3825). On my dev cluster (2node+1arbitrator) I found the following behaviour:

node1: bootstrap new cluster (using galera_new_cluster, with pc.weight=3)
node2: join cluster (pc.weight=3)
arbitrator: join cluster (pc.weight =1)

So, I have a three node cluster, with total pc.weight of 7. Then I do:

arbitrator: shutdown  garbd
node2: shutdown mysqld

At this point, I expect node1, being only node out of three still alive, should have lost wsrep_cluster_status=Primary status, and no longer accept writes.

Instead, I find wsrep_cluster_status = Primary, wsrep_cluster_size=1, and yet node1 will still accept writes.

Is there something I am missing?

Thanks in advance!


texts:
I have a function that was written in Language SQL (not plpgsql) that is scheduled to be called nightly from pgAgent.  It was running fine until last night.  The function usually takes under 3 minutes to execute, but when I came in this morning it was still running 5 hours later.

I queried pg_stat_activity and see that the call is hanging on a wait_event: Extension, wait_event_type: Extension.  I have been trying to research this issue, and the only information I have found is that the query is waiting on a Postgres extension.  The only extensions I currently have installed are plpgsql, postgres_fdw, tablefunc, adminpack, and pgAgent.  The only extensions that seem likely are pgAgent - which is calling the function at the right time, postgres_fdw - foreign tables are used in the function, but querying the foreign tables outside of the function seem to be working fine.

How do I find which extension is causing the wait, and what are the next steps for solving this issue?  There is a lot of information out there about resolving locks, but I was unable to find anything about the Extension wait_event other than a brief description.


texts: Postgresql select row with minimum value by certain column?

texts:
I've data like this

id  student  grade subject
-----------------------------
0   Amy      5     Art History
1   Amy      2     Ethics
2   Jhon     7     Calculus
3   Joe      4     Poetry
4   Joe      2     Cooking
5   Jhon     2     PE

Find out everyone's lowest scores

Results
--------------
1   Amy      2     Ethics
5   Jhon     2     PE
4   Joe      2     Cooking

Tried Solution

SELECT *, min(grade)
FROM data
GROUP BY student

I get error must appear in the GROUP BY clause or be used in an aggregate...