Using MyISAM for reading and InnoDB for writing data I read many articles about comparisons of MyISAM and InnoDB from performance point of view and I decided to use MyISAM for reading data and InnoDB for writing data.

I want to use two structure like an image below. Two same (synchronized) databases but different table engines.

What is your suggestions about this structure?
What are disadvantages of this design?

I recommend against using this. Consider:

This would only be of some advantage if on two separate servers (no point in mixing both on same MySQL server, naturally, since reads & writes will take place on both)

Which means you would follow RolandoMySQLDBA's advice, and set up an InnoDB master with MyISAM slave.

Do you have foreign keys on your InnoDB tables? If so, you're into integrity issues. If you happen to have ON DELETE CASCADE or ON DELETE SET NULL - prepare for bad news -- the cascading will not propagate on the slave. See: Impact of foreign keys absence on replicating slaves

OK, so maybe it's just this one table, and no foreign keys. But then you get to inherit all of MyISAM's other disadvantages, such as the likely possibility of not being able to recover from crash. Transactions won't work; the slave may find itself trying to execute queries on a table that hasn't really recovered from a crash; contains incorrect data. You may run into replication failures.

Phil's comment is also very valid: by synchronizing the tables you actually mean any write to your InnoDB table must propagate to your MyISAM table. This means same amount of writing.

If you are willing to relax some constraints, such as only batching (somehow?) the writes to the slave every once in a while, then that makes somewhat more sense.

So, in general, while the solution could work, you would have a constant headache to take care of.


postgres listen only runs when I do something Consider the following demo in postgres 9.6.

I did the following in this order:

I ran in PSQL: LISTEN foo;

I ran in DataGrip.  NOTIFY foo,'thad man';

Went back to the PSQL console and don't see my expected:

 Asynchronous notification "foo" with payload "thad man" received from server process with PID 4117.

It is only in PSQL when I run any statement do I get my notification. How might I be able to see notification are made without doing anything? Is this possible? If I have to run a statement like SELECT 1; what  benefit might I get from listening in the application layer? Would I have to be running SELECT 1; in a constant loop every second or something to get notified?

It is only in PSQL when I run any statement do I get my notification

An application doesn't have to poll the database to receive notifications, but it has to poll the status of its socket (network connection) to know that there is a notification to collect, otherwise there's no way to know about it. psql doesn't do that when waiting on user input. It blocks on user input, as most command-line oriented applications.

  Would I have to be running SELECT 1; in a constant loop every second
  or something to get notified?

No, but you need to implement some sort of event loop in your application to detect the notification and handle it. But neither the detection nor the handling need to send any query to the database server.


Do databases make a delete and an insert when having to update rows? So today a professor told us that when the database has to make an update, internally (at low level) it makes a delete and then an insert with the updated fields. He then said that this is something made across all databases and then I started a discussion telling that I thought that it had no sense but I didn't had enough resources to support my position. He seems to know a lot but I can't understand why would dbs do that.

I mean, I know that if you update a field and you need more space for that row, then it may delete the row physically and put it at the end with the new data. But if for example you reduce the space used, why would it delete and re insert it at the end?

Is this even true? What are the benefits?

Is this even true?

No, it is an implementation detail. A database may implement a suitable update in place if it so chooses.

  What are the benefits?

Splitting an update into a delete followed by an insert generally makes the implementation simpler. Potential side benefits include the ability to avoid transient key violations in a unique index, by suitably sorting the split delete/insert operations.

A split update may be a little slower and generate more log than a true in-place update (which is not always possible anyway).

As Kin noted in a comment, if you need an example (for SQL Server) see:

Are all updates split into delete-insert? by Gail Shaw; and
Do changes to index keys really do in-place updates? by Paul Randal

It is also related to implementing MVCC. In Wikipedia page about MVCC, it is mentioned:

  When an MVCC database needs to update an item of data, it will not overwrite the old data with new data, but instead mark the old data as obsolete and add the newer version elsewhere. Thus there are multiple versions stored, but only one is the latest. This allows readers to access the data that was there when they began reading, even if it was modified or deleted part way through by someone else.

See also page 60 of PostgreSQL Internals pdf by Bruce Momjian (Postgres uses MVCC):  "UPDATE is effectively a DELETE and an INSERT."


SQL Server Agent/Properties/Alert System/Mail Session/Test is disabled In order to enable operator notifications I navigated to SQL Server Agent/Properties/Alert System/Mail Session, checked "Enable mail Profile", selected Mail System and Mail Profile. At this time Test button next to the Mail Profile was disabled, so I clicked OK and decided to return to this configuration dialog later.

This worked and notification are being sent but when I returned to this configuration dialog later the Test button is still disabled.

Can anyone explain that?

Test button referred in the question is only available for older version of SQL Servers that supported SQL Mail. Below picture is from a SQL2008 SP4 instance where you can use the test button.

From books online:

  SQL Mail is not installed by default in SQL Server. For more
  information, see Understanding Surface Area Configuration. SQL Mail is
  not supported on 64-bit versions of SQL Server. SQL Mail stored
  procedures cannot be installed on 64-bit versions.

For Database mail you can use this feature for testing.

Using SSMS, Instnace-->Management-->Database Mail-->Right click-->Sent test Email.


Create unique index oracle with where clause I m trying to create a unique index with where clause like this:

CREATE UNIQUE INDEX cmt_unique_sid_yid_ct
          ON COMMENTARY (source_id, year_id)
       WHERE comment_type_id = (
         select comment_type_name
         from comment_type
         where comment_type_name = 'Final'
       );

Getting this error :

02158. 00000 -  "invalid CREATE INDEX option"
*Cause:    An option other than COMPRESS, NOCOMPRESS, PCTFREE, INITRANS,
           MAXTRANS, STORAGE, TABLESPACE, PARALLEL, NOPARALLEL, RECOVERABLE,
           UNRECOVERABLE, LOGGING, NOLOGGING, LOCAL, or GLOBAL was specified.
*Action:   Choose one of the valid CREATE INDEX options.

I m basically trying restrict comments with type Final from comment_type table, to only one row per source/year. So scope to :

comment_type_name | source_id | year_id

How can I do this with SQL, specifically oracle?

No such thing in Oracle, just some workaround with function based indexes:

CREATE UNIQUE INDEX cmt_unique_sid_yid_ct
ON COMMENTARY (
  case when comment_type_name = 'Final' then source_id end,
  case when comment_type_name = 'Final' then year_id end
);

Sample query that can use this index:

select * from commentary
where
case when comment_type_name = 'Final' then source_id end = :B1
and case when comment_type_name = 'Final' then year_id end = :B2
;


How to Create Aggregate function in SQL Server 2008 My requirement is to create concatenate function so I am trying to create Aggregate function as shown

CREATE AGGREGATE [dbo].[Concatenate]
    (@value [NVARCHAR](MAX))
RETURNS[NVARCHAR](MAX)
EXTERNAL NAME [Test].[Test_SQL.Concatenate]
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'Concatenate.vb' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'AGGREGATE',@level1name=N'Concatenate'
GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=N'28' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'AGGREGATE',@level1name=N'Concatenate'
GO

But it showing error as:

Assembly 'Test' was not found in the SQL catalog of database 'Test'.

How to create Assembly.

You must compile your CLR code as a library (.dll) using the .NET build tools or Visual Studio to create the assembly. See the MSDN article on Deploying CLR Database Objects for details. Assuming your source file is Concatenate.vb, you can compile from a command prompt (set your PATH to include the appropriate .NET Framework directory or cd to it):

vbc /target:library /out:Test.dll C:\Concatenate.vb

After compiling the library, you must notify SQL Server of the assembly's existence using CREATE ASSEMBLY. This step will resolve the error you are seeing by adding the assembly to the database catalog. Copy the DLL to the database server and execute T-SQL similar to the following:

CREATE ASSEMBLY Test from 'C:\Test.dll' WITH PERMISSION_SET = SAFE;

After these steps are complete, you can access the functions defined in an assembly using the CREATE AGGREGATE syntax.


How to join tables without identifiers? For school I have to do this:

Query what salary scale an employee belongs to displaying these columns:

s_schaal.schaal, werknemer.name, werknemer.salaris

But there are no identifiers between the tables. All I have is a bottom line and upper line column (s_schaal.ondergens & s_schaal.bovengrens).

What MySQL query can I use to compare and join this data?

I finally figured that joining these tables would not bring a solution. I solved it doing this:

SELECT S.schaal, W.wnr, W.wnaam, W.salaris FROM Werknemer W, S_schaal S
WHERE W.salaris >= S.ondergrens && W.salaris <= S.bovengrens;


SQL Server Database going into suspect mode after bulk insert and delete I'm working on an application that does a bulk copy of data from one table to another within the same database (using an Insert... Select) and then deleting the data from the source table. In production, sometimes the database goes into suspect mode and I have noticed that it always happens after this process is run. This process does this to multiple tables (around 20 tables). First it copies the relevant data from each table to its corresponding archive table. Then it deletes the relevant data from the source tables.

I checked on common reasons for this and there is enough free space in the disk and no antivirus software installed

Edit: Auto Close is disabled for the database

I'm quite sure that the transaction log might be broken. Did you check the serverlogs? Another idea could be that to set the emergency mode in your database and run a dbcc checkdb and take a look at the results.

I can't find a good reason for this behaviour. Maybe a bit code would help. I don't have any of this issues, even as I'm using such operations quite often.

Maybe another idea is this way:

Call procedure
Copy tables
Run Checkpoint
Clean tables on source


Table was not locked with LOCK TABLES when recreating an index I have a weird problem when recreating the index. This is what happens. This is done through JDBC, in Java btw.

A table 'stuff' is truncated by a process. The application is reinitalizing a cycle (for some processes) by recreating data in this table. Data is around 700 000 records and changing for each cycle. So happens:

First:

lock tables stuff write, inventory read;

Next step is:

insert into stuff (x,y,z) select x,y,z from inventory;

And here is a trick. We experienced that when this table is recreated this way, we need to recreate an index, because if we don't do this, the processes querying by this field are locked while the index is rebuilt. So this is what is done:

alter table stuff drop index idx_UpdatedTime;
alter table stuff add index idx_UpdatedTime(UpdatedTime);

The interesting thing is what happens: either the first alter or the second fails.

1 - The first one fails, if the index doesn't exist (this isn't a problem, this is expected behaviour). The exception says:  "Can't DROP 'idx_UpdatedTime'; check that column/key exists". This is fine. In this case, the index is created by the second alter command. This will lead to point 2- in the next cycle.

2 - If the second one fails, the error says: "Table 'stuff' was not locked with LOCK TABLES". This is only happening when the first alter succeeds. This means that the index gets dropped, but isn't recreated. This will lead to point 1 - in the next cycle.

So, this means that these two behaviors are changing each other. 1,2,1,2,1,2... forever. I don't understand why is this happening, what am I missing.

Try creating a temp table, and then perform a bait-and-switch

