texts: DBCC INPUTBUFFER shows Event Type as Language Event for an unused Database

texts:
In my production SQL Server 2016 server,i had few dummy databases created as part of migrating from SQL Server 2008.
I was cleaning up all the unwanted databases and i dropped few databases.But when i tried to drop an unused database ,i get the error message "Cannot drop the database as it is currently in use".
So i checked the process/session using that database.
I see that spid 144 uses it and when i did DBCC INPUTBUFFER(144),i see an Event Type as Language Event.

I am not sure what it means.

I tried to kill the spid earlier ,but in few seconds a new session started and i am still not able to drop that database.

This particular database is not in use for atleast 6 months now and that is why i tried to KILL the process using that db,cause i was sure that it won;t affect any.

Any idea why a new session is getting created and the database is always in use.


texts: What are possible causes for consistently volatile INSERT performance in MySQL tables?

texts:
I have a Java program running on a VM that is sequentially feeding a queue of single row INSERTs through a single connection to a MySQL server on the same VM. The queue execution is not artificially capped - once a query is done executing, the next one gets executed immidiately. The queue is also never empty.

The data gets inserted into four different MyISAM tables. Two of these have a UNIQUE column and the other two each have a UNIQUE CONSTRAINT across two columns. Most of the data generated by the program already exists in the db, but instead of checking for duplicates, the program just throws the data at the db and ignores duplicate errors.

Now I am logging the execution time of every query with the java program. Obviously that execution time is slightly skewed because it is timed by Java and not MySQL, but I am able to verify the accuracy of the average execution time by dividing a longer time frame by the total number of queries executed in that time frame. So I will assume that the timing from my Java application is accurate.

Onto the actual problem: I have observed that the average time per INSERT is going up and back down by up to factor two fairly consistently (but not gradually) every few hours or minutes. That increase or decrease is consistent across the different kinds of INSERTS, so it should be independent of the table used.

MySQL is steadily using 22% RAM of the VM, Java is using about 7%.
The VM has four CPU cores. Two are never used, Java is using 10-30% of one and MySQL is using 30-60% (never more) of another one.

There's nothing else running on the VM.

If I fire up a second application, queueing INSERTs for another set of tables, CPU usage goes up by 50-100% on each cores and the average execution time goes up a bit (I am not sure about this because the execution time is so inconsistent anyway).

Once the average execution time was at the minimum for about 20 hours, usually it's at the minimum or at the maximum for a few hours a day and then goes up and down a bit for most of the day.

I am pretty clueless about the possible causes for this seemingly random volatility in performance, so any ideas are greatly appreciated.

Edit: I should add that I am experiencing the same phenomenon when doing 100-row to 1000-row bulk INSERTs into InnoDB tables.


texts: Cannot access mysql from a program outside

texts:
I am running a VM that uses windows server 2012 R2 to host a mysql Database , the database works good , i can access it from inside or outside the VM, the problem is that once i try to use the database with Bdoc suite the connection is refused, here are some screens of my problem:

Is the problem comming from my database ? (despite the fact that i can access it using Navicat and the mysqlcmd and :

the firewall is turned off.

I've tried with other users that have the rights on the database.

I've tried using the IP of the VM , and it still did not work.)
Is there any other cause to this problem ?


texts: Arithmetic overflow error varchar to numeric

texts:
I'm getting an arithmetic overflow error and I can't seem to find out the case.
I've recently had to adjust some stored procedures, to allow for dates in the future to be put in.

The following part of the ensuing data extraction now fails with arithmetic overflow errors:

SELECT
  CONVERT(varchar(8),DATEDIFF(week, CONVERT(date,convert(varchar(8),Column1)), GETDATE()))/[Column2]
FROM Table1;

I'm not entirely sure why this error is occurring.
There are no more than 8 characters in any given entry, and changing it to varchar(max) doesn't do anything (as you'd expect).

Removing the /[Column2] does remove the error, but I can't for the life of me figure out why.

EDIT:
CAST([Column2] as numeric(8)) does the trick.
However, select max(len([Column2])) says the longest field is 2 chars long.

[Column1] is a decimal (32,0)
[Column2] is a decimal (3,0)

So what's going on? Why is this causing a problem?


texts: Synchronize 2 sql server 2019 database over intermittent connection (no replication)

texts:
I'm a PostgreSQL DBA learning SQL Server 2019.
The setup :

1 Desktop, 1 Laptop
Both running SQL Server 2019 Developer
Windows 10 Home (nothing else, no active directory, nas, san, ...)
a database in simple recovery model, with partial containment enabled

The problem :

I want to be able to synchronize a database from my laptop to the desktop, or the other way around
I don't want to use replication (log shipping or others) since I may not connect both servers for a long time and my laptop is very limited in disk space. (I'm in simple recovery model for the same reasons)
both (desktop/laptop) database must be writable (so no primary/secondary)
if the database is modified on both side, I'll lose modification on one side. It's ok! I accept this.

The solution I know :

Create a SQL Server dump, send it to the desktop over shared network, apply the dump. or the other way around.

Is there a way to export a database to another database, on demand ? Or is the dump/transfer/restore the way to go ?  (that's what I would do with PostgreSQL)
I don't know all the SQL Server keywords and terminology and my google-fu was unsuccessful. Most solutions I find are obviously about how to set up replication over a reliable/permanent network. which doesn't fit my requirements.


texts:
I am using MySQL 5.5 RHEL 6
I have made these changes in my.cnf file.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-bin=/var/lib/mysql/mysql-bin.log
log-bin-trust-function-creators=1

I restarted the mysql service and its not enabled still:

mysql>  show variables like 'log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |

What seems to be the problem?


texts:
My requirement is to track old rows and modified rows from some particular table and the time when changes are made. Using "Change Tracking" and with help of any system table.

Due to some specific reasons I have to use change tracking as I was told not to use triggers and Change Data Capture.

Right now I am able to fetch current data and which column is modified. But in addition I want to track time of modification and old data.

But if anyone can help me to get any one of these two will be very helpful for me.


texts: I can't add extra node after reset root password mariadb 10.1.20

texts:
I have a mariadb 10.1.20 cluster with 5 nodes.

I don't know root password so I did the following steps:

On second node, I stopped mysql service
I set wsrep = OFF on /etc/my.cnf.d/server.cnf
I executed instructions to reset root password, now password root is empty.
I started mysql service and I logged as root and it works well until now.

But when I set wsrep = ON and then restarted mysql service I got the following error using systemctl status mariadb.service | more:

mariadb.service - MariaDB database server
  Loaded: loaded (/usr/lib/systemd/system/mariadb.service; enabled)
 Drop-In: /etc/systemd/system/mariadb.service.d
       ââmigrated-from-my.cnf-settings.conf
  Active: failed (Result: exit-code) since Tue 2018-02-27 16:18:29 PET; 7s ago
 Process: 27727 ExecStartPost=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
 Process: 2091 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS $_WSREP_NEW_CLUSTER $_WSREP_START_POSITION (code=exited, status=1/FAILURE)
 Process: 1695 ExecStartPre=/bin/sh -c [ ! -e /usr/bin/galera_recovery ] && VAR= ||   VAR=`/usr/bin/galera_recovery`; [ $? -eq 0 ]   && systemctl set-environment _WSREP_START_POSITION=$VAR || exit 1 (code=exited, status=0/SUCCESS)
 Process: 1693 ExecStartPre=/bin/sh -c systemctl unset-environment _WSREP_START_POSITION (code=exited, status=0/SUCCESS)
 Main PID: 2091 (code=exited, status=1/FAILURE)
   Status: "MariaDB server is down"

Feb 27 16:18:24 cluster-test-node-2.reniec.gob.pe systemd[1]: Starting MariaDB database server...
Feb 27 16:18:25 cluster-test-node-2.reniec.gob.pe sh[1695]: 2018-02-27 16:18:25
139937440761984 [Note] /usr/sbin/mysqld (mysqld 10.1.18-MariaDB) starting as process 2050 ...
Feb 27 16:18:27 cluster-test-node-2.reniec.gob.pe sh[1695]: WSREP: Recovered position bbbe7e3b-4e00-11e6-bb1e-4e7ce2820fe5:8300414
Feb 27 16:18:28 cluster-test-node-2.reniec.gob.pe mysqld[2091]: 2018-02-27 16:18:28 140112964700288 [Note] /usr/sbin/mysqld (mysqld 10.1.18-MariaDB) starting as process 2091 ...
Feb 27 16:18:29 cluster-test-node-2.reniec.gob.pe systemd[1]: mariadb.service: main process exited, code=exited, status=1/FAILURE
Feb 27 16:18:29 cluster-test-node-2.reniec.gob.pe systemd[1]: Failed to start MariaDB database server.
Feb 27 16:18:29 cluster-test-node-2.reniec.gob.pe systemd[1]: Unit mariadb.service entered failed state.

Mysql service on second node turns on when wsrep is OFF.

What is missing in this case?


texts: How to SELECT a column based on a condition on the column's name?

texts:
I have the following table named MyTable:

+------+--------+--------+--------+
| Code | U_Col1 | U_Col2 | U_Col3 |
+------+--------+--------+--------+
|  123 |      1 |      2 |      3 |
|  456 |      4 |      5 |      6 |
+------+--------+--------+--------+

I want to write a query to give me the output based on the condition on the column's name. The names of the column are not fixed. They can be anything and come from another table.

CREATE FUNCTION [dbo].[udf_Value] (@Code VARCHAR(3), @Name VARCHAR(10))
RETURNS TABLE AS RETURN (

    SELECT *
    FROM MyTable T0
    WHERE T0.Code = @Code AND <columnname> = CONCAT('U_',@Name)
)

The expected output of

SELECT * FROM [dbo].[udf_Value]('123','Col3')

is 3.

How do I get this kind of a WHERE condition to work? I realize I would normally do it with dynamic SQL, but I want to do this in an inline table-valued function, which won't allow me doing it dynamically.

I tried an UNPIVOT:

SELECT X.Value
FROM (SELECT * FROM MyTable WHERE Code = @Code) T
UNPIVOT (
Value FOR Name IN (@Name)
) AS X

But there is something wrong with the syntax.


texts:
I have a fairly complicated query and running it on two databases with same schemas, but with different data produces different result. On database D1 the elapsed time is around 1 second, and the number of returned rows is 4936. On database D2 the elapsed time is around 10 seconds, adn the number of returned rows is 135. The returned data is correct and expected in both cases, but the execution time is what confuses me.

Both databases have the same indexes. All statistics have been updated and indexes reorganized or rebuilt where neccessary. However, the actual execution plans on both databases differ slightly in their layout and costs.

On database D2 29% of time is wasted on Index Scan of a non-clustered index that executes 409 times (expected executes = 1) and returns 2195 rows, even though it expected only 146. With a Clustered Index Seek (only 2% cost) on another table that executes 2195 times (estimated executions = 146) and that returns 2195 rows (estimated rows = 146, both based on upper Index Scan) it enters into a Nested Loops Inner Join (9% cost).

The same Index Scan on D1 takes only 4.1% of time and executes only once and returns 4451 rows, even though it expected 4491. After it goes Compute Scalar, and then the arrow joins with output from Compute Scalar from Clustered Index Scan (instead of Clustered Index Seek in D2) into Hash Match Inner Join.

Can anyone tell me why this might be happening?

Also, in the plan on D2 I have another Index Scan of the same problematic index that also has 29% cost, but returns 0 rows, even though it expects 27 rows. In D1 this returns 7 rows, expects 8, and has 4% cost.

I'm completely lost. Any help will be appreciated.


texts: Mongoexport halts when it reaches 101 records


texts:
I have slowness.

Can anyone review this plan (PasteThePlan) and tell me what I need to optimize? This is the actual plan.

That is a part of process to get Customer Info from an application. In a trace I found that query takes a long time. It took about 14 sec and in another POS it took less than 1 sec.

I'm using SQL Server 2016 - Express Edition


texts: Consolidating data changes in SQL Server by team

texts:
What is a good way to manage developers' data changes?

I am using RedGate SQL Source Control to monitor changes to tables and stored procecures, so that when we do a production build, I can include all the changes in a SQL update script.

My question is: what is a good way to consolidate and monitor changes made to the data? (As this is something RedGate Source Control cannot track)

For example, new records included in lookup tables? And new records included in tables used for routing procedure calls. Thank you!


texts: What happens when all secondary read only nodes are powered off?

texts:
As per my understanding when all the secondary read only replicas are shut down, the primary replica is the one that will take all the read write requests and read-only requests as well.

But when I was validating this scenario yesterday, my primary instance didn't take read-only requests from my application server; however, it did take write requests.

My question here is: Will the primary take read-only requests whenever read-only secondaries are not available?

This is an Always On 3-node environment. The configuration properties are:

As shown, all nodes are configured to allow all connections when they are primary.

For all read requests, we are using the listener name and application intent= read only. For all write requests we use the listener name. We are not using the node name explicitly anywhere. I just tested in my acceptance environment with my application.


texts: Updating a column name to be a concatenation based on the same table

texts:
Need Help in SQL SERVER!
I have two tables: Students: Student_Id, Student_Name
Grades: Student_Id, Profession, Grade.
I need to change the column Profession to have the value Profession + Grade only for those students who have more than one profession. Is it possible?
I tried:
UPDATE Grades SET profession = CONCAT(profession," + ",grade) WHERE student_id IN (SELECT student_id FROM Grades GROUP BY student_id HAVING COUNT(profession)>1);
Results
Query Error: Error: ER_UPDATE_TABLE_USED: You can't specify target table 'Grades' for update in FROM clause


texts: How do I ALTER all columns of one type to another type?

texts: Let's say I want to change the data type of all columns of one type to another. How would I go about doing this, for example converting all bigints to ints.


texts: Cassandra writing (too?) large partitions

texts:
I have a Cassandra cluster running for quite some time now (months) and, as expected, it has been consuming large quantities of data every day.

In the past few days the cluster has been having problems because one of the nodes keeps crashing and I don't see much in the logs.

One thing that stands out is the following warning but I'm now sure if it is related:

insufficient space to compact all requested files. 846201.56MB required

which is quite more than it is available. Nevertheless, even if there was enough disk to support such compaction, it seems excessive, doesn't it?

Does anyone have any idea what my problem is?

thank you for your attention.


texts: Forced Parameterization and CASE statements

texts:
We have an application which uses an ORM which does not (easily) support parameterized queries. To combat this, I have enabled Forced Parameterization on our database (SQL2008 R2) in order to avoid cache bloat, and encourage plan re-use.

One of the more complex queries is not being completely parameterized, and I have narrowed it down to literal values being used in CASE statements:

SELECT
CASE WHEN auth_date > '2019-02-03 08:48:03' THEN authorisations.authorisation_amount END
FROM authorisations
WHERE auth_date > '2019-02-03 07:48:03'
AND merchant_id = 400000000031

(This query may not look intuitive, but it is a fragment of a larger set of nested subqueries, and we have to use this structure at the lowest level. This works as an example.)

The parameterized statement used by the query plan is:

StatementText="select case when auth_date &gt; '2019-02-03 08:48:03' then authorisations . authorisation_amount end from authorisations where auth_date &gt; @0 and merchant_id = @1"

You'll notice that the literals in the WHERE clause have been parameterized, but the date literal in the CASE statement has not.

Why is this? What am I missing? What could I try to fix this? The literals will change for every execution, so at the moment, even with forced parameterization, I will end up with a new plan per execution.


texts: Postgres 11: fastest way to convert a small list of numbers to text (to be used as NOTIFY payload)

texts:
I am on Postgres 11.5 and in a trigger function I issue one or more pg_notify calls for each row modified in the transaction. The most common payload is a list of 12 float numbers, representing two edges of two 3D points each. Additionally, there are a project ID integer and a short update type string, e.g. the values 1, 'edges', 0.0, 1.0, 2.1, 3.2, 4.3, 5.4, 6.5, 7.8, 8.7, 9.8, 10.9, 11.0 would be a common payload. I can change the exact format used and so far, I called pg_notify like this to create a JSON string:

pg_notify('catmaid.spatial-update','{"project_id": ' ||
    t.project_id || ', "type": "edges", "edges": [[[' ||
    t.location_x || ',' || t.location_y || ',' || t.location_z || '], [' ||
    t.px || ',' || t.py || ',' || t.pz || ']], [[' ||
    t.otx || ',' || t.oty || ',' || t.otz || '], [' ||
    t.px || ',' || t.py || ',' || t.pz || ']]]}')

During the optimization of this trigger function (thanks auto_explain for the trigger plans!), I noticed that the pg_notify call adds a substantial amount of time to the query. On my production system this trigger typically runs for updates on 5-10k rows at a time many times a day. The test case I worked with had 9500 rows to update (= number of pg_notify calls). My trigger function takes about 1800ms for these 9500 rows. If I remove the pg_notify call it's only 270ms. However, if I only use strings during the concatenation (rather then the numeric row values) or alternatively use only a single string as payload, the function runs in 360ms, which makes me think that not the concatenation, but the casts from float to text might be the bottleneck (or the parser is clever and combines the string constant only concatenations).

Also, it turns out that a pg_notify call that doesn't use || for string concatenation, but concat_ws() to create a string from my number is faster:

pg_notify('catmaid.spatial-update', concat_ws(',',
    '[', t.project_id, 'edges', '2', t.location_x, t.location_y,
    t.location_z, t.px, t.py, t.pz, t.otx, t.oty, t.otz, t.px, t.py, t.pz, ']'))

This takes only 1100ms (reproducible) and I am not sure it is only because of the slightly fewer concatenations or fewer intermediate strings. Still, it would be nice if I could get this 1100ms closer to the 360ms with strings only and therefore I wonder if there are faster ways to create a pg_notify payload than what I have written above.


texts: Calculated Measure to get only most current from one dimension on snapshot fact but keep other filters

texts:
I'm working on a tabular cube in SSAS 2012 SP1 CU4. I have 3 dimensions (Requisition, Requisition Status, Date) and 1 fact (Requisition Counts).  My fact table is at the grain of requisitionKEY, RequisitionStatusKEY, SnapshotDateKey.

I have calculated measures that essentially get the lastnonempty value (like a semi-additive measure) for the given period whether it is Year, Month, Or Date:

Openings:=CALCULATE(Sum('Requisition Counts'[NumberOfOpeningsQT]),
Filter('Date','Date'[DateKey] = Max('Requisition Counts'[SnapshotDateKEY])))

This works well until you throw Requisition Status into the mix.  I have rows for each requisition for every day in 2013.  For one of the requisitions, the Requisition Status was Pending for the first 6 months and then it changed to Approved on all data from July 1 to date.  When I summarize the number of openings for the requisition at the month level for July, users see two rows: the sum of the openings for the last populated day in July that it was pending and the sum of the openings for the last populated day in July that it was approved.

Although the total of 2 is correct, I need to change this calculation so that I only get the most current requisition status for the date period selected (Approved) and either show 0 or null or blank for the Pending approval line in the pivot table.

The Requisition Status table looks like this:

Update: Here is a link to a PowerPivot model I made that has some scrubbed data in it to help answer this question. This should better explain the table schemas. The NumberOfOpeningsQT field is basically the number of open positions they have for that job.  Sometimes it is 1, sometimes is is more than 1.  It doesn't usually change over time, but I guess it could. I'm trying to make the Openings calculation give me the desired answer. I threw some other calculations in there so show some things I had tried that had promise but that I couldn't get to work.


texts: Can I automate the "on" statement from MySQL queries?

texts:
Background

In the process of QA/QC, I am writing lots of queries with multiple joins. I am using MySQL.

I would like to know how (if) I can simplify join statements, e.g. by setting default fields to join on.

My tables are named with a plural form of the object that they contain:

 names
 types
 actions
 surnames
 names_surnames

where names_surnames is a many-many lookup table

the primary keys are always id and the foreign keys are always, e.g.

 names.type_id
 names.action_id
 names_surnames.name_id
 names_surnames.surname_id

My joins would be something like

select names.col1
from names join types on names.type_id = type.id
           join actions on names.action_id = actions.id;

Question

Is there a way to get MySQL to always assume that tables will be joined with on thistables.thattable_id = thattable.id?


texts: Update table with sum from other table for the same id

texts:
Given two tables:
Table src
user_id | counter | text
------------------------
5       | 1       | aa
7       | 7       | bb

Table dst
user_id | counter | text
------------------|-----
3       | 5       | ee
7       | 6       | ff

I need to do the following:
If row with user_id exists in src table but not exists in dst table - copy it from src to dst
If row with same user_id exists in both tables, then update the counter in dst with sum of counter from src and counter from dst, and overwrite the text
Final dst should be like that
Table dst
user_id | counter | text
------------------------
3       | 5       | ee
5       | 1       | aa
7       | 13      | bb

I started to insert the rows with missing user_id:
insert into dst
select *
from src as s
where s.user_id not in (select d.user_id from dst as d);

How do I do the update for the exist user_id values?
UPDATE:
What will be the answer in case I have key of two columns user_id and group_id?


texts:
Given a matrix t1 as below, I want to verify the field id_ has at least one 'b' on column value1. Could you help me build the query?
CREATE TABLE IF NOT EXISTS t1 (
   id_ INTEGER  NOT NULL,
   value1 INT NOT NULL
)

CREATE TABLE IF NOT EXISTS result (
   id_ INTEGER  NOT NULL,
   is_b INT NOT NULL
)

INSERT INTO t1 (id_, value1) VALUES
(1, 'a'),
(2, 'a'),
(2, 'b'),
(3, 'a'),
(4, 'b'),
(5, 'a')

INSERT INTO result (id_, is_b) VALUES
(1, 0),
(2, 1),
(3, 0),
(4, 1),
(5, 0)

Thank you very much!


texts: Multiple differences of opinion about transaction log autogrowth settings

texts:
We have a two-node SQL Server 2017 AG. There's a job that looks at autogrowth settings for our databases and makes sure they're not set to use a percentage.  For the past day, its been continually saying it's updating the settings for a particular database.  This hasn't happened before.  Upon investigation, I found sys.master_files on node 01 says the transaction log file for the database will grow in increments of 512MB, but node 02's sys.master_files says it will grow in increments of 10%  Server 02 is the primary.  On both replicas, is_percent_growth is cleared in sys.database_files for this database.  Looking at file properties in SSMS, both replicas show 512MB autogrowth, most likely because SSMS looks in sys.database_files (a guess on my part).
There haven't been any failovers on this AG.
Questions

How could there a difference of opinion between sys.master_files and sys.database_files on node 02?
How did the autogrowth settings for the database get out of sync between the two replicas?
How can I clear the is_percent_growth flag on node 02 so I don't have any more false positives?  Ideally, I'd like to do this without taking the AG offline.

I've read https://stackoverflow.com/questions/4174520/sql-server-sys-master-files-vs-sys-database-files which explains the difference between sys.master_files and sys.database_files, but it doesn't explain the circumstances here.


texts: Converting Access SQL HAVING command to T-SQL

texts:
I have this SELECT query I need to convert to a T-SQL view:

SELECT
    T_Compras.Guia, T_Compras.Data, T_Compras.Id_Embarcação,
    T_Compras.[Nº Maré], T_ComprasDet.Id_Peixe,
    T_ComprasDet.Id_Tamanho,
    SUM([Peso Kg] * [Caixas]) AS Peso,
    T_ComprasDet.Id_Frescura, Q_Guia_Fact.Valor
FROM
    (T_Compras
LEFT JOIN
    Q_Guia_Fact ON T_Compras.Guia = Q_Guia_Fact.Guia)
INNER JOIN
    T_ComprasDet ON T_Compras.Guia = T_ComprasDet.Guia
GROUP BY
    T_Compras.Guia, T_Compras.Data, T_Compras.Id_Embarcação,
    T_Compras.[Nº Maré], T_ComprasDet.Id_Peixe,
    T_ComprasDet.Id_Tamanho, T_ComprasDet.Id_Frescura,
    Q_Guia_Fact.Valor
HAVING
    (((T_Compras.Guia) = [Forms]![F_Compras]![Guia]));

I am able to get everything working but I don't know how that last HAVING clause would be in T-SQL. In Access what it is doing is prompting the user for a guide number (guide="guia") so that it only shows rows with that guide number.

Thanks in advance!


texts: What is the best practice for physical deployment of data marts?

texts:
We are just starting to get into business intelligence at our company. We have some architects who are designing our data warehouse and also our data marts. They are using star schema for both designs.

The architects have stated that the design they are using for the marts is the same, that we just need "another copy" of it for whichever customer wishes to query their mart and that the data may differ per mart.

We only had one mart to start with. This was one AIX LPAR, one DB2 database instance, and one database, with one schema.

Now we have requests for three more data marts. What is the best practice? Should these be different schemas within the same database? Should each mart be a separate database within the same instance? Should we have separate databases, separate instances?

What is the best practice here from a physical deployment and maintenance perspective? Or what guidelines are used to define when it should be which option?


texts: Restore exceeds licensed limit of 10240 MB per database. All I really need is the DB structure

texts: I received a .bak file from a customer that I need to Restore, but the Restore exceeds licensed limit of SQL Server 2008 R2 10240 MB per database.  All I really need is the DB structure. Is there a way to Restore anyway or for me to just Restore the structure?


texts: SQL Server Database Engine Tuning Advisor vs DMV queries

texts:
What is the best way to tune the indexes on my production database? I normally use the following query :

SELECT
    user_seeks * avg_total_user_cost * (avg_user_impact * 0.01) AS [index_advantage],
    migs.last_user_seek,
    mid.[statement] AS [Database.Schema.Table],
    mid.equality_columns,
    mid.inequality_columns,
    mid.included_columns,
    migs.unique_compiles,
    migs.user_seeks,
    migs.avg_total_user_cost,
    migs.avg_user_impact
FROM
    sys.dm_db_missing_index_group_stats AS migs WITH (NOLOCK)
    INNER JOIN sys.dm_db_missing_index_groups AS mig WITH (NOLOCK)
        ON migs.group_handle = mig.index_group_handle
    INNER JOIN sys.dm_db_missing_index_details AS mid WITH (NOLOCK)
        ON mig.index_handle = mid.index_handle
ORDER BY index_advantage DESC;

This gives me a list of missing indexes and I used to add them and re-run the query a few hours later to see the result. Will it be better for me to capture a workload table using SQL Profiler and then use the Database Tuning Advisor?

Also if I use the tuning advisor does it re-run all the queries in the workload file e.g. if there was an insert statement captured will it re-insert the record on the database I run the analysis against? Or does it just analyze the insert statement? I could not find any information on how that data is treated and if it would be safe to run the analysis to against my production database after hours? (my worry is that the analyzer changes the data in my production database).

An alternative would be to backup my production database in the morning, capture the workload file during the day then restore the production database on my test environment together with the workload file and execute the DTA overnight on my test environment. Is this a good approach?


texts: How to execute dependent multiple SQL queries from multiple databases via same PHP web page?

texts:
We can connect a single PHP page with more than one databases. But what about executing multiple dependent queries that belong to different databases?

mysqli_multi_query(connection,query);

does not seems helpful as it asks for a single shot while my second query uses result of the first. I tried using two connection strings and using them with two queries but this does not seems to be helpful. My code uptil now is here:

   <?php
   //Connection with DB
   $user = "root";
   $pass = "";
   $db1 = "raw_images";
   $db2 = "labelled_images";
     $conn1 = mysqli_connect('localhost',$user,$pass,$db1,true);
   $conn2 = mysqli_connect('localhost',$user,$pass,$db2,true);
   if(!$conn1||!$conn2)
   {
   die(mysqli_error());
   }
*/
//query to display image
   $query1 = "SELECT * FROM raw_images.tbl_raw_image WHERE id IN
    (SELECT id FROM (SELECT id FROM raw_images.tbl_raw_image ORDER BY RAND() LIMIT 1) t)";
   $sth = $conn1->query($query1);
   $result=mysqli_fetch_array($sth);
   $id=$result['id'];
   $image=$result['image'];
   $query2="INSERT INTO `labelled_image`.`tbl_labelled_image` (`id`,`image`, `label`, `id_in_raw_image_table`) VALUES (NULL, '$image', '0', '$id')";
   $rs  = mysqli_query($conn2, $query2);
   echo ''; ?>

When using such approach, first query executes successfully but not the second one. While executing it simply does not inserts values which I retrieved from the first table but only the values which are given directly to the query like label which I pass zero hence it results in the following outcome:


texts: What is the commonly-accepted practice to denote the different meaning of NULLs?

texts:
I am thinking about what's the meaning of NULL in my database systems. In some columns it mean unknown / missing data. In some columns it mean a not-applicable field. In some columns it mean the entity represented does really not have the property.

For example, imagine a database which stores a field to identify if the user is opted-in to certain kind of promotion channel which uses an ad ID. The promotion channel will send ad to users using ad ID. If the user does not have an ad ID (i.e. the ad ID is NULL) ads will not be sent.

However, the business now want to improve ad coverage so it starts to think about what the meaning NULL is apart from not having a valid ad ID stored in the system. The business logic is that, eligible users can type in their ad ID to opt-in, however the eligibility is determined by some other business constraint (such as age) with those non-eligible users can't get one. Also we want to know if the user has explicitly said "no" to the question v.s. if the user hasn't been asked at all.

So we now have 3 reasons of NULL: the user hasn't been asked, the user has explicitly said no, the field is not applicable to the user, and these are all stored as NULL in the ad ID column which result in ad not being set. We also want to avoid any special handling (sentinel values) in the code to send ad as it is very simple - it just send ad to the list of ad ID without any exceptions.

So what is the common practice to denote those? If NULL can only mean one thing for a certain column, it is simple.

If NULL can mean either "missing data" or "known to have none", it is also simple - refactor the row to another table where the existence of a NULL row means known to have none, and a missing row means "missing data".

However, now there is the third meaning of "inapplicable to the row", what is everyone's practice to handle this? Is it a common practice adding constraints that trying to set an ad ID on the row for an ineligible user will cause error?

Also, has anyone encountered the case where a NULL column can have even more meanings on the NULL field?


texts: How can I tell which client process owns a particular Unix domain socket connection?

texts:
I recently tried to call ALTER TABLE, which got blocked because one of the transactions holds a lock on the related table. With the help of #postgresql IRC channel, I managed to track down the problem to this particular process:d33tah

47329  0.9  0.0 275836 95384 ?        Ss   Mar03  23:59 postgres: d33tah projectname [local] idle in transaction

Still, I have no idea which client process owns this connection, so I cannot tell what it is doing. How can I debug that?


texts: How to model entity types that can contain each other?

texts:
I'm having a hard time figuring out how to model a database with a Many-to-Many relationship between a concept of Markets & regions of the world.

The issue I'm running into is that a Market is just a region for doing business. It could be North America, and include multiple cities, provinces, and countries, or it could be the city of Ottawa, Ontario, Canada. Meaning, a Market can contain many regions, or regions can contain many Markets.

I've seen some structures for this concept with a separation between a Global Market which would be ones that include more than one country, and a Regional Market which would be only within one country. But this is treating them as two different entities when, by nature, they're the same.

Is there a standard model for structuring data that could efficiently convey the parent in the relationship? To easily be able to say The North American Market contains these countries, which would enable deriving provinces & cities, but also say The USA contains these Markets or Texas contains these Markets.


texts: How database size affects performance: Theory vs reality

texts:
There is a lot out there that says database size should not affect performance to any great degree. As long as indexes on tables fit in memory the database should remain performant.

However what is the reality? If the database architecture isn't the best, indexes don't fit in memory, and there is potentially a lot of redundant data are there significant gains to be made simply by deleting redundant data? I estimate that 60-80% of data in my database could be deleted.

I believe that reducing the database size and upping the RAM so that indexes can fit in memory would give a significant performance increase which would give some breathing room for a few months to rearchitect the system.

Are there also other factors such as IO, fragmentation, working dataset etc that affect performance based on database size?


texts: How to handle duplicates in an UPDATE on a primary key in Postgres?

texts:
Let's assume a scenario with the entities Person and Company and a table PersonCompanyStocks that models how many stocks a persons owns of a certain company (N:M cardinality). For example:
person | company | num_stocks
-----------------------------
Alice  | foo     | 300
Bob    | foo     | 100
Bob    | bar     | 200

This table uses (person, company) as a primary key to guarantee unique entries, and foreign keys to the respective person/company table (I'm only using string IDs for simplicity).
Now let's assume company bar buys company foo. We want to update the table in a way that it becomes:
person | company | num_stocks
-----------------------------
Alice  | bar     | 300
Bob    | bar     | 300

Looking only at Alice's record suggests to use a naive approach like:
UPDATE
  PersonCompanyStocks
SET
  company = "bar"
WHERE
  company = "foo"

However this update fails with duplicate key value violates unique constraint ... because for Bob there already is a row with the key ("Bob", "bar"). For INSERT's Postgres supports ON CONFLICT DO ..., but it looks like there is no equivalent for UPDATE. And clearly we also have to deal with properly merging the num_stock value of the two rows.
What is the best strategy to approach this problem? I only see a relatively ugly solution:

One query to determine the duplicates.
One UPDATE to merge the duplicates into the final row.
One DELETE to remove the offending duplicates.
The above UPDATE to do the renaming in rows without duplicates.

This feels complex and is probably prone to race conditions. Does Postgres offer any trick to solve this more elegantly?


texts: MS Access copy/paste of table fails with message about clipboard size

texts: I have an mdb file that has some large tables in it (greater than 65000 rows). I used to not have any problems duplicated the table by copying and pasting but just recently I started getting a message about the size of the clipboard not being able to hold more than 65000 lines.  What's weird is the operation works on another computer with the same version of Access (32bit 2010) and the same table and doesn't report this error message. It just started reporting this message on the "problematic" computer recently, and nothing has changes as far as I know except for Windows automatic updates. Any help appreciated.


texts: How best to store complex cost information

texts:
I am creating a database to store data related to costs. The cost information can be very varied and I won't be able to fully determine all possible columns currently.

An example piece of data might be the cost of a F-15 jet. So taking this from Wikipedia:

$27.9 million in 1998 dollars.

I will also need to store contextual information, for example:

Model Variant
Weight
Wingspan
Length

The model must also be able to store completely different information such as price of a pressurised water reactor used in a nuclear power plant. The cost could be:

€200 million in 2011 euros.

The contextual information for this would be things like:

Throughput
Max Operating Temperature
Weight
Country of Origin

From a database design point of view the cost information seems quite easy to capture, all costs will have:

Description
Economic Conditions
Unit
Value

The contextual information however can be anything and as such it's hard to design a schema that will easily capture all the relevant data.

The purpose of having this data is to use it to estimate future costs. So all contextual information should be searchable, the user might need all costs from the database that are related to aircrafts or all information on the cost of valves under 100Kg in weight.

So my question is, what is the best way of storing this data? Will I just have to try and create a schema as detailed as possible, will something like Entity-Attribute-Value work best or maybe a NoSQL document store?

Thanks for your help.


texts: Nonclustered Index still doing index scan

texts:
I have this simple query.

SELECT a.RecordID SubmissionID,
  a.[DateSubmitted],
  a.[FinalAction],
  a.[FinalActionReason],
  a.[FinalActionDate],
  b.GradeSubmissionListID,
  b.[Action],
  b.ActionReason,
  b.ActionDate
FROM [dbo].[GradeSubmissionList] a
  LEFT JOIN dbo.GradeSubmissionApproveRejectLog b
  ON a.RecordID = b.GradeSubmissionListID
WHERE a.EdpCode = '1314ACT1258'
ORDER BY a.DateSubmitted

This produces the following execution plan,

I want to get rid all Clustered Index Scan. When I hover the first clustered index scan, it generated this image,

It looks like it's using the Primary Key index,

ALTER TABLE [dbo].[GradeSubmissionList]
ADD CONSTRAINT [GradeSubmissionList_pk_RecordID] PRIMARY KEY CLUSTERED
([RecordID] ASC) ON [PRIMARY]

So I created one based on this article: Why use the INCLUDE clause when creating an index?

CREATE NONCLUSTERED INDEX idx_dmcasarms_GradeSubmissionList_RecordIDEdpCode
ON [dbo].GradeSubmissionList(RecordID, FinalApprovedRejectedBy, EdpCode , DateSubmitted)
INCLUDE ( FinalAction, FinalActionDate, FinalActionReason );

I am expecting that the sql statement will now be converted into Index Seek, but shows out it's still scanning the entire row.

What are the thing the I need to do in order to make this work? If there are things that you want to know about my situation please do ask.


texts:
I am testing how certain operations are logged under different recovery models.Below are steps i have did till now
1.create database in full recovery model
2.take backup
3.create a table and insert 10 million records
4.Take log backup,check VLF count and see log space free percentage
5.Now do an index rebuild and see the records generated using fn_dblog function
6.Now i swicthed to bulk recovery model
7.Took  a backup
8.Taken a log backup
9.did an index rebuild
Strangely index rebuild is failing with below error.
The statement has been terminated.
Msg 9002, Level 17, State 2, Line 1
The transaction log for database 'bulklogging' is full due to 'LOG_BACKUP'.
thats not true ,actually
1.log space autogrowth is not restrcited

2.Space on where logfile stored

Can some one help me understand why i am getting above error ,eventhough space is there and autogrowth is not restrcited
adding image of index size..
Issue got resolved,but not sure how this change made difference.Any pointers would be much appreciated..
Comment is saying two long  --so posting here...
i scripted out index again ,after changing recovery model and then it worked.Moreover scripted index before and after exactly remain same,only session got changed.But i am not sure how this got worked .
Before :
USE [bulklogging]
GO
ALTER INDEX [PK__bcc__3213E83FAC9DB5ED] ON [dbo].[bcc]
REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

After:
USE [bulklogging]
GO
ALTER INDEX [PK__bcc__3213E83FAC9DB5ED] ON [dbo].[bcc]
REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF,
STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
GO

Update to close this question:
I am using Fn_dblog to check log records,This seems to have a hidden Bug as described here,which may be affecting my log growth..

Edit 8/15/13: Beware – Jonathan just found out from a customer system that uses this extensively that every time fn_dump_dblog is called, it creates a new hidden SQLOS scheduler and up to three threads, which will not go away (and will not be reused) until a server restart. It’s a bug that the SQL team is going to fix now we’ve alerted them to it. Use with caution.
Edit 5/15/15: It’s fixed in SQL Server 2012 SP2+ and SQL Server 2014. The fix won’t be backported any earlier.


texts: What is best way to handle transaction in sql server when inserting in 15 to 20 tables in one batch

texts:
What is best way to handle transaction in SQL Server when inserting in 15 to 20 tables in one batch.

Currently I am using single transaction for multiple insert statements in stored procedure.For this I am passing table type object to the stored procedure which does multiple table insert and updates. However, it is taking too much time.

CREATE PROCEDURE InsertRecurringInvoice]
(

    @InvoiceItems tabletype  object

    @InvoiceCredits table type
    @InvoiceShipmentAddress table type
    @InvoiceNotes table type
    @InvoiceAccountingEnteries table type
    @InvoiceAccountingEnteryRows table type
    ......15 table types

)
as

Begin

  begin transaction

  INSERT INTO [dbo].[InvoiceAccountingEnteries]
  SELECT * from InvoiceAccountingEnteries

  INSERT INTO [dbo].[InvoiceAccountingEnteryRows]
  SELECT * from InvoiceAccountingEnteryRows

  ...15 tabless

END


texts: Unable to connect SQL Managed instance in the vnet

texts:
One of our team member wanted to create a Managed instance for his testing and was asked to use existing vnet which was already configured to connect remotely and had other Managed instances for POC.

While building he checked the 'Prepare subnet for Managed instance'. The deployment which should not have taken more than an hour took 6-7 hours and we knew it has done some changes that will cause issues. The deployment was successful and managed instance is up and running.

The real issue :

We can't connect to the MI remotely from the domain anymore. We are investigating ,but would really be helpful if anyone can point out what it would be.

(PS: I am weak in Networksconcepts)


texts: SQL Server 2012: NUMA node shown as offline without special affinity settings

texts: My main production server has four NUMA nodes with two SQL Server 2012 instances on it.  One of the instances has the CPU affinity set to use only one NUMA node, and sys.dm_os_nodes reflects that correctly. The other instance has no affinity settings, so it should show all 4 NUMA nodes as online, I would think, but it shows Node 3 as offline, which happens to be the node that's online for the other instance. Is there a bug in SQL Server 11.0.3128?


texts: Connecting remote MySQL database to local MySQL database?

texts:
I want to write PHP code to be embedded in a Drupal7 module.

I want to call a procedure which can copy newly generated data in the local MySQL database to a remote MySQL database.

When data is inserted in table A of my local database it should be copied to the specific table B on the remote MySQL database.

Table 'A' is on local host.

Table 'B' is on remote server.

INSERT data on 'A' -> copied to 'B'

Is this possible?


texts: I have a DB with 180 tables, and I was doing a logical deletion, now I need to do some testing with physical deletion. Is there a way to alter all my tables and to drop their fk's and add then add their corresponding fk's with its on delete cascade?


texts:
Is it possible to disable the option to generate a database script from SSMS? From Right click on a DB >> Task >> Generate scripts..

Thanks,


texts: Matching single column against multiple values without self-joining table in MySQL

texts:
We have a table that we use to store answers to questions. We need to be able to find users that have certain answers to particular questions. So, if our table consists of the following data:

user_id     question_id     answer_value
Sally        1               Pooch
Sally        2               Peach
John         1               Pooch
John         2               Duke

and we want to find users who answer 'Pooch' for question 1 and 'Peach' for question 2, the following SQL will (obviously) not worK:

select user_id
from answers
where question_id=1
  and answer_value = 'Pooch'
  and question_id=2
  and answer_value='Peach'

My first thought was to self-join the table for each answer we are looking for:

select a.user_id
from answers a, answers b
where a.user_id = b.user_id
  and a.question_id=1
  and a.answer_value = 'Pooch'
  and b.question_id=2
  and b.answer_value='Peach'

This works, but since we allow an arbitrary number of search filters, we need to find something much more efficient. My next solution was something like this:

select user_id, count(question_id)
from answers
where (
       (question_id=2 and answer_value = 'Peach')
    or (question_id=1 and answer_value = 'Pooch')
      )
group by user_id
having count(question_id)>1

However, we want users to be able to take the same questionnaire twice, so they could potentially have two answers to question 1 in the answers table.

So, now I'm at a loss. What's the best way to approach this? Thanks!


texts: How to remove triggers from the restored database in SQL Server

texts:
I am using SQL Server 2008 Management Studio. I've got a .bak backup file from a client to resolve a specific issue.

Once I restore the database from the .bak file, I am unable to do any alter query or alter master key.

In my new database, all the triggers that were created at the client environment DB is attached to it. So getting the below error.

  Msg 208, Level 16, State 1, Procedure dbtrg_DBEventsmodel, Line 16
  Invalid object name 'DBMonitor.dbo.ddl_DBEvents'.

Is there any way to detach those triggers in the restored DB ?


texts: Moving MAX and MIN calculation into a sub query to preserve the full result set in the outer query

texts:
The query's objective in brief is to list all the posts according to its score, which is calculated according to a calculate scores of date, views, and rating

The problem with the query is that it only give 1 row result instead of showing all of the posts.

I think the problem is with using MAX() and MIN() without separating it from the LEFT JOIN query used to get the SUM() of the Rating and the Views.

THE MCVE http://sqlfiddle.com/#!9/70d1ec/1

SELECT
          p.id
         , p.date
         , p.title
         , r.module
         , r.module_id
         , IFNULL(v.total_views,0)  AS views
         , r.total_rating           AS rating

     #the following formula calculates the Score 10*(MAX(points)-(points))/(MAX(points)-MIN(points))

    , round((round(10-(((PD.MaxDate-p.date)/(PD.MaxDate-PD.MinDate))*10), 3) + round(10-(((MAX(v.total_views)-v.total_views)/(MAX(v.total_views)-MIN(v.total_views)))*10), 3) + round(10-(((MAX(r.total_rating)-r.total_rating)/(MAX(r.total_rating)-MIN(r.total_rating)))*10), 3))/3, 3) AS Score

  FROM  posts p

  LEFT  JOIN ( SELECT ra.module_id
              , ra.module   AS module
              , SUM(ra.ilike)    AS total_rating
           FROM rates ra
          WHERE ra.module = 'posts'
          GROUP
             BY ra.module_id
       ) r ON r.module_id = p.id

  LEFT  JOIN ( SELECT pv.post_id
              , SUM(1)    AS total_views
           FROM posts_views pv
          GROUP
             BY pv.post_id
       ) v ON v.post_id = p.id

   JOIN (SELECT MIN(date) AS MinDate, MAX(date) AS MaxDate FROM posts) PD

    ORDER BY Score DESC


texts:
I have a mySQL db setup in MAMP in which I connected mySQL Workbench to administer the design of the database.

When I inserted some data today from within myPhpAdmin (localhost:8888), I noticed the data was being displayed with hashed or hex values.

Here is a screen of the table view which you can see the odd display and text format offering a link to download (never seen this).

If I edit a record, the data is properly displayed.

But if I export the data, the values look to be hexadecimal.

Is my install hosed? Or did mySQL Workbench do something?
Thanks for any answers here.

Rich


texts: mysqladmin - u root -p ping is not executing

texts:
I am getting the below error in mysql while trying to ping. i am using mysql version 5.5.37-0ubuntu0.12.04.1. when ever i use mysqladmin i am getting the below error.

Please suggest how to clear this issue

karthick.g.s@sumtwo:~$ mysqladmin -u root -p ping
Warning: mysqladmin: unknown variable 'loose-local-infile=1'
mysqladmin: unknown variable 'local-infile=1'


texts: What is the effect of having an opened transaction in MSSQL for too long?

texts:
I'm just wondering what happens if you begin a transaction in a DB and forgot to commit or rollback it. Will the server be down? Lets say you left it for 3 days.

There are also users who are using it assuming that the other users did not know that there is an unclosed transaction (let's just assume that the users are just inserting data on the database). What are the consequences of this action?


texts: I am receiving the following error - The definition of object x has changed since it was compiled

texts: One of my clients have been receiving this error that is causing issues in their process. Basically, they are thinking the error is related to replication of stored procedures that they assume I messed up. I had to drop some stored procedures that was part of replication and in order to do that I had to remove them from replication and I did generate a new snapshot for synchronizing with the subscriber and it was from then on they saw this error popping up in one of their stored procedures which happened during the same day, but then later on Sunday it happened again for other stored procedures. Now, my question is this related to replication or there is something else to it. Client already mentioned that they did not make any changes at their end.


texts: Matching same unknown characters in two columns

texts:
So here is the situation. I have a database with values in columns that look something like this

field_0_data1, field_0_data2, field_1_data1, field_1_data2

Is it possible to only select the columns where the values in the middle is the same. Honestly I'm not even sure if I'm asking right but.

Say it was something like

SELECT * FROM mytable WHERE column1 LIKE "field\_(%)\_data1" AND column2 = "fields\_\1\_data2"

where \1 is a back reference to (%)

I'm sure I have the terminology wrong but hopefully you can understand what I'm trying to do.

"No, this cannot be done." is a valid answer.

Thanks

Edit: More information

Let's say that the table looks something like this.

ID | page_id | key           | value
1  | 100     | field_0_data1 | 1
2  | 100     | field_0_data2 | 500
3  | 100     | field_1_data1 | 2
4  | 100     | field_1_data2 | 1000

So what I need to do is in one query I want to search for a value in field_%_data1 and get the value from the matching field_%_data2. So what I need to do is match the result of the % when getting the value of the second field.

Edit: Even more information
I know I asked this a long time ago and there probably isn't an answer. While I no longer need this at the moment, it would still be nice to find a solution because I'm sure I'll run into it again in the future.

The query I'm looking for is to query a WordPress database. I did not say WP because I already know that this is impossible using something that WP provides and I would need to build my own SQL query to do it. This also has to do with Advance Custom Fields (ACF) but also not something that it can do.

I have 1 piece of information, a value that might be in a repeater field. Let's say that the value I have is "1000". I have no other information to work with. In a single query I need to be able to have it return the value "2". Either of the fields with the key "field_0_data2" and/or "field_1_data2" could hold the value that I'm looking for.

Yes, this could be done with multiple queries and some PHP in between. (please exuse the code, it is not meant to be perfect and I know there are errors in it, it's just to highlight the example)

1)

SELECT ID, key FROM table WHERE key LIKE "field\_%\_data2" AND value ="1000"

2)

$row = preg_match('/field_([0-9]+)_data2/', $key, $matches);
$new_key = 'field_'.$matches[1].'_data1';

3)

SELECT value FROM table WHERE id = "{$ID}" AND key = "{$new_key}"

What I was hoping what some type of back-referencing in the SQL language that would accomplish the same thing is a single query.

Once again, telling me that this is impossible is completely acceptable. It will keep me from wasting time in the future looking for something that does not exist.


texts: Database Mail stops working after browser service stopped

texts:
After stopping the browser service on a 2-node SQL Server failover cluster system, the Database Mail component stopped working. It fails and creates log entries as follows:

  Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException
  Message: There was an error on the connection.
  Reason: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.
  Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified), connection parameters:
  Server Name: SQLCLNETWORK1\INSTANCE1,
  Database Name: msdb
  Source: DatabaseMailEngine

The instance behind "SqlClNetwork01" is running on non default Port. The SQL Server engine itself continued without any problems.

So my question is, why does the Mail Service have issues with the browser service stopped? Where is it trying to connect to the SQL Server engine without success?


texts: Finding exact row in Clustered index leaf page

texts:
When traversing through Clustered index in Sql Server, the engine will start from Root page and traverse over non leaf nodes until it reaches leaf level page (data page).
Every data page has row offset array where every slot is ordered by clustered key (physical order of data may not be ordered). Slots only have 2byte offset of the row (doesn't contain key value).
My question is:
If I have say table with two int columns (8 byte) i would have around 1000 entries per data page. How does engine locate exact row with index 500 (if that page contains rows with indexes from 1 to 1000). Does it traverse through slot sequentially examining every row or is there any more efficient algorithm which sql server use?


texts: How to downgrade my mdf file from version 661 to 655 or earlier?

texts:
I have a problem when attaching the file AdverntureWorksLT2008R2_Data.mdf to Microsoft SQL Server Management Studio (SSMS).

  The database 'AdventureWorksLT2008R2' cannot be opened because it is version 661. This server supports version 655 and earlier. A downgrade path is not supported. Could not open new database 'AdventureWorksLT2008R2'. CREATE DATABASE is aborted. )Microsoft SQL Server, Error: 948)

So, I was wondering if it is possible to do something on the .mdf file so that the .mdf file will be downgrade to version 655 or earlier.

There is a similar question(s) such as this - Attaching database to the SQL server

The correct answer mention about getting to know more about the version and one suggest that latest Service Pack must be installed.

I have checked the version and notice that the latest Service Pack have been installed.

So, I was thinking of downgrade the mdf file but how do I downgrade it?


texts: How to relate 2 user profile tables to a single login table?

texts:
I have an Employee table , and another table named Student. Now I have a third table named the Login.
Employee has emp_id as PK , and other specific attributes for an employee.
Student has std_id as PK , and other specific attributes for a student.

The login table has user_id as PK , but the user_id should also be FK from both employee and student. Means that if there is a profile for an Employee or Student then there will be a record for them in the login table. But I am not able to link the user_id of the Login table to both Employee and Student at the same time in a one-to-one relationship.

Could you please help me how to that. I am using MySQL for this.


texts: How to create a database for unknown kinds of data?

texts:
I am in the process of designing a database for a new PHP/MySql-based application.

My problem is that I do not and cannot represent what should be saved in the database because it is unlimited and changing.

Here is the problem example:
The application will be a shopping website that has many kind of products all of them have some shared attributes such as title and price but some kinds have specific details such as expiry date some have isbn some non.

This is just an example but I really have many kinds with many different attributes.

I can create a table for each kinds, but what I have is not all the available kinds, many kinds of items are unknown at this time.

Is their a way to accommodate this problem without over head in the user's side?


texts: I am now using latin1 (CHAR 10 column type) and I was wondering if there is more efficient character-set to store password hash consisting of a-z0-9 characters.


texts:
Let's I have two  tables(I have removed some columns for brevity):

  ADULTS -> ID

  CHILDS -> ID

And I have one table which includes detials about these persons like that:

  USERS-> ADULT_ID, CHILD_ID, REGISTER_DATE

The other choice is that, but in this case I need another container:

  PERSONS -> ID, ADULT_ID, CHILD_ID

  USERS-> PERSON_ID, REGISTER_DATE

In second approach, the datas will be like this:

PERSONS:

Isn't it poor design?

I have read this question, but the problem here is a little bit different.

What do you recommend me?


texts: Add data to second table on UPDATE (PostgreSQL only)

texts:
I have table ticket which has these (and other) columns:

id
ticket_type
client

If ticket_type or client gets updated, then the table transfere_table should get filed. The ticket.id of the changed ticket should get inserted into the transfer table.

Here is the working code:

CREATE FUNCTION add_ticket_to_transfere_table () RETURNS trigger
       LANGUAGE plpythonu AS $$

plpy.execute(plpy.prepare(
'''INSERT INTO transfere_table (ticket_id) VALUES ($1)''',
     ['int']), [TD['new']['id']])
$$;

CREATE TRIGGER add_ticket_to_transfere_table_trigger
    AFTER UPDATE OF ticket_type_id, client_id ON ticket
    FOR EACH ROW
    WHEN (OLD.ticket_type_id != NEW.ticket_type_id or
         OLD.client_id != NEW.client_id)

    EXECUTE PROCEDURE add_ticket_to_transfere_table();

Since I only used plpythonu up to now, I solved it like this. It works, but I see two things to improve:

get rid of FOR EACH ROW: If N rows get updated, it would be nice if only one INSERT gets executed
Don't use plpythonu

How to realize these improvements?

Are there other things which could get improved?

I am using PostgreSQL 9.3.13


texts: PIVOT, Row_Number() returning multiple rows

texts:
I'm trying to pivot a list of emails onto a single row, but for some reason my primary email address is getting a row of its own, so Im ending up with two rows per contact.

CREATE TABLE #tmp (
ID VARCHAR(20)
, EMAIL VARCHAR(50)
, isPrimary VARCHAR(1)
)

INSERT INTO #tmp (ID, EMAIL, isPrimary) VALUES ('B4100358183Q!ZH.ETES', 'ABoyd@Energize.com', '1')
INSERT INTO #tmp (ID, EMAIL, isPrimary) VALUES ('B4100358183Q!ZH.ETES', 'BSykes@Energize.com', '0')
INSERT INTO #tmp (ID, EMAIL, isPrimary) VALUES ('B4100358183Q!ZH.ETES', 'TC6@test.com', '0')

How would I amend the script below to return only one row containing all 3 email addresses rather than two rows?

SELECT ID, [1], [2], [3]
FROM (
  Select ID, EMAIL, isPrimary
  , ROW_NUMBER() OVER (PARTITION BY ID ORDER BY isPrimary desc) AS rn
  from #tmp
) eml
PIVOT (
  min(EMAIL)
  FOR rn IN ([1],[2],[3])
) P

Any help would be appreciated, thanks.


texts: Connections remain active even after terminating the session in Postgres

texts:
Yesterday, I added the pldbgapi extension in my Postgres DB to debug a function. I was using PgAdmin for debugging the same. But today, when I tried invoking a simple query, it was taking a lot of time to execute. I tried to find out the active sessions using the below query:
select * from pg_stat_activity where datname = 'dbname';

In the results, 6 sessions were there, out of which 4 were active. On checking the query that these active sessions were executing, I found that all the 4 sessions were executing the same query and these queries were invoked yesterday when debugging the function.
So every time I debug a function, a new session was created and all the created sessions didn't get closed and remained in the active state.
I tried to close the session using pg_terminate_backend and pg_cancel_backend but nothing works and all 4 sessions remain in the active state.
I didn't know how this happened and why I am not able to close these connections.


texts: MySQL Command to Mass Change Specific DB from MyISAM to InnoDB and vice versa?

texts:
I have an mu WordPress database and I want to change it from MyISAM to InnoDB to test its performance.

Which MySQL command/commands will do it for all of the tables instead of doing manually one by one?

Is there anything that I should keep my eye on in this application?


texts: Is it possible to create a view in one database from another database in oracle when databases are different machine?

texts:
Lets say I have a oracle database db1(actually user)  in machine A
and another database db2 in machine B. Is it possible to create view in
db2 from db1.If possible how ?


texts:
I have a table in my Azure SQL database named "tablex". I want to bulk insert the content of a csv file in that table (the csv file has the same columns as tablex in the Azure DB)
This is the command I've tried:
bcp dit.dbo.tablex IN "/Users/maker/DEV/env_dev/tablex.csv" -S example.database.windows.net -U user@example.database.windows.net -P pass -c -t,-r\n

I have encounter this error:
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC Driver 17 for SQL Server]Unable to open BCP host data-file

Any ideas on how to solve it?


texts: mysql database migration of schemas with updated data and structures

texts:
I am a newbie with MySQL database dumps and am currently trying to work with data migration between a source schema and a target schema.

Assuming the source schema is defined by (source_table_structure, source_data) and the target schema is defined by (target_table_structure, target_schema), the resultant schema that I am looking at is (target_table_structure, source_data_WithNewSize) where the source_data_WithNewSize is basically the same source schema data with additional columns values filled by NULL.

Is it possible to use MySQL workbench to perform this type of migration? Are there other tools that are optimized for such migration?


texts:
When I try to migrate a database from Postgres 9.0 to a new server using Postgres 10.8, I got the error from pg_restore which says

pg_restore: [archiver (db)] could not execute query: ERROR:  constraint "idpost" for relation "classification" already exists

After looking into the dataset, I found two constraints of the same name idpost was created. However, I am not sure whether this is because the version 9.0 allows constraints of the same name while version 10.8 forbids it. Is there any way I can confirm my guess?


texts: SQL Server 2005 merge replication failure

texts:
We have a customer who, when trying to sync a client (subscriber) to the server (publisher,) receives the following error:

2012-11-30 21:03:33.334 Percent Complete: 18
2012-11-30 21:03:33.334 Data validation failed for one or more articles. When troubleshooting, check the output log files for any errors that may be preventing data from being synchronized properly. Note that when error compensation or delete tracking functionalities are disabled for an article, non-convergence can occur.
2012-11-30 21:03:33.334 OLE DB Distributor 'EnvisionSQL\SQL2005': {call sys.sp_MSadd_merge_history90 (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}
2012-11-30 21:03:33.506 Percent Complete: 0
2012-11-30 21:03:33.506 Category:NULL
Source:  Merge Process
Number:  -2147200953
Message: Data validation failed for one or more articles. When troubleshooting, check the output log files for any errors that may be preventing data from being synchronized properly. Note that when error compensation or delete tracking functionalities are disabled for an article, non-convergence can occur.
2012-11-30 21:03:33.521 Percent Complete: 0
2012-11-30 21:03:33.521 Category:NULL
Source:  Merge Process
Number:  -2147200953
Message: Article 'TB_CORE_DAILY' failed data validation (rowcount only). Rowcount actual: 80367, expected: 0.
2012-11-30 21:03:33.537 Percent Complete: 0
2012-11-30 21:03:33.537 Category:NULL
Source:  Merge Process
Number:  -2147200953
Message: Article 'TB_CORE_INSPECTION_VIOL' failed data validation (rowcount only). Rowcount actual: 19451, expected: 0.

The Server is 2005 and the local database engine is 2005 express.

What could be causing the mismatch of actual/expected row counts?

I'm trying to do more research on this so I thought I would re-ignite this discussion to see if anyone else has some input.

I understand how rowcount validation works, but why would I receive an expected value of 0 on a table that has known data?


texts: Reasons for incremental backup on Progress OpenEdge

texts:
In the case that storage is not a problem: are there actually any good reasons for doing incremental backups instead of just doing full backups?

Edit
This could actually refer to any database with full & incremental backups. In this case we use a Progress OpenEdge RDBMS with support for both backup plans as well as real time transactional log archiving. Apart from that I don't think the question must relate to a single vendor. The choice of full/incremental backup can apply to lots of different database engines.


texts: Performance: Postgres (Read-Committed) vs MySQL (Read-Uncommitted)

texts:
I have a workload that involves very frequent writes and reads. It is very performance sensitive, but not accuracy-sensitive. Hence, to maximize performance and minimize locking-delays, my plan was to issue all the reads using Read-Uncommitted transaction isolation level, on a Postgres database.

However, I started reading up a little on this topic, and realized that Postgres treats Read-Uncommitted exactly the same way as Read-Committed.

MySQL does offer Read-Uncommitted functionality, which led me to believe that it might be a superior option for my performance needs. However, I came across another source which claims that snapshotting isolates the readers from the effects of the writer until the writer commits... readers do not block the writer, and the writer does not block readers, so concurrent performance is excellent.

I'm aware that there are many performance differences between Postgres and MySQL, and I don't want to take this discussion off on a tangent. Specifically for the case of having multiple reads/writes, and using of Read-Uncommitted transactions in order to minimize locking delays, how does Postgres compare to MySQL in this particular scenario? Is the absence of true Read-Uncommitted functionality in Postgres a significant liability? Or does their snapshotting functionality work sufficiently well in eliminating locking overhead?


texts: Export subset of data for development environment

texts:
I'm developing an application that is using SQL Server for storage. For development/testing purposes I need a subset of production data that will be anonymised, converted into insert into table.. statements and checked into version control.

So far I've been doing

select top 100 *
into tempTableName
from TableName

then removing any personal data:

update tempTableName set
firstname = 'Joe',
Surname = 'Doe',
Email = 'joe@doe.com',
etc...

Then generating scripts with data and tick only tempTableName.

That takes a fair chunk of time and error-prone - especially the bit where I remove personal data (think what would happen if I put wrong table name into the update).

Is there a better way to get sample data from production into scripts?


texts: Stored proc returning customers a user cannot support

texts:
I've created a stored proc to allocate a customer to a user based on the number and type of customer requests versus the skills of the user. Below is an extract of part of the stored proc - status = 0 means unallocated.

SELECT TOP 1 gdd.customerReference
FROM customerRequests gdd
LEFT OUTER JOIN userSkills us ON us.requestTypeId = gdd.requestTypeId AND us.userId = @pinUserId
LEFT OUTER JOIN requestAttributes dt ON dt.requestTypeId = gdd.requestTypeId
WHERE gdd.status = 0
GROUP BY gdd.requestDateTime, gdd.customerReference, gdd.requestId, gdd.requestTypeId
HAVING COUNT(*) = COUNT(us.userId)

Example data

**Customer Requests**
requestId   requestTypeId   policyNumber    customerReference   requestDateTime userId  status
1   3   Policy A    Customer 1  30/11/2015 10:13        0
2   4   Policy A    Customer 1  30/11/2015 10:33        0
3   11  Policy B    Customer 2  26/11/2015 15:26        0
4   17  Policy B    Customer 2  26/11/2015 15:27        0
5   1   Policy B    Customer 2  27/11/2015 10:05        0

Users Skills

skillId userId  requestTypeId
1   user1   3
2   user1   17
3   user1   11
4   user1   1
5   user2   1
6   user2   3
7   user4   4

Request Attributes

requestTypeId   description priority    tolerance
1   Type A  200 90
2   Type B  999 999999
3   Type C  100 7
4   Type D  100 5
5   Type E  50  5
6   Type F  100 999999
7   Type G  999 999999
8   Type H  999 999999
9   Type I  999 999999
10  Type J  999 999999
11  Type K  100 999999
12  Type L  999 999999
13  Type M  999 999999
14  Type N  999 999999
15  Type O  100 5
16  Type P  100 10
17  Type Q  100 10

By using TOP 1 in the select the query is correctly returning Customer 2 when I substitute @pinUserId with user1. The problem is that if I take TOP 1 out of the query I also see Customer 1 in the result set even though user1 is not able to handle both of Customer 1's requests. If Customer 1's requests came in before Customer 2 user1 would have been incorrectly allocated Customer 1.

Can anyone suggest a solution to this problem.


texts: Figure out what operation is was done: `INSERT` OR `UPDATE`

texts:
I need to exactly know what operation is was done: INSERT OR UPDATE.
SQL:
INSERT INTO votes (tg_user_id, post_id, message_id) VALUES (%s, %s, %s)
ON CONFLICT (tg_user_id, post_id) DO UPDATE SET message_id = %s

If was update I should to execute some internal operations (not related) to DB. Is it possible?


texts: SELECT all the children of a row and concat


texts: Why can't I obtain OBJECT_NAME() for a temporary table?

texts:
According to this MSDN article http://msdn.microsoft.com/en-us/library/ms190328.aspx

  When a temporary table name is specified, the database name must come before the temporary table name, unless the current database is tempdb. For example: SELECT OBJECT_ID('tempdb..#mytemptable').

But when I am actually creating a temp table in a database other than tempdb the function is not working as specified:

use beta1
create table #t(id int)
select object_name(object_id('tempdb..#t')) as from_beta1
use tempdb
select object_name(object_id('tempdb..#t')) as from_tempdb
drop table #t

This is what I see:

from_beta1
----------
NULL

from_tempdb
---------------
#t_________________...______0000000000B9

What am I missing or doing wrong?


texts: How to repair mysql tables using myisam storage engine from multiple repairing threads?

texts:
I am loading a csv file into a mysql database table that uses MyISAM as storage engine. From the process list I can see that this is accomplished in two phases: 1) reading the csv file and 2) repairing the indexes by sorting.
FYI, I have kept the indexing to enabled as it was by defult.

Although I am setting myisam_repair_threads to 2 I think mysql is not taking this into count as show processlist shows only one entry in the process list. Also, it takes a little bit of time interval before starting repairing, why?

Please correct my understanding if I am wrong! Why mysql is repairing the tables with only one thread although I have set it to 2? Is there anything I am getting wrong about repairing strategy? TIA!


texts:
Given the following table my_table :

my_column
------
A
B
C
D

I would like to be able to join on itself but without duplicate pairs like so:

-- SELECT a.*, b.* FROM my_table a JOIN my_table b ON a.my_column <> b.my_column;
-- something like so but without the duplicate pairs

a.my_column | b.my_column
------------+-----------
A           | B
A           | C
A           | D
B           | C
B           | D
C           | D

How should I tackle this in SQL ?


texts:
I need to design an ER diagram for a centralized healthcare management system for my college mini-project.

Firstly, I came up with the following ER diagram.

ER-diagram-1

While designing the tables in the database, I found the patient table to be redundant as every person is a patient in our case so, I came up with the following ER-diagram.

ER-diagram-2

Now, I am confused about which ER diagram shall I use for the project.
Can you please me with the ER diagram? Thanks in advance.


texts: Postgresql citext datatype unique constraint fail

texts:
I need to create a unique constraint on citext column, but it fails.
Here is the example:

create table test (name citext);

ALTER TABLE ONLY test
    ADD CONSTRAINT test_name_uq UNIQUE (name);

  insert into test(name) values ('SIMIT'); --succeded.

  insert into test(name) values ('SİMİT'); --failed

Here is the error code:

  ERROR:  duplicate key value violates unique constraint "test_name_uq"
  DETAIL:  Key (name)=(SİMİT) already exists. SQL state: 23505

SIMIT and SİMİT is not the same thing. It should not return an error.
I don't want to add any upper or lower function in my code. Is there any suggestion?


texts: Will auto vacuum analyzer delay visibility of bulk insertion or updation

texts: I am reading records from table A and doing some calculations and inserting and updating the table B. I am doing yearly migration from 2017 to now. For2017-2019 I am using different query(Function  A) and 2020 jan to now different query(Function  B). My migration has started at 9:57 and all the migration are over by 16:37. I am looping the year and months through service. Both the function s returning true to the service and I couldn’t find and bugs in postgres logs as well.But there are no inserted records in the table B. But auto-vacuum analyzer is running even more than 12 hours after the migration over which is showing background started time as  9:58 which is my migration started time. Someone please advise me what could be the reason and how can I solve it. Since I am new bee to the postgres and I am a software engineer not a DBA not sure how can I solve.


texts: Not Able to Increase InnoDB Buffer Pool Size in Mysql

texts:
I am facing a very rare problem with my DB. I have Windows Server with 64 GB RAM but I am not able to increase innodb buffer pool size in mysql.

Current InnoDb buffer pool Size : 1.6G

When I increase it and restart Mysql then it throws Error :

161026  0:52:30 [Note] Plugin 'FEDERATED' is disabled.
161026  0:52:31  InnoDB: Initializing buffer pool, size = 2.6G
161026  0:52:31  InnoDB: Error: cannot allocate 2816491520 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 22604620 bytes. Operating system errno: 8
InnoDB: Check if you should increase the swap file or
InnoDB: ulimits of your operating system.
InnoDB: On FreeBSD check you have compiled the OS with
InnoDB: a big enough maximum process size.
InnoDB: Note that in most 32-bit computers the process
InnoDB: memory space is limited to 2 GB or 4 GB.
InnoDB: We keep retrying the allocation for 60 seconds...
161026  0:53:31InnoDB: Fatal error: cannot allocate the memory for the buffer pool
161026  0:53:31 [ERROR] Plugin 'InnoDB' init function returned error.
161026  0:53:31 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
161026  0:53:31 [ERROR] Unknown/unsupported table type: INNODB
161026  0:53:31 [ERROR] Aborting

I dont have 32 bit OS but my MYSQL is 32 bit .  I have 64 bit OS on my Windows server.

Memory space is limited to 2 GB or 4 GB. For 32 bit Mysql but I am not able to make it 2 GB, my Innodb is only 1.6 GB  .

Increasing size of Innodb is very necessary because i am getting following error in my error log :

MySql.Data.MySqlClient.MySqlException: Can't create a new thread (errno 12); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

What I have Already Tried :

I confirmed that My my.ini has no issue .
I restrted My mysql many times but innodb size not increasing.
At last I got from somewhere that Memory allocation to Mysql in my OS is very less thats why OS is not increasing its innodb size.
I increased virtual memory of windows. It was 8GB before and I increased to to 16 GB. ( Did this change just now and dont know its result because it requires server restart and before 7 hours I cant restart my Server) - Do you think this can solve my problem ?

MySQL Version : 5.1

If anyone know the solution then let me know, i will be very thank ful to you.


texts:
I have just learned about SPARSE columns in SQL Server.  I have never used them; I've just read about it on the Internet.

Can I ALTER an existing Nullable column in large transactional tables to take advantage of the SPARSE property? Would it be beneficial for space and performance?


texts: is it possible to generate create index sql in MySQL 5.7

texts: I have a table have many index, now I want to move the index to another table. Is it possible to auto generate the index create sql using SQL command? so I do not need to generate index by myself(generate create index sql by hande).


texts:
I need to implement an "autocomplete-like" query in MySQL, one on a fairly short textual field varchar(11) and one on a potentially long field varchar(140).

As they are autocomplete query, they will be LIKE-query. What sort of indexes I need to configure?


texts: Accurate PG database size for comparing representations

texts:
I have a Java/JDBC program that takes a sample file and imports the data into the database, shredding it across multiple relations. The program does this multiple times for several different representations, one of which uses Large Objects. I can share more details of these representations but they are quite long and aren't relevant to this question since I'm looking for something generic.

I would like to compare the sizes of these different representations by examining the size of the database after each import. The database is on a PostgreSQL 9.4 local Windows server instance, with no other users and default configuration. Its only purpose is to conduct this test.

My initial plan was as follows:

for each representation {
  call VACUUM ANALYZE
  record old DB size with SELECT pg_tablespace_size('pg_default');
  import data into database
  call VACUUM ANALYZE
  record new DB size with SELECT pg_tablespace_size('pg_default');
  store storage cost as new DB size - old DB size
}

Obviously there are limitations to this approach, but my expectation is that for large files (~100MB) the reported storage costs should be reasonable approximations. Note that I use pg_tablespace_size in order to include the contribution of data outside of the main schema, such as large objects (in pg_catalog.pg_largeobject and pg_catalog.pg_largeobject_metadata).

I'm wondering whether this is a correct approach, and whether there is a better approach. I'm unsure whether VACUUM ANALYZE properly updates the stats used by pg_tablespace_size, even though it is called in the same session. It would also be better if I could avoid calling VACUUM ANALYZE, since this requires connecting as the superuser in order to run on the pg_catalog relations.

Any thoughts?


texts: Actual Execution Plan - Estimated/Actual Rows Differ Greatly

texts:
I'm wondering if I can speed this query up. One thing I noticed is the estimated number of rows differs greatly from the actual number of rows.

https://www.brentozar.com/pastetheplan/?id=H1ya3-3pe


texts: Changing varchar(max) to varchar(255) requires rebuilding table

texts: Our developers (prior to me being DBA) set the data type for a field to VARCHAR(MAX) when in reality it only needs to be VARCHAR(255). This wasn't discovered until after our table had grown to over several million records and almost a terabyte of data. When I go to design mode on that table to test this change in our test environment I find that the script it creates makes a _tmp table copy of the table and then drops the original and renames _tmp to the original. I seem to remember learning the reason behind this had something to do with having to move the data from the "LOB" section of a record to the fixed length section of a record (Or so my memory recalls). However, I can't find any documentation on why exactly this occurs (instead of just changing the size in place). Can someone point me in the right direction so I can better explain to management why making this change now on a several million record table is going to take a long time.


texts: Restore Oracle tablespace to its state from 2 days ago

texts:
I have multiple test tablespaces in an  Oracle SID.  One of the tablespaces is updated with wrong data. It worked fine 2 days ago. I want to restore the tablespace to its state from 2 days ago.

I tried to restore the tablespace from the Coldbackups, but during startup it gave the error :

ORA-01113: file 10 needs media recovery
ORA-01110: data file 10: '/user/oracle/metadata/lmptest/gkpdbspace.dbf'

And

recover database

or

recover datafile '/user/oracle/metadata/lmptest/gkpdbspace.dbf'

didn't work.


texts: Creating a view (or table) from JSON data with two different shapes

texts:
I'm pulling in JSON data from two different sources that have roughly the same information, but in different shapes.  Let's say I'm pulling in data about music albums, and one of the API responses looks like this:

{
  "id": "6289d24d-1a49-499b-90a7-8f9e43b4d6af",
  "artist-credit": [{
    "name": "The Hot Sardines"
  }],
  "title": "The Hot Sardines",
  "date": "2014",
  "media": [{
    "tracks": [{
      "position": 1,
      "id": "5928bc3b-a5b9-4149-8a1e-b224707dab5a",
      "title": "Bei Mir Bist Du Schoen",
      "artist-credit": [{
        "name": "The Hot Sardines"
      }],
      "length": 246000,
    },  {
      "length": 246000,
      "number": "11",
      "artist-credit": [{
        "name": "The Hot Sardines"
      }],
      "title": "(I Don't Stand) A Ghost of a Chance With You",
      "id": "be97c530-d2d4-4a68-a777-637a2c23852e",
      "position": 11
    }],
    "track-count": 11
  }],
  "packaging-id": "ec27701a-4a22-37f4-bfac-6616e0f9750a"
}

While the other looks like:

{
  "album_type" : "album",
  "artists" : [ {
    "id" : "2BTZIqw0ntH9MvilQ3ewNY",
    "name" : "Cyndi Lauper"
  } ],
  "id" : "0sNOF9WDwhWunNAHPD3Baj",
  "name" : "She's So Unusual",
  "release_date" : "1983",
  "tracks" : {
    "items" : [ {
      "artists" : [ "some artists"],
      "disc_number" : 1,
      "duration_ms" : 305560,
      "id" : "3f9zqUnrnIq0LANhmnaF0V",
      "name" : "Money Changes Everything",
      "track_number" : 1,
      "type" : "track",
    }, {
      ...
    } ],
    "total" : 13
  },
  "type" : "album",
  "uri" : "spotify:album:0sNOF9WDwhWunNAHPD3Baj"
}

I'd like to be able to query this data via SQL in PostgreSQL, with the table structure looking like:

album               (id, title,    release_date, spotify_id, musicbrainz_id)
track               (id, title,    album_id,     duration,   spotify_id, musicbrainz_id, position)
artist              (id, name,     spotify_id,   musicbrainz_id)
artist_track_credit (id, track_id, artist_id)
artist_album_credit (id, album_id, artist_id)

From what I've gathered there are a couple of ways to do this.

One would be to take each API response, convert them into SQL INSERT queries in software and then send those queries to the database like so:

INSERT INTO album (title, release_date, etc) VALUES (?, ?, ? ...);

-- Get the album ID back and use it in the following queries

INSERT INTO track (title, album_id, etc) VALUES (?, ?, ? ...), (?, ?, ? ...), ...;

INSERT INTO artist (name, etc) VALUES (?, ...), ... ON CONFLICT DO NOTHING

-- Get the new artist ids back (if created) and use them in the following queries

INSERT INTO artist_track_credit VALUES ...
INSERT INTO artist_album_credit VALUES ...

The problem here is that it is pretty messy, and requires several round trips to the DB in order to get the album ID to insert with the tracks, then the artist IDs to insert with the artist and album credits.

One alternative is to insert the API response directly into the database, and then use database functions to manipulate the data and insert it into the relational structure. The downside here is that I'm not as familiar with writing functions for postgresql, but if this is the best solution I'd go for it.

Another alternative is to take advantage of postgresql's JSON handling abilities and insert the API responses directly into the database, and then develop indices and views on that JSON data to simulate ordinary relational data. This seems promising, but I'm not sure how to handle the data having different shapes. Is it possible to write a set of views or indices for each schema, and then some kind of union that will take, say SELECT * FROM album and convert that into SELECT * FROM musicbrainz_album_view UNION SELECT * FROM spotify_album_view without having to write it out every time?

What is the best way to handle this scenario?


texts: Mariadb concurrent insert not working for MYISAM tables

texts:
I am using mariadb 10.1.13. I have a large MYISAM table ( > 2000000 rows ) which used to have frequent 'INSERT' operations from multiple scripts.
Recently I had to add  a new 'SELECT' query to the same table as a part of another script , which is causing the 'INSERT' query to be locked. I used to 'DELETE' some of the old data to keep the table size from increasing beyond a limit. From MySQL documentation I saw that deletion can cause holes in data which will cause insert to lock.
But now the concurrent_insert is set to 'ALWAYS' so that it will work even if there are holes.
MySQL documentation says
Enables concurrent inserts for all MyISAM tables, even those that have holes. For a table with a hole, new rows are inserted at the end of the table if it is in use by another thread. Otherwise, MySQL acquires a normal write lock and inserts the row into the hole.

But still the insert queries seems to be blocked with 'waiting for table level lock' status when SELECT is going on. After 'OPTIMIZE TABLES' is executed it will work without issues but locking happens again after holes are created. I couldn't find any reason for the locking. Nobody seems to have reported such an issue anywhere.
Any help is appreciated.


texts:
I have opened SSMS today, and went to open my database ADMINISTRATOR (in the picture) but this option is unavailable:

What's this problem, how can I cancel this restore?
I have just added a new database, I didn't perform any restore operation for this database.


texts: Getting an error when trying to start the database (end-of-file on communication channel)

texts:
I installed Oracle 12c on Ubuntu 3-4 months ago, and it worked perfectly until now. I'm not sure what's causing the error.
Logging in with: sqlplus / as sysdba

Running: SQL> startup
I get the error:

SP2-0642: SQL*Plus internal error state 2133, context 3114:0:0
Unsafe to proceed
ORA-03114: not connected to ORACLE

Running: startup open  I get this

SQL> startup open
ORACLE instance started.

Total System Global Area 2483027968 bytes
Fixed Size          2927432 bytes
Variable Size         671089848 bytes
Database Buffers     1795162112 bytes
Redo Buffers           13848576 bytes
Database mounted.

ORA-03113: end-of-file on communication channel
Process ID: 4438
Session ID: 357 Serial number: 16101

I tried searching info on the SP2-0642, and see that things that could cause this kind of error, is if environmental variables end in a /

For example: old LD_LIBRARY_PATH=/Applications/instantclient10_1/
Or if NLS_DATE_FORMAT is entered incorrectly etc. But none of these apply to me, as I have not changed anything. I just turned on my computer, and trying to connect to the database, it gives me the error mentioned above. I'm have no idea what to do, since the solutions described are already as should be (those I found).

One thing I suspect is... I live in Denmark, when it is fall, we change our time, so that it is one hour behind, and when it is spring, we put so it is one hour ahead. So our time will change all of a sudden compared to other countries. And this weekend that has passed, we changed our clock so the time was put one hour ahead. It hits me that is after we changed our clock that I try to restart the database. Could this be the problem? And how can I check this, and if possible, change the timezone when I can't connect to the database?

---------------------------------------------Update--------------------------------------------------

I found out that (possibly) the real problem is this (seen in the alert log):

ORA-19815: WARNING: db_recovery_file_dest_size of 4781506560 bytes is 100.00% used, and has 0 remaining bytes available.

I should be able to change the size like this:

SQL>  alter system set db_recovery_file_dest_size=20G scope=both;

But since I get the error described above (end-of-file...) I am not able to change or evne see the values of any parameter. Is there some other way to change the size of the db_recovery_file_dest?


texts: Grouping by overlapping arrays, transitively, without duplicates

texts:
I've found:

Group by array overlapping
https://stackoverflow.com/a/23650080/5419599

However, I'm having trouble putting it to use in my case.

I have a table like so (the real myid values are hashes, but simplified here for illustration):

create temp table a (myid text, ip inet);
insert into a (myid, ip)
values
  ('0a', '10.10.1.1'),
  ('0a', '10.10.1.2'),
  ('0a', '10.10.1.3'),
  ('0b', '10.10.1.2'),
  ('0b', '10.10.1.4'),
  ('0c', '10.10.1.5'),
  ('0d', '10.10.1.3'),
  ('0e', '10.10.1.6'),
  ('0e', '10.10.1.7'),
  ('0f', '10.10.1.8'),
  ('0f', '10.10.1.9'),
  ('10', '10.10.1.9'),
  ('11', '10.10.1.10'),
  ('12', '10.10.1.11'),
  ('12', '10.10.1.4'),
  ('1a', '10.10.1.2'),
  ('1a', '10.10.1.4'),
  ('1e', '10.10.1.11'),
  ('1f', '10.10.1.12'),
  ('23', '10.10.1.12');

The result I can't work out how to produce is:

         ids         |                         ips
---------------------+------------------------------------------------------
 {0a,0b,0d,12,1a,1e} | {10.10.1.1,10.10.1.2,10.10.1.3,10.10.1.4,10.10.1.11}
 {0c}                | {10.10.1.5}
 {0e}                | {10.10.1.6,10.10.1.7}
 {0f,10}             | {10.10.1.8,10.10.1.9}
 {11}                | {10.10.1.10}
 {1f,23}             | {10.10.1.12}

The logic here is that any ids with ips in common are grouped together, transitively.  For instance, 0a has an ip in common with 0b; 0b has one in common with 12; 12 has one in common with 1e, and so forth.

There are tens of thousands of rows, no specific limit on how many ips for any given id, and no specific limit on how many ids may show any given ip.

I know how to aggregate by ip, or aggregate by id, but doing both transitively is giving me trouble.  I tried a recursive CTE but I couldn't seem to get it right, and I'm not sure if that was the right approach in the first place.  (If I could first group by id and then group by overlapping array of ips, and avoid duplicates in the aggregation, I would be all set, but there may be a better approach.)

Is there a way to produce the above results with standard SQL?  Or at least with standard Postgres?  (I'm using 9.6.6.)

Here is a failed attempt.  (It is a legal query that does return results, but not the desired results.)  It fails because:

It includes intermediate results rather than replacing them with later results, and
It doesn't sort the array concatenations, so it includes each result multiple times.  This is also a hugely slow query for the actual dataset I am working with, since it returns up to n! times each result.

Here is the query:

with recursive b as (
  select
    array[myid] as ids,
    array_agg(ip) as ips
  from a
  group by myid
), c as (
  select
    ids,
    ips
  from b
  union
  select
    b.ids || c.ids,
    b.ips || c.ips
  from
    b
    join c on
      (not b.ids && c.ids)
      and (b.ips && c.ips)
)
select * from c
;