In MySQL can I drop a database by moving the directory containing the database? We ran ran into a problem where MySQL kept crashing because of corruption in one of our databases.  MySQL would start, crash, and then restart.  We determined that the corruption was in a database containing archive tables (i.e. tables using the archive engine).  We weren't able to drop the database in MySQL because it wouldn't stay up long enough to drop it, so we shut down MySQL and moved the directory containing the database to another location.

This prevented MySQL from crashing, and all of our other databases seem to be working fine.  My question is: is moving the database directory containing the database out of MySQL's data directory a safe way to remove a database?  Is it the same as dropping a database?

SHOW DATABASES;

I suspect it will not show the database that you "moved".

Moving (or deleting) the directory would be unsafe it it contained any InnoDB tables.  It is safe for MyISAM tables, and probably MERGE and Archive.

ibdata1 contains information about InnoDB tables.  Non-InnoDB table types are discovered by looking in the file system.


Indexed views on double linked server A server SERVER_A has some data.

A server SERVER_B defines A as a linked server, and defines some views on the data. The queries are like this:

CREATE view myview as
select * from
openquery ( SERVER_A, select .... )

A server SERVER_C defines B as a linked server.

I have full access to SERVER_C, I can ask for some changes to SERVER_B, but not to SERVER_A. All my applications will access server SERVER_C, and need the data from SERVER_A. The views on SERVER_B are quite complex, and they cause timeout before returning anything.

I think that to improve the performance on these queries I need to turn the views on SERVER_B into "indexed views". To index a view, I must create a clustered index.

Questions:

1) Where should I define the clustered index? Should it be at SERVER_B, or I can somehow define it on SERVER_C?

2) Will the use of an indexed view on SERVER_B affect in any way the performance of SERVER_A even when the view is not being used?

You can't create an Indexed view on linked server table.

According to BOL http://msdn.microsoft.com/en-us/library/ms191432.aspx
"The view must reference only base tables that are in the same database as the view."


Postgres: How to insert row with autoincrement id There is a Table "context".
There is an autoincrement id "context_id".
I am using sequence to retrieve the next value.

SELECT nextval('context_context_id_seq')

The result is: 1, 2, 3,...20....

But there are 24780 rows in the "context" table

How can I get the next value (24781)?

I need to use it in the INSERT statement

Apparently you inserted rows into that table without using the sequence and that's why they are out of sync.

You need to set the correct value for the sequence using setval()

select setval('context_context_id_seq', (select max(context_id) from context));

Then the next call to nextval() should return the correct value.

If the column is indeed defined as serial (there is no "auto increment" in Postgres) then you should let Postgres do it's job and never mention it during insers:

insert into context (some_column, some_other_column)
values (42, 'foobar');

will make sure the default value for the context_id column is applied. Alternatively you could use:

insert into context (context_id, some_column, some_other_column)
values (default, 42, 'foobar');


Set Oracle Home (install Oracle Home) for Toad 12.6 I had Toad 11.5 and I'm trying to install Toad 12.6, to connect to Oracle 11g on a 64-bit Windows 7 machine.

No matter what I try, when I start Toad 12.6, I get the error: "No valid Oracle clients found.  You need at least one 64-bit client properly configured."  In the install, I used a downloaded win64_11gR2_client.zip file and it sure looked like it installed.

I've finally blitzed everything I could of Toad 11 and Oracle (using https://superuser.com/questions/256762/how-do-i-completely-remove-oracle-11g/762432#762432).

Somehow, I need to still install Oracle, or somehow set the Oracle Home.  http://documents.software.dell.com/DOC220605 says how to edit the oracle home, but even when I had Toad 11 working, I couldn't get anything when clicking the ... button in 12.6, because "You do not have any Oracle homes installed."

I've tried installing the client directly, and I get an error about the path too long.  I ignore the error and the install dies.  From research, it has said that uninstalling the 32 bit client is the way to go.  It doesn't appear to be the way to go.

What do I try next?

Here's what worked, although I don't know if it's a good answer or not.  I had someone who could log in as an admin on my pc do the Oracle client install.  In other words, if you're not a full admin on your machine, it may fail, and not tell you why.

I had admin rights, I could edit the registry and path, but there were still some missing rights.  Logging on as administrator and doing the install worked fine.


why when I change a column from DATETIME to DATE, I can't see any storage improvement? Long story short. I have a table with more than 10millions rows with a datetime field. WE can change that to DATE ( because date uses 3bytes, and datetime 8).

I thought if I:

alter table table1 alter column column1 date

I would reclaim more than half the space allocated.

but doing some tests, I can't see any space being reclaimed.

exec sp_spaceused datas2
datas   110000                  8136 KB 8080 KB 8 KB    48 KB --with datetime
datas   110000                  8136 KB 8080 KB 8 KB    48 KB --with date

both with 8080KB.

but then if I create a new table, with the same ammount of data, but with DATE since the beggining, I can see a good storage improvement:

datas2  110000                  4744 KB 4688 KB 8 KB    48 KB

Now my table has 4688 KB.

How can I reclaim that space on a table that would be altered from datetime to date?

In several cases, you will need to rebuild an index and/or the clustered index in order to observe any freeing up of space.

If this is a heap, you can just say:

ALTER TABLE dbo.MyHeap REBUILD;


How to simulate a situation when non-empty Transaction Log file will be added to the SQL Server Full Database backup file I created a test database "StoredData" with a single table:
CREATE DATABASE StoredData
 ON  PRIMARY
( NAME = N'StoredData', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\StoredData.mdf')
 LOG ON
( NAME = N'StoredData_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA\StoredData_log.ldf')
GO

ALTER DATABASE [StoredData] SET RECOVERY FULL

GO

USE StoredData
GO

CREATE TABLE dbo.Users
(
    Id INT NOT NULL,
    Name NVARCHAR(50) NOT NULL
)

Then I inserted 3 records into that table:
INSERT INTO dbo.Users (Id, Name)
VALUES (1, 'John'),
       (2, 'Giovanni'),
       (3, 'Anna')

After that I run this query to have some active transactions:
DECLARE @i INT = 0;

WHILE @i < 1000
BEGIN

    IF(@i%10 = 0) BEGIN TRAN

    INSERT INTO dbo.Users
    SELECT TOP 10 * FROM dbo.Users

    WAITFOR DELAY '00:00:01';

    IF(@i%10 = 9) COMMIT

    SET @i = @i+1
END

During the last query execution I'm trying to do full database backup:
BACKUP DATABASE StoredData
TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\StoredData_FullRecovery_Uncompressed.bak'
WITH FORMAT, NO_COMPRESSION

Then I'm looking into the backup file content:
RESTORE FILELISTONLY
FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup\StoredData_FullRecovery_Uncompressed.bak'

And I'm getting zero log file inside backup:
LogicalName:       StoredData_log
BackupSizeInBytes: 0

There are two things going on here:
1: The database is effectively in simple recovery mode when doing your modifications, since you never did your first full backup after creating the database.
2: The column BackupSizeInBytes seems to be 0 for all ldf files, quite simply, even when there is log records in there. The documentation is vague for this column, so it difficult to say whether it is by design or not.


Can a Join happen with same attribute names, but different domains? Problem

When doing any sort of Join all common attributes are listed under one column in one way or another.  However, I have not been able to find a source that deal with columns of the same name, but with different domains.

Put in a visual perspective, I am taking about columns like the case below:

The domain is different because one B column is numbers but the other is letters.

Question

Is a Join possible with attributes of the same name but different domains?

I think you are being misled by your terminology.

SQL is a standard that has the following schematics

Schema
Table
Column
Attribute

*Oracle does not have separate databases in an Instance. schemas are tied to a user.

SQL is a 4th generation language that separates the need to know where to query an object to high, almost human language-style programming

When you write a Query in SQL, the compiler (called the SQL Optimizer) translates this into machine-level language and retrieves your data. It is therefore highly dependent on the Optimizer to correctly retrieve the data how it thinks is best.

What this means is that your SQL is nothing more than meaningful suggestions to the Optimizer. All you need to know is what the Structures’ names are, not where or how to grab it.

SQL has hints and Operators that help the Optimizer to pick the right datasets, but it is the Optimizer that chooses the ORDER of the Query to parse first.

Thus, you are writing Logical queries. If two tables are joined, it is your responsibility to help the Optimizer by specifically calling out the columns that can be joined.

Even if the attributes are not the same, the Optimizer is fully agile enough to know how to translate on the fly these values. For example, a VARCHAR type value of ‘13’ is still rather similar to a tinyint, integer, numeric Value, so the Optimizer will Implicitly convert these columns into the same data type!

The following example is in TSQL:

CREATE TABLE TableA (col_A int, col_B varchar(24) )
CREATE TABLE TableB (col_A varchar(24), col_B int)
INSERT INTO TableA (col_A, col_B)
VALUES (1, ‘My Value’)
INSERT INTO TableB (col_A, col_B)
VALUES (‘Not my Value’, 1)

SELECT A.Col_B
FROM TableA AS A
INNER JOIN TableB AS B ON A.Col_A = B.col_B

Notice we used an alias to help the optimizer know what we mean by the join and what we mean by which column to return.


How can I get this "rolling total" query to sum correctly? I have a problem with the following query:

SELECT  t1.idcliente,
        c.nombre,c.apellido,
        t1.fecha,
        t1.haber,
        t1.debe,
        SUM(t2.haber -t2.debe) as SUM
FROM estado_cliente t1
INNER JOIN estado_cliente t2 ON t1.idestado_cliente >= t2.idestado_cliente
INNER JOIN cliente c ON t1.idcliente= c.idcliente
where t1.idcliente=t2.idcliente
GROUP BY t1.idcliente ,
         t1.fecha,
         t1.haber,
         t1.debe,
         c.nombre,c.apellido
ORDER BY t1.idcliente,t1.fecha
;

See this sqlfiddle.

Here's part of the results:

idcliente  nombre  apellido  fecha       haber  debe   SUM
   6       Matias  Blanco    2017-07-03  0       8100   -8100
   6       Matias  Blanco    2017-07-08  0      15000  -23100
   6       Matias  Blanco    2017-09-24  4312       0  -38788    <--- problem
   6       Matias  Blanco    2017-09-25  0      10000  -33100
   6       Matias  Blanco    2017-09-26  0      10000  -43100

Each result row should have the SUM column return the cumulative total of all haber values minus all the debe values, up to and including the given date. These represent payments and debits, and I want the total amount still outstanding on that date.

However in the third line, that's not what I'm getting. The results should look like:

idcliente  nombre  apellido  fecha       haber  debe   SUM
   6       Matias  Blanco    2017-07-03  0       8100   -8100
   6       Matias  Blanco    2017-07-08  0      15000  -23100
   6       Matias  Blanco    2017-09-24  4312       0  -18788  <-- correct
   6       Matias  Blanco    2017-09-25  0      10000  -28788
   6       Matias  Blanco    2017-09-26  0      10000  -38788

Given the sqlfiddle link above, can anyone help me figure out what I'm doing wrong?

The problem with your query is simply that you're not actually processing the records in the order you want to.

Your query picks up all records with an idestado_cliente less than the current row. This would work fine if all records were entered in order strictly by date. however, as we see in your test data, they are not; the last three entries are for earlier dates than the previous entries for each cliente.

In order to deal with that, we need to modify the INNER JOIN to the t2 version of estado_cliente, as follows:

INNER JOIN estado_cliente t2 ON (   t1.fecha > t2.fecha
                                 OR (    t1.fecha = t2.fecha
                                     AND t1.idestado_cliente >= t2.idestado_cliente
                                    )
                                )

Here, we're grabbing the current record, any records with an earlier date, and any records with the same date, but an earlier idestado_cliente.

This will give you the results you want.

That said, as pointed out by Rick James, this isn't necessarily the best way to do it. In your query, you are selecting a second set of rows from estado_cliente for every row you want to show. You don't need to do that; instead, you can just touch each row once in order to get the totals you need, using variables.

You can store the current row's idcliente and sum in variables. Then, when you go to process the next row, you can check if the idcliente has changed (in which case you need to start over), and then calculate this row's sum (using the old sum if the idcliente didn't change, and starting from 0 again if it did).

This version of the query looks like this:

SET @idcliente = 0;
SET @sum := 0;

SELECT c.idcliente,
        c.nombre,
        c.apellido,
        summed.fecha,
        summed.haber,
        summed.debe,
        summed.`SUM`
FROM (SELECT  t1.fecha,
              t1.haber,
              t1.debe,
              t1.idestado_cliente,
              @sum := (  CASE WHEN @idcliente = t1.idcliente THEN @sum ELSE 0 END
                       + t1.haber - t1.debe )
              as `SUM`,
              @idcliente := t1.idcliente as idcliente
      FROM estado_cliente t1
      ORDER BY t1.idcliente,t1.fecha,t1.idestado_cliente
      ) summed
INNER JOIN cliente c ON summed.idcliente= c.idcliente
ORDER BY c.idcliente,summed.fecha,summed.idestado_cliente
;

This updated sqlfiddle link includes all three versions of the query:

the original query, as you posted it (left in primarily to ensure that all values are indeed in memory for the following two queries);
the fixed version of the original query (using the self-join); and
the final version of the query, using variables.

I've run it several times, and even considering the small amount of data involved, the version of the query using variables always seems to run at least as fast as the version that uses a self-join, and usually runs 1 millisecond faster.

Follow-up:

  In another use case, I will need to run that query for a specific date range only. For best performance, should I make a temporary table filtering estado_cliente by date, and then use that temp table in the query?

First, and most important: Your rolling sum will be based on the rows used in the query only. You may already have realized that, but just in case, I thought I should cover it.

If you processed the sample data from the question, but only included rows dated September 1-25, 2017, you won't get:

idcliente  nombre  apellido  fecha       haber  debe   SUM
   6       Matias  Blanco    2017-09-24  4312       0  -18788
   6       Matias  Blanco    2017-09-25  0      10000  -28788

Instead, you'll get:

idcliente  nombre  apellido  fecha       haber  debe   SUM
   6       Matias  Blanco    2017-09-24  4312       0    4312
   6       Matias  Blanco    2017-09-25  0      10000   -5688

That's because the rows from July are excluded, so their haber and debe are not calculated into the SUM.

To get the first set of results (with an absolute SUM, where you get the same value for each row even when you exclude older rows from your output), you have to include all records prior to your date range in the full process, and filter out the ones from before the target dates in the final query.

If the second set of results are what you're after (where the SUM is relative to the set of dates being displayed), then you can fully filter out your full date range before calculating the SUM.

In either case, the upper bound of a date range should be applied before calculating the SUM - records past the dates you want have no impact on the SUM for earlier dates, regardless.

This sqlfiddle link shows the same three queries as the previous link, plus versions of the self-join and variable queries that restrict the output to rows from September 1-25, 2017 and still show the "absolute" sums. I've added an initial column to the results of each, to make it clear which query is which.

So, there are a few ways to do the filtering before processing. Let's just consider two: simply filter out the rows by adding WHERE clauses to the existing queries, or write the estado_cliente data for the only the rows we need to process to a temporary table. The 4th and 5th queries in the link just above are using WHERE clauses.

Ultimately, I can only recommend that you try both, and see which performs better for you.

I can provide some guidance as to what I would expect:

Writing to disk is generally the most time-consuming operation you can perform in a database (reads from disk are a close second, normally - solid state drives tend to reduce the cost of both of these operations). So, writing the data you need to a temporary table can actually make a query take longer; if the database engine was able to process everything in memory, and didn't need to write any intermediate data to disk, then I would expect writing to the temp table would be worse.
If you would be using the temporary table for other queries as well, then creating it could result in a net improvement overall. If there are a large number of estado_cliente columns you won't be using, the temporary table would fit more data into each page, and you'd have fewer pages to bring into memory. If estado_cliente doesn't have many columns beyond the ones you're using, then there is less benefit. The fact that every page has only the rows you want to work with might still mean fewer page to bring into memory. Note that, in the version of the query with the self-join, you are accessing estado_cliente twice, so a temporary table might benefit that version of the query as well.
I don't use MySQL heavily, but in MS SQL Server (where I do most of my work), I have sometimes seen queries that completed faster broken into two queries (one that populates a temp table, and one that performs the final steps using the temp table) than as a single query. I believe this is simply a matter of the complexity of the query making it difficult for the database engine to find an optimal execution plan within the time limits it has for that step of things. It does find optimal plans for the two separate queries, so they go fast; it picks the best plan it can for the more complex query in the time allotted, and that plan can turn out to be bad.
My "gut instinct" says that the query that uses variables is pretty unlikely to benefit from the temp table (say, 25% chance), and the query using a self-join is somewhat unlikely (say, 45% chance).

But like I said earlier - It's probably worth the time to try both options (filter the unwanted date rows in the query, and write the desired date rows to a temporary table), and see which way seems better with your data and system.


Adding Boolean field to a view I have a view that is being used to export data from my database to a 3rd party system, I have been asked to add a Boolean flag to the view that can be updated if the record has been exported to the other system. Is this possible? If so, how can it be done?

To do that you need a column to say when is true or false.
You can change the schema view or the query that run against the view.

Just use a case clause

select *, case flag_column when 1 then 'TRUE' else 'FALSE' end as boolean_column from view

I suppose that is what you will need


Averaging 2 values based on an ID in SSRS I am hoping I might be able to get some help with a problem I am having. I am currently trying to build a report in SSRS to replace an excel spreadsheet we use for data analysis so the report can be dynamic. I have set the report to be a matrix and pull through all the reported grades for a student. I also have a column that shows their Most Likely Grade (MLG). My problem is that students have more than one teacher for each subject, so what I need to do is average out the two attainment grades and then compare it to the average MLG. The MLG has a transposed value stored in the database that I use in calculation, so for example an A actually = 1.

In essence, in the example pictured - History MLG = A,A = (1+1)/2 = MLG of 1
Attainment score for Spring is (1+2)/2 = 1.5, so the value returned to the column would be 1.5.

My logic would be to look for the subject ID, if the subject ID is the same as another in the column,then add the 2 attainment scores, average them and return the average value into a new column.

I am very new to SSRS so I am not sure if this is possible and if it is how to get this to work.

This is the SQL query on the dataset

WITH Pupils AS
(SELECT DISTINCT txtSchoolID
FROM TblReportsStorePupilArchive
WHERE (TblReportsStorePupilArchive.intReportCycle = @intReportCycle)
AND (TblReportsStorePupilArchive.intNCYear IN
(SELECT ID FROM dbo.SplitCSV(@intNCYear) AS SplitCSV_1) OR @intNCYear IS NULL)
AND (TblReportsStorePupilArchive.txtForm IN
(SELECT ID FROM dbo.SplitCSV(@txtForm) AS SplitCSV_1) OR @txtForm IS NULL)
AND (TblReportsStorePupilArchive.txtSchoolID IN
(SELECT ID FROM dbo.SplitCSV(@txtSchoolID) AS SplitCSV_1) OR @txtSchoolID IS NULL)
AND (TblReportsStorePupilArchive.txtBoardingHouse IN
(SELECT ID  FROM dbo.SplitCSV(@txtHouse) AS SplitCSV_1) OR @txtHouse IS NULL
OR
TblReportsStorePupilArchive.txtAcademicHouse IN
(SELECT ID FROM dbo.SplitCSV(@txtHouse) AS SplitCSV_1) OR @txtHouse IS NULL)
),

Cycles AS
(SELECT TblReportsManagementCycleID FROM  TblReportsManagementCycle AS AcademicYearCycles
    WHERE AcademicYearCycles.intReportYear =
            (SELECT intReportYear FROM TblReportsManagementCycle AS CurrentCycle
            WHERE CurrentCycle.TblReportsManagementCycleID = @intReportCycle)
)

SELECT AcademicReports.*

FROM VwReportsManagementAcademicReports AS AcademicReports

INNER JOIN Pupils ON AcademicReports.txtSchoolID = Pupils.txtSchoolID
INNER JOIN Cycles ON AcademicReports.intReportCycleID = Cycles.TblReportsManagementCycleID

I managed to do this in the end by altering the SQL query on the Dataset

SELECT        txtSchoolID, intNCYear, intReportCycleID, intSubjectID, txtCurrentSubjectReportName, AVG(CAST(intGradeTransposeValue AS decimal(5, 2))) AS avg_attainment, txtReportCycleName, txtGradingName, txtGrade, txtReportTermName, intReportCycleTerm, txtReportCycleShortName, dtReportCyclePrintDate, intGradeTransposeValue
FROM            VwReportsManagementAcademicReports
WHERE        (intReportCycleAcademicYear = 2017)
AND (intNCYear > 6)
AND (intGradeID = 1)
GROUP BY intSubjectID, txtCurrentSubjectReportName, txtSchoolID, intNCYear, intReportCycleID, txtReportCycleName, txtGradingName, txtGrade, txtReportTermName, intReportCycleTerm, txtReportCycleShortName, dtReportCyclePrintDate, intGradeTransposeValue
ORDER BY intReportCycleID, intSubjectID


Update trigger seemingly won't fire if multiple rows are updated I have table_a and table_b, below is the data from table_a.

table_a

Bill_No   P_Name  Price   Stock_in  Stock_out  Stock_in_hand  P_Value
1         fish     100       20        10            10          1000
2         water    50        40        30            10          500
3         soda     30        50        10            40          1200
4         cake     5         10        10             0          0

Now whenever Stock_out gets updated I want to insert those updated rows into table_b via a TRIGGER. Bill_No is identity column and primary key here.
This is what I have tried so far.

ALTER TRIGGER [dbo].[copytblab]
   ON  [dbo].[table_a]
   AFTER UPDATE
AS
IF (UPDATE(Stock_out))
BEGIN
  SET NOCOUNT ON;
  INSERT table_b(Bill_No,P_Name,Price,Stock_in,Stock_out,Stock_in_hand, P_Value)
    SELECT i.Bill_No,i.P_Name,i.Price,i.Stock_in,i.Stock_out,i.Stock_in_hand,i.P_Value FROM inserted AS i
    INNER JOIN deleted AS d
    ON i.Bill_No = d.Bill_No
    AND i.Stock_out <> d.Stock_out
END

The code works fine if I update a single row but If i update more than one row at one time then it does not send Stock_out, P_Value and Stock_in_hand values to table_b. I don't understand where the problem is.

Your INSERT is nested under IF UPDATE(Stock_out) but then filters on i.Stock_out <> d.Stock_out which is redundant.

If Stock_out is ever allowed to be NULL then your WHERE clause will never match (because NULL IS NULL evaluates as true but NULL <> NULL always evaluates as NULL which will not match). Please check that.

I am unsure how the UPDATE() function responds to multi-row invocations as the Microsoft Docs don't seem to mention it! Somebody please update me on this. I would avoid using something with unclear behavior.


Oracle 11.2: Backup and recovery strategy based on full cold backup plus expdp Are there any issues with the following strategy for backup/recovery in Oracle 11.2.0.4?

Perform a full database cold backup once a month, moving the file off the server for safekeeping.
Write a cron job to perform expdp of all schemes except SYS daily, retaining only the last two weeks of files, and moving them off the server for safekeeping.
In the event of a failure, perform full database recovery using the cold backup, then do an impdp from the latest file (to restore all schemas except SYS). I can accept losing all database data since the last expdp file was taken (which is 24 hours, worst case).

The strategy is based on the belief that as long as I don't write or change anything in SYS schema since the full cold backup, that only changes to the other schemas need to be recovered after the restoring the full (cold) backup. Is that correct?

I would highly recommend taking weekly RMAN hot full backups and daily RMAN incremental backups as well as retaining archivelogs for at least 1 week.

The issue with using expdp/impdp is that it can and will create inconsistent snapshots of your schema during the hot backup process (unless you do a cold backup daily...which is not very practical). If you happen to lose your entire database and begin to recover using impdp, you're at the mercy of the exports to be logically consistent which is only possible if no inserts, updates, or deletes happened at the time of the expdp export. You'll get data, but you can't guarantee that all the tables will be consistent with one another.

Your weekly hot backup will provide you a new "ground truth" database backup that will have guaranteed consistency after applying enough recovery to push through the database changes after the end of the hot backup. You can either use archivedlogs or incremental backups to apply recovery.

A daily incremental backup will speedup mean-time-to-recovery in the event that you do need to make a restore happen. Incremental backups recover faster than archivedlogs. On their own, incremental backups are meaningless without some sort of full backup that preceded them.

To this statement:

  The strategy is based on the belief that as long as I don't write or change anything in SYS schema since the full cold backup, that only changes to the other schemas need to be recovered after the restoring the full (cold) backup. Is that correct?

You're technically correct with the information you're providing. However, above I point out a critical flaw about the schema that makes this information irrelevant. That flaw is that you cannot guarantee data integrity with an expdp export without making it a cold export, and being cold is not advantageous for anyone.


When placing an order in a shop, should a shapshop be taken of the products in an order also? ...to prevent messing up with data updates, such as prices, titles, of the products that are placed in an order.

Namely, a customer buys 3 items: for $5, $10 and $33 and pays for them. All is well. When I as an owner of a shop step in and, before I've delivered those products to a customer, decide to descrease the price of the product, say, #2. Its new price will be $8 instead of $10. And I'll also rename the product #3 a little bit. A customer then would go to a status tracking page and they'll see updated data. Yes, they've paid and the order is being delivered, but the data has already changed in the database.   They'll be consufed.

And so will be I in a year after I'll have changed the prices again, renamed something, etc... and decide to view history of the orders for a year.

Question 1: is there a practise of making a snaphot of the current prices, names, total order price and other characteristics of the products that constitute an order, as well as other details (chosen shipping rate and shipping method, taxes, discounts, etc) at the moment when it's being placed? Rather than calculating those dynamically when an "order page" is opened?

If yes, does it have a name? And are there recommendations of how to do it properly?

Question 2: where and how should I store a snaphot?

option #1:

I'd have to create multiple tables then:

frozen_products
frozen_discounts
frozen_shipping_method_and_rates

etc...

that will have the same structure as their dynamics corresponding ones.

Laborious. Is there a better way?

option #2:

along with an order, in the "orders" table. But how again, given the fact that an order is a single row? For instance, the products in an order is a list. How would I store a list with its characteristics (price, weight, colour, material, what have you) in a row in such a way that'll be more or less easy to retrieve in the future? Not as a string.

The best way of freezing the information for this would be to have a separate table for invoice details. This would store not  the basic invoice information (date_of_purchase, customer_ID) , but it would store the individual items in the invoice (Invoice_ID, Product_ID, Product_QTY, Product_Price_Per_Unit). Check out this link here, it covers the general idea that I'm talking about.
https://itextpdf.com/en/resources/books/zugferd-future-invoicing/3-simple-invoice-database


Daily database maintenance using SQL Server 2008 and a stored procedure I have created a database maintenance plan using wizard though that link for SQL Server 2005.

I have created a Maintenance Plan for SQL Server 2008 and it works fine.

Now I required to complete the same task using a stored procedure for my desktop application.

I have tried this stored procedure for that but it doesn't works for me.

Stored Procedure Execute Successfully and also i can see the entered values in the table sysdbmaintplans of  msdb the system database but created maintenance plan doesn't shows Under tree(MANAGEMENT=>MAINTENANCE PLAN) of Object Explorer(SQL Server Management Studio).

Please Help...

Below is the code snippet I have tried.

//   sp_add_maintenance_plan [ @plan_name = ] 'plan_name' ,
//@plan_id = 'plan_id' OUTPUT

String planid = "";

string maintenancePlan = "MyMaintenance";

string SQLstr = "DECLARE @plan_id as Varchar(50)";
SQLstr += " EXEC sp_add_maintenance_plan ";
SQLstr += " @plan_name='" + maintenancePlan + "',";
SQLstr += " @plan_id= @plan_id OUTPUT";
SQLstr += " SELECT @plan_id";

SqlConnection cnn = new SqlConnection(MSDBconnStr);
SqlCommand cmd = new SqlCommand(SQLstr, cnn);

cnn.Open();
planid = (string)cmd.ExecuteScalar();
cnn.Close();

//   sp_add_maintenance_plan_db [ @plan_id = ] 'plan_id' ,
//[ @db_name = ] 'database_name'

string maintenancePlanDataBase = "MyDataBase";
string SQLstr1 = " EXEC sp_add_maintenance_plan_db ";
SQLstr1 += " @plan_id='" + planid + "',";
SQLstr1 += " @db_name='" + maintenancePlanDataBase + "'";

SqlConnection cnn1 = new SqlConnection(MSDBconnStr);
SqlCommand cmd1 = new SqlCommand(SQLstr1, cnn1);

cnn1.Open();
cmd1.ExecuteNonQuery();
cnn1.Close();

Instead of using maintenance plans, check out Ola Hallengren's free maintenance stored procedures.  They're way more powerful than maintenance plans and they don't require SSMS (per your requirements) to implement.  You can set them up with any T-SQL connection.


Delete duplicate records and update the mapping table I have store_table_map and item_price_map. I want to delete duplicate records in store_table_map  and retain the latest row based on updated_date because i want to put constraints in store_id and item_id. But I also want first to update the item_price_map as seen below before deleting old duplicate records.
Current Table

Expected Result

Here's the script
CREATE TABLE store_table_map (
    map_id bigserial NOT NULL,
    store_id int8 NOT NULL,
    item_id int8 NOT NULL,
    updated_date date NOT NULL DEFAULT now()
);

INSERT INTO store_table_map (store_id,item_id,updated_date) VALUES
(1,123,'2020-07-09')
,(1,123,'2020-07-10')
,(2,456,'2020-07-08')
,(2,456,'2020-07-10')
;

CREATE TABLE item_price_table_map (
    map_id bigserial NOT NULL,
    store_table_map_id int8 NOT NULL,
    price float8 NOT NULL,
    updated_date date NOT NULL DEFAULT now()
);

INSERT INTO item_price_table_map (store_table_map_id,price,updated_date) VALUES
(1,99.0,'2020-07-09')
,(2,199.0,'2020-07-10')
,(3,299.0,'2020-07-08')
,(4,399.0,'2020-07-10')
;

postgres version: PostgreSQL 9.2.24

You first need to create the mapping from the old map_id to the new one, this can be done using distinct on() and a self join:
select m1.map_id old_map_id, m2.new_map_id
from store_table_map m1
  join (
    select distinct on (store_id, item_id) store_id, item_id, map_id as new_map_id
    from store_table_map
    order by store_id, item_id, updated_date desc
  ) m2 on (m1.store_id, m1.item_id) = (m2.store_id, m2.item_id)

The above returns:
old_map_id | new_map_id
-----------+-----------
         1 |          2
         2 |          2
         3 |          4
         4 |          4

This can be used in an UPDATE statement:
update item_price_table_map ip
  set store_table_map_id = t.new_map_id
from (
  select m1.map_id old_map_id, m2.new_map_id
  from store_table_map m1
    join (
      select distinct on (store_id, item_id) store_id, item_id, map_id as new_map_id
      from store_table_map
      order by store_id, item_id, updated_date desc
    ) m2 on (m1.store_id, m1.item_id) = (m2.store_id, m2.item_id)
) t
where t.old_map_id = ip.store_table_map_id
  and ip.store_table_map_id <> t.new_map_id;

After that you can remove the rows with the now unused map_ids
delete from store_table_map stm
where not exists (select *
                  from item_price_table_map ip
                  where ip.store_table_map_id = stm.map_id);

(I could only test this on Postgres 12, but I think it should work on 9.2 too)


How to model a table in a relational database where all attributes are foreign keys to another table? I've been working on the data model for a healthcare mobile app that will collect the user's symptoms and will book an appointment with a doctor if requested by the user.
I was thinking the app would list the available symptoms and the patient could choose from them, and then the system would estimate some illness based on the chosen symptoms and perhaps this Illness record could be a foreign key in the Diagnosis table. Then, when booking an appointment, there would be a foreign key to this Diagnosis record in the Appointments table. Later, this Diagnosis tuple could be updated by the doctor during the appointment. The problem with this approach is that this way in the Diagnosis table we are only storing some estimated illness instead of the actual symptoms of the patient that were present when booking the appointment.
I'm having trouble figuring out the model for the Diagnosis table, and also what to include in the Appointments table (besides doctor name, appointment date etc. which are not relevant to the question) regarding the diagnosis provided by the app.
What do you suggest should constitute the Diagnosis table? Should I store the symptoms of the patient instead of the estimated illness and refer to this in the Appointments table rather than some illness which later might get overwritten by a doctor anyway? If so, this would mean that we don't know how many attributes (symptoms) one record of the Diagnosis table could have, but they would all be just foreign keys to the Symptoms table.
Also, do you think I can model and do this in a relational database or should I be thinking of a NoSQL database?
I'm only listing the tables that I think are relevant to the question:
symptoms:            illnesses:                                 diagnosis:         appointments:

id    name           id    name               FK_symptoms?      id    ?    ?       id    FK_patient_name     FK_diagnosis
1     fever          1     pneumonia                            1                  1
2     chills         2     allergic rhinitis                    2                  2
3     sore throat    3     asthma                               ...                ...
4     headache       ...
...

What other approach would you suggest?

Frame challenge: unless this is a toy project not intended for a real-life application I suggest you study existing models that represent healthcare data instead of reinventing the wheel. There are more complexities in this area than you might think. One example would be the HL7 FHIR model, which, incidentally, has multiple open-source server implementations that are ready to be used.
This intro on Coursera might be helpful too.


When converting a replica set with an arbiter to a shard, does the arbiter also get added to the shard? I'm converting an existing replica set that has an arbiter to a shard as shown in this guide:

https://docs.mongodb.com/v3.2/tutorial/convert-replica-set-to-replicated-shard-cluster/

and it's unclear if the arbiter needs needs to be included in the sh.addShard() method as shown here:

https://docs.mongodb.com/v3.2/tutorial/convert-replica-set-to-replicated-shard-cluster/#add-initial-replica-set-as-a-shard

I'd guess that it doesn't since it doesn't carry a copy of the data but I couldn't find a distinct answer one way or the other.

When you add a new shard using sh.addShard(...) you are providing a seed list of members which will be used to discover the current replica set configuration. You can specify as few as one member of the replica set or as many as all, and you do not need to specifically include the arbiter.

FYI, the sh.addshard() mongoshell helper wraps the underlying addShard command (which has a few additional options not currently exposed via the sh.addShard() helper).


Test if a string is a valid, unquoted identifier? I'm just wondering if there is an established method for testing if a string can be used as an unquoted PostgreSQL identifier? (unquoted because almost any string can be a quoted identifier).

I ask because as shown in a previous question (How to quote qualified table name with format() function?), there are times when I would need to specify an identifier (such as the name of a table to be created) that does not yet exist, as string values (text) instead of a safer type such as regclass. Quoting the string/name can be problematic as shown there and probably else where. Without quoting, it's susceptible to SQL injection.

I guess if one programs it hard enough, a string parsing function can be written ultimately. Just wanted to check if there are existing solutions.

Related:

What are the valid formats of a PostgreSQL schema name?

Is the function PARSENAME() the opposite of QUOTENAME() (sql-server)

Here is what I ended up using based on @a_horse_with_no_name's comment.

CREATE OR REPLACE FUNCTION is_unqualid(_ident text)
 RETURNS bool AS $func$
  SELECT format('%I',lower(_ident)) = lower(_ident);
$func$ LANGUAGE sql;

The idea is that the format function (with %I) will not add any quotes if it is unnecessary (i.e. when the input is already a simple identifier). I also normalized identifier names to lower cases using lower() before the comparison, which may or may not be desirable depending on whether the identifiers are supposed to be case insensitive or sensitive.

Sample output:

=> SELECT is_unqualid('Ab'), is_unqualid('ab'), is_unqualid('a b'), is_unqualid('a. b');
 is_unqualid | is_unqualid | is_unqualid | is_unqualid
-------------+-------------+-------------+-------------
 t           | t           | f           | f
(1 row)


Master-master Replication with 3 Nodes I need to configure mysql master/master replication with 3 nodes. Currently I have master/slave setup and I need to move it to master/master with 3 nodes. I have gone through some posts about configuring it in ring structure, I'm little nervous about it.

If anyone has the similar setup on production, would be great if you can suggest me how to achieve this.

There are multiple reason why you wouldn't want to do multimaster replication in a circular fashion, but most of them can be summarized in one: You will have 3 different single points of failure. Check this article (which is exactly what you asked for, but probably not what you want). Standard replication is very prone to drifts in data, as it is asynchronous, producing -if you are lucky- the replication to stop for all nodes or -if you are unlucky- replication to continue with different data between nodes.

While 5.6 GTID and other features minimized those consistency problems, you still have the 3 single points of failure, as replication is single-master (multi-source replication is available only in MariaDB and MySQL 5.7, negating the need for circular replication).

If you want a setup that is multimaster (write anywhere), I would highly recommend going for a different technology that manages conflicts between nodes. Galera (you may also find it under the names of Percona XtraDB Cluster or MariaDB Cluster) is probably the way to go. It works on WANs, "resolves" conflicts (rollbacks them and retries the transaction) is multithreaded, and can be used to substitute regular replication and/or for clustering. Very recommended if your goal is HA or read scaling. It is free and open source, very widespread (I've helped several banks and hosting companies using it), compatible with standard replication and uses standard InnoDB -not a different engine- for storage.

The biggest cons, of course, are that it is a different technology which may take some time to understand (although probably easier than other clustering technologies), and it has its small quirks. But, in my very own opinion it is worth the time learning about it to make things "work properly".

Can you setup circular replication? Sure, in the article I mentioned above you have the typical recommendations of log-slave-updates, auto_increment_increment and auto_increment_offset for each node. However, the few people that may be running this have to still either avoid multi-master writes or run it in a very controlled environment, where you cannot execute updates and deletes to the same tables in concurrency. Some people also over-engineer solutions involving GTID and semi-sync replication, etc., but generally not all companies have the dedication and knowledge to put patches over a protocol that is not natively prepared for this.


Can't access MySQL after sourcing a database and reboot I'm trying to set a local dev environment on a Debian VirtualBox and am now stuck for days with the following situation.

After setting up apache, php, mysql, pma and got eveything running, I open the mysql console with root user.
I create the empty database, "use" it then "source" the file I want to load.
When finished, I eventually exit from the client.
I can connect again, exit as many times as I want...
I also can access to phpmyadmin with the same root user, and confirm that the database was properly loaded.

Everything's fine, till I reboot debian. From now on, I stick to :

  ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I've tried to access with different users, nothing changes.
Same thing for phpmyadmin.

As I work with Vbox snapshots,I've determined that the issue seems related to the database.
Rebooting after creating the empty database is fine, I'm still able to connect.

I've crawled many related topics accross the web, and couldn't come up with that particular situation, and all the approaching topics solutions start with connecting with root user - which I can't do.

What am I missing ?

Thanks for your help !

Oh, you nailed it!

It seems that my dump actually contained mysql.user data, so that after loading the database, I could only connect with these distant login information.
Taking a look at this mysql.user table, it appears that these login were specified, not for localhost, but the distant host.

Anyway... Knowing this, I've  been able to connect to mysql client, then reset these login info :

mysql –u root –p [distant password]
use mysql.user UPDATE mysql.user
SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;
quit

I finally deleted these distant host lines from phpmyadmin, to be sure not to mess anything up.

Thanks a lot for your help !


Configuring Listener on Oracle Server I am very much new to Oracle environment. I was trying to connect to my Oracle server from another machine on the same network. I found out that I need to configure Oracle Listener on the server that I did using the "Net Configuration Assistant" tool.

After saving the configuration file, I opened the listener.ora file and saw that in my new listener the hostname is the IP/machine name of the Oracle server itself rather than the client machine, what I was thinking that the host name in the listener should be IP/machine name of the client machine? Is that right?

Its clear to me now that the Host name in the listener configuration on the Oracle server is indeed the IP/Name of the Oracle server where the database exists and the port number is the port this database will listen to incoming connections. I was wrong in assuming that each incoming connection must be defined as a listener.


How to calculate the max number of worker threads in SQL Server? I use 64-bit OS with two CPUs (each of them makes 7 logical processors) and with total number of logical processors equal to 14.

When I run this code I get 2048

SELECT max_workers_count FROM sys.dm_os_sys_info

On the other way, when I use the formula below I get totally different number which is 672

For a 64-bit operating system:
Total available logical CPUs <= 4
Max Worker Threads = 512
Total available logical CPUs > 4
Max Worker Threads = 512 + ((logical CPUs - 4)*16)

Can you explain to me why I get different numbers? I tested it on another server and got exact same numbers for both cases.

EDIT

P.S Thanks to Dan Guzman for his suggestions to check server settings for max worker threads. That is what EXEC sp_configure 'max worker threads' returns:

It seems someone has changed the maximum worker threads setting to a non-default value, which will override the default calculated value. The current configured value can be determined with:

EXEC sp_configure 'max worker threads';

I'll add the value is sometimes set mistakenly as a knee-jerk reaction to address symptoms rather than the underlying cause. Some valid reasons for configuring the value are called out in the documentation but the default calculation is appropriate for most workloads.


Copy over data from previous rows in oracle 11g table Our DB is Oracle 11g. I have a table with the structure is as shown below:

Person_Id |  Eff_Dt    |   Wid | Prv_Wid
----------------------------------

P1       |   1/1/2001   | 2001 |
P1       |   1/10/2001  | 2001 |
P1       |   10/10/2001 | 2002 |
P1       |   1/1/2002   | 2003 |
P1       |   5/4/2002   | 2003 |
P1       |   8/6/2002   | 2002 |
P1       |   1/1/2005   | 2001 |
P1       |   1/10/2006  | 2001 |

I have a requirement that the Prv_Wid should be derived from the previous row's WID. But if the current and previous WIDs are same, then I have to go to the old previous row where the WID is different and put it in my current row's Prv_Wid.
It should be as shown below:

Person_Id | Eff_Dt | Wid| Prv_Wid
----------------------------

P1        | 1/1/2001    |    2001   |    0
P1        | 1/10/2001   |    2001   |    0
P1        | 10/10/2001  |    2002   |  2001
P1        |  1/1/2002   |    2003   |  2002
P1        |  5/4/2002   |    2003   |  2002
P1        |  8/6/2002   |    2002   |  2003
P1        |  1/1/2005   |    2001   |  2002
P1        | 1/10/2006   |    2001   |  2002

I have tried several ways like lead, lag, first_value, last_value and procedure to achieve. But, I am not successful. Could you please provide any solution?

All right, so we need some mechanism to keep track of the previous wid value regardless of the number of repeating values, so we can not use LAG directly, because we do not know how far back was the last different value.

Our data is:

with data as
(
  select 'P1' as person_id, date'2001-01-01' as eff_dt, 2001 as wid from dual union all
  select 'P1' as person_id, date'2001-01-10' as eff_dt, 2001 as wid from dual union all
  select 'P1' as person_id, date'2001-10-10' as eff_dt, 2002 as wid from dual union all
  select 'P1' as person_id, date'2002-01-01' as eff_dt, 2003 as wid from dual union all
  select 'P1' as person_id, date'2002-05-04' as eff_dt, 2003 as wid from dual union all
  select 'P1' as person_id, date'2002-08-06' as eff_dt, 2002 as wid from dual union all
  select 'P1' as person_id, date'2005-01-01' as eff_dt, 2001 as wid from dual union all
  select 'P1' as person_id, date'2006-01-10' as eff_dt, 2001 as wid from dual
)
select
 *
from
 data
order by
 eff_dt
;

PE EFF_DT            WID
-- ---------- ----------
P1 2001-01-01       2001
P1 2001-01-10       2001
P1 2001-10-10       2002
P1 2002-01-01       2003
P1 2002-05-04       2003
P1 2002-08-06       2002
P1 2005-01-01       2001
P1 2006-01-10       2001

I will not repeat the with section any more to save space. Let's calculate a new field, called prev_marker as:

If wid = previous wid, this field becomes NULL. If wid != previous wid, this field gets the value of the previous wid

select
  person_id,
  eff_dt,
  wid,
  case when lag(wid) over (order by eff_dt) != wid
    then lag(wid) over (order by eff_dt)
    else null
  end as prev_marker
from
  data
order by
  eff_dt
;

PE EFF_DT            WID PREV_MARKER
-- ---------- ---------- -----------
P1 2001-01-01       2001 (null)
P1 2001-01-10       2001 (null)
P1 2001-10-10       2002        2001
P1 2002-01-01       2003        2002
P1 2002-05-04       2003 (null)
P1 2002-08-06       2002        2003
P1 2005-01-01       2001        2002
P1 2006-01-10       2001 (null)

Using this coluumn, we can get the desired prv_wid value with the LAST_VALUE function if we inspect rows up to the current row. The trick is, that the LAST_VALUE function supports the IGNORE NULLS option, so when we encounter a NULL value, we can easily ignore all the rows with the same wid and fall back to the previous value:

select
  person_id,
  eff_dt,
  wid,
  last_value(prev_marker ignore nulls) over (order by eff_dt rows between unbounded preceding and current row) as prv_wid
from
(
select
  person_id,
  eff_dt,
  wid,
  case when lag(wid) over (order by eff_dt) != wid
    then lag(wid) over (order by eff_dt)
    else null
  end as prev_marker
from
  data
)
order by
  eff_dt
;

PE EFF_DT            WID    PRV_WID
-- ---------- ---------- ----------
P1 2001-01-01       2001 (null)
P1 2001-01-10       2001 (null)
P1 2001-10-10       2002       2001
P1 2002-01-01       2003       2002
P1 2002-05-04       2003       2002
P1 2002-08-06       2002       2003
P1 2005-01-01       2001       2002
P1 2006-01-10       2001       2002

Our last task is to replace the NULLs with 0s, that is just a simple NVL:

select
  person_id,
  eff_dt,
  wid,
  nvl(last_value(prev_marker ignore nulls) over (order by eff_dt rows between unbounded preceding and current row), 0) as prv_wid
from
(
select
  person_id,
  eff_dt,
  wid,
  case when lag(wid) over (order by eff_dt) != wid
    then lag(wid) over (order by eff_dt)
    else null
  end as prev_marker
from
  data
)
order by
  eff_dt
;

PE EFF_DT            WID    PRV_WID
-- ---------- ---------- ----------
P1 2001-01-01       2001          0
P1 2001-01-10       2001          0
P1 2001-10-10       2002       2001
P1 2002-01-01       2003       2002
P1 2002-05-04       2003       2002
P1 2002-08-06       2002       2003
P1 2005-01-01       2001       2002
P1 2006-01-10       2001       2002


Unable to create view with temp table Hi I am working on one project and want to create a view. I am using temp table in my query. can any one let me know how we can create such view ?
Following is my query, I tried using WITH..AS , but it gives due to insert query.
create temporary table temp_testtbl as
select id,dte,name,hrs_total
FROM dttbl.dts limit 0;

insert into temp_testtbl
select id,dte,name,hrs_total
FROM dttbl.dts
group by id,dte,name,hrs_total;

SELECT i.dte,
    idm.empl_id,
    1 AS entity_id,
    sum(i.hrs_total) AS myhrs
   FROM temp_testtbl i
   JOIN schema1.mytable idm ON idm.src_id = i.iex_id::text AND idm.src_type_id = 123 AND idm.end_dte IS NULL
   JOIN schema1.mytable idm2 ON idm2.empl_id = idm.empl_id AND idm2.src_type_id = 345 AND idm2.end_dte IS NULL
  GROUP BY i.dte, idm.empl_id limit 100;

A view can't have multiple statements within it, you have to express the entire thing as a SELECT. A subquery with the SELECT part of your INSERT can be used in place of the temp table reference. Below should give you a rough idea, although there are errors in the original SQL that I can't fix based on the information I have (eg, the first join condition references i.iex_id, which doesn't exist in the temp table or the subquery).
select i.dte, idm.empl_id, 1 as entity_id, sum(i.hrs_total) AS myhrs
FROM (
select id, dte, name, hrs_total
FROM dttbl.dts
group by id,dte,name,hrs_total
) AS i
JOIN schema1.mytable idm ON idm.src_id = i.iex_id::text AND idm.src_type_id = 123 AND idm.end_dte IS NULL
   JOIN schema1.mytable idm2 ON idm2.empl_id = idm.empl_id AND idm2.src_type_id = 345 AND idm2.end_dte IS NULL
  GROUP BY i.dte, idm.empl_id limit 100;


How can a field with multiple values be used with SQL Server? For example, I have a field in MongoDB like this:

"SignedDocs" : [

    {
     "_id" : "blablabla"
     "title": " bla bla bla"
     },

    {
     "_id" : "blablabla2"
     "title": " bla bla bla2"
     },

    {
     "_id" : "blablabla3"
     "title": " bla bla bla3"
     }
]

For you to understand, inside this field there are 3 values. What would be the best way to achieve this with SQL Server? What is the best practice to save 3 values inside a column?

I don't want to do something like this:

NAME    | DOCSSIGNED
abduh   |      1
abduh   |      2
abduh   |      3

Having lots of rows with the same values only changing this DOCSSIGNED field as a FK pointing to the DOCS table.

EDIT: Ohh maybe a 3rd table between these 2 linking them?

On newer versions of SQL Server, you might consider bringing over the JSON data as-is, storing it in an nvarchar(max) column, and using the built-in JSON related functions for parsing and querying JSON data.  This is available in SQL Server 2016+ (compatibility level 130).  You can read more about it here: JSON data in SQL Server

If you plan to "convert" the MongoDB documents into a more relational pattern, the mapping table you mentioned would be a good way to prevent the "Name" of the signer being repeated in each row.

It looks like what you're modeling in the MongoDB document is a collection of documents that have been signed by a given Person.  So the relational structure would be something like this:

A table for documents:

CREATE TABLE dbo.Docs
(
    ID int IDENTITY PRIMARY KEY NOT NULL,
    [Title] varchar(100) NOT NULL
);

A table for the people signing these documents:

CREATE TABLE dbo.Person
(
    Id int IDENTITY PRIMARY KEY NOT NULL,
    [Name] varchar(100) NOT NULL
);

And then the table that brings together all of the documents a person has signed:

CREATE TABLE dbo.SignedDocs
(
    PersonId int NOT NULL,
    DocId int NOT NULL,

    CONSTRAINT FK_SignedDocs_Person FOREIGN KEY (PersonId)
        REFERENCES dbo.Person (Id),

    CONSTRAINT FK_SignedDocs_Docs FOREIGN KEY (DocId)
        REFERENCES dbo.Docs (Id)
);

You could add more constraints to the table if there are other rules that need to be enforced (a document can only be signed by one person, for example).

Note that performing this sort of "ETL" process to convert NoSQL data to relational data can have a lot of maintenance overhead.  One of the advantages of a document database like MongoDB is that the structure of documents can easily be changed.  This has great potential to break the ETL.  Of course, every environment is different, so it might not be a problem for you.  I just thought it was worth mentioning.


SQL Central for Sybase 16 SP 02 ALL,

First time post here in DB Administrators, so please be patient.

In the past long time ago I was working with Sybase SQL Anywhere on Windows. There I got the actual server, ISQL utility, and a little GUI application called SQL Central, which helped managing the database I was working for in the GUI mode.

Couple of days ago, I installed "big" Sybase - version 16 SP02 on Linux. To my surprise I saw only the server itself and the ISQL utility. No SQL Central or an application of that nature. Going thru the docs, I also didn't see anything.

Now my question is: is SQL Central an outdated software? Or maybe it is only supplied with SQL Anywhere? This utility was very helpful and I was even running ISQL from it if I needed to run some queries on the DB

I don't mind just using ISQL but that application was very helpful.

Thank you for any hints you can provide.

Sybase Central is no longer provided in the new ASE versions. With ASE16 SP02, you can use ASE cockpit instead.

http://help.sap.com/ase1602#section7


Restrict users from modifying datasources in SSRS We had a situation lately when a particular user of the SSRS reports modified the datasources defined on the reporting server. These users have access rights to create ad-hoc reports using reportbuilder. Because of this it's turning out to be tricky to manage the access using pre-defined roles.

Is there a way to restrict users form having access to modify the datasource, yet have the ability to use datasources for ad-hoc reports?

I'm going to guess that you are running SSRS in Native mode rather than SharePoint integrated, and that you are using the pre-defined roles to grant access to users. If you want to post more information about the version of SQL Server you are using and the security role these users are in, I can update my answer. For now, try this.

There are pre-defined security roles in SSRS:

Content Manager
Publisher
Browser
Report Builder
My Reports
System Administrator
System User

I'm going to guess that these users are currently in the My Reports Role.  This role is allowed to do quite a bit, including manage data sources and manage reports.  This means these users can create delete, view, and modify data sources.

You might want to consider creating a new role that has only the item-level permissions you want.  You can find the list of item-level tasks here. You basically want to include all of the item-level tasks included in the My Reports role, except Manage Data Sources. You could also modify the My Reports role to remove this one item rather than creating a new role.


How to avoid Msg 9514, Level 16, State 1, Line 3 error for linked server? I have a query to compare data between my local SQL Server and linked SQL Server. My problem is that the table I compare contains XML column. I get:

  Msg 9514, Level 16, State 1, Line 3 Xml data type is not supported in
  distributed queries. Remote object [srv].[DB].dbo.[table]' has xml
  column(s).

I don't know how to fix this, because I don't show this column inside the select statement. I though that I can CAST, but when the column is not in the select list what to cast?

I see that there is workaround with cast by I can not. So may be I miss something.

Any ideas?

My test code looks like:

SELECT
TA2.ID FROM [DB].[dbo].[table1] TA2
  INNER JOIN [linkedsrv].[DB].[dbo].[table1] PA ON (TA2.ID = PA.ID)

I need to update PA:

--UPDATE ALL ON PA

UPDATE XSRV
SET
  XSRV.[Description] = SRV.[Description],
  XSRV.[Code] = SRV.[Code],
  XSRV.[Active] = SRV.[Active],
  XSRV.[Expired] = SRV.[Expired],
  XSRV.[SType] = SRV.[SType],
  XSRV.SupportParameters = CAST(SRV.SupportParameters AS VARCHAR(MAX))
FROM
  [PANI_TEST].dbo.[Services] AS SRV
  INNER JOIN (SELECT X.ID, X.Code, X.[Description], X.Code, X.Active, X.Expired,
      X.SType, CAST(X.XmlParams AS VARCHAR(MAX)) AS XmlParams, X.CreatedDate, X.CreatedBy,
      X.ModifiedDate, X.ModifiedBy FROM [PANI_TEST].[dbo].[Services] AS X) AS XSRV ON
    (SRV.ID = XSRV.ID)
WHERE
  (SRV.[Description]     <> XSRV.[Description] ) OR
  (SRV.[Code]            <> XSRV.[Code]        ) OR
  (SRV.[Active]          <> XSRV.[Active]      ) OR
  (SRV.[Expired]         <> XSRV.[Expired]     ) OR
  (SRV.[SType]           <> XSRV.[SType]       ) OR
  (CAST(SRV.XmlParams AS VARCHAR(MAX)) <> XSRV.XmlParams);

Of course the code above is not working because I use derived table, which is not updatable. I don't figure out right now how to update. If I use simple CAST without derived table I still get error.

What is probably happening is that SQL Server wants to download the whole remote table to your tempdb before attempting to join to the local table, including the XML column.

Try something like this:

SELECT TA2.ID
FROM [DB].[dbo].[table1] TA2
INNER JOIN (
    SELECT col1, col2, col3, CAST(xmlCol AS varchar(max)) AS xmlCol
    FROM [linkedsrv].[DB].[dbo].[table1]
) AS PA
    ON (TA2.ID = PA.ID)

Or like this:

SELECT TA2.ID
FROM [DB].[dbo].[table1] TA2
INNER JOIN (
    SELECT *
    FROM OPENQUERY(linkedsrv, '
        SELECT col1, col2, col3, CAST(xmlCol AS varchar(max)) AS xmlCol
        FROM [DB].[dbo].[table1]
    ')
) AS PA
    ON (TA2.ID = PA.ID)


Why SELECT COUNT() query execution plan includes left joined table? In SQL Server 2012 I have table valued function with join to another table I need to to count number of rows for this 'table valued function'. When I inspect the execution plan, I can see the left join table. Why? How can left joined table influence number of rows returned? I would expect that the db engine does not need to evaluate left joint table in SELECT count(..) query.

Select count(realtyId) FROM [dbo].[GetFilteredRealtyFulltext]('"praha"')

The execution plan:

The table valued function:

CREATE FUNCTION [dbo].[GetFilteredRealtyFulltext]
(@criteria nvarchar(4000))
RETURNS TABLE
AS
RETURN (SELECT
realty.Id AS realtyId,
realty.OwnerId,
realty.Caption AS realtyCaption,
realty.BusinessCategory,
realty.Created,
realty.LastChanged,
realty.LastChangedType,
realty.Price,
realty.Pricing,
realty.PriceCurrency,
realty.PriceNote,
realty.PricePlus,
realty.OfferState,
realty.OrderCode,
realty.PublishAddress,
realty.PublishMap,
realty.AreaLand,
realty.AreaCover,
realty.AreaFloor,
realty.Views,
realty.TopPoints,
realty.Radius,
COALESCE(realty.Wgs84X, ruian_cobce.Wgs84X, ruian_obec.Wgs84X) as Wgs84X,
COALESCE(realty.Wgs84Y, ruian_cobce.Wgs84Y, ruian_obec.Wgs84Y) as Wgs84Y,
realty.krajId,
realty.okresId,
realty.obecId,
realty.cobceId,
IsNull(CONVERT(int,realty.Ranking),0) as Ranking,

realty.energy_efficiency_rating,
realty.energy_performance_attachment,
realty.energy_performance_certificate,
realty.energy_performance_summary,

Category.Id AS CategoryId,
Category.ParentCategoryId,
Category.WholeName,
okres.nazev AS okres,
ruian_obec.nazev AS obec,
ruian_cobce.nazev AS cobce,
ExternFile.ServerPath,
Person.ParentPersonId,
( COALESCE(ftR.Rank,0) + COALESCE(ftObec.Rank,0) + COALESCE(ftOkres.Rank,0) + COALESCE(ftpobvod.Rank,0)) AS FtRank

FROM realty
JOIN Category ON realty.CategoryId = Category.Id
LEFT JOIN ruian_cobce ON realty.cobceId = ruian_cobce.cobce_kod
LEFT JOIN ruian_obec ON realty.obecId = ruian_obec.obec_kod
LEFT JOIN okres ON realty.okresId = okres.okres_kod
LEFT JOIN ExternFile ON realty.Id = ExternFile.ForeignId AND ExternFile.IsMain = 1 AND ExternFile.ForeignTable = 5
INNER JOIN Person ON realty.OwnerId = Person.Id
Left JOIN CONTAINSTABLE(Realty, *, @criteria) ftR ON realty.Id = ftR.[Key]
Left JOIN CONTAINSTABLE(ruian_obec, *, @criteria) ftObec ON realty.obecId = ftObec.[Key]
Left JOIN CONTAINSTABLE(Okres, *, @criteria) ftOkres ON realty.okresId = ftOkres.[Key]
Left JOIN CONTAINSTABLE(pobvod, *, @criteria) ftpobvod ON realty.pobvodId = ftpobvod.[Key]
WHERE Person.ConfirmStatus = 1
AND ( COALESCE(ftR.Rank,0) + COALESCE(ftObec.Rank,0) + COALESCE(ftOkres.Rank,0) + COALESCE(ftpobvod.Rank,0))  > 0
)

UPDATE:

I add unique index to follow idea of Rob Farley:

 Create unique nonclustered index ExternFileIsMainUnique ON ExternFile(ForeignId) WHERE IsMain = 1 AND ForeignTable = 5

And indexed suggested by DB Engine:

CREATE NONCLUSTERED INDEX [RealtyOwnerLocation] ON [dbo].[Realty]

(
    [OwnerId] ASC
)
INCLUDE (   [Id],
    [okresId],
    [obecId],
    [pobvodId])
GO

For simplicity I remove condition

WHERE Person.ConfirmStatus = 1

from tabled valued function above.

Now the execution plan is much simpler but it still touch table ExternFile:

Maybe sql server is not clever enough?

If ForeignId, ForeignTable, IsMain is not known* to be unique in ExternFile, then the QO will need to include that table to work out the count. Any time multiple rows match, the count will be affected.

Join Simplification in SQL Server
Designing for simplification (SQLBits recording)

* The optimizer does not currently recognize filtered unique indexes as unique

UPDATE (by OP):
The solution is to change line in query from LEFT JOIN (which can produce multiple rows):

LEFT JOIN ExternFile ON realty.Id = ExternFile.ForeignId AND ExternFile.IsMain = 1 AND ExternFile.ForeignTable = 5

to OUTER APPLY with TOP (which produce one row and does not affect COUNT)

OUTER APPLY (SELECT TOP (1) ServerPath FROM ExternFile WHERE ForeignId = realty.Id AND IsMain = 1 AND ForeignTable = 5) AS ExternFile

The query is now more effective. Adding a unique index could not be done, because values weren't unique, they were unique only for combination in the condition and this is not considered as unique as mentioned above.


Rebuild a mysql slave adding innodb_file_per_table without locking the master I would like to rebuild a mysql slave, adding innodb_file_per_table to the configuration in order to reduce the disk space used (idbdata1 has grown a lot). I want to do it without locking the master's tables and I sketched up a procedure that seems to work, but I would like to know if it is really accurate. I tested this on two vms.

Here are the configurations for the test:

On the master:

server-id               = 10
log_bin                 = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_do_db            = test
#binlog_ignore_db       = include_database_name

On the slave:

server-id               = 20
log_bin                 = /var/log/mysql/mysql-bin.log
relay_log               = /var/log/mysql/mysql-relay-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
binlog_do_db            = test

Before starting the procedure:

On the slave:

Seconds_Behind_Master: 0

At this point, this is the procedure I tried to rebuild the slave with the new configuration and then let him resume the replication where it stopped

on the slave: stop slave
on the master: insert into t1 values (4,1) # I insert values in tables t1 in database test to validate that the slave is able to recover the replication data that occured during the process
on the slave: mysqldump -u root -p --opt test > test_slave.sql
on the master: insert into t1 values (5,1)
on the slave: drop database test
on the master: insert into t1 values (6,1)
on the slave:

change configuration to set innodb_file_per_table:

innodb_file_per_table
innodb_flush_method=O_DIRECT

on the master: insert into t1 values (7,1)
on the slave:

store bin log replication position from the last stop slave:

mysql -u root -p -e "show slave status \G;" > status

on the slave: service mysql restart
on the slave: stop slave
on the master: insert into t1 values (8,1)
on the slave:

create database && import dump:

mysql -u root -p -e "create database test;"
mysql -u root -p -b test < test_slave.sql

on the master: insert into t1 values (9,1)
on the slave:

reconfigure the replication to start from the last stop slave position (with the master_log_pos and master_log_file stored in the status file):

change master to MASTER_HOST='db01',MASTER_USER='myuser',MASTER_PASSWORD='mypasswd',MASTER_LOG_FILE='mysql-bin.000004',MASTER_LOG_POS=477;

on the slave: start slave

After the 16th step, all inserts made on the master during the process appear in test.t1 on the slave and the replication is working.

               Slave_IO_State: Waiting for master to send event
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
             Master_Server_Id: 10

It appears to be successful. However, if someone sees a pitfall in this procedure (that I can't see on this limited lab environment) it would be great to share :)

Thank you,

really all could be shorter:

Stop mysql on Slave, change settings, Start MySQL on Slave
Login to MySQL; Stop slave; exit from mysql client
make dump from Master direct from Slave:

  mysqldump --host=Master_IP --port=Master_port -u
  user_with_enought_rights -p --single-transaction --all-databases
  --master-data=1 > dump.sql

restore dump on Slave

  mysql -u root -p < dump.sql

start Slave

It will not lock master (in case of InnoDB tables) and automatically adjust Slave to Master position


How to join multiple results into one row with dynamic columns? Foreword - I don't know how to formally describe the situation in a single question so the below is as verbose as I can make it...

Taking wordpress as an example, there's the "wp_users" table which has all the standard things like email and login and then there's the "wp_usermeta" table which looks like the following:

+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| umeta_id   | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id    | bigint(20) unsigned | NO   | MUL | 0       |                |
| meta_key   | varchar(255)        | YES  | MUL | NULL    |                |
| meta_value | longtext            | YES  |     | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+

This table has many rows for each user with the meta_key and meta_value columns being the important bits of actual data.

What I'd like to do is query for all users and all meta, but only returning one row per user with every meta_key/meta_value as additional columns.

As an example scenario: there are three key/value pairs in the meta table, associated with the user_id 42. I'd like to query for the following result:

user_login,user_email,mk1,mk2,mk3
userone,one@example,"one","two","three"
usertwo,two@example,"yes","wow","moars"

Where "mk1", "mk2" and "mk3" are the values of the "meta_key" and the "one", "two", "three" are the corresponding "meta_value"s.

Can this be done in pure SQL (no procedures or code)?

One SQL sentence can't have variable fields per row; every row has same fields. But every field can have or haven't (null) value.

If you want that in pure SQL you can group_concat the fields of wp_usermeta, like:

SELECT use.name, use.email, group_concat(concat('{', meta_key, '##', meta_value, '}')) AS metadata
FROM wp_users use
INNER JOIN wp_usermeta met ON (use.user_id = met.user_id)
GROUP BY met.user_id

But then you need post-process outside Mysql.


What's worse, too many entities or too many tables? I just had a question about database design, I am in fairly early learning stages of this so bear with me.

Suppose I have a parent table called "Projects" each "Project" Entity can have any number of "Attachments" and each "Attachment" can have any number of "Anchors".

I can only see a few possible solutions for this.
Estimations assume 10 years use of this database:
300 Projects/year * 3 Attachments/Project * 20 Anchors/Attachment

Solution 1 (My initial design idea):

Involves 3 tables. "Projects"->"Attachments"->"Anchors"

Number of Tables: 3

Max entities in a table: 180,000 in "Anchors"

Solution 2:

Involves 2+n tables.  "Projects"->"Attachments"->"Anchors[n]"

Number of Tables: 9,002 (9,000 Anchor Tables)

Max entities in a table: 9,000 (In Attachment Tables)

Solution 3:

Involves 1+n1+n2 tables. "Projects"->"Attachments[n1]"->"Anchors[n2]"

Number of Tables: 12,001 (3,000 Attachment + 9,000 Anchor)

Max entities in a table: 3,000 (In Project Tables)

To summarize:

What's worse, more entities or more tables?
AND/OR
Is there simply a better design for this situation that I am unaware of?

The number of entities is driven by the functional specs on the database.  You discover entities by analyzing the subject matter.  This is different from inventing entities because it feels good.  You determine whether any given subject matter entity is relevant to the database by looking at the scope of the database, per the requirements.

The number of tables is driven by the database design.  You create tables as needed to fulfill the design goals.  You are typically pursuing multiple goals:  normalization, simplicity, flexibility, and speed, among others.  Typically each entity will have a table of its own, and there will be extra tables for relationships that are not simple enough to just add FKs to existing tables.

The difference between analysis and design is crucial to all projects beyond simple exercises.


Need a “pg_dump” command to take the backup of the child tables along with its master table for PostgreSQL database We have one master table and multiple child tables (dependent tables)  associated with it.

While taking the backup of the master table , I would like to take the backup of all its child (dependent) tables backup also.

Please guide me how to take the backup of the  maste table and its dependent tables using pg_dump commad.

You can specify multiple tables to "pg_dump" by specifying multiple "-t" flags.

If you are hoping to specify only one "-t" flag and have pg_dump automatically include all the dependent tables, that is not a feature pg_dump has.  You will have to enumerate the tables yourself, either manually or by querying the system catalogs.  I can't give you the query, because your question is not well defined.  We don't know how you want to handle recursive dependents or cross-dependencies, for example.


SQL Server Replace not returning expected result Shouldn't the below code return 0?

SELECT REPLACE(ISNULL('',0),'',0)

This doesn't return 0 either....

SELECT REPLACE('','',0)

I have an nvarchar field in a table that contains numerical values but sometimes is blank (not null).

I have a query that checks if that field is < a number in another field but I get

  "Error converting data type nvarchar to numeric."

errors if the field is blank. I am trying to convert the blank values ('') to zeroes during query execution so that I don't get the conversion error but I just realized that

SELECT REPLACE('','',0)

will not convert the blank field to a 0.

I tried using the below code to try and avoid the conversion error but it is not evaluating to 0 when the threshold field is blank as I expected:

SELECT CAST(REPLACE(ISNULL(Threshold,0),'',0) as decimal(4,2))

How can I get the code to evaluate to 0 when the threshold field is blank.

I'm guessing I could write an if or case statement that skips the blank ('') value threshold but I am thinking there has to be a way to do it all inline, something like what I am trying to do with the above code.

ISNULL('','0')

Returns the first argument '' (as that is not null) so your question boils down to asking why

SELECT REPLACE('','','0')

Doesn't return 0.

The documentation for REPLACE states

  string_pattern Is the substring to be found. string_pattern can be of
  a character or binary data type. string_pattern cannot be an empty
  string (''), and must not exceed the maximum number of bytes that fits
  on a page.

TSQL is not unusual in this respect.

In C# attempting "Some String".Replace("", "0") would give an error "String cannot be of zero length."

This is not surprising as in any string of text you could argue that there are infinitely many empty strings.

There could be 20 between the "T" and "h" at the start of this sentence. As they are zero length how could we tell?

For your actual use case you can use

SELECT CASE
         WHEN Threshold <> ''
           THEN CAST(Threshold AS DECIMAL(4, 2)) --Not null and not empty string
         ELSE 0
       END


Scheduling a task that happens very frequently in Sql Server I've just added some caching to my Sql server to speed up a particularly important SELECT query. This cache will be refreshed every ~10 minutes. The time between activations does not have to be predictable, so long as there is not a long delay between activations.

Is using the Sql server agent the right solution here? This seems to be more aimed at large database maintenance tasks. Should I use a different SQL Server method, or perhaps trigger the update from my client application?

Also, my dev machine does not have the server agent available, so using a different method is attractive for that reason.

I've used SQL Server Agent to run jobs every 10 seconds. It's OK.

If you don't have it, you can use the Windows Scheduler to run osql or sqlcmd as required.
Or say Quartz.net in your app. Or anything that can call a database on a schedule (AutoSys etc)


Sorting by DATETIME slower than by TIMESTAMP? I have the a DATETIME key on which I sort the results and the query is running quite slow. Will it run faster if I change it to TIMESTAMP ?

Because I noticed that ordering by numbered keys is done almost instantly.

You can shorten the running type of queries by indexing certain columns.

Since you mentioned sorting DATETIME fields, one of the best ways to bypass sorting of temp table results is to use a covering index which includes the DATETIME as the last column of any given compound index you need.

I know this is entirely possible because in a link about Covering Indexes By Ronald Bradford, he gave a list of future topics he was giving. Here is that list:

Some future topics on indexes not discussed here include:

Using UNIQUE Indexe
The impact of NULL columns and values on indexes
Eliminating filesort by using indexes <<<---- (PLEASE NOTE THIS)
The affect of too many indexes
Index cardinality


"Cannot open user default database. Login failed" after altering MDF and LDF locations and bringing DB offline I was attempting to move my database to a separate hard drive on the server, so I ran the following commands in the following order (I was following this question : How to change location of SQL Server 2012 database which is already configured with a merge replication):

use master;
ALTER DATABASE db_name
MODIFY FILE (NAME = db_name,
     FILENAME = 'E:\Data\db_name.mdf');

ALTER DATABASE db_name
MODIFY FILE (NAME = db_name_log,
     FILENAME = 'E:\Data\db_name_log.ldf');

Here is where my mistake happened: I either did one or both of the following mistakes:

I may have input the incorrect Name parameter above (Would the mdf/ldf files be dropped in this case?)

...and/or...

I also did not do step 2 in the answer referenced above (Stop log reader agent, distribution and merge agent (if they are running).

Then I brought the database offline:

alter database db_name set offline

Now when I try to connect to the database server, I get the error I described in this question.
How can I remedy this? Do I need the sa login? Or is there something else I can do?

Thank you.

Other users are unable to connect to the database also, getting the same error. Did I actually corrupt the master db somehow?

I've been researching the causes of this error and they all seem to point to the need to restore access using sa login or something to that effect.

I may have input the incorrect Name parameter above (Would the mdf/ldf files be dropped in this case?)

This would not have dropped the files, if the NAME values weren't correct, the command would have failed.

  I also did not do step 2 in the answer referenced above (Stop log reader agent, distribution and merge agent (if they are running).

Are you using replication on this database? If not, then this doesn't matter as these steps are not necessary.

When you login to SQL Server via Management Studio, you can explicitly select a database to connect to or use your default database. If your default database is unavailable, you will get a login failure.

This link details how to explicitly select a database to login to in SSMS. Basically, on the login window, click Options then select to Connection Properties tab. Under "Connect to Database:" type "master" and try logging in again.

If this allows you to regain access to the SQL Server, follow the steps in this SE answer to change the default DB for your administrators to "master" so that they have a better chance of being able to log in even when user databases are taken offline.


Very Large Log Files I'm running Windows Server 2008 R2 and executed a large query by accident. Next, the sever ran out of hard drive space.

I now have a file in my administrtor documents (log.log) that is over 18GB. I can't seem to delete the file as it just appears again. Any help.

When I delete the log it says I don't have administrator privilages. (I do)

MySQL is a big pain in the neck in Windows because of the way open files behave.
In Linux, whenever I see a log file filling up a disk (i.e., /var/log/mysqld.log), I attempt to truncate the file
echo -n > /var/log/mysqld.log

Linux and mysqld has no problem with it. Windows, on the other hand, does.
I tried to do this in MySQL 5.5.12 on my desktop, that is, zapping the error file and I got this message:
C:\>copy con C:\MySQL_5.5.12\data\LW-REDWARDS2.err
^Z
The process cannot access the file because it is being used by another process.
        0 file(s) copied.

C:\>

You must go to the Task Manager and kill the mysqld process.
Then, with the lock of the file handle released, you could attempt a zap of the log file.
Then, restart mysql with
C:\> net start mysql

You will have to allow time for mysqld to perform InnoDB Crash Recovery
UPDATE 2012-04-25 17:37 EDT
Diskspace is needed to write shutdown information into the mysql error log. During any mysql operation that produces temp tables (which are always MyISAM), diskspace is always needed. What happens if there is no diskspace for temp tables?
According to MySQL 5.0 Certification Study Guide Page 408,409 Section 29.2 bulletpoint 11 says:

If you run out of disk space while adding rows to a MyISAM table, no
error occurs. The server suspends the operation until space becomes
available, and then completes the operation.

Trying a standard shutdown of mysqld with no available diskspace complicates the problem. Just kill the process, delete the 18G log file, start mysql back up again, and live with InnoDB crash recovery. Trust me, it is better than looking for alternatives in a Windows environment which will not work because of the same disk issue.


How SELECT loaded into Memory - If the table is huge I want to understand one basic thing in relation to databases specifically on SQL Server. SQL Server will perform any actions in-memory.
But lets say if we have a 100GB table on the Server with 2GB, if we do select * from table then how will it handle?
My assumption:
it pushes the data to in-memory, once the allocated memory gets full, then it serve that data to the client, when client receiving the data(lets say 100MB received) then the sent data will flush and read 100MB more data from the disk.

Not exactly, but in effect pretty close.
Pages are read as they are needed (imagine the query thread is reading page after page). If a page isn't in cache, then it is read into cache. The cost in cache for that page would then be 1, since it has been accessed 1 time. When there is memory pressure (imagine your thread continues and reaches a point where all memory is now used), then memory pressure will trigger removal of "cheap pages" from cache. A page which has been read only only once (like the pages read in the beginning of your query processing) will have a cost of 1, and are likely to be targets from removal compared to other pages, which might have been read multiple times (having a cost higher than 1).
It is basically like any cache handling mechanism, in principal.


Creating many-to-many relation on demand? Users can join projects.

Projects can have many tasks.

Each user completes the task at their own time, they can also have some other user specific settings on the task like the priority in their todo list and whether to hide it on completion or not.

The problem I'm facing is that some of these settings must have default values e.g. priority = 0, hideOnComplete = true.

These must be available to the client for each project user at all times. How do I make that the TaskUser rows with the default values for the each user exist at all times?

Option A:

I create a TaskUser relation for every user in the project each time a new task is added to the project.
I create a new TaskUser relation for each task in the project every time a new user joins the project.

I don't think this is the way to go since you have to be reacting to these events all the time and acting in response.

Option B:

Duplicate the default fields in the Task table. I include a priority and hideOnComplete field in the Task table. The client can then know what what to display on Task setting forms even if the TaskUser relation doesn't exist.

I no longer have to react to new users or tasks. A client can request the task object and populate the settings form with the default fields even when there is no TaskUser relation. Afterwards, when the users submits his own settings the client can look into the existing TaskUser relation for the correct values.

This seems to be a much better option to me. What do you think?

In your scenario, since (if I'm understanding correctly), every User will need to complete every Task in the Projects they belong to, there will need to be one UserTask per Task per User if you want to store things such as dateCompleted.

Because of this, Option A makes sense to me. You need to be "reacting to these events all the time and acting in response" anyway. One modification I would make though is that I would include a defaultHideOnComplete field in the User table. The TaskUser's hideOnComplete field would be initially set to the defaultHideOnComplete value for the User associated with it.

Option B doesn't make sense to me with what you're trying to accomplish, because it doesn't allow for every User to set their own hideOnComplete and priority values.

Let me know if I'm misunderstanding something : ]


Comparing INT and NVARCHAR T-SQL newbie here. I am sure you guys can help me with a little problem I'm having.

Here's my situation:

I want to do a select from a certain table. And with that select I want to display the count of a referenced entry in another table.

So here's my script:

/****** Skript für SelectTopNRows-Befehl aus SSMS ******/
SELECT [ID]
  ,[ExpenseNumber]
  ,[Mandator]
  -- some more rows here

  -- Memo
  ,(SELECT COUNT(*)
    FROM [Foobar].[dbo].[MemoUnion]
    WHERE ([Mandator] = 096)
    AND ([ReferenceType] = 4001)
    AND ([Reference] COLLATE SQL_Latin1_General_CP1_CI_AS = [ID])) AS [MemoCount]
    FROM [Foobar].[dbo].[Expense]
    WHERE Mandator = 096 AND ExpenseNumber = 1;

So here's my problem, the column [MemoCount] shows 0, although I am 100% sure that there is an actual entry in dbo.MemoUnion where Reference = ID is actually true.

My suspicion is that it's not working because [Reference] is nvarchar and [ID] is an int.

So I fiddled around with CONVERT() and CAST(). But the result stays 0 none the less.

I did not get any error messages while using CONVERT and/or CAST. Just always a result of 0.

Any tips?

Thanks in advance

I solved it myself.
In case someone runs into a similar problem:
[dbo].[MemoCount] also has an ID Column.
so i changed

AND ([Reference] COLLATE SQL_Latin1_General_CP1_CI_AS = [ID]))

to:

AND ([Reference]  = [Foobar].[dbo].[Expense].[ID])) AS [MemoCount]

Apparently SQL took the column [dbo].[MemoUnion].[ID] to compare it with [Reference].
That problem was of course not solvable for you guys because I never posted the Columns of [dbo].[MemoUnion] in the first place.
Thanks for all the suggestions nonetheless!


mysqldump dump file size I have used mysql-administrator to back up my mediawiki and bugzilla database.
Now there is an issue  with charsets I can't fix.
So I deciced to try mysqldump
with -single-transaction --routines --triggers.  My database is a mix of inno and mysiam tables.

I am confused about the different size of the dump files.
While the mysqldump is ~150 MB, the mysqladmin file is ~220MB.
I cannot explain this, and I can't analyze the dump files, it  is too big for my notepad.

I have two questions:

What's the cause of the different sizes?
Anyone any suggestions about my options to backup?

It was because mysqladmin didn't compress the the data.
when you open it, you will see nice query and code. and also easy to study.

if after compress, the query will stick in 1 line. and hard to study.


Generate unique id based on criteria from multiple rows I've got the following scenario, I've got a table like this

| pid | itemId |
|-----|--------|
|123  | item1  |
|123  | item2  |
|234  | item1  |
|234  | item3  |
|456  | item1  |
|456  | item2  |
|567  | item1  |
|567  | item2  |
|567  | item3  |

I need to get a unique integer id based on the unique combination of items. So if two persons have chosen the same items, the unique combination should be the same number.

For example:

| pid | itemId | comb |
|-----|--------|------|
|123  | item1  |  1   |
|123  | item2  |      |
|234  | item1  |  2   |
|234  | item3  |      |
|456  | item1  |  1   |
|456  | item2  |      |
|567  | item1  |  3   |
|567  | item2  |      |
|567  | item3  |      |

The number of possible combinations at the moment are around 300. So when filtering it might be easier to filter by just one number in one column than filtering with multiple criteria.

One way to approach this problem is to concatenate all of the item values together for each PID and to assign a value to them using the DENSE_RANK window function. Unfortunately, SQL Server does not make it easy to do string concatenation at an aggregate level until STRING_AGG() in SQL Server vNext. Since you're on SQL Server 2008 I'm going to use the FOR XML path method of string aggregation. An explanation of that method along with other ways of doing it can be found in Grouped Concatenation in SQL Server.

Data prep using your sample data:

CREATE TABLE #X165719 (
PID INTEGER NOT NULL,
ITEMID VARCHAR(10) NOT NULL
);

INSERT INTO #X165719
VALUES
(123, 'item1'),
(123, 'item2'),
(234, 'item1'),
(234, 'item3'),
(456, 'item1'),
(456, 'item2'),
(567, 'item1'),
(567, 'item2'),
(567, 'item3');

First let's do the string aggregation. One implementation is as follows:

SELECT
  PID
, ITEMID
, (
    SELECT ',' + SUB.ITEMID
    FROM #X165719 SUB
    WHERE SUB.PID = t1.PID
    ORDER BY SUB.ITEMID
    FOR XML PATH (''), TYPE).value('.', 'varchar(max)') all_items
FROM #X165719 t1

You should pick a delimiter that doesn't appear in your source data if possible. I used a comma. Here is what the result set looks like:

╔═════╦════════╦════════════════════╗
║ PID ║ ITEMID ║     all_items      ║
╠═════╬════════╬════════════════════╣
║ 123 ║ item1  ║ ,item1,item2       ║
║ 123 ║ item2  ║ ,item1,item2       ║
║ 234 ║ item1  ║ ,item1,item3       ║
║ 234 ║ item3  ║ ,item1,item3       ║
║ 456 ║ item1  ║ ,item1,item2       ║
║ 456 ║ item2  ║ ,item1,item2       ║
║ 567 ║ item1  ║ ,item1,item2,item3 ║
║ 567 ║ item2  ║ ,item1,item2,item3 ║
║ 567 ║ item3  ║ ,item1,item2,item3 ║
╚═════╩════════╩════════════════════╝

Now we need to assign a different number for each unique value in the all_items column. One way to accomplish this is with the DENSE_RANK function. Quoting from BOL:

  Returns the rank of rows within the partition of a result set, without any gaps in the ranking. The rank of a row is one plus the number of distinct ranks that come before the row in question.

The final query is:

SELECT
  t2.PID
, t2.ITEMID
, DENSE_RANK() OVER (ORDER BY all_items) COMB
FROM
(
    SELECT
      PID
    , ITEMID
    , (
        SELECT ',' + SUB.ITEMID
        FROM #X165719 SUB
        WHERE SUB.PID = t1.PID
        ORDER BY SUB.ITEMID
        FOR XML PATH (''), TYPE).value('.', 'varchar(max)') all_items
    FROM #X165719 t1
) t2;

The final result set is:

╔═════╦════════╦══════╗
║ PID ║ ITEMID ║ COMB ║
╠═════╬════════╬══════╣
║ 123 ║ item1  ║    1 ║
║ 123 ║ item2  ║    1 ║
║ 456 ║ item1  ║    1 ║
║ 456 ║ item2  ║    1 ║
║ 567 ║ item1  ║    2 ║
║ 567 ║ item2  ║    2 ║
║ 567 ║ item3  ║    2 ║
║ 234 ║ item1  ║    3 ║
║ 234 ║ item3  ║    3 ║
╚═════╩════════╩══════╝


MySQL Replication - replication issues; procedures registered on master not giving same result on slave in MySQL 5.5.20 I am not able to replicate procedures registered in the Master to the Slave in MySQL 5.5.20

The Procedure is registered on the Master and can be seen on Slave. However, results fetched using the same tables are different.

Anyone know how can I debug and solve this???

You have to pay attention on you replica configuration, first of all it is a statement-based o row-based replica?

Probably you run an "unsafe" statement in you master server so the slave can't apply same statement in deterministic mode.

Check the master error.log for warnings like:

[Warning] Unsafe statement written to the binary
log using statement format since BINLOG_FORMAT = STATEMENT.

For more information: Determination of Safe and Unsafe Statements in Binary Logging


Get partial match from GIN indexed TSVECTOR column I would like to get results by query this:

SELECT * FROM (
  SELECT id, subject
  FROM mailboxes
  WHERE tsv @@ plainto_tsquery('avail')
) AS t1 ORDER by id DESC;

This works and return rows with tsv containing Available. But if I use avai (dropped lable) it cannot find anything.

Do all queries have to be be in dictionary? Can't we just query such letters? I have a database that contains e-mail body (content) and I would like to make it fast as its grow every second. Currently I am using

... WHERE content ~* 'letters`

Do all queries have to be be in dictionary?

No. Because only word stems (according to the used text search configuration) are in the index to begin with. But more importantly:
No. Because, on top of that Full Text Search is also capable of prefix matching:
This would work:
SELECT id, subject
FROM   mailboxes
WHERE  tsv @@ to_tsquery('simple', 'avail:*')
ORDER  BY id DESC;
Note 3 things:

Use to_tsquery(), not plainto_tsquery(), in this case because (quoting the manual):

... plainto_tsquery will not recognize tsquery operators, weight labels, or prefix-match labels in its input

Use the 'simple' text search configuration to generate the tsquery since you obviously want to take the word 'avail' as is and not apply stemming.

Append :* to make it a prefix search, i.e find all lexemes starting with 'avail'.

Important: This is a prefix search on lexemes (word stems) in the document. A regular expression match without wildcards (content ~* 'avail') is not exactly the same! The latter is not left-anchored (to the start of lexemes) and would also find 'FOOavail' etc.
It's unclear whether you want the behavior outlined in your query or the equivalent of the added regular expression. Trigram indexes (pg_trgm) like @Evan already suggested are the right tool for that. There are many related questions on dba.SE, try a search.
Overview:

Pattern matching with LIKE, SIMILAR TO or regular expressions in PostgreSQL

Demo
SELECT *
FROM (
   VALUES
     ('Zend has no framework')
   , ('Zend Framework')
   ) sub(t), to_tsvector(t) AS tsv
WHERE tsv @@ to_tsquery('zend <-> fram:*');

 id |       t        |          tsv
----+----------------+------------------------
  2 | Zend Framework | 'framework':2 'zend':1

Related answer (see chapter "Different approach to optimize search"):

How can I generate all trailing substrings following a delimeter?

Emails?
Since you mentioned emails, be aware that the text search parser identifies emails and does not split them into separate words / lexemes. Consider:
SELECT ts_debug('english', 'xangr@some.domain.com')

(email,"Email address",xangr@some.domain.com,{simple},simple,{xangr@some.domain.com})

I would replace the separators @ and . in your emails with space (' ') to index contained words.
Also, since you are dealing with names in emails, not with English (or some other language) words, I would use the 'simple' text search configuration to disable stemming and other language features:
Build the ts_vector column with:
SELECT to_tsvector('simple', translate('joe.xangr@some.domain.com', '@.', '  ')) AS tsv;