ALTER TABLE stuff RENAME oldstuff
CREATE TABLE newstuff LIKE oldstuff;
ALTER TABLE newstuff DROP INDEX idx_UpdatedTime;
INSERT INTO newstuff SELECT * FROM oldstuff;
INSERT INTO newstuff (x,y,z) SELECT x,y,z FROM inventory;
ALTER TABLE newstuff ADD INDEX idx_UpdatedTime(UpdatedTime);
ALTER TABLE newstuff RENAME stuff;
DROP TABLE oldstuff;

This approach may take longer but should establish some continuity for your processing

I renamed stuff to oldstuff to remove any possibility access to the stuff table during this process


user permission cache in session or check from database? On each page of my websites 2 or 3 permissions have to be checked and I use role-permission model. Which one is better? to check permissions of each user always through database directly or once calculate it and store in session of user(cache permissions in sessions)?

which one is more common?

For maximum performance, caching (i.e., precomputation) is pretty much a requirement. Unfortunately, though, there's no cookie-cutter answer for how to implement it. It's not as simple as picking either solution x or solution y.

What you want to do is come up with a strategy that caches the information you need as close to the request for the information as possible, while still meeting the business requirements and staying within the architectural limitations (which, by the way, you may want to change to encourage the use of caching, not only for this, but for other areas, too).

Here are a few factors and some questions related to each to get you started thinking about how best to implement this for your own scenario:

The application architecture. Are you using a caching tier? Are user sessions sticky on the web tier, or is it round-robin? (Should you change this?) Are there any other applications accessing the database for the same security information besides this particular web application?
The requirements for the application. Should changes to the security permissions take effect immediately (i.e., on next page load), or is a delay acceptable? How long a delay? Immediate effects generally limit our ability to use caching to its maximum effectiveness, but that's definitely not to say it's infeasible to cache at all.
How expensive it is to compute the required information? Will it be computed on-demand, or proactively when the underlying data changes? Synchronously, or asynchronously? Caching can be introduced within the database itself, which alone may be enough to satisfy the immediate performance requirements.
How frequently will the information be needed? You say it's needed for every page on the site, but what does that mean in terms of requests/sec and overall server load? Could the mechanism this information is retrieved by be optimized further?
How frequently will the information be changing? Since you're asking about caching at all, I assume it doesn't change frequently, but that could be a bad assumption on my part, so I'm mentioning it. If the information changes frequently, pay particular attention to the performance of the cache invalidation strategy you put in place.

As I eluded to, there are multiple opportunities and configurations for caching in this scenario, using any combination of: the data tier, the web (application) tier, and the caching tier (if applicable). Of course, the more levels of caching you do, the more complex the environment and codebase becomes, so you definitely want to watch out for that.


How do you move a database from one server to the other using SQL Server Management Studio? I need to move a database, but I want to make sure I do it safely and with all triggers, stored procedures and functions.

I was think to simply detach the database from my server using SQL Server Management Studio and move the .mdf and .ldf files to the new directory and attach it to the new server.

Can someone guide me please?

If you use "Detach/Attach" function you'll be able to transfert the database with all triggers and objects that are inside the DB. You must know that only the database will be tranfert. If you need to have all the logins for the old server, you can use this link from Microsoft. After you run the procedure, you can have some orphaned users. If you read carefully the Miscrosoft KB, you will find a link to solve this issue.

If you tranfer from 2005 to 2008 you must use this link to tranfer logins.

Hope this help you.


How to select the first row of each group? I have a table like this:

 ID |  Val   |  Kind
----------------------
 1  |  1337  |   2
 2  |  1337  |   1
 3  |   3    |   4
 4  |   3    |   4

I want to make a SELECT that will return just the first row for each Val, ordering by Kind.

Sample output:

 ID |  Val   |  Kind
----------------------
 2  |  1337  |   1
 3  |   3    |   4

How can I build this query?

Use a common table expression (CTE) and a windowing/ranking/partitioning function like ROW_NUMBER.

This query will create an in-memory table called ORDERED and add an additional column of rn which is a sequence of numbers from 1 to N. The PARTITION BY indicates it should restart at 1 every time the value of Val changes and we want to order rows by the smallest value of Kind.

WITH ORDERED AS
(
SELECT
    ID
,   Val
,   kind
,   ROW_NUMBER() OVER (PARTITION BY Val ORDER BY Kind ASC) AS rn
FROM
    mytable
)
SELECT
    ID
,   Val
,   Kind
FROM
    ORDERED
WHERE
    rn = 1;

The above approach should work with any RDBMS that has implemented the ROW_NUMBER() function. Oracle has some elegant functionality as expressed in mik's answer that will generally yield better performance than this answer.


Designing a join table for max n records I know that a basic rule for designing a relational dataset is to not do things like this:

create table Order (
    OrderID int indentity(1, 1) not null, -- PK
    CustomerID int identity(1, 1) not null, -- FK to Customer table
    ProductID1 int not null, -- FKs to Product table
    ProductID2 int not null,
    ProductID3 int not null,
    ...
    ProductID10 int not null,
    primary key (OrderID)
)

Because it results in problems like:

What if the customer orders more than 10 products in a single order? This can't support that.
What if the vast majority of customers only ever order 1 or 2 products? That's a lot of surplus fields.

And the correct answer of course is to normalize:

create table Order (
    OrderID int identity(1, 1) not null, -- PK
    CustomerID int not null -- FK to Customer,
    primary key (OrderID)
)

create table OrderProduct (
    OrderID int not null, -- FK to Order table
    ProductID int not null, -- FK to Product table
    Quantity int not null,
    primary key (OrderID, ProductID)
)

However, what about in cases where there is a known maximum or range of the number of Products that can be associated with a single Order?

For example, where there is a strict limit of 10 Products in an Order, and/or a minimum of 2 Products in an Order?

This normalized structure does not set limits on how many records per Order can be created in OrderProduct - an Order could be created with 0 associated OrderProducts, or 200.

Is there a standard approach to handle this kind of requirement in the database design, or is it something that can only be handled in the program code whilst inserting records?

In that case the application should manage the amount of products on each order.

If you want to enforce a limit on database level you can add a column ProductOrderPosition in OrderProduct that's the ordering position of each product by each order, and you can add a CHECK constraint to limit this value, for example:

ALTER TABLE OrderProduct ADD CONSTRAINT CHK_OrderProduct_ProductOrderPosition
    CHECK (ProductOrderPosition <= 10)

If you don't want additional columns you could validate with an after insert trigger, checking that each order doesn't have more than X products.

CREATE TRIGGER dbo.utrOrderProductCheckAmount ON OrderProduct AFTER INSERT
AS
BEGIN

    DECLARE @v_ProductsByOrderLimit INT = 10

    IF EXISTS (
        SELECT
            'more than allowed!'
        FROM
            OrderProduct AS O
            INNER JOIN (SELECT DISTINCT I.OrderID FROM inserted AS I) ON O.OrderID = I.OrderID
        GROUP BY
            O.OrderID
        HAVING
            COUNT(1) > @v_ProductsByOrderLimit
        )
    BEGIN

        ROLLBACK

        RAISERROR('Maximum products by order limit reached.', 11, 1)

    END

END

You definitely don't want to store pivoted Products IDs on the Order table for the reasons you mentioned before. I'll add another reason, what happens if you want to know in how many orders does a product appear? You need 10 poorly indexed joins, instead of 1 optimized SELECT against OrderProduct.


What is the purpose of Database Tuning? This sounds like a stupid question but when posed with it on an exam yesterday, I wasn't entirely sure how to answer...I mean I know you have to put indexes on certain fields to speed up slow queries, and I know you have to make sure to load test it to

I know it has the end goal of making the database faster for read / write access from it's users but the test had alot of ways of putting this:

Giving the same access time to all database users.
Ensuring that all resources are used effectively.

Do either of those answers really embody the purpose of database tuning?

This is obviously opinion based:

What is the purpose of Database Tuning?

My answer would be:

  To minimize (within reason) the expense involved of maintaining a set of
  data that has been deemed important.

Database tuning is an expense in itself.  It costs money to tune.  That's where the 'reasonable' part of my statement comes from.

Does it make sense to spend 40 hours to tune something that runs once?  Maybe.  Maybe not.

Does it make sense to spend 40 hours to tune something that runs every second million of times a day?  Probably.

Why do we tune?

We want our queries to run fast because the people waiting for the
results are an expense.  Tuning 'may' allow us to reduce memory, cpu and disk space requirements, saving money.

Tuning is a balancing act - you have to compare the expense involved with 'tuning' against the expense involved in 'not tuning'.

I think searching the Internet for 'What is the purpose of Database Tuning?', you'd find statements similar to this Wiki article.

  Database tuning aims to maximize use of system resources to perform
  work as efficiently and rapidly as possible.

I think, if you took any tuning exercise and kept asking the question

"So, why am I doing this??? Because of this. And why is that important??...", I think you'll ultimately come down to 'money'.

And that's why we tune (within reason).  Hey - my 2 cents...


What is the rationale of dropping all indexes for an altered view in SQL Server? This is a follow up of ALTER VIEW drops Index from View question.

The accepted response indicates that ALTER VIEW will automatically drop any defined indexes.

This is somewhat counter intuitive to how other similar operations are happening:

it is natural to expect for related things such as permissions or indexes to be dropped on object drop not on alter
altering a table does not drop any index (actually you have to drop things like indexes, constraints etc.)

I am wondering about why having this behavior by default in the first place (i.e. is there any objective aspect like performance reduction in some cases due to view alteration).

Question: What is the rationale of dropping all indexes for an altered view?

Kendra Little lays out some good theories for why this happens in her recent blog post: Altering an indexed view in SQL Server drops all indexes

  This behavior makes some sense when I think about how indexed views are implemented

  When you create an index on a view (we’re talking views using classic disk-based tables here), it materializes the view — in other words, it stores the data as if it is a table. That data is stored in rows on 8KB pages. These pages can fill up. So if you add a column to a view, just like adding a column to a table, the database engine needs to rewrite those pages to add the new column to each and every row (depending on datatypes and nullability, etc etc).

  Also, it’s quite possible that when altering an indexed view, the alteration makes the view no longer adhere to the many rules required for indexed views.

Altering the view could completely change the structure of the data - number of columns, number of rows, the size of the rows, etc.  So basically the table would need to be dropped and recreated anyway.

I find the second point, about how likely a change is to run afoul of the indexed view restrictions, to be particularly compelling.

Like Kendra mentions later, and others have mentioned in the comments here, it would be nice if one were simply blocked from altering indexed views.  But that change will likely never happen due to backwards compatibility concerns.


How do I parse addresses in PostgreSQL? Let's say for instance that I want to parse these addresses for the Chicken Ranch

Chicken Ranch
10511 Homestead Rd
Pahrump, NV 89061

Chicken Ranch
1600 Pennsylvania Avenue
NW Washington, D.C. 20500

In both of these cases, I'd like to get rid of Rd and Avenue. For instance in the first case, I'd like to get "Homestead", and in the second "Pennsylvania". Not every address has a designation like this though.

This is a question address canonicalization and parsing. Essentially what you're talking about is handled through a gazetteer (geographical rule set). There are two ways to do this right,

address_standardizer from the PostGIS project and certainly better if you're only using United States addresses.
pgsql-postal may be a better method for international addresses.

I'll show the address standardizer version for the address,

Chicken Ranch
10511 Homestead Rd
Pahrump, NV 89061

Using standardize_address from address_standardizer, returns a composite type of stdaddr. First we install it,

CREATE EXTENSION address_standardizer;
CREATE EXTENSION address_standardizer_data_us;

And, then we can use it like this.

SELECT * FROM standardize_address('us_lex',
   'us_gaz', 'us_rules', '10511 Homestead Rd, Pahrump, NV 89061');
 building | house_num | predir | qual | pretype |   name    | suftype | sufdir | ruralroute | extra |  city   | state  | country | postcode | box | unit
----------+-----------+--------+------+---------+-----------+---------+--------+------------+-------+---------+--------+---------+----------+-----+------
          | 10511     |        |      |         | HOMESTEAD | ROAD    |        |            |       | PAHRUMP | NEVADA | USA     | 89061    |     |
(1 row)

So you can see, ROAD is pulled out in suftype

Likewise,...

SELECT * FROM standardize_address('us_lex',
   'us_gaz', 'us_rules', '1600 Pennsylvania Avenue, NW Washington, D.C. 20500');
 building | house_num | predir | qual | pretype |     name     | suftype |  sufdir   | ruralroute | extra | city | state | country | postcode  | box |     unit
----------+-----------+--------+------+---------+--------------+---------+-----------+------------+-------+------+-------+---------+-----------+-----+--------------
          | 1600      |        |      |         | PENNSYLVANIA | AVENUE  | NORTHWEST |            |       |      |       | USA     | D C 20500 |     | # WASHINGTON


Restore Database with Move Failure I am attempting to automate restoring .bak files on a secondary database in the case of a primary server failure.  Both servers are SQL server 2014.

Here is a sample SQL script:

USE master
GO

DECLARE @DBName varchar(50)
DECLARE @BackupFile varchar(256)

Set @DBName = 'test-db'
Set @BackupFile = 'D:\MSSQL\Backups\test-db\test-db_backup_2016_03_18_001622_2611392.bak'

-- Find Logical and Physical file names
declare @LogicalDataFile      sysname
        , @LogicalLogFile       sysname
        , @PhysicalDataFile     nvarchar(260)
        , @PhysicalLogFile      nvarchar(260)

-- Data file
select  @LogicalDataFile = name
        , @PhysicalDataFile = physical_name
from    sys.master_files
where   database_id = db_id(@DBName)
        and type_desc = 'ROWS'

-- Log file
select  @LogicalLogFile = name
        , @PhysicalLogFile = physical_name
from    sys.master_files
where   database_id = db_id(@DBName)
        and type_desc = 'LOG'

-- Print variables
Print 'Logical Data File: ' + @LogicalDataFile
Print 'Logical Log File: ' + @LogicalLogFile
Print 'Physical Data File: ' + @PhysicalDataFile
Print 'Physical Log File: ' + @PhysicalLogFile

-- Set as single user mode
EXEC('ALTER DATABASE [' + @DBName
+ '] SET SINGLE_USER
WITH ROLLBACK IMMEDIATE')

--DB that will be over written
RESTORE DATABASE [@DBName]
--Restore file with data
FROM DISK = @BackupFile
WITH MOVE @LogicalDataFile TO @PhysicalDataFile,
MOVE @LogicalLogFile TO @PhysicalLogFile,
REPLACE,
STATS=10

-- Return to multi-user mode
EXEC('ALTER DATABASE [' + @DBName
+ '] SET MULTI_USER')

When I run the above script I get the following output:

Logical Data File: test-db
Logical Log File: test-db_log
Physical Data File: D:\MSSQL\Data\test-db.mdf
Physical Log File: D:\MSSQL\Data\test-db_log.ldf
Msg 1834, Level 16, State 1, Line 50
The file 'D:\MSSQL\Data\test-db.mdf' cannot be overwritten.  It is being used by database 'test-db'.
Msg 3156, Level 16, State 4, Line 50
File 'test-db' cannot be restored to 'D:\MSSQL\Data\test-db.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 50
The file 'D:\MSSQL\Data\test-db_log.ldf' cannot be overwritten.  It is being used by database 'test-db'.
Msg 3156, Level 16, State 4, Line 50
File 'test-db_log' cannot be restored to 'D:\MSSQL\Data\test-db_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 50
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 50
RESTORE DATABASE is terminating abnormally.

I wanted to make sure that the MOVE statements weren't being ignored for some reason, so I changed the variable names for the logical and physical file names - as expected, I got an error stating that the variables must be declared.

Is there something else that needs to be done to allow the database to be overwritten?  There are no other connections to this database.

You're trying to restore a database named [@DBName] (that's the literal name since it is surrounded by square brackets) onto files for the database named [test-db].  Remove the [ and ] from the [@DBName] piece of your code and you'll see you cannot use variable names in a RESTORE DATABASE command.

This needs to be performed with dynamic SQL.  The list of files to move should be generated by looking at RESTORE FILELISTONLY FROM DISK = 'C:\some_db_backup.bak';

Here is a very basic example of how to generate a RESTORE DATABASE command using dynamic SQL, for SQL Server 2005 to SQL Server 2012 (i.e. this is not tested on 2014, and may or may not work without modification there):

SET NOCOUNT ON;
DECLARE @FileListCmd NVARCHAR(MAX);
DECLARE @RestoreCmd NVARCHAR(MAX);
DECLARE @cmd NVARCHAR(2000);
DECLARE @BackupFile NVARCHAR(MAX);
DECLARE @DBName SYSNAME;

DECLARE @Version NVARCHAR(255);
DECLARE @VersionINT DECIMAL(10,2);

SET @BackupFile = 'C:\temp\backup.bak';
SET @DBName = 'SomeNewDatabase';

SET @cmd = '';
SET @Version = CONVERT(NVARCHAR(255),SERVERPROPERTY('ProductVersion'));
SET @VersionINT = CONVERT(DECIMAL(10,2),
    SUBSTRING(@Version,1 ,CHARINDEX('.',@Version, 4)-1));
IF @VersionINT >= 10.5
BEGIN
    -- WITH TDE OPTION
    DECLARE @FileList TABLE (
        LogicalName SYSNAME
        , PhysicalName VARCHAR(255)
        , Type CHAR(1)
        , FileGroupName SYSNAME NULL
        , Size NUMERIC(20,0)
        , MaxSize NUMERIC(20,0)
        , FileId BIGINT
        , CreateLSN NUMERIC(25,0)
        , DropLSN NUMERIC(25,0) NULL
        , UniqueId UNIQUEIDENTIFIER
        , ReadOnlyLSN NUMERIC(25,0) NULL
        , ReadWriteLSN NUMERIC(25,0) NULL
        , BackupSizeInBytes BIGINT
        , SourceBlockSize INT
        , FileGroupId INT
        , LogGroupGUID UNIQUEIDENTIFIER NULL
        , DifferentialBaseLSN NUMERIC(25,0) NULL
        , DifferentialBaseGUID UNIQUEIDENTIFIER
        , IsReadOnly BIT
        , IsPresent BIT
        , TDEThumbprint VARBINARY(32)
    );
END
ELSE
BEGIN
    --NO TDE OPTION
    DECLARE @FileListNoTDE TABLE (
        LogicalName SYSNAME
        , PhysicalName VARCHAR(255)
        , Type CHAR(1)
        , FileGroupName SYSNAME NULL
        , Size NUMERIC(20,0)
        , MaxSize NUMERIC(20,0)
        , FileId BIGINT
        , CreateLSN NUMERIC(25,0)
        , DropLSN NUMERIC(25,0) NULL
        , UniqueId UNIQUEIDENTIFIER
        , ReadOnlyLSN NUMERIC(25,0) NULL
        , ReadWriteLSN NUMERIC(25,0) NULL
        , BackupSizeInBytes BIGINT
        , SourceBlockSize INT
        , FileGroupId INT
        , LogGroupGUID UNIQUEIDENTIFIER NULL
        , DifferentialBaseLSN NUMERIC(25,0) NULL
        , DifferentialBaseGUID UNIQUEIDENTIFIER
        , IsReadOnly BIT
        , IsPresent BIT
    );
END

SET @FileListCmd = 'RESTORE FILELISTONLY FROM DISK = ''' + @BackupFile + ''';';

IF @VersionINT >= 10.5
    INSERT INTO @FileList
    EXEC (@FileListCmd);
ELSE
BEGIN
    INSERT INTO @FileListNoTDE
    EXEC (@FileListCmd);
    INSERT INTO @FileList
    SELECT *, NULL
    FROM @FileListNoTDE;
END

DECLARE @MoveFiles NVARCHAR(MAX);
SELECT @MoveFiles = (SELECT ', MOVE ''' + fl.LogicalName + ''' TO '''
    + 'C:\Database\Data\' + @DBName + '\'
    + CASE WHEN fl.FileGroupName = 'PRIMARY' THEN 'system'
      WHEN fl.FileGroupName IS NULL THEN 'Log'
      ELSE fl.FileGroupName END + '\' + fl.LogicalName + '''
    '
FROM @FileList fl
FOR XML PATH(''));

SET @MoveFiles = REPLACE(@MoveFiles, '&#x0D;', '');
SET @MoveFiles = REPLACE(@MoveFiles, CHAR(10), CHAR(13) + CHAR(10));
SET @MoveFiles = LEFT(@MoveFiles, LEN(@MoveFiles) - 2);

SET @RestoreCmd = 'RESTORE DATABASE ' + @dbName + '
FROM DISK = ''' + @BackupFile + '''
WITH REPLACE
    , RECOVERY
    ' + @MoveFiles + ';
GO';

PRINT @RestoreCmd;


How to name a new table storing location at which transaction occurred My client wants to begin collecting data about where transactions occurred.  We have a list of their events and locations where payments might be received, I'm just stumped on what to call the table in my database.
transaction context?

I'd recommend keeping a few things in mind.  You will want to be consistent no matter what you decide.  For example, will you shorten 'transaction' to 'tx' so all of your stored procedures and scripts are easier to use?  How often will you be changing it?  Here are some good best cases from stackoverflow:

Singular names for tables

Singular names for columns

Schema name for tables prefix (E.g.: SchemeName.TableName)

Pascal casing, camel casing, your own casing, but uniform and standard.

Just in case I'll point out that this is an auditing record of transaction data.  I'd keep ACID compliance on the top of my mind if this data is very sensitive and requires to be kept/accurate such as for auditing or billing.

If so, please consider how you'll get the data in there with full ACID compliance.  You can update your stored procedures, add triggers/cdc (though those have their own limitations), or keep it within the same transaction for example.


Tracking SQL queries and rows operated on for documentation/audit Is there a way to record the SQL queries (and rows that were acted upon) in a session for documentation/audit purposes?

For example, in deleting duplicate rows from a table I use the following SQL which gives me a sequence of SQL that I can refer back to later in case of issues with the deletion.

select 'delete from table1 where rowid=''' || rowid || ''' and column1='''
|| column1 || ''' and column2=''' || column2 || '''' from table1 a where
rowid > (select min(rowid) from table1 b where a.column1 = b.column1 and
a.column2 = a.column2);

However this takes longer to build then just issuing the queries outright. I work across a lot of different tables which I don't own so adding triggers is unfortunately not an option.

Oracle provides in-built auditing tools - consider using them instead of hand-rolling out these things.

Oracle Base provides some basic info as to how to get started on Auditing:

  Auditing can enabled by setting the AUDIT_TRAIL static parameter, which has the following allowed values.

AUDIT_TRAIL = { none | os | db | db,extended | xml | xml,extended }

The following list provides a description of each setting:

none or false - Auditing is disabled.
db or true - Auditing is enabled, with all audit records stored in the database audit trial (SYS.AUD$).
db,extended - As db, but the SQL_BIND and SQL_TEXT columns are also populated.
xml- Auditing is enabled, with all audit records stored as XML format OS files.
xml,extended - As xml, but the SQL_BIND and SQL_TEXT columns are also populated.
os- Auditing is enabled, with all audit records directed to the operating system's audit trail.

To enable auditing to database audit trail enable auditing to db

SQL> ALTER SYSTEM SET audit_trail=db,extended SCOPE=SPFILE;

System altered.

Shutdown & restart the db

SQL> SHUTDOWN
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP
ORACLE instance started.

Now to audit SELECTS, INSERTS, UPDATES, DELETES by user cube do this:

CONNECT sys/password AS SYSDBA

AUDIT ALL BY cube BY ACCESS;
AUDIT SELECT TABLE, UPDATE TABLE, INSERT TABLE, DELETE TABLE BY cube BY ACCESS;

The audited logs can be brought up by querying DBA_AUDIT_TRAIL

Further reading:

Auditing
Fine grained auditing
Configuring and administering auditing


Postresql Client and Version Mismatch Given:
$which psql
/Library/PostgreSQL/12/bin/psql
$which createdb
/Library/PostgreSQL/12/bin/createdb

Recently I created a database:
$createdb -U postgres postgres
Password:

However, I don't understand why the server version varies from the client.
$psql -U postgres
Password for user postgres:
psql (12.1, server 9.6.2)
Type "help" for help.

How can I create the server on 12.1 as well?

createdb doesn't create a new database instance (aka 'cluster', unfortunately, as that term has grown other uses).  It just creates a new database in an already running instance, same as the CREATE DATABASE SQL command does.
To create a new instance use initdb or pg_ctl initdb.
You clearly already have a 9.6 instance created and running.  You should probably figure out why and what is in it, and either shut it down, shut it down and delete it, or upgrade it.  You could leave it running and pick a new port of your new instance, but that is likely to create even more confusion of the same sort you already have.


Oracle MV requires object type to be defined as FINAL? I want to create an Oracle 18c materialized view (MV) on a table that has a user-defined datatype called ST_GEOMETRY:

The MV would do a COMPLETE refresh on a schedule — on a local table.

Some info about ST_GEOMETRY:

The ST_Geometry data type implements the SQL 3 specification of
user-defined data types (UDTs), allowing you to create columns capable
of storing spatial data such as the location of a landmark, a street,
or a parcel of land. It provides International Organization for
Standards (ISO) and Open Geospatial Consortium, Inc. (OGC) compliant
structured query language (SQL) access to the geodatabase and
database. This storage extends the capabilities of the database by
providing storage for objects (points, lines, and polygons) that
represent geographic features. It was designed to make efficient use
of database resources; to be compatible with database features such as
replication and partitioning; and to provide rapid access to spatial
data.
ST_Geometry itself is an abstract, noninstantiated superclass.
However, its subclasses can be instantiated. An instantiated data type
is one that can be defined as a table column and have values of its
type inserted into it.

The SQL definition of the user-defined datatype can be found here: db<>fiddle.
CREATE OR REPLACE TYPE SDE."ST_GEOMETRY" (
...
) NOT final

(Although, I'm guessing most of the logic is stored in the EXTPROC.)

MV object types must be FINAL:
There is a known issue when trying to create an MV on the ST_GEOMETRY user-defined datatype (ORA-30373):

Creating an Oracle materialized view for a table containing an
ST_Geometry attribute returns the following error:
"ORA-30373: object data types are not supported in this context".

Code:
SQL> CREATE MATERIALIZED VIEW parcel_view
  2  AS SELECT * FROM parcel@remote_server;

CREATE MATERIALIZED VIEW parcel_view
*
ERROR at line 1:
ORA-30373: object data types are not supported in this context

Cause:
Oracle's Advanced Replication functionality requires that all
object types be defined as FINAL to participate within a materialized
view.

The reason that the ST_Geometry cannot be defined as FINAL is because
the type contains subtypes used for type inheritance. Oracle does not
allow a type with subtypes to be defined as FINAL.
Esri is currently working with Oracle to address this issue and
limitation. The following Oracle TAR file is available for reference:
"6482996.992 - ORA-30373 MATERIALIZE VIEW UNABLE TO REPLICATE A TYPE
WHICH CONTAINS SUBTYPES. Enhancement Request (ER) 6370112".

Note: I've contacted ESRI support and they say there hasn't been any progress with Oracle regarding the enhancement request.

Question:
Is there a way to workaround this issue?

I ask because ESRI has a reputation of not being great with Oracle. So there is a
chance that they have misunderstood the issue or haven't gotten creative enough.

For example, what about the ONLY keyword?
9.9 Materialized View Support for Objects

For both object-relational and object materialized views that are
based on an object table, if the type of the master object table
is not FINAL, the FROM clause in the materialized view definition
query must include the ONLY keyword.
For example:
CREATE MATERIALIZED
VIEW customer OF cust_objtyp AS
SELECT CustNo FROM ONLY HR.Customer_objtab@dbs1;

Otherwise, the FROM clause must omit the ONLY keyword.

Notes:

Unfortunately, that blurb is about object tables, not about object columns (I believe the ST_GEOMETRY user-defined datatype would be considered
an "object column").

But I wonder if the concept of the ONLY keyword could be used to help with
the FINAL problem with the ST_GEOMETRY object column. Any ideas?


Validation of availability for a new order I have a management application of sales, stock and payment on a warehouse whole saler from a web interface. In particular, when a order is effectuated it must create a line corresponding to each product ordered with the respective quantity. The validation of the stock availability is done at the moment of the order.

Considering the following two ways of validation on order:

Use a trigger BEFORE INSERT on the table OrderLine, that does a SELECT on Product verifying there is enough stock.
Do a SELECT ... FROM OrderLine JOIN Product WHERE quant < stock.

My question is: Which of these two alternatives is preferable, and why / for what scenario?

I only see you doing a SELECT in both variants. If you want to make sure you don't sell more than you have in store (stock), you must decrease your stock in the same transaction you place the order. Use a data-modifying CTE for the job (Postgres 9.1 or later):
WITH u AS (
   UPDATE product SET quant = quant - <put_order_quant_here>
   WHERE  product_id = <order_prod_id>
   AND    quant >= <put_order_quant_here>
   RETURNING product_id, <put_order_quant_here> AS quant
   )
INSERT INTO order_detail (order_id, product_id, quant)
SELECT <put_order_id_here>, product_id, quant
FROM   u;

The UPDATE in the CTE only returns values if the product has sufficient stock (and it takes a write lock on the row). In this case, the quantity is reduced in the same transaction, just before the order is placed.
Put all order-details into one transaction, if any of them fails to INSERT then ROLLBACK.
Possible deadlocks
This scenario can easily lead to deadlocks. Say, you have two orders coming in at the same time, both want product A and B. The first order starts with A, the second starts with B. Then the two transactions block each other out. Each waits for the other to complete. A deadlock ensues.
In PostgreSQL a transaction will wait for some time when it is stalled by locks. Depending on your setting of deadlock_timeout (default is 1 sesond), checks for possible deadlocks are performed.
Once detected, one transaction is aborted and a deadlock exception reported. The other one can finish. Which one is hard to predict.
There is a simple way to avoid this kind of deadlocks: Always place your order_details in a consistent order. Like products ordered by product_id. This way, the above scenario can never happen.


How to optimize this crazy SQL Query (Specific) In short, I have this SQLite3 query that uses up 30% of CPU when being run.
SELECT s.*
    ,r.RaagEnglish
    ,(
        SELECT w.WriterEnglish
        FROM Writer w
        WHERE w.WriterID = (
                SELECT MAX(s1.WriterID)
                FROM Shabad s1
                WHERE s1.ShabadID = s.ShabadID
                )
        ) AS WriterEnglish
FROM Shabad s
    ,raag r
WHERE r.RaagID = s.RaagID
    AND s.ShabadID = $SHABADNO
ORDER BY ShabadID ASC

I have 3 tables in my Database: Writer, Raag, and Shabad.
A row in Shabad has a WriterID and a RaagID column.
This SQL Query has to get all the Rows in Shabad that match with the Column ShabadID, plus they have to match the WriterID column with WriterEnglish column from the Writer Table. And match RaagID column to RaagEnglish column from the Raag table.
This is a big query, and I am a SQL n00b, so I would appreciate if someone could help me optimize this query.
In the query above, Shabad is the Article, Raag is the Article Format. Max chooses the maximum value from all of the rows of a specific column.
Here is my DB (no private info, don't worry):
Raag

Writer

Shabad:

What do you want to achieve?  How are the two tables linked?

Perhaps a LEFT JOIN + USING(RaagID) or something like that is better?

Also, it might help to know what Shabad and Raag mean in English.

Update from comments:
shabad is the main table and has references to the other two, so I believe the answer is

SELECT *
FROM shabad s
LEFT JOIN raag r using (raagid)
LEFT JOIN writer w using (writerid)

then filter that with where-clause and your criteria


Package performs differently on two different instances on the same server I'm investigating some performance issues for our BI team.  They have a package which runs extremely slow and will sometimes run indefinitely on one instance but finishes normally on another instance.  Both instances reside on the same server and both are using the same package.  I've tried collecting PerfMon counters which show Page Life Expectency well bellow 300 and eventually reaching 0.

I'm also seeing ASYNC_NETWORK_IO wait types when I use Activity Monitor to see what is occurring.  The ASYNC_NETWORK_IO occurs when it tries to execute a stored proc which I've scripted out and am able to run to completion in under 30 seconds.

I'm not quite sure where I should look for what is causing the Page Life Expectency to be so low or if I should be tracking down the ASYNC_NETWORK_IO waits.

These instances are running SQL Server 2008 R2 on a W2K8 R2 VM with 32 GB of ram.

What is the ideal troubleshooting path for two instances on the same server performing differently?

It turns out this issue was due to a restore of a database from an incorrect media set.  Since the databases were not exact copies, the package was performing differently on the second instance.  After performing a backup with INIT and then restoring that database to the second instance, the package now performs the same on both instances.


MySQL: COLLATE performance I have a table called users with a column firstname and with collation utf8_bin

I want to know what happens under the hood when I execute an query like:

SELECT * FROM `users` `a` WHERE `a`.`firstname` = 'sander' COLLATE utf8_general_ci

The column firstname isn't an index, what happens with the performance when the command executed?

And what if the default collation was utf8_general_ci and the query is executed without COLLATE?

I want to know the impact it has on a big table.

In this case, since the forced collation is defined over the same character set as the column's encoding, there won't be any performance impact (versus defining that collation as the column's default; whereas utf8_general_ci will almost certainly perform slower in comparisons than utf8_bin due the extra lookups/computation required).

However, if one forced a collation that is defined over a different character set, MySQL would have to transcode the column's values (which would have a performance impact); I think it does this automatically if the forced collation is over the Unicode character set, or raises an "illegal mix of collations" error in any other circumstance.

Note that the collation recorded against a column's definition is merely a hint to MySQL over which collation is preferred; it may or may not be used in a given expression, depending on the rules detailed under Collation of Expressions.


Join to a Culture Table vs Select Culture ID I have a query that is executed several hundred thousand times a day. Due to legacy support, the incoming variables on this SP cannot change. Currently it looks like this:

Select id,
       name,
       otherdata
FROM Table1 t1
--<Various joins with aliases>--
INNER JOIN Culture c on c.id = t1.ID_Culture
WHERE
c.CultureTag = @CultureTag -- Usually 'en-US'

My question is: Would it be more efficient to remove the join to the culture table, and instead, SELECT the value of the primary key on the culture table and use THAT in the where clause? Would it be negligible?

EDIT: To clarify, would changing the code to this be more efficient?

DECLARE @CultureIdLocal int = (Select id from Culture where CultureTag = @CultureTag)

Select id,
           name,
           otherdata
    FROM Table1 t1
    --<Various joins with aliases>--
    --INNER JOIN Culture c on c.id = t1.ID_Culture
    WHERE t1.ID_Culture = @CultureIdLocal

No need to refactor the single query into two separate ones.  SQL Server should be able to figure out the most optimal way to execute the query on your behalf.  Make sure the Culture table has a unique index on CultureTag and, in the case of a non-clustered index, add all of the columns needed by the query as INCLUDED columns.


Select exact subset from table I want to select an exact subset from a table.

DDL to create table:

CREATE TABLE groups (group_id NUMBER, activity_id number);
INSERT INTO groups (group_id, activity_id) values (1,1);
INSERT INTO groups (group_id, activity_id) values (1,2);
INSERT INTO groups (group_id, activity_id) values (2,1);
INSERT INTO groups (group_id, activity_id) values (2,2);
INSERT INTO groups (group_id, activity_id) values (2,3);
INSERT INTO groups (group_id, activity_id) values (3,1);
INSERT INTO groups (group_id, activity_id) values (3,2);
INSERT INTO groups (group_id, activity_id) values (3,3);
INSERT INTO groups (group_id, activity_id) values (3,4);

If I want the group_id of activity_ids (1, 2 and 3), then I would like only the exact match for group_id 2 to return, not 1 and 3.

I can do this with LISTAGG but wanted to see if there was a cleaner solution or suggestions if I should structure my table differently.

Prologue

This is enhancing my answer from your other question:

Enforce uniqueness across multiple rows

pre-19c - easy method

Calculate the hash-value of your list of activities and look it up in the group_activities#lock table.

pre-19c - dark side

Create an OPERATOR that does the comparison between two activity_list_nt values.

I consider this a "not ideal solution" because it is tempting you down the path of building your own Domain Index.

19c+

19c introduces the JSON_EQUAL operator.  This makes list comparison easy.

with group_activities_dml as (
    select group_id, json_arrayagg( activity_id ) activity_list_json
    from groups
    group by group_id
)
select *
from group_activities_dml
where json_equal( activity_list_json, '[1,2]' )


Disable AUTO_UPDATE_STATISTICS We had an issue earlier on in the week whereby a query had horribly regressed and as a result, it had completely taken over the SQL instance (OLTP Server) as it is frequently executed. One of the outcomes of the incident was that we were to consider disabling AUTO_UPDATE_STATISTICS, allowing for the the overnight maintenance jobs to handle statistics updates, and keep stats static during the day. The arguement for disabling statistics updates is that it will ensure that we retain the same consistent plans. By default my preference is not to do that, and more proactively monitor memory grants, and memory used, using query store and/or extended events, and to alert when significant issues occur.
I had looked around and couldn't really find any instances where people had blogged/posted about disabling this option and the sucesses of it. My question is are the any noted instances that people have of success of disabling statistics/ please can people relay successful implementations of doing so?
SQL Version: 2016 Standard EE

There are valid reasons for disabling AUTO_UPDATE_STATISTICS, such as wanting to have more control over the updating process of your stats. However disabling the auto update to prevent plans from changing destroys the purpose of the optimizer.
If you know your data very well and you are confident that a given plan will lead to good results regardless of the growth in certain tables, you should just force the plan with query store or a plan guide (https://docs.microsoft.com/en-us/sql/relational-databases/performance/plan-guides?view=sql-server-ver15). However before jumping into these kind of solution you should try the more standard approach of adding indexes, rewriting queries, adding hints, etc.
Avoid "shotgun" approaches such as disabling auto_update_statistics or setting MAXDOP =1 for whole instances. Instead you should identify the specific queries that are becoming pain points and analyze their executions. Contained "patches" such as index creation, hints or even forcing plans on specific queries should always be preferred over changes in configuration that will affect your workloads all across the board.
I hope this helps.


Question about 'mysql error 1064' ALTER TABLE sample62 CHANGE newcol c;

This code makes me mad. I just followed the book I had.

Program said

  You have an error in your SQL syntax; check the manual that corresponds
  to your MySQL server version for the right syntax to use near '' at line 1.

My MySQL version is 5.7. Can you please help me?

mysql error 1064

The error that gets thrown if the syntax is wrong. You did not clearly state what you are doing.

If you are changing a column

ALTER TABLE table_name MODIFY COLUMN column_name datatype

If you are trying to change column definition

ALTER TABLE table_name CHANGE old_column_name new_column_name data_type(size)

If you are adding a new column

ALTER TABLE table_name ADD column_name datatype


Which DBMS are fast enough for an online game (few thousand players)? I'm currently making an MMORPG game, which could have a few thousand players online at the same time (probably not; just wishful thinking). First we wanted to use MySQL, but I have heard it isn't fast enough for this scale.

Which DBMS is fast enough? How much like SQL Server is it (as I have learned SQL Server in school)?

This is a really tough question to answer. You can take the fastest database platform in the world, design a horrible schema, write a crappy application around it, and then be tempted to blame the database platform. At the same time, you could take the free SQL Server Express and, with the right design, application logic, and an approach at sensible scalability (e.g. scaling out reads by data caching, etc), you could write an application that handles 1000s of users no problem.

Do I believe SQL Server can handle 1000s of users? Absolutely. Do I believe Oracle and DB2 can do so as well? Certainly. MySQL? Not sure, not enough experience there. Access? Probably not a wise choice at all. If you are familiar with SQL Server, then I suggest that is the route you consider, just keep in mind that your choice of RDBMS will not in and of itself dictate success or failure.


Oracle: duplicate schema to another database I want to copy an Oracle schema from a production database to a validation database.
Both databases are Oracle 11g R1 standard edition and need less interrupting time as I can (but I can do if it absolutely needed).

Both are installed on it's own proper server (Linux RedHat 5 Enterprise), instance names differ, tablespace and datafiles are already exist, both use archive logs.

What I want to do is select a whole schema on the production database and duplicate it to the validation database. The process must include all tables, indexes and all dependent schemas objects. There are some Lobs in some tables but no "exotical" objects (like types), no packages, no procedures, no functions, ...
All objects reside in two well identified tablespaces (one for tables and datas, one for indexes).

I can't duplicate all instance. So RMan duplicate command is not an option.

Currently a process exist based on exp/imp utility but it's so slow, need database unavailability (in order to preserve data integrity) and error prone (mainly because full human's hand process).
I've the feeling that use RMan Oracle utility would be fine but I need some some external advises or comments to define the right way to do it. Datapumps utilities could be a valuable second choice (I'm currently evaluating).

Thanks for your advises
Thomas

Use expdp with the flashback_scn or flashback_time parameters, along with SCHEMAS= to export the schema at a consistent point in time.

This is the same as using CONSISTENT=Y in the legacy exp utility. Examples  here.

If the data for the schema in question is in a tablespace of its own with no other objects from other schemas, you can use rman to do a tablespace point in time recovery.


Multiple column partitioning I have a very large table that needs to be partitioned by two columns. One column is an ID and partitioning is simple enough. The second partitioning should be done daily on the date column. I need to keep data of up to 30 days in this table and partitions older than 30 days need to be dropped. What is the best way to do the partitioning for this scenario.

Thanks

The use case of "purge after 30 days" is an excellent use of PARTITIONing.  (It is one of only 4 use cases that I know of.)

PRIMARY KEY(id, date)
...
PARTITION BY RANGE (TO_DAYS(date))
(...)

Then every day do

ALTER TABLE .. DROP PARTITION ...
ALTER TABLE .. REORGANIZE PARTITION future INTO
           PARTITION ...,  -- preparing for tomorrow
           PARTITION future ...;  -- this partition should stay empty.

Partition Details -- this is a follow-on to @jkavalik's "Mandatory reading", as @mootmoot provided.

Note:  No subpartition.  No partition by id.  No partition by account_id.  The big advantage is making the big nightly DELETE run instantly via DROP PARTITION.

You probably need some kind of index on account_id.  Consider a composite index.

If you need to discuss this further, please provide SHOW CREATE TABLE (with or without partitioning) and the main queries.


Database partition efficiency Let's assume that you have a table that partitions on CATEGORY and SUBCATEGORY. Also assume that every subcategory can be mapped to a category, i.e. subcategory are proper subsets of categories.

Let's also say that for my query, I know I am only interested in a subcategory of 'A', and that I know that everything with a subcategory of A has a category of A.

Which of these should I go with this:

Select blah from blahblah where subcategory = 'A'

Select blah from blahblah where subcategory = 'A' and category = 'A';

Which is to say, is the second faster?

Assuming that you mean that the table is partitioned on CATEGORY and subpartitioned on SUBCATEGORY, the query that includes the CATEGORY predicate will be much more efficient.  Since Oracle cannot know that subcategory A only exists where the category is A, it would generally not be able to eliminate any partitions.  Instead, it would have to search the subpartition of each partition where a SUBCATEGORY of A would be stored in order to fulfill the query (assuming that there is no global index that could be used).  If you include both predicates, Oracle should be able to eliminate all but the partition where CATEGORY A values are stored and then search only the subpartition where SUBCATEGORY A values are stored.


SQL Server XML Insert Optimize? I have a 400k XML document trying to insert into an 86-column SQL Server 2008 table.
It takes about 35 minutes to get that XML into a temp table using this code:

with XmlNamespaces ( 'urn:xpaykj-report-xml-1.0' as xkj)
    Select
     xItem.value('declare namespace xkj="urn:xpaykj-report-xml-1.0"; (xkj:prop[@idx=1])   [1]   ','Date')  as [InputDate]
    -- and then 85 other columns --
Into #loadtemp
    From @xmldoc.nodes('xkj:output-data/xkj:childs/xkj:child[@name="output"]/xkj:childs/xkj:child[@name="lvla"]/xkj:childs/xkj:child[@name="lvlb"]/xkj:childs/xkj:child[@name="lvlc"]/xkj:properties') as x(xItem)

I need to optimize... Something. But I am not sure what. Can anyone help me with ideas?

Thanks.

Can you stage it twice?  First, shred the data into rows of XML, and then read the columns from the rows.  Something like:

with XmlNamespaces ( 'urn:xpaykj-report-xml-1.0' as xkj)
    Select
     xItem.query('.')  as RowXML
Into #loadtemp
    From @xmldoc.nodes('xkj:output-data/xkj:childs/xkj:child[@name="output"]/xkj:childs/xkj:child[@name="lvla"]/xkj:childs/xkj:child[@name="lvlb"]/xkj:childs/xkj:child[@name="lvlc"]/xkj:properties') as x(xItem)

should give you rows of XML; then apply your value statements to shred the table into columns.


PostgreSQL 9.3 is not returning existing rows with Ruby on Rails or Squirrel I dumped an existing PostgrSQL database (generating "Insert" statements), and imported it on another computer. The source runs Debian (UK) OS, the target Mint (FR) OS.

For some tables, when I try to visualise in Squirrel SQL, I get the following:

Squirrel "Row count" tab displays 351
Squirrel "Content" does not show anything
but executing the query "select * from myTable" returns all the lines

For the same table, when ruby tries to extract one record, it does not find it. But I can check that the record exists in Squirrel.

All indexes were rebuilt, no transaction pending.
The same data base is accessed with Ruby and Squirrel, with the same user.

What could be the reason, how to solve this blocking issue ?

Thanks,
Fred

The source of the issue was the name of one colunm of the table: PCF_index

psql generated "PCF_index" when exporting the schema. This caused trouble to Squirrel when trying to read it.

After the column is renamed pcf_index, the issue was solved.


Service Broker Procedure that will be used by multiple initiators to begin dialog conversation.. how to pass a parameter into it The standard format for beginning a dialog is like this:

Declare @dialog_handle UNIQUEIDENTIFIER
BEGIN DIALOG CONVERSATION @dialog_handle
   FROM SERVICE initiator_1
   TO SERVICE 'target_1'
   ON CONTRACT MyContract ;

But I would like to make a flexible generic procedure that can send a particular conversation from any of a number of initiator services (say I have 10 potential initiator services that would be using the same stored procedure).

Create Procedure SendJoinRequest (@initiatorServiceName varchar)
AS
   Declare @dialog_handle UNIQUEIDENTIFIER
BEGIN DIALOG CONVERSATION @dialog_handle
   FROM SERVICE @initiatorServiceName
   TO SERVICE 'target_1'
   ON CONTRACT MyContract ;

I guess this won't work because the identifier of the service in

FROM SERVICE @initiatorServiceName

actually cannot be a string, and must be the actual name of the service...  Is there an easy way to pass this as an argument? or do I have to nest all of this in a nasty execution of a concatenated string ?

@String = blahblah + blah blah + escapecharacters + blah
execute(@String)

Thanks for your help

Actually it will work. Most SSB verbs accept parameters for they arguments (except queuename for RECEIVE, of course). The parameters are of type sysname:

Create Procedure SendJoinRequest
   @from sysname,
   @to sysname,
   @contract sysname,
   @messageType sysname,
   @body XML
AS
Declare @dialog_handle UNIQUEIDENTIFIER
BEGIN TRANSACTION
BEGIN DIALOG CONVERSATION @dialog_handle
   FROM SERVICE @from
   TO SERVICE @to
   ON CONTRACT @contract
   WITH ENCRYPTION = OFF;
SEND ON CONVERSATION @dialog_handle MESSAGE TYPE @messageType (@body);
COMMIT


MySQL Cluster Setup for WAN on Windows I would like to know whether replication or clustering for MySQL is it possible over a WAN environment?

For example

Site A and Site B are both at different remote place.
Both have MySQL installed.
Both are using different internet modems.
Both are running Windows.

Can they replicated each other's date?

Are there any guides that I can follow to setup clustering over WAN?

MySQL replication can work over a WAN and is likely your simplest solution. My main concerns (other than running MySQL on Windows for something important enough to need failover) would be making sure the sites have enough bandwidth, and deciding whether to implement compression and/or encryption. I don't have a specific guide to recommend, but they are easy enough to find.

Windows multi-site clustering would be more difficult. You'll need block-level replication of storage and probably high throughput, low latency networking between sites if you want to fail over in a timely manner. Also, the MySQL installer does support Windows clustering now, but I don't know anyone actually running MySQL on a Windows cluster, and I don't know if it was tested on multi-site clusters.

Personally, if I were tasked with improving reliability of MySQL servers running Windows, I'd want to re-evaluate why Windows was chosen as an OS. Windows is perfectly capable of running highly available systems, but historically MySQL has had a number of deficiencies when running on Windows (bugs, resource usage limits, missing features and optimizations), and the MySQL development and support communities are far more active on Linux than Windows. This has improved somewhat since MySQL 5.5 when it was no longer compiled against the Windows POSIX API, but I'm not convinced that MySQL on Windows is at parity with MySQL on Linux. I know this isn't what you asked, but it's hard to honestly answer the question without bringing it up.


Should I combine these tables into one, or keep them seperate? In a question/answer forum component I am writing as part of a site, I have two tables that theoretically look like this(that is, I want said data kept for each entity):

THREAD                 POST
thread_id              post_id
type_id (question)     writer_id
writer_id              thread_id
title                  parent_id
body (optional)        post_body
followers              date
merge_id               upvotes
date                   dnvotes
upvotes                replies
dnvotes                views
replies                post_type_id (e.g. answer, comment to an answer etc.)
views
anonymous

The only real differences between these entities are merge_id (if a question is merged into another one, this id will reference said question_id), followers, and title. Combining them into one seems a bit cumbersome and bloated to me, and seems to be starting off a bit denormalized I think. And, if they're one, both body and title would have to be be nullable which they shouldn't (I know there can be safeguards in the code).

I also thought of having thread consist of only the attributes not in post which would look like:

THREAD
thread_id (or post_id which would be primary and foreign key refing POST)
title
merge_id
followers

but that doesn't feel quite right either.

Also, if it makes any difference, I plan to have the questions displayed along with the highest ranking answer quite often, in the style of quora.

Any design advice would be much appreciated.

(also, I am keeping them someone generic (thread/post) so that if needed I can use these tables for a blog or whatever else later on.)

I do think you need two tables - there are things in thread that apply to the entire conversation, not just the initial "question". It makes a lot of sense to have everything that represents a user-entered message of some sort stored in one place. There may be some differences in formatting for the initial post vs. follow-ups, but presumably most things that would apply to rows in post would also apply to parallel fields in thread.

I would, however, look at eliminating redundancy between thread and post.

A thread must have a title, and can have a body; all follow ups do not have individual titles, but must have a body. You have multiple options here; a couple are:

If only a title is provided for the starting post, store that in post.body; if a separate title and body are provided, store the title in thread.title, and the body in post.body.
Allow for a NULL or empty body if and only if post.parent_id is NULL.

I'll assume that upvotes, dnvotes, replies, and views are basically counts. If those are distinct between a thread and the posts that follow-up to the thread, then the only question is should we track these numbers for both the thread and the initial post.

While it's a little trickier to set up, I would actually be inclined to put the post_id of the initial post in the thread table. It should be true (I expect) that the post row with the desired thread_id and where parent_id is NULL should be the initial post. However, if users are allowed to delete posts, and if one user's follow-up to the initial post can be the parent for another user's post, then to delete a post with children, you'd have to set the children's parent_ids to the parent_id of the deleted post; you wouldn't have the option to leave the reply posts in the thread without having a parent_id.

If the initial post's ID is part of thread, then fields like upvotes and dnvotes would not exist at the thread level - the thread would simply pass on whatever values were tied to that initial post row.


mysql replication goes out of sync for some tables We are running mysql 5.1.61 on redhat systems and have the following setup

One master and four slaves replicating from the master, we recently added a new slave for replication and over a few days we have started noticing that on the newly added slave some tables ( not all ) loose some records , this happens only on this slave and it is not regular , over a period of 3 weeks this issue seems to have happened on 5-7 days .

We use statement based replication. I am not sure why this happens on only one slave. There seems to be no error in the mysql error logs. The only difference between the old slaves and the new slave is that the new slave has a slightly lower ram than the other ones but the new slave is not being used for anything right now.

Is there a way to trouble shoot this issue to see why this happens on only one slave ?. Could it be network related or anything else ? Any pointers on where to start looking at ?

Here is the memory info
Old slave

            total       used       free
 Mem:      24731204   24641572      89632

New slave

Mem:      16431192   10112880    6318312

You could be suffering from what is known as data drift.
QUERIES
This can happen if there are queries that are unsafe for replication.
One of the more common types is running UPDATE or DELETE using LIMIT. Using LIMIT on DML can work just fine on a Master. On a Slave, the rows selected (and perhaps certain ORDER BY choices) may not be the same set being updated or deleted as the set on the Master. See the MySQL Documentation for a Comprehensive Description of Unsafe Statements that can affect MySQL Replication.
Baron Schwartz once dealt with this and had to refactor his query to get around this
The following hypothetical scenario illustrates one way to introduce data drift:
BINLOGS
Master

20 DB Connections writing changes (INSERTs,UPDATEs,DELETEs)
The I/O thread from a Slave has to serialize the SQL coming from the 20 DB Connections
The serializing of the queries into the binary logs may be in an order different from when each DB Connection executed its change.
sync_binlog set to 0 (default), which leaves the responsibility of flushing binlogs to disk in the hands of the OS

Slave

I/O Thread reads binlog events in the order the Master wrote them
SQL Thread executes binlog events in the order the Master wrote them

Observation
If binlogs are not flushed to Disk in a timely, predictable manner, any binlog events the Slave needs could easily be bypassed. This could cause data simply not exist on the Slave. Depending on the data recorded or not recorded, Replication's SQL Thread could break because of missing data or data that should be missing.
EPILOGUE
Not every Slave can be affected this way. Masters keep a list of all Slave I/O Threads and transmits binlog events to the Slave in order by ProcessID on the Master. I can see later slaves being victimized first.
If sync_binlog is indeed an issue, perhaps all Slaves have data drift and we just don't know of it yet.
The only way to tell is to download one of the following

mk-table-checksum
mk-table-sync

or

pt-table-checksum
pt-table-sync

and checksum everything on every Slave against the Master. You may find more data drift problems than you think. Just run the sync scripts to correct them.
CAVEAT
You suggested that network latency could be at issue. With binlogs not being flushed by the OS yet, any disconnect and reconnect of MySQL Replication due to latency or dropped packets is worth looking over as well. It could also be a major contributor to data drift.
Also to be noted is what network route the new Slave is communicating with back to the Master. If it is not the same route as the older Slaves (perhaps passing through a different switch, over public IP, etc.) needs to be investigated.


How to split a SUM column based on another BIT column? MySQL v5.7
Having this table:

id   | sprId    | load    | calc
-----|----------|---------|---------
   1 |        1 |       1 |       7
   2 |        2 |       1 |       1
   3 |        2 |       1 |       4
   4 |        2 |       0 |      11
   5 |        3 |       1 |       2
   6 |        3 |       0 |      85
   7 |        4 |       1 |       1
   8 |        4 |       1 |      13

load is BIT.
How to split calc column sum based on the load column value, and then group by sprId.

I'm trying to get results like

 sprId    | sum_calc_load0  | sum_calc_load1
----------|-----------------|-----------------
        1 |               0 |               7
        2 |              11 |               5
        3 |              85 |               2
        4 |               0 |              14

Right now I'm doing two queries, and works, but I know for sure it can be done with subqueries, something I don't know that much.

SELECT sprId,
       COALECSE(SUM(calc*(1-load)), 0),
       COALECSE(SUM(calc*load), 0)
FROM source_table
GROUP BY sprId


How to get identical values for different customers I have a table which has different customers and each customer is having different insurance. say

Customer Id Bank Account
1           Test insurance 1
1           Test Insurance 2
1           Test Insurance 3
2           Test Insurance 4
2           Test insurance 5

I want to write a query to get below output

Customer Id   Insurance1            Insurance2              Insurance3 ---
1             Test insurance 1      Test Insurance 2        Test Insurance 3
2             Test Insurance 4      Test Insurance 5

Can anyone help me with query please.
Note: insurance for each customer is unique

First, a simple test-bed setup:

USE tempdb;

IF OBJECT_ID(N'tempdb..#vals', N'U') IS NOT NULL
DROP TABLE #vals
CREATE TABLE #vals
(
    CustomerID int NOT NULL
    , AccountName varchar(25) NOT NULL
);

INSERT INTO #vals (CustomerID, AccountName)
VALUES (1, 'Test Insurance 1')
    , (1, 'Test Insurance 2')
    , (1, 'Test Insurance 3')
    , (2, 'Test Insurance 4')
    , (3, 'Test Insurance 5')

Assuming you want separate columns for each account name, you could use a PIVOT transform, combined with CROSS APPLY to do that:

SELECT *
FROM (
SELECT *
FROM #vals v
    CROSS APPLY (VALUES (AccountName))dummy(val)
) src
PIVOT (
    MAX(val)
    FOR AccountName IN (
        [Test Insurance 1]
        , [Test Insurance 2]
        , [Test Insurance 3]
        , [Test Insurance 4]
        , [Test Insurance 5]
    )
) pvt;

The CROSS APPLY creates a third column that we can use in the aggregate part of the PIVOT statement.

The results:

╔════════════╦══════════════════╦══════════════════╦══════════════════╦══════════════════╦══════════════════╗
║ CustomerID ║ Test Insurance 1 ║ Test Insurance 2 ║ Test Insurance 3 ║ Test Insurance 4 ║ Test Insurance 5 ║
╠════════════╬══════════════════╬══════════════════╬══════════════════╬══════════════════╬══════════════════╣
║ 1          ║ Test Insurance 1 ║ Test Insurance 2 ║ Test Insurance 3 ║ NULL             ║ NULL             ║
║ 2          ║ NULL             ║ NULL             ║ NULL             ║ Test Insurance 4 ║ NULL             ║
║ 3          ║ NULL             ║ NULL             ║ NULL             ║ NULL             ║ Test Insurance 5 ║
╚════════════╩══════════════════╩══════════════════╩══════════════════╩══════════════════╩══════════════════╝

Unfortunately, the above solution creates more columns than your question asks for.  You might be interested in a solution that limits the number of columns to some predetermined number, as in:

;WITH src AS (
    SELECT v.CustomerID
        , v.AccountName
        , rn = ROW_NUMBER() OVER (PARTITION BY v.CustomerID ORDER BY v.AccountName)
    FROM #vals v
)
, s2 AS (
    SELECT src.CustomerID
        , Acct1 = CASE WHEN src.rn = 1 THEN src.AccountName ELSE NULL END
        , Acct2 = CASE WHEN src.rn = 2 THEN src.AccountName ELSE NULL END
        , Acct3 = CASE WHEN src.rn = 3 THEN src.AccountName ELSE NULL END
    FROM src
)
SELECT s2.CustomerID
    , Acct1 = MAX(s2.Acct1)
    , Acct2 = MAX(s2.Acct2)
    , Acct3 = MAX(s2.Acct3)
FROM s2
GROUP BY s2.CustomerID

The solution above requires you to predetermine the maximum number of columns you need to support.

The results:

╔════════════╦══════════════════╦══════════════════╦══════════════════╗
║ CustomerID ║      Acct1       ║      Acct2       ║      Acct3       ║
╠════════════╬══════════════════╬══════════════════╬══════════════════╣
║          1 ║ Test Insurance 1 ║ Test Insurance 2 ║ Test Insurance 3 ║
║          2 ║ Test Insurance 4 ║ NULL             ║ NULL             ║
║          3 ║ Test Insurance 5 ║ NULL             ║ NULL             ║
╚════════════╩══════════════════╩══════════════════╩══════════════════╝

If you are looking for the accounts to be included in a single column, you could use a subquery to obtain the list of accounts for each customer, something like this:

SELECT v.CustomerID
    , STUFF((SELECT ', ' + v1.AccountName FROM #vals v1 WHERE v1.CustomerID = v.CustomerID FOR XML PATH('')), 1, 2, '')
FROM #vals v
GROUP BY v.CustomerID
ORDER BY v.CustomerID;

╔════════════╦══════════════════════════════════════════════════════╗
║ CustomerID ║ (No column name)                                     ║
╠════════════╬══════════════════════════════════════════════════════╣
║ 1          ║ Test Insurance 1, Test Insurance 2, Test Insurance 3 ║
║ 2          ║ Test Insurance 4                                     ║
║ 3          ║ Test Insurance 5                                     ║
╚════════════╩══════════════════════════════════════════════════════╝

If you are using SQL Server 2017, you can take advantage of the STRING_AGG functionality to accomplish the same thing in a more elegant manner:

SELECT v.CustomerID
    , STRING_AGG(v.AccountName, ', ')
FROM #vals v
GROUP BY v.CustomerID

+------------+------------------------------------------------------+
| CustomerID |                   (No column name)                   |
+------------+------------------------------------------------------+
|          1 | Test Insurance 1, Test Insurance 2, Test Insurance 3 |
|          2 | Test Insurance 4                                     |
|          3 | Test Insurance 5                                     |
+------------+------------------------------------------------------+


Toad For MySql Give error on new connection Recently i installed Toad For MySQL latest version but when ever i create new connection i got this error.

Please check following condition

root user has privilege to connect to localhost
you have given right password for the user root


SQL Server - failed to update records when RAM is fully occupied We are using SQL server 2008 in Windows Server 2008 R2 and Ram is 32 GB.
When we check in the Windows Task manager 25GB is always occupied even though no one is accessing the server.

Around 10 clients connected to the server. When we run a report from application,  (2 or more clients runs it simultaneously) the memory occupied fully ie 32 Gb and saving records from other application got failed.

I checked in RAMmap tool AWE occupies 25 GB.

How to handle this situation?  ie., how  to limit the usage of memory for SQL server  or adding records when  the report query takes long time to run?
Thanks

As suggested set the “Maximum Server Memory”.

https://sqlserverperformance.wordpress.com/2009/10/29/suggested-max-memory-settings-for-sql-server-20052008/

Additionally, please note that your error is more likely related to locking or timeouts rather than “running out memory”. An extensive exclusive lock on the target table combined with missing indexes for the update statement can result in what you are experiencing.

1)  Tune your report query and ensure that its not having redundant excessive reads. Verify that hash and sort operators in your execution plan are not spilling to disk.

2)  Make sure that your update statement has logical supporting indexes.

Tuning both process might enhance concurrency of your application.

Good luck :)


Install MySQL on Ubuntu 12.0.4 This is my first time using Linux. I am trying to install MySQL 5.6.15 on an Ubuntu box. I download MySQL from their website and put it on a USB stick then plugged the stick into the box.

How can I install it using the downloaded package and not the built in package?

You need to visit the MySQL download page.
Select your preferred platform (I prefer Debian Linux). And then select your OS-type (32-bit or 64-bit).
Click on the Download button. It will ask if you want to Sign Up or Login, just choose the No thanks, just start my download. If you are downloading the .deb file on the terminal right click the link and choose Copy Link Address. Open the terminal, type wget (paste the link).
After downloading, you can double click on the .deb file or type in your terminal sudo dpkg -i (filename)

You are done. I usually have trouble installing the 5.6 version, if so visit this site.


Convert seconds into HH:MM:SS in Informix How is it possible to convert a float number of seconds to HH:MM:SS in Informix. I have a column that has a run duration of 1449.448520410. I want to convert this to a human-readable format. There are many ways of going about this, but I am struggling to find a method of doing so.
I have identified that running the below gives close to what I want, but excludes the hours:
select b.run_duration, floor(run_duration / 60) || ':' || lpad(mod(run_duration, 60), 2, '0') as run_duration_time from ph_task a, ph_run b where a.tk_id = b.run_task_id order by run_duration DESC

output:
24:09

What I would like to see is:
00:24:09

How can I customize my SQL to provide that?

Using the below sql, I managed to give exactly what I am looking for. I used the number 6346 to display the output populating hours, minutes and seconds.
Query used:
select lpad(floor(6346 / 60 / 60),2,'0') || ':' || lpad(mod(6346/60, 60), 2, '0') || ':' || lpad(mod(6346,60), 2, '0') as run_duration_time from systables

Output:
01:45:46

Another example without hours:
select lpad(floor(1000 / 60 / 60),2,'0') || ':' || lpad(mod(1000/60, 60), 2, '0') || ':' || lpad(mod(1000, 60), 2, '0') as run_duration_time

from systables
Output:
00:16:40

Another example without hours and minutes:
select lpad(floor(45 / 60 / 60),2,'0') || ':' || lpad(mod(45/60, 60), 2, '0') || ':' || lpad(mod(45, 60), 2, '0') as run_duration_time

from systables
Output:
00:00:45


Any system tables/views to determine query volume per table I was digging through the SQL Server system views and Windows performance counters and can't find any metrics regarding query volume PER TABLE in a given database. I am looking for any type of information to gauge the usefulness of each table in a given database. Query count/row count/data size of query results, or anything along those lines would be great. Does anyone have suggestions for how to see this data per table? I could do a Profiler trace or XE session over time, and then query those results to do some grouping, but don't want the added overhead if the data is already there somewhere.

You can use statistics data by tracking the most modified ones

   SELECT
   --into ##SQL_STATS_INFO
   --comment out h. columns for non-sql 2016 servers
  distinct
    (select db_name()) as Database_name
    ,o.name as Table_name
    ,c.name AS column_name
    ,s.name AS statistics_name

    ,p.last_updated
    ,p.rows
    ,p.rows_sampled
    ,h.range_high_key
  ,h.range_rows
  ,h.equal_rows
  ,h.distinct_range_rows
  ,h.average_range_rows
,p.modification_counter
  ,h.step_number
   --,sc.stats_column_id
  FROM sys.stats AS s
cross apply sys.dm_db_stats_properties(s.object_id, s.stats_id) p
cross apply sys.dm_db_stats_histogram(s.object_id, s.stats_id) h
INNER JOIN sys.stats_columns AS sc
ON s.object_id = sc.object_id AND s.stats_id = sc.stats_id
INNER JOIN sys.columns AS c
ON sc.object_id = c.object_id AND c.column_id = sc.column_id
inner join sys.objects o
on o.object_id = s.object_id
WHERE o.object_id > 100
and p.rows > 1000
and p.modification_counter > 100
order by o.name


Whats the name for columns which purpose is not to identify? I have a contacts table.

Columns are:

id
user_id
provider_id
title
phone_number
phone_ext

I want to know if there's a word that refers to the columns that make up the meat of the table. ie. (title, phone_number, phone_ext)

Thanks in advance

Those columns are generally referred to as non-key attributes, in my experience.

Googling for that phrase backs up the fact that it is commonly used, if you need a reference.


How does the PowerShell SQLPS SQLSERVER provider detect central management servers in SQLRegistration\Central Management Server Group I'm experiencing odd behavior with the PowerShell SQLSERVER provider and the SQLRegistration\Central Management Server Group directory. Below are the behaviors. I would like to know how the SQLSERVER provider knows what CMS's are registered so that I can understand why it doesn't seem to notice the registration when done in SSMS 2014 (see below for detailed behavior). Also, why my connection fails when I know the instance is online.

Setup
Machine

localhost

Instances

localhost\SQL2012
localhost\SQL2014
localhost\SQL2014_1

Behavior 1
Using SSMS 2014, I registered localhost\SQL2012 as a CMS. Executing this code returns no items.

PS SQLSERVER:\SQLRegistration\Central Management Server Group> dir

Open and closing the PowerShell console and SSMS does not change the results. If I open SSMS 2012 and register localhost\SQL2012 as a CMS and re-run the above command, I see localhost\SQL2012 registered as expected.

Behavior 2
After getting the server to list successfully...

  Directory: Microsoft.SqlServer.Management.PSProvider\SqlServer::SQLSERVER:\SQLRegistration\Central Management
      Server Group

  Mode Name

  localhost\SQL2012

And running the below command to try and navigate to my registered server groups fails with the below error, even though the instance is online and available.

PS SQLSERVER:\SQLRegistration\Central Management Server Group> Set-Location "localhost\SQL2012\"

  Set-Location : Cannot find path 'SQLSERVER:\SQLRegistration\Central Management Server Group\localhost\SQL2012\'
  because it does not exist.
  At line:1 char:1
  + Set-Location "localhost\SQL2012\"
  + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
      + CategoryInfo          : ObjectNotFound: (SQLSERVER:\SQLR...alhost\SQL2012:String) [Set-Location], ItemNotFoundE
     xception
      + FullyQualifiedErrorId : PathNotFound,Microsoft.PowerShell.Commands.SetLocationCommand

Update 1
SMO seems to work fine. The below code successfully returns my server groups.

#Load SMO assemblies
$CentralManagementServer = "localhost\sql2012"
$MS='Microsoft.SQLServer'
@('.SMO', '.Management.RegisteredServers', '.ConnectionInfo') |
     foreach-object {if ([System.Reflection.Assembly]::LoadWithPartialName("$MS$_") -eq $null) {"missing SMO component $MS$_"}}

$connectionString = "Data Source=$CentralManagementServer;Initial Catalog=master;Integrated Security=SSPI;"
$sqlConnection = new-object System.Data.SqlClient.SqlConnection($connectionString)
$conn = new-object Microsoft.SqlServer.Management.Common.ServerConnection($sqlConnection)
$CentralManagementServerStore = new-object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($conn)

$CentralManagementServerStore.ServerGroups[ "DatabaseEngineServerGroup" ].ServerGroups

Shawn's explanation about discrepancies between SQL Server version seems on target. For this reason, I believe the SMO is a more stable method. I'm going forward using the below code.

Import-Module SQLPS -DisableNameChecking

Function Parse-ServerGroup($serverGroup)
{
    $results = $serverGroup.RegisteredServers;
    foreach($group in $serverGroup.ServerGroups)
    {
        $results += Parse-ServerGroup -serverGroup $group;
    }
    return $results;
}

Function Get-ServerList ([string]$cmsName, [string]$serverGroup, [switch]$recurse)
{

    $connectionString = "data source=$cmsName;initial catalog=master;integrated security=sspi;"
$sqlConnection = New-Object ("System.Data.SqlClient.SqlConnection") $connectionstring
    $conn = New-Object ("Microsoft.SQLServer.Management.common.serverconnection") $sqlconnection
    $cmsStore = New-Object Microsoft.SqlServer.Management.RegisteredServers.RegisteredServersStore($conn)
    $cmsRootGroup = $cmsStore.ServerGroups["DatabaseEngineServerGroup"].ServerGroups[$serverGroup]

    if($recurse)
    {
        return Parse-ServerGroup -serverGroup $cmsRootGroup | select ServerName
    }
    else
    {
        return $cmsRootGroup.RegisteredServers | select ServerName
    }
}

Get-ServerList -cmsName "localhost\SQL2012" -serverGroup "Production" -recurse | Format-Table


How to avoid deadlock while updating 2 rows in MySQL This is a interview problem:

There is a perl program that updates the database, and it could run in different processes.
One process may execute a transaction like:
update row A -> update row B -> commit

The other process may execute a transaction like:
update row B -> update row A -> commit

The rows need to be updated is selected in the program before the transaction.

I was asked how to avoid deadlocks without changing the transaction logic (I cannot commit after updating A and commit again after updating B).
They want me to propose at least 3 different methods. What I know is to use select .. for update when selecting row A and B. Can anyone help to suggest some other methods?

I don't like the interview question because in addition to the (presumably) correct SELECT ... FOR UPDATE on all rows, I suspect the other answers they're looking for may be something theoretically accurate but not necessarily the best idea (especially in a high-concurrency environment), such as:

use table level locking to force serialization of the transactions
use a semaphore mechanism such as GET_LOCK() and RELEASE_LOCK() to serialize the transactions with named locks... admittedly, this is a feature in MySQL that I really do find very useful (such as for preventing recurring scheduled events that run longer than expected from firing up parallel instances, and serializing the execution of unrelated low-priority background/maintenance jobs to keep resource utilization by the maintenance jobs at a minimum) but that I do not normally use for serializing live transactions.

@Chris Travers has already given what may be more useful real-world answers than these, but from my reading of the question, standardizing the order (#2) and using stored procedures (#3) aren't permitted modifications, perhaps, by definition... so I offer the speculation, above.


Replicate MySQL 5.0 Master to 5.5 Slave? We're planning out a series of server/software upgrades and were curious.

We have a RHEL5 install running MySQL 5.0 that we'd like to replicate to a new (virtualized) server running CentOS 6 and MySQL 5.5. Then, in the future, we would bump the RHEL5 machine to CentOS6 and MySQL5.5. We want to get this replication going first if possible.

So the short question is, has anyone tried replicating a MySQL 5.0 master to a 5.5 slave?

I have done that dozens of times for my employer's clients. In fact, I just did this a week ago. The client was using MySQL 5.0.81 and had degraded performance in InnoDB.

I create a new DB Cluster

running MySQL 5.5.27
Two DB Servers (ServerA and ServerB)
Configure InnoDB for Multicore Engagement

Master/Slave (ServerA Master and ServerB Slave)
I enable binary logging and log_slave_updates on Production's Slave
mysqldump all databases (except mysql schema) from Production's Slave to /root/MySQLData.sql
run pt-show-grants on  Production's Master to /root/MySQLGrants.sql
load ServerA with /root/MySQLGrants.sql
load ServerA with /root/MySQLData.sql
I replicate from Production's Slave (MySQL 5.0.81) to ServerA (MySQL 5.5.27)

All that was left to do was

Have the client shutdown the app
Remove DBVIP from Master running MySQL 5.0.81
Bring up DBVIP on Master running MySQL 5.5.27
Have the client start up the app

CONCLUSION

MySQL 5.5 can handle replication from previous major releases, and not the other way around.

I have discussed this before

Feb 04, 2011 : MySQL master binlog corruption
Jun 09, 2011 :  Yum update mysql from 5.1 to 5.5
Feb 08, 2012 : will replication from 5.5.20 to 5.0.XX server work?

GOOD NEWS !!!

I already answered a post back on Feb 06, 2012 ( How to setup replication(Master/slave) in MySQL 5.5.20? ) with all the steps you need.

BTW with regards to the step about dumping the MYSQL grants as SQL commands, here is my personal emulation of pt-show-grants:

mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -hhostaddr -umyuserid -pmypassword --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql

Give it a Try !!!

UPDATE 2012-11-27 11:07 EDT

I wanted to add additional posts I made for setting up Circular Replication should you decide to setup the two DB servers as Master/Master

Sep 24, 2012 : Setting up MySQL circular replication in existing replication topology?
May 07, 2012 : Setting Circular Replication in mysql


Having primary key in both fields but repeated in either fields allowed repeating Is it possible to have both fields to be primary key but allows repeating  For either 1 field in Oracle SQL.

   Student_ID  Date
   1001        23/10/2016  // allowed
   1002        23/10/2016  // allowed
   1002        24/10/2016  // allowed
   1002        24/10/2016  // not allowed

You can achieve the same goal by having a UNIQUE composite index on the desired fields.

ALTER TABLE YourTable
  add CONSTRAINT YourTable_unique UNIQUE (A, B,....);

(A, B,...) - fields A, B,... should be declared as NOT NULL.

This might be helpful if you have a surrogate PRIMARY KEY which you may want to keep. Something similar was recently asked for MySQL here, but the principle is the same.

You can also perform the same operation during the CREATE TABLE statement:

CREATE TABLE table_name
(
  column1 datatype NOT NULL,
  column2 datatype NOT NULL,
  ...

  CONSTRAINT constraint_name UNIQUE (uc_col1, uc_col2, ... uc_col_n)
);


How to select bytea data as binary data, inserted as hex-encoded from binary The post How to insert (file) data into a PostgreSQL bytea column was helpful.

However, when I select these columns, I get the same hex encoded data.

For instance:

> select substring(smallblob,0,64) from longdata where blobi d=1;
\xd0cf11e0a1b11ae1000000000000000000000000000000003b000300feff09000600000000000 00000000000010000006a0000000000000000100000670000
(1 row)

How can I SELECT this data in binary format?  CAST' andCONVERT` do not appear to support a 'binary' option.

You're not going to get binary data out of psql and SELECT. psql will format it for readability.  You could try something with lo_export similar to the answer in How to insert (file) data into a PostgreSQL bytea column to get the data written to a file.

However, most PostgreSQL client adapters (perl DBI, Ruby 'pg', etc) can give you the data in binary when you SELECT bytea columns.  Just write a little program using an appropriate adapter in the language of your choice.