Segmentation fault (core dumped) with self-complied mysql-5.1.9-beta.tar.gz I am compiling mysql-5.1.9-beta.tar.gz source code under Ubuntu 12.04. My steps are following:

tar -xzf mysql-5.1.9-beta.tar.gz
cd /home/gaoyu/mysql-5.1.9-beta
./configure --prefix=/home/gaoyu/mysql-5.1.9 --with-debug
sudo make
sudo make install
sudo cp /home/gaoyu/mysql-5.1.9-beta/support-files/my-medium.cnf /etc/my.cnf
cd /home/gaoyu/mysql-5.1.9
bin/mysql_install_db --user=mysql
sudo cp /home/gaoyu/mysql-5.1.9-beta/support-files/mysql.server /etc/init.d/mysql
sudo bin/mysqld_safe --user=mysql &

The bug is :

gaoyu@FlashGroup:~/mysql-5.1.9$ sudo bin/mysqld_safe --user=mysql &
[1] 26196
gaoyu@FlashGroup:~/mysql-5.1.9$ nohup: ignoring input and redirecting stderr to stdout
Starting mysqld daemon with databases from /home/gaoyu/mysql-5.1.9/var
^C

(here I wait for a very long time, so I must press Ctrl + C to stop it and continue)

gaoyu@FlashGroup:~/mysql-5.1.9$ bin/mysql
Segmentation fault (core dumped)

After I input sudo bin/mysqld_safe --user=mysql &, I must wait for a very long time. Why is that?
Why do I get a segfault?
Has anyone encountered a similar problem?

There are issues with this question that suggest a fundamental lack of familiarity with MySQL.

  After I input sudo bin/mysqld_safe --user=mysql &, I must wait for a very long time. Why is that?

What did you expect to happen?  Here are a couple of examples of startup behavior from other versions:

MySQL 5.0.45

$ sudo bin/mysqld_safe
Starting mysqld daemon with databases from /var/lib/mysql

MySQL 5.5.17

$ sudo bin/mysqld_safe
130320 20:04:20 mysqld_safe Logging to '/var/log/mysql/error.log'.
130320 20:04:20 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

Nothing else is supposed to happen.  So... what you're seeing is normal, so far.  You can wait an infinite amount of time, and MySQL isn't normally going to say anything else on the console.  It's a server daemon, not an interactive program.

What does the MySQL error log say?  There's nothing here, yet, to tell us that your server isn't actually running just fine.

gaoyu@FlashGroup:~/mysql-5.1.9$ bin/mysql
Segmentation fault (core dumped)

Interesting, but unrelated to the other issue... bin/mysql is not MySQL Server, it's the MySQL command line client.  This could be a defect in the bundled or your local libedit, or curses, or SSL, or any other library that's involved... but if you can get the server working, you can connect to it with any newer version client, so the inability to compile a working client might not be particularly relevant, depending on the nature of what's happening here.

You also appear to be trying to start with a debug build, which seems like a backwards approach, since the debug instrumentation can change the behavior of the server.

But this discussion all seems academic, since, as has already been aptly stated, MySQL 5.1.9 is a fundamentally flawed codebase to try working from.  Your code might be great, but you're working against a base that was not production quality.

In fact, it's a beta version that came out almost 7 years ago, and it took two and a half years' worth of releases after that one before the MySQL 5.1 release series achieved General Availability status.

It seems inconceivable that 5.1.9 could be relevant to anyone, anywhere, when there were still major, major defects in that code... but granting, for a moment, the assumption that it is... then it seems like the bottom line is that if you have some 5.1.x-related add-on source code you're working with, there is not any intrinsic reason why it would not be equally usable with newer 5.1.x releases... 5.1.68 was released in 2013.

Accomplish what you want to accomplish with 5.1.67 (my current production choice for 5.1 constrained environments) or 5.1.68 and there should be very few incompatibilities when you back-port to prior releases as long as you stay within 5.1.x.

Testing whether you can actually compile the newer versions of 5.1 successfully might tell you something about your build environment, too.


Add a constraint to a column on an Oracle table so that only positive values are allowed I'm not allowed to drop the table, or delete the columns.
The task is to add a constraint to a column possibly by using an ALTER statement so that it disallows any negative numbers. Is this possible?

I tried the following:

SQL> alter table SPEND_PLAN_DETAIL_VALUE modify (SP_DETAIL_VALUE_ID CHECK (SP_DETAIL_VALUE_ID > 0));

but this doesn't work.

To add a constraint you need to use add constraint:

alter table SPEND_PLAN_DETAIL_VALUE
  add constraint check_detailvalue CHECK (SP_DETAIL_VALUE_ID > 0);

Online example


Import error from mysql command line I tried so many ways to restore a sample database (with the command on top of the picture) but i failed every single time. i successfully restored with this command in past. but now i am facing this problem. And when i am putting "\" to specify path, there is an error saying '\s' is not a valid command. You can see it's showing "D:ample.sql" :(

look like You try to run mysql(?d) from inside mysql console
this command must be run from OS command line:

c:\Program Files\MySQL\bin>mysql -u root -p sample < d:/sample.sql

all from Windows command line


Time series data of several sources in one SQLite table or several? File size impact? Here is the situation:

A large amount of time series data from a very limited set of sources is to be kept in a SQLite database file.
Time series sources are identified by a 10 character long string. The number of sources is very low and not at all dynamic.
There is no immediate need for any correlation analysis between the time series sources.

I guess, conventionally, one would dump all time series data in a single table with a text field identifying the source of each data point.

However, I am wondering whether it would be more efficient to create a separate table for each source of time series data? Will the file size of the SQLite database eventually be much smaller by eliminating the repetitive text field? Will queries run much faster?

Or should I completely refrain from creating a table for each source and for what reason?

The database will not be huge, which may warrant the use of SQLite over MySQL. Apart from the integer timestamps, there are four more integer fields. Measurements are made every 3 minutes, but it could easily run for a year or more. Part of the design might end up in an embedded device with a very limited amount of flash memory (64Mbit), hence my initial interest for SQLite. However, I am open to other suggestions.

Personally, I hate to throw away potentially useful data. I'd create a table for the sources, and include source_id in the time series table as a foreign key to the source table. This should take up less space, but still retain the source information (without requiring multiple identical tables).

I've put together a short example; see this db-fiddle link.

Here's the code form my example:

CREATE TABLE source
     ( source_id INTEGER PRIMARY KEY ASC
      ,name varchar(10)
     );

CREATE TABLE time_series
     ( series_id INTEGER PRIMARY KEY ASC
      ,timestamp INTEGER
      ,value1 INTEGER
      ,value2 INTEGER
      ,value3 INTEGER
      ,source_id INT
      ,FOREIGN KEY (source_id) REFERENCES source(source_id)
     );

INSERT INTO source (name)
VALUES ('AAAA'), ('BBBB'), ('QZQZ');

INSERT INTO time_series (timestamp, value1, value2, value3, source_id)
VALUES (12345678, 100, 105, 110, 1)
      ,(12345681, 105, 105, 105, 1)
      ,(12345684, 110, 105, 100, 1)
      ,(12345678, 9, 27, 81, 3)
      ,(12345681, 27, 81, 243, 3)
      ,(12345684, 81, 243, 729, 3)
;

SELECT * FROM source;

SELECT s.name as source, timestamp, value1, value2, value3
  FROM source s INNER JOIN time_series ts ON (s.source_id = ts.source_id)
 ORDER BY source, timestamp
;

I should note that I don't normally use SQLite. As you noted in your response to my original comment, SQLite maintains a 64-bit integer row ID column in every table by default. I've set up the tables in the example to use that row ID value as the primary key for each table. If I've read the documentation correctly, the foreign key column should just be big enough to hold the value from the primary key. Assuming you don't manually insert a source_id that's huge, I believe that source_id in the time_series table should only require 1 byte.

You might want to put a UNIQUE index on source_id and timestamp (you should almost certainly have some sort of index on them); presumably, you should never have two entries for the same source and the same time. As pointed out by Serge Stroobandt, this could even be the primary key. However, since SQLite will create a unique row ID value anyway, I'd be inclined to leave that as the key. If you really wanted to use source_id and timestamp as the primary key, look into creating the table WITHOUT ROWID.


How to run a job in snapshot isolation? We have a long running job for report data generation and we are not worried about changes to the source data after the job has started. For this case the SNAPSHOT isolation mode seems to be a good fit. But we are looking at a possible way to use Snapshot isolation from a scheduled SQL job.

When connecting through a client using ADO.NET we are able to mention the transaction to use Snapshot Isolation (SqlTransaction sqlTran =  connection.BeginTransaction(IsolationLevel.Snapshot)). But there seems to be no way of running a scheduled job with the same isolation.

Is there a way we can run an SQL job under snapshot isolation level?

If this is a job consisting of a Transact-SQL step, you can specify a SET TRANSACTION ISOLATION LEVEL statement at the beginning of your Transact-SQL script:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;

…  /* the rest of your script */


Improving query performance on mysql star catalog I have a mysql database that contains a table with roughly 645 million records. I am trying to improve the query performance. Below is the SQL for the table as well as the indexes I use.

CREATE TABLE `Catalog` (
`id` BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
`sourceFile` CHAR(5) NOT NULL,
`starID` BIGINT(20) UNSIGNED NOT NULL,
`ra` DOUBLE NOT NULL,
`decl` DOUBLE NOT NULL,
`muRA` FLOAT NOT NULL,
`muDecl` FLOAT NOT NULL,
`magB` FLOAT NOT NULL,
`magV` FLOAT NOT NULL,
`magU` FLOAT NOT NULL,
`magG` FLOAT NOT NULL,
`magR` FLOAT NOT NULL,
`magI` FLOAT NOT NULL,
`magZ` FLOAT NOT NULL,
`magY` FLOAT NOT NULL,
`magJ` FLOAT NOT NULL,
`magH` FLOAT NOT NULL,
`magK` FLOAT NOT NULL,
`w1` FLOAT NOT NULL,
`w2` FLOAT NOT NULL,
`w3` FLOAT NOT NULL,
`w4` FLOAT NOT NULL,
`magSST` FLOAT NOT NULL,
`flag` INT(11) NOT NULL,
PRIMARY KEY (`id`),
INDEX `UFilterIndex` (`magU`, `decl`, `ra`),
INDEX `GFilterIndex` (`magG`, `decl`, `ra`),
INDEX `RFilterIndex` (`magR`, `decl`, `ra`),
INDEX `IFilterIndex` (`magI`, `decl`, `ra`),
INDEX `ZFilterIndex` (`magZ`, `decl`, `ra`),
INDEX `YFilterIndex` (`magY`, `decl`, `ra`)) ENGINE=MyISAM;

My typical query looks something similar to this:

SELECT * FROM `Catalog`
WHERE
`magG` >= 7.32 AND `magG` <= 15.68 AND
`decl` <= -1 AND `decl` >= -2 AND
`ra` >= 75 AND `ra` <= 75.23

It takes on average 17 seconds to execute this query. When I EXPLAIN the query I can see that it is using the GFilterIndex using index condition. The types of queries I perform will always involve magG and decl. The queries will also involve ra the vast majority of the time. So I think my index is doing what I want it to do. When I attempt to query without the index I get query times of 8min+.

This database won't change (no inserts or updates). I will be performing thousands of these queries and at ~17 seconds each it will be very painful. Is there something I can do to further improve the query performance? Thank you for any help you can offer.

Instead of SELECT *, try selecting the fields you need (unless you need all of them!): SELECT id, magG, ... FROM
Try to have the fields you are selecting in the used index. This way you will have a covering index, so the data will be read once only from the index
If you need many fields to be returned, you may first select the IDs using the query you already have, then send another query like this: SELECT * FROM Catalog WHERE id IN (list of IDs from first query) [In total, this may not be much faster, but try it to see if the difference is significant]


Allow a user to create folders and linked reports, but not "real" reports I have a need to allow certain users the ability to create folders and linked reports within specified folders, but I don't want them to be able to put actual reports in those folders. Is there a magic combination of permissions I can put on a role that will allow this? I'm assuming the "Create linked reports" permission applies to the source report that's being linked, and I'm not sure if linked reports fall under "Manage reports" or "Manage resources".

(For the curious, I want to be able to differentiate between the people developing and deploying reports to a designated location, and people linking those reports to directories that certain end users will have access to.)

Edit:

Just to make it clearer what I'm trying to do... Say I've got two directories on the report server: Base Reports, and Sales Department. I want the developers to be able to create reports in Base Reports (no problem there). I also want to allow another group to be able to create linked reports in Sales Department which are based on reports within Base Reports. I do not want this group to be able to create actual reports within Sales Department.

In order to allow the second group to create linked reports based on what's in Base Reports, I set Create Linked Reports permission on the Base Reports directory. What do I have to set on Sales Department to allow them to put the linked reports in there, without allowing them to create actual reports?

Yes. As per MSDN

  Create linked reports | Reports | Create Link, Read Properties
  ...
  Manage folders | Folders | Create Folder, Delete Update Properties, Read Properties

From the same link, one can see that "Create linked reports" does not infer or grant the separate permission "Manage reports"


Query on 250 million records I have a table with below structure

CREATE TABLE [dbo].[ColumnValues](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ColumnId] [int] NOT NULL,
    [Value] [nvarchar](max) NULL,
    [OutputArea] [nvarchar](max) NULL,
 CONSTRAINT [PK_dbo.ColumnValues] PRIMARY KEY CLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[ColumnValues]  WITH NOCHECK ADD  CONSTRAINT [FK_dbo.ColumnValues_dbo.CensusColumns_ColumnId] FOREIGN KEY([ColumnId])
REFERENCES [dbo].[CensusColumns] ([Id])
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[ColumnValues] CHECK CONSTRAINT [FK_dbo.ColumnValues_dbo.CensusColumns_ColumnId]
GO

It contains almost 250 Million records. I need to filter records based on list of OutputArea codes and ColumnIds and the query which I used is like this:

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[OutputArea] AS [OutputArea],
[Extent1].[ColumnId] AS [ColumnId],
[Extent1].[Value] AS [Value]
FROM [dbo].[ColumnValues] AS [Extent1]
WHERE ([Extent1].[OutputArea] IN (N'E00139237', N'E00139238', N'E00139239', N'E00139255', N'E00139257', N'E00139258', N'E00139259', N'E00139260', N'E00139261', N'E00139262', N'E00139263', N'E00139264', N'E00139265', N'E00139266', N'E00139267', N'E00139268', N'E00139689', N'E00139690', N'E00139691', N'E00139694', N'E00139814', N'E00139815', N'E00139816', N'E00139817', N'E00139818', N'E00139835', N'E00139837', N'E00139838', N'E00139847', N'E00139848', N'E00139849', N'E00139850', N'E00139855', N'E00139856', N'E00139857', N'E00139858', N'E00139939', N'E00139940', N'E00139941', N'E00139980', N'E00139981', N'E00139982', N'E00140008', N'E00140010', N'E00140011', N'E00140012', N'E00140013', N'E00140014', N'E00140015', N'E00140016', N'E00140017', N'E00140018', N'E00140019', N'E00140020', N'E00140021', N'E00140022', N'E00140023')) AND ([Extent1].[OutputArea] IS NOT NULL)
AND ([Extent1].[ColumnId] IN (298, 299, 300, 301, 302, 303, 304, 305, 306, 307, 308, 309))

When I have small list of entries like less than 50 members for input the query works fine, not with great performance though. But when I run a query on an instance of SQL Server 2008 R2 which is hosted on a powerful server which has 128 GB memory it mostly fails to execute and I get below error:

  The query processor ran out of internal resources and could not
  produce a query plan. This is a rare event and only expected for
  extremely complex queries or queries that reference a very large
  number of tables or partitions. Please simplify the query. If you
  believe you have received this message in error, contact Customer
  Support Services for more information.

Is it very complex query? because I expect this to execute fast and without problem due to powerful resources on the server.
How do I fix the problem and increase the performance?

Likely, the issue is caused by the very long IN lists. Instead of using the IN list, create a temporary table with the values you want to filter on. Then rewrite the query as a join:

CREATE TABLE #FilterOutputArea (...)
INSERT #FilterOutputArea VALUES ('E00139237')
... etc...

CREATE TABLE #FilterColumnID ()
INSERT #ColumnID VALUES (298)
... etc...

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[OutputArea] AS [OutputArea],
[Extent1].[ColumnId] AS [ColumnId],
[Extent1].[Value] AS [Value]
FROM [dbo].[ColumnValues] AS [Extent1]
JOIN #FilterOutputArea ON ...
JOIN #ColumnID ON ...


need sql to represent relation negation and date compare problem I have the following tables:

Employee(eid,ename,salary,did,classification)
Project(pid,pname,did,budget,ddate)
Department(did,dname,floor,head)
dbudget(did,byear,budget)
Project(pid,pname,did,budget,ddate)
Onproject(pid,eid,fdate)

did department identifier, so we need something that is in employee.did but non on project.did

and I want to retrieve all of the projects whose duedate is not passed, and that all of its workers belong to a department  that is not responsible for the project.

now, I don't know how to check if the ddate is over(ddate is due date, and I don't know if it's passed or not), and how to represent it in a query.

I'll show the query I constructed so far:

SELECT   Project.pname
from         Project
FULL OUTER JOIN Employee
on   Project.did <>  Employee.did
AND ....

(don't know how to check if ddate is passed) is there a function to show date in SQL to compare (so the difference will be positive) or any trick to work with the given info?

The problem is not simple as it involves 2 entities/tables (Employee and Project) that have 2 relationships between them. One is through the Department (a project belongs to a department, an employee belongs to a department) and the other through Onproject (which shows that an employee has started working on a project).

Moreover, the answer needs a relational division query ("all is workers belong ..."). There are many ways to do relational division - and all are in the range of not simple to very complicated.

One way to express the problem is with a (double) negative. Instead of finding projects where all workers do not do something, find projects where none of the workers do something. This can be expressed with a NOT EXISTS subquery:

SELECT p.*
FROM Project AS p
WHERE p.ddate > CURRENT_DATE
  AND NOT EXISTS
      ( SELECT *
        FROM Employee AS e
            JOIN Onproject AS w
            ON w.eid = e.eid
        WHERE w.pid = p.pid
          AND e.did = p.did
      ) ;

Or with EXCEPT:

SELECT p.*
FROM Project AS p
    JOIN
      ( SELECT p.pid, p.did
        FROM Project AS p
        EXCEPT
        SELECT w.pid, e.did
        FROM Employee AS e
            JOIN Onproject AS w
            ON w.eid = e.eid
      ) AS pw
      ON pw.pid = p.pid
WHERE p.ddate > CURRENT_DATE ;

Given the tables definitions, the latter can be (simplified?) to use NATURAL JOIN:

SELECT *
FROM Project
    NATURAL JOIN
      ( SELECT pid, did FROM Project
        EXCEPT
        SELECT pid, did FROM Employee NATURAL JOIN Onproject
      ) AS pw
WHERE ddate > CURRENT_DATE ;


Can you edit the context menu options? Is there a way (registry hack is acceptable) to remove options from the context menus in SSMS?

Specifically I'd like to remove the delete option from the context menu when you right click on a merge replication publication.  If we really want it deleted we'll do it via tsql.

And yes, there is a tragic tale motivating this question.

I suspect the "tragic tale" might be that someone who had sysadmin privileges (but shouldn't have) went in and deleted one of these things (possibly by mistake).

But another thought occurred to me: someone who should have sysadmin privileges might have done the same thing (by mistake).

In lieu of being able to cripple Management Studio (which I suppose you could do by writing your own an add-in), you should consider simply not logging in as sysadmin except when absolutely necessary.

At my old job we tried to implement a policy where by default you connect to SQL Server as you, and you are given the rights you should have for most tasks. When you need to escalate to do something out of the ordinary, only then do you connect as sysadmin. This worked with some success - however you need to be tolerant of the moaning and griping that will happen. We actually had different database roles such as a read only for our QA department - allowing them, say, to validate a report output by selecting from certain tables directly.


Copy from one MySQL table to another MySQL table of same database I have around 40 million rows in a MySQL table and I want to copy this table to another table in same database. What is the most efficient way of doing this? How much time will it take (approx.)?

Suppose you have mydb.mytb and you want to create mydb.mytbcopy

I have five(5) approaches to doing this copy

APPROACH #1

In the mysql client, run the following

USE mydb
CREATE TABLE mytbcopy LIKE mytb;
INSERT INTO mytbcopy SELECT * FROM mytb;

APPROACH #2

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysql ${MYSQL_CONN} -ANe"CREATE DATABASE IF NOT EXISTS test"
mysqldump ${MYSQL_CONN} mydb mytb | mysql ${MYSQL_CONN} -Dtest
mysql ${MYSQL_CONN} -ANe"ALTER TABLE test.mytb RENAME mydb.mytbcopy"

APPROACH #3

DUMPFILE=/some/path/tabledata.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysql ${MYSQL_CONN} -ANe"CREATE DATABASE IF NOT EXISTS test"
mysqldump ${MYSQL_CONN} mydb mytb > ${DUMPFILE}
mysql ${MYSQL_CONN} -Dtest < ${DUMPFILE}
rm -f ${DUMPFILE}
mysql ${MYSQL_CONN} -ANe"ALTER TABLE test.mytb RENAME mydb.mytbcopy"

APPROACH #4

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysqldump ${MYSQL_CONN} mydb mytb | sed 's/mytb/mytbcopy' | mysql ${MYSQL_CONN} -Dmydb

APPROACH #5

DUMPFILE=/some/path/tabledata.sql
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysqldump ${MYSQL_CONN} mydb mytb | sed 's/mytb/mytbcopy' > ${DUMPFILE}
mysql ${MYSQL_CONN} -Dmydb < ${DUMPFILE}
rm -f ${DUMPFILE}

ANALYSIS

APPROACH #1 is the easiest in terms of steps, but requires pushing 40 million rows into one transaction. This will be the most taxing on the InnoDB Storage Engine.
For the other approaches, mysqldump will send 40 million row in chucks of thousands of rows

APPROACH #2 and APPROACH #3 will mysqldump the table into the test database. After creating the table in the test database, it is subsequently renamed and moved into the original database
APPROACH #4 and APPROACH #5 rename the table using sed against the stream coming from the mysqldump as it echoes the INSERT commands
APPROACH #2 and APPROACH #4 use pipes instead of an output file
APPROACH #3 and APPROACH #5 use an outpuit file for subsequent reload

If you want to copy mydb.mytb to an already existing table mydb.mytbcopy, and the two tables have identical structures:

APPROACH #6

INSERT INTO mytbcopy SELECT * FROM mytb;

Like #APPROACH 1, #APPROACH 6 would have a single transaction of 40 million rows

APPROACH #7

MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
mysqldump ${MYSQL_CONN} -t mydb mytb | sed 's/mytb/mytbcopy' | mysql ${MYSQL_CONN} -Dmydb

This approaches does not drop the table. It simply generates the INSERTs

EPILOGUE

I cannot give you a time estimate since I do not know the make up of the DB Server, table structure, index layout, and things like these.

GIVE IT A TRY !!!


TSQL for "Ignore databases were the state is not online" I have a few custom jobs on a mirrored SQL Server 2005 and they fail from time to time because of automatic failover.

Is there some T-SQL code that I can write into my automated jobs or do I just have to remember to set failover so the job doesn't fail?

I would suggest to use Ola's backup solution (and Index maintenance  solution as well). This is a well applauded solution that is smart enough to work with mirroring (in your case).

Maintenance plans are not bad, but when your environment grows, the limited flexibility and functionality that maintenance plans provide wont be sufficient.

For e.g Maintenance plans suffer from below drawbacks :

There is no CHECKSUM option when taking backup of databases or there is no option of verifying backups using CHECKSUM.
There is not much logic you can implement when dealing with index fragmentation and once you configure it, they run regardless if you really need a reorg or rebuild on your indexes... and many more :-)

To address your issue, you need to check sys.databases where state_desc = 'ONLINE' and adjust your scripts.

Also, you can have a script that detects failover and disables the jobs on secondary server.

Gentle Note: Extended support for SQL Server 2005 ends on April 12, 2016


Can syslog be configured for Oracle Database Cloud Service? Short Version:
If I create a Cloud Database (on Oracle Database Cloud Service) -

1. Can it log to Syslog?
 2. Can I set where that syslog is located
    (i.e., at which host:port)?

Long Version:
If an Oracle Database runs on a machine, then I can direct its logs (e.g., audit logs) to either a non-default directory, or to a centralised syslog on another machine.

How can I do the same with DBaaS like Oracle Database Cloud Service?

You can see Syslog using root user. In Oracle cloud machine you cannot login as root directly. But you can login as OPC user and after that switch yourself as root using the sudo command. SYSLOG is located in /var/log/messages. If you installed Oracle management cloud then you can get GUI called "Oracle Log Analytics interface". You can check from this interface.


Stored procedure performs worse than straight query I have a stored proc that takes 2 dates as parameters. The dates are used to specify a range of dates for the first in a series of CTEs to limit the scope.

When I run the query in SSMS as a query, it runs in 15 seconds and the query plan shows 81% on a sort.

When I make it a proc with the two date variables as parameters, it runs 19 MINUTES. 46% is on a clustered index scan.

Removing the parameters from the stored proc and adding them as variables inside the proc, it's back to 15 seconds and 81% on a sort.

Can someone explain to me why this would be?

This might be a case of parameter sniffing.

You could try simply defining two local date variables at the top of your stored procedure, and assign them the value of the two input date parameters. Then replace the usage of the input parameters in the stored procedure code with those new local variables.

This has usually done the trick for me in the past, when dealing with parameter sniffing.


MySQL memory usage issues? I have a MySQL instance running on my Ubuntu server that is eating up most of the memory on the machine.  I currently have 2 GB of ram installed on the machine, and MySQL is using 1983 MB.

Most of my experience is with SQL Development so I'm a little unsure about how to go about freeing up memory.  So far I've tried running FLUSH TABLES; but that didn't really do anything.  I also ran some diagnostic queries, but am not sure what to make of them:

SHOW ENGINE INNODB STATUS;

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 22114338; in additional pool allocated 1048576
Dictionary memory allocated 1061936
Buffer pool size   512
Free buffers       0
Database pages     510
Modified db pages  33
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 12944231, created 26985, written 3625935
2.17 reads/s, 0.00 creates/s, 0.00 writes/s
Buffer pool hit rate 976 / 1000

select sum(data_length+index_length) from information_schema.tables where engine='memory';
+-------------------------------+
| sum(data_length+index_length) |
+-------------------------------+
|                             0 |
+-------------------------------+

Can anyone make any suggestions about how to free up memory and prevent MySQL from eating up all memory on the server with the information I have provided?  If I haven't provided enough info here, any suggestions on what I should look at next to diagnose the problem?

UPDATE:

SHOW STATUS LIKE 'threads%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 0     |
| Threads_connected | 31    |
| Threads_created   | 38    |
| Threads_running   | 1     |
+-------------------+-------+

and

SHOW VARIABLES LIKE 'thread_cache_size';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| thread_cache_size | 8     |
+-------------------+-------+

MySQL Community Manager Morgan Tocker posted a blog earlier this year on how to reduce memory consumption in MySQL.

http://www.tocker.ca/2014/03/10/configuring-mysql-to-use-minimal-memory.html

Some of the settings he uses are not really realistic for a production instance of MySQL, because the settings are far below what it takes to give good performance. But his blog was an exercise, not a recommendation. Anyway, it can show you many of the settings that do have an effect on memory use.

Your server with only 2GB of RAM is pretty undersized for a MySQL server. Consider this survey of MySQL users, asking how much RAM they have in their servers. The top answers were 16-64 GB and 4-16 GB.

http://www.mysqlperformanceblog.com/2012/11/02/how-much-memory-do-you-use-to-run-mysql/


MySQL: trying to create root user Upgrading MySQL database to 5.5 and it seems that the root user has disappeared.

I'm trying to create it again using this commands:

javier@javier-mbp:~$ sudo pkill mysqld
[sudo] password for javier:
javier@javier-mbp:~$ sudo /usr/local/mysql/bin/mysqld_safe --skip-grant-tables&
[1] 1927
javier@javier-mbp:~$ 111028 16:37:44 mysqld_safe Logging to '/var/log/mysql/error.log'.
111028 16:37:44 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data

Then I do:

javier@javier-mbp:~$ mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.5.17-log MySQL Community Server (GPL)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> GRANT CREATE, DROP ON *.* TO root@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;
ERROR 1290 (HY000): The MySQL server is running with the --skip-grant-tables option so it cannot execute this statement

Any help?

EDIT: Solved, well… I just should do a backup of the user root since I'm removing the database before upgrading to 5.5.

When you start mysql with --skip-grant-tables, you cannot use GRANT/REVOKE commands. You can INSERT directly into the mysql.user table.

Whereas this command cannot work using --skip-grant-tables:

GRANT CREATE, DROP ON *.* TO root@localhost IDENTIFIED BY '123456' WITH GRANT OPTION;

This command should be run instead:

REPLACE INTO mysql.user SET
user='root',
host='localhost',
password=PASSWORD('123456'),
create_priv='Y',
drop_priv='Y',
grant_priv='Y';

and restart mysql.

You may also want to backup your grants from the previous mysql installation as SQL commands.


SQL Server: multiple failover cluster instances with disjoint storage This is for SQL Server 2012. I currently have two datacenters that have separate SANs. I'm using failover cluster instances (FCIs) for server redundancy, and then AlwaysOn Availability Groups (AGs) for datacenter redundancy. This is what it looks like:

The problem is that I'd like FCIs at both datacenters, like so:

When we try to add the storage for Nodes3&4 we can't because the storage is not shared with Nodes1&2. At Add Storage to a Failover Cluster it says:

The disk must be available to all nodes in the cluster before you can add it to the set of storage for the cluster.

It is my understanding that Nodes1,2,3,4 must all be in the same Windows Failover Cluster in order to use AGs. However, this requirement seems to prevent us from being able to setup FCIs in both datacenters.

Is there any way to have my cake and eat it to?

I've found the answer to this problem. It is possible, but you either need Windows 2008R2 SP1 or higher, or a hotfix. The full details are at:

AlwaysOn Architecture Guide: Building a High Availability and Disaster Recovery Solution by Using Failover Cluster Instances and Availability Groups

To quote a line from this article: This arrangement of storage, where a cluster disk is shared between a subset of nodes within a WSFC, is referred to as asymmetric storage


Error Code 1064, Cant figure this out CREATE TABLE 'mydb.nation' (
'natcode' CHAR(3),
'natname' VARCHAR(45) NULL,
'exchange_rate' DECIMAL(9,5) NULL,
PRIMARY KEY ('natcode'));

CREATE TABLE 'mydb.stock' (
'stckcode' CHAR(3),
'stckname' VARCHAR(45) NULL,
'stckprice' DECIMAL(9,5) NULL,
'stckqty' DECIMAL(9,5) NULL,
'stckdiv' DECIMAL(9,5) NULL,
'stckPE' DECIMAL(9,5) NULL,
'natcode' CHAR(3) NOT NULL,
PRIMARY KEY('natcode'),
CONSTRAINT 'natcode'
    FOREIGN KEY('natcode')
    REFERENCES'mydb'.'nation'('natcode')
    ON DELETE RESTRICT
    ON UPDATE NO ACTION);

INSERT INTO 'mydb.nation' ('natcode','natname','exchange_rate') VALUES ('AUS','Australia','0.46'),
INSERT INTO 'mydb.nation' ('natcode','natname','exchange_rate') VALUES ('IND','India','0.0228'),
INSERT INTO 'mydb.nation' ('natcode','natname','exchange_rate') VALUES ('UK','United Kingdom','1'),
INSERT INTO 'mydb.nation' ('natcode','natname','exchange_rate') VALUES ('USA','United States','0.67'),

You will need to respect MySQL syntax, here's something that passes the muster of DB Fiddle  (MySQL 5.7).  See https://www.db-fiddle.com/f/kMfBNBx9WRe65cwWyRmUMn/0

CREATE TABLE nation (
natcode CHAR(3),
natname VARCHAR(45) NULL,
exchange_rate DECIMAL(9,5) NULL,
PRIMARY KEY (natcode));

CREATE TABLE stock (
stckcode CHAR(3),
stckname VARCHAR(45) NULL,
stckprice DECIMAL(9,5) NULL,
stckqty DECIMAL(9,5) NULL,
stckdiv DECIMAL(9,5) NULL,
stckPE DECIMAL(9,5) NULL,
natcode CHAR(3) NOT NULL,
PRIMARY KEY(natcode),
CONSTRAINT natcode
    FOREIGN KEY(natcode)
    REFERENCES nation(natcode)
    ON DELETE RESTRICT
    ON UPDATE NO ACTION);

INSERT INTO nation (natcode,natname,exchange_rate) VALUES
  ('AUS','Australia','0.46'),
  ('IND','India','0.0228'),
  ('UK','United Kingdom','1'),
  ('USA','United States','0.67');


Constraint spanning several tables Imagine the following tables:

Products (Id, Name, ...)

Used for storing products

ProductVersions (Id, Version, ReleasedOn)

Used for storing a version history of a single product (v1.0, v2.0, v3.0, ...)

Customers (Id, ForeName, ...)

Well, yeah. Used for storing customers.

Now a single customer can have multiple products. That requires another table:

CustomerProducts (Id, CustomerId, ???)

What is the best approach here? Should I reference  to the Products table (ProductId) or the ProductVersions table (ProductVersionId)?

In terms of joining I'd have access to the Product entity when querying over the ProductVersion of a customer (Customer -> ProductVersion -> Product)

I know it is dependant on my very own design (so if a user owns a product or a version of a product), but what's the best way in general? What if a user can own an entire Product (Product1), but only owns Version 3.0 of Product2?

On product what you have is a Type 2, Slowly Changing Dimension.

A More in-depth discussion on Slowly Changing Dimensions here

A model for this would look something like the following:

Customer stays as is.

Product has a hierarchy (self referencing to the parent [previous version]
Product has a different ProductId for each version.

You did not mention whether multiple versions are valid at the same time.
Let us assume not.

Valid_From:Valid_To are one way to identify when a product is valid. The 'current' product version has the valid_from set, but valid_to NULL.

When it is time to move to the next 'Version':

Update the current records Valid_To date timestamp to now()
Get the next ProductId (nextval on a sequence ususally)
Get the next Version number (derived from the current version
number)
Insert new record into Product, valid_From to now(), use new version
number, reference the old parentId in the Parent_prodid
Commit

You will get new ProductIds for each version, but you will be able to give details of the product/customer relationship over time.

'ownership' of a product may be assigned at any version number. You can cascade the ownership from by maintaining the appropriate CUST_PRODUCT entries, they can be singular (for a specific version) or multiple for all or a subset of the versions.

You can find all versions by cascading thru the self referencing structure until you get the first (oldest, smallest version number, ...)


Insert new rows when item is added to another table SQL I have to tables. One dimension table with projects and one fact table with projects, value and a period(month).
Looking to add 12 new rows pr project ID into my fact table, when a new project is added to the dimension table. The 12 rows should be the next 12 months. (always the month following the current)
The issue I have is how to write a SQL statement that I can use with a trigger to make this work. I have tried some versions on INSERT INTO, but I'm not experienced enough to know have to write this statement.
example: project 2 has been added to dim table.

project
status

1
A

2
B

Then create a trigger that adds these 12 new rows in my facttable

project
status
Period
Value

2
B
6/1/2021
NULL

2
B
7/1/2021
NULL

2
B
8/1/2021
NULL

2
B
9/1/2021
NULL

2
B
10/1/2021
NULL

2
B
11/1/2021
NULL

2
B
12/1/2021
NULL

2
B
1/1/2022
NULL

2
B
2/1/2022
NULL

2
B
3/1/2022
NULL

2
B
4/1/2022
NULL

2
B
5/1/2022
NULL

Any suggestions on how to write this statement?
Thank you so much!

If you want to avoid a trigger you can use the OUTPUT clause and a table variable:
drop table #pPeriod
drop table #dimProject
drop table #factProject

create table #dimProject (project int, status varchar(10))
declare @out table (project int, status varchar(10))
create table #factProject ( project int, status varchar(10),
                            pPeriod datetime2, vValue varchar(10))
create table #pPeriod( pPeriod datetime2)

-- you will need more rows here
insert #pPeriod values ('20210501'),('20210601'),('20210701'),('20210801'),
                       ('20210901'),('20211001'),('20211101'),('20211201')

insert into #dimProject (project, status)
output
  inserted.project,
  inserted.status
into @out
(
  project
, status
)
values (1, 'A')

insert #factProject (project, status, pPeriod)
select o.project, o.status, p.pperiod
from @out o
cross join #pPeriod p
-- add a filter to join to todays date and fetch 12 months forward

select * from #dimProject

select * from #factProject


Importing MySQL database using Linux Terminal I have an existing database, named dlti.sql

I want to transfer it to a Linux-based server. I tried using this code, however I get no response:

mysql -u root -p Desktop < dlti.sql

The .sql file is located at the desktop.

Can anyone help me with this?

You have to add the target database name.

$mysql -u root -p -h localhost

mysql> create database foo;
mysql> exit;

$mysql -u username -p -h localhost DATA-BASE-NAME < data.sql


How to use SELECT statement to get data from SQlite table using with dictionary values? I have below dictionary values.
Example:
dic = {'ID1': ['Name1', 'Name2'],
       'ID2': ['Name1', 'Name3'],
       'ID3': ['Name2']}

I want to print address of every matched names from above dict values into below format.
Example Output:
['ID1', 'adress1' ,'adress2'],
['ID2', 'adress1' ,'adress3'],
['ID3', 'adress2']

Below is my example code of database table that has "Emp_ID", "Emp_Name", "Emp_Address". The dict values are "Emp_Names", that should be match emp_Names and get the Adresses in to the list as Example out.
import sqlite3

conn=sqlite3.connect('emp.db')
conn.execute("""CREATE TABLE IF NOT EXISTS branch
        (Emp_ID TEXT UNIQUE,
    Emp_Name    TEXT UNIQUE,
    Emp_Address TEXT
    )""")

conn.execute("""INSERT OR IGNORE INTO branch(Emp_ID, Emp_Name, Emp_Address) \
    VALUES('Emp1', 'Name1', 'adress1')""")

conn.execute("""INSERT OR IGNORE INTO branch(Emp_ID, Emp_Name, Emp_Address) \
    VALUES('Emp2', 'Name2', 'adress2')""")

conn.execute("""INSERT OR IGNORE INTO branch(Emp_ID, Emp_Name, Emp_Address) \
    VALUES('Emp3', 'Name3', 'adress3')""")

conn.execute("""INSERT OR IGNORE INTO branch(Emp_ID, Emp_Name, Emp_Address) \
    VALUES('Emp4', 'Name4', 'adress4')""")

conn.execute("""INSERT OR IGNORE INTO branch(Emp_ID, Emp_Name, Emp_Address) \
    VALUES('Emp5', 'Name5', 'adress5')""")

conn.commit()
conn.close()

For example 'ID1' (column) should not to be changed.
And 'name1' should take its address and 'name2'should take its address and all matched names should take their addresses. Which statements are useful for my scenario. Is it possible? If possible how to to?

Have you tried implementing row factory? https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.row_factory
More details here: https://xbuba.com/questions/3300464/how-can-i-get-dict-from-sqlite-query


Determine if existing Trigger acts on field DB2 I am eventually going to be writing a trigger on a field named "user2". Currently most of the values in this field are blank, but there are some seemingly random rows with values of '00' for the field.

What I was wondering, is there anyway to determine if there is an existing trigger that is adding these values to the field? If not, what are some methods I can use to figure out how this data is getting there?

Thanks,

Andrew

If you are asking if you can determine if the table in question has any triggers defined, then yes - you can either

Look at the view SYSCAT.TRIGGERS, filtering on the TABSCHEMA and TABNAME columns
Extract the DDL for the table using the db2look command.

If you are asking if there's any way to determine whether existing rows that have a value were updated as a result of an existing trigger, then the answer is no.  There is nothing inherent to triggers that would allow you to identify them as the source of the data in the column.  The trigger could contain some logic to identify itself as the source of the value, but that would be stored in some other column or table.


MySQL Workbench query tabs I am trying MySQL Workbench 6.0 and when I execute a query the results are displayed with a tab called "Result 1". When I fire another query the same tab is reused, now named "Results 2".

I found no way to open a new results tab (not query tab). What am I missing?

MySQL Workbench always closes all existing resultset tabs when it executes the content in a specific tab. If you have several queries in your editor and execute all of them (and they return a result set) then a new resultset tab is created for each returned set.

If you want a specific resultset tab to stay while running other queries open it in a separate editor tab.


Cannot start XAMPP MySQL InnoDB Please help..

I have website running on XAMPP Apache and MySQL (v3.1.0) with Windows Server 2003, it works fine for almost 5 year. I often restart the MySQL service. But today when I stopped the MySQL service and tried to start, it nothing happen, MySQL service still stopped.

When I saw the log, it seems failed to initialize buffer pool. Below snippet of the log

171214 10:13:06 [Note] Plugin 'FEDERATED' is disabled.
171214 10:13:06 InnoDB: The InnoDB memory heap is disabled
171214 10:13:06 InnoDB: Mutexes and rw_locks use Windows interlocked functions
171214 10:13:06 InnoDB: Compressed tables use zlib 1.2.3
171214 10:13:06 InnoDB: Initializing buffer pool, size = 171214 10:14:28 [Note] Plugin 'FEDERATED' is disabled.
171214 10:14:28 InnoDB: The InnoDB memory heap is disabled
171214 10:14:28 InnoDB: Mutexes and rw_locks use Windows interlocked functions
171214 10:14:28 InnoDB: Compressed tables use zlib 1.2.3
171214 10:14:28 InnoDB: Initializing buffer pool, size = 171214 10:17:32 [Note] Plugin 'FEDERATED' is disabled.
171214 10:17:32 InnoDB: The InnoDB memory heap is disabled
171214 10:17:32 InnoDB: Mutexes and rw_locks use Windows interlocked functions
171214 10:17:32 InnoDB: Compressed tables use zlib 1.2.3
171214 10:17:32 InnoDB: Initializing buffer pool, size = 171214 10:23:24 [Note] Plugin 'FEDERATED' is disabled.

Below are snippet log for success mysql startup

171213 13:39:47 [Note] Plugin 'FEDERATED' is disabled.
171213 13:39:47 InnoDB: The InnoDB memory heap is disabled
171213 13:39:47 InnoDB: Mutexes and rw_locks use Windows interlocked functions
171213 13:39:47 InnoDB: Compressed tables use zlib 1.2.3
171213 13:39:47 InnoDB: Initializing buffer pool, size = 1.0G
171213 13:39:48 InnoDB: Completed initialization of buffer pool
171213 13:39:48 InnoDB: highest supported file format is Barracuda.
171213 13:39:49  InnoDB: Waiting for the background threads to start
171213 13:39:50 InnoDB: 1.1.8 started; log sequence number 197337651912
171213 13:39:50 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
171213 13:39:50 [Note]   - '0.0.0.0' resolves to '0.0.0.0';
171213 13:39:50 [Note] Server socket created on IP: '0.0.0.0'.
171213 13:39:50 [Note] Event Scheduler: Loaded 0 events
171213 13:39:50 [Note] d:\xampp\mysql\bin\mysqld.exe: ready for connections.
Version: '5.5.27-log'  socket: ''  port: 3306  MySQL Community Server (GPL)

(from Comment)

[mysqld]
port    = 3306
socket  = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
max_connections = 500
key_buffer = 256M
table_cache = 256M
slow-query-log=1
slow-query-log-file=D:\TABLETEMP\my.slow.log
long_query_time=1
log_error="mysql_error.log"

171214 is terribly small.  Change my.cnf to set it to about 70% of available RAM.  Example, for a 16GB system, use 11G.  If that antique server is only 2GB, use 500M.

table_cache = 256M is terribly big -- the units is tables, not bytes.  Furthermore, in newer versions, the name is table_open_cache, so:

table_open_cache = 500

(How did the date get there?  171214 = Dec 14, 2017)

Or is the field left blank??

(From Comment, plus annotations)

 [mysqld]
 port   = 3306
 socket = /tmp/mysql.sock
 skip-external-locking
 key_buffer_size = 256M     -- Change to 50M
 max_allowed_packet = 1M
 table_open_cache = 64      -- let's change to 200
 sort_buffer_size = 512K
 net_buffer_length = 8K
 read_buffer_size = 256K
 read_rnd_buffer_size = 512K
 myisam_sort_buffer_size = 8M
 max_connections = 500    -- 100, until you have a good reason for more
 key_buffer = 256M      -- Remove, old spelling
 table_cache = 256M     -- Remove; this is an old spelling
 slow-query-log=1
 slow-query-log-file=D:\TABLETEMP\my.slow.log
 long_query_time=1       -- good
 log_error="mysql_error.log"

 innodb_buffer_pool_size = 1000M  -- compromise between 'safe' and 'useful'


SQL Server - NTEXT columns and string manipulation I have a table with an NTEXT column called comments. I have a second string, let's call it anothercomment (a varchar) that needs placing inside a given comments string after the word UPDATEHERE.

Casting to nvarchar(max) truncates the comments string, so I cannot use the likes of CHARINDEX() (Msg 8152, Level 16, State 10, Line 2
String or binary data would be truncated.). I have used datalength() to check that there are a few thousand columns that are >8000 characters.

An example of what I want to achieve (albeit with much longer strings):

  comments - This is a test UPDATEHERE This is the end of the test

  anothercomment - . This is inserted.

  Resulting string -  This is a test UPDATEHERE. This is inserted.  This is the end of the test

I realize that this is trivial with a normal varchar()/nvarchar(), but ntext is a complete and utter nightmare to work with. I realize it's a deprecated data type, but I did not write the application in question.

Converting to nvarchar(max) and back to ntext does make life simpler from a code point of view, but it does mean converting and rewriting the whole (perhaps very large) value, with all the CPU and logging overhead that implies.

An alternative is to use UPDATETEXT. This is deprecated, just like ntext, but it can reduce the logging overhead significantly. On the downside, it means using text pointers, and it only operates on one row at a time.

The following example code uses a cursor to work around that limitation, and uses PATINDEX instead of CHARINDEX since the former is one of the few functions that work directly with ntext:

Sample data

CREATE TABLE dbo.PhilsTable
(
    comment ntext NULL,
    anothercomment nvarchar(50) NULL
);

INSERT dbo.PhilsTable
    (comment, anothercomment)
VALUES
(
    CONVERT(ntext,
        N'This is a test UPDATEHERE This is the end of the test ' +
            REPLICATE (CONVERT(nvarchar(max), N'x'), 1000000)),
    CONVERT(nvarchar(50), N'. This is inserted.')
),
(
    CONVERT(ntext,
        N'This is a test UPDATEHERE This is the end of the test ' +
            REPLICATE (CONVERT(nvarchar(max), N'x'), 1000000)),
    CONVERT(nvarchar(50), N'. This is inserted.')
),
(
    CONVERT(ntext,
        N'This is a test UPDATEHERE This is the end of the test ' +
            REPLICATE (CONVERT(nvarchar(max), N'x'), 1000000)),
    CONVERT(nvarchar(50), N'. This is inserted.')
);

Cursor declaration

DECLARE c
    CURSOR GLOBAL
    FORWARD_ONLY
    DYNAMIC
    SCROLL_LOCKS
    TYPE_WARNING
FOR
SELECT
    TxtPtr = TEXTPTR(PT.comment),
    Src = PT.anothercomment,
    Offset = PATINDEX(N'%UPDATEHERE%', PT.comment) + LEN(N'UPDATEHERE') - 1
FROM dbo.PhilsTable AS PT
WHERE
    PT.comment LIKE N'%UPDATEHERE%'; -- LIKE works with ntext

OPEN c;

Processing loop

DECLARE
    @Ptr binary(16),
    @Src nvarchar(50),
    @Offset integer;

SET STATISTICS XML OFF; -- No cursor fetch plans

BEGIN TRANSACTION;

    WHILE 1 = 1
    BEGIN
        FETCH c INTO @Ptr, @Src, @Offset;

        IF @@FETCH_STATUS = -2 CONTINUE; -- row missing
        IF @@FETCH_STATUS = -1 BREAK; -- no more rows

        IF 1 = TEXTVALID('dbo.PhilsTable.comment', @Ptr)
        BEGIN
            -- Modify ntext value
            UPDATETEXT dbo.PhilsTable.comment @Ptr @Offset 0 @Src;
        END;
    END;

COMMIT TRANSACTION;

CLOSE c; DEALLOCATE c;


Is it recommended to use Identity as an alternative to primary key? We can declare an Identity like id_num so that id_num will have an increment of unique numbers.

CREATE TABLE new_employees
(
  id_num int IDENTITY(1,1),
  fname varchar (20),
  minit char(1),
  lname varchar(30)
)

Is it recommended to use Identity as a alternative to Primary key since Identity provided a unique number for each row?

Identity columns and Primary Keys are two very distinct things.  An Identity column provides an auto-incrementing number.  That's all it does.  The Primary Key (at least in SQL Server) is a unique constraint that guarantees uniqueness and is usually (but not always) the clustered key.  Again in MS SQL Server it is also an index (in some RDBMS they are not as closely tied).  As an index it provides faster lookups etc.  Frequently Identity columns are used as the Primary Key if no good natural key exists, but are not a substitute.


Any way of forcing MySQL to use smaller files? This is actually Maria DB.  I'm using it on W10 and Linux Mint 18.3.

The issue here is that I recently strated to use Google Backup & Sync to sync between my various machines running W10 (I'm also trying to get rclone in Linux to do the same thing).

The problem is that the main files used by MariaDB/MySQL are rather big:  ibdata1 (currently 77MB), ib_logfile0 (currently 51 MB) and ib_logfile1 (currently 51 MB).

Each time I make even a minimal change to some data on one machine it takes a lot of time for this to propagate to the other machines synched with the same Gmail Drive account.  This is because each time (apparently) the new versions of all three files have to be laboriously uploaded to the Cloud and then laboriously downloaded to the other machines.

Are there any solutions to this to make the updating between machines quicker? I just spotted the tag (here) "Google-cloud-sql" ... could this be an answer???

Don't use a generic file-based backup and sync mechanism against a database.

Those files you mentioned will almost always change, perhaps even if you do nothing.  The two log files are always the same size, but they do not have to be that big (unless you have big LONGTEXT or LONGBLOB columns).  But changing there size is tricky, and version-dependent.

Consider "Replication" ("replicas", "slaves") as an alternative way to get redundancy, but there could be issues with writing to both copies.

Galera clustering (an add-on to MySQL; included with MariaDB) is a way to have multiples copies of a dataset continually maintained in sync, and all being writable.  Also, it can be self-healing.

MariaDB is a "client-server" app.  Your "client" can be anywhere in the world; you can have any number of clients active at the same time.  The "server" can be hosted on your machine or in the cloud.  It sounds like you want it to be hosted in the cloud and 'never' go down.  That is the normal way of doing things these days.  There is no need to clone the server's data (except as a backup).  Cloud services take care of backing up (via cloning or dumping or whatever) without you taking action.


insert all record into table1 from table2 using where like clause I have 2 tables

Table1

ID - Metrics - t000
1 - chattervolume - BTC0908473
2 - negsent - NULL

Table2

ID - Metrics - t000

I want to insert Table1 data to Table2 using WHERE LIKE t000 '%BTC%' clause and If any column of field t000 not found anything in LIKE clause then it inserts N/A in column

If you want all rows from table1 to be inserted into table2 (and only some modifications in column t000) then you can't use WHERE. You could use a CASE expression:

INSERT INTO table2
  ( ID, Metrics, t000 )
SELECT
    ID, Metrics, CASE WHEN t000 LIKE '%BTC%' THEN t000 ELSE 'N/A' END
FROM
    table1 ;

If you want only rows that have t000 LIKE '%BTC%' or t1000 is NULL then yes, you need a WHERE clause::

INSERT INTO table2
  ( ID, Metrics, t000 )
SELECT
    ID, Metrics, COALESCE(t000, 'N/A')
FROM
    table1
WHERE
  ( t000 LIKE '%BTC%' OR t1000 IS NULL ) ;


unpivot 2 columns into one column I have tables like this:

ID|ServiceName|ServiceCode|Age      |Gender|Number|TotalCost|Location
0 | service1  |  0000234  |under 12 | Male | 121  |1234     |loc1

I want to change the table like this:

ID|ServiceName|ServiceCode|Age      |Gender|Measure    |Value |Location
0 | service1  |  0000234  |under 12 | Male | Number    |121     |loc1
1 | service1  |  0000234  |under 12 | Male | TotalCost |1234    |loc1

How can I do that?

You can use two SELECTs, one for each Measure.

create table tbl2 (ID int identity, ServiceName varchar(100), ServiceCode varchar(100),
                 Age varchar(100), Gender varchar(100),
                 Measure varchar(100), [Value] int, Location varchar(100));

insert into tbl2 (ServiceName, ServiceCode, Age, Gender, Measure, Value, Location)
select ServiceName, ServiceCode, Age, Gender, 'Number', [Number], location
from tbl
union
select ServiceName, ServiceCode, Age, Gender, 'TotalCost', [TotalCost], location
from tbl

select * from tbl2;
GO

ID | ServiceName | ServiceCode | Age      | Gender | Measure   | Value | Location
-: | :---------- | :---------- | :------- | :----- | :-------- | ----: | :-------
 1 | service1    | 0000234     | under 12 | Male   | Number    |   121 | loc1
 2 | service1    | 0000234     | under 12 | Male   | TotalCost |  1234 | loc1

db<>fiddle here


BACKUP AND RESTORE FileGroup (PRIMARY) - WHY after restore I still see Other FileGroups? /********************************** CREATE DATABASE PiecemealDB *************************************/
use [master]
go
if exists (select * from sys.databases where database_id = DB_ID('PiecemealDB'))
    drop database [PiecemealDB]
go
CREATE DATABASE [PiecemealDB]
    CONTAINMENT = NONE
    ON  PRIMARY
( NAME = N'PiecemealDB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PiecemealDB.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB )
    LOG ON
( NAME = N'PiecemealDB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PiecemealDB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
    COLLATE Latin1_General_100_CI_AS
GO

USE PiecemealDB
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY')
ALTER DATABASE [PiecemealDB] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
-----------------------------------------------------------------------------------------------------
use PiecemealDB
go
/* Creating table in PRIMARY FG & init data*/
if OBJECT_ID('dbo.MainFG') is not null
    drop table dbo.MainFG;
go
create table dbo.MainFG (Col1 int identity(1,1),Col2 varchar(100)) ON [PRIMARY];
go

insert into dbo.MainFG(Col2)values ('Primary FG')
go 100
/*******************************ADDING NEW FileGroup [ReadOnlyFG] ***********************************/
USE [master]
GO
ALTER DATABASE [PiecemealDB] ADD FILEGROUP [ReadOnlyFG]
GO
ALTER DATABASE [PiecemealDB] ADD FILE
    ( NAME = N'ReadOnlyDF_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\ReadOnlyDF_Data.ndf' , SIZE = 4096KB , FILEGROWTH = 1024KB ) TO FILEGROUP [ReadOnlyFG]
GO
/******************************* create table under the new filegroup [ReadOnlyFG] ******************/
use PiecemealDB
go
if OBJECT_ID('dbo.ReadOnlyFG') is not null
    drop table dbo.ReadOnlyFG;
go
create table dbo.ReadOnlyFG (Col1 int identity(1,1),Col2 varchar(100)) ON [ReadOnlyFG];
go
/*************** Insert data into table under FileGroup [ReadOnlyFG] *******************************/
use PiecemealDB
go
insert into dbo.ReadOnlyFG(Col2)values ('ReadOnlyFG FG')
go 100
/********************************** SET ReadOnlyFG FileGroup to READ ONLY **************************/
USE [PiecemealDB]
GO
ALTER DATABASE [PiecemealDB] MODIFY FILEGROUP [ReadOnlyFG] READONLY
GO
/********************************** BACKUP PRIMARY FILEGROUP **************************************/
BACKUP DATABASE [PiecemealDB]
    FILEGROUP = N'PRIMARY' TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\PiecemealDB.bak'
    WITH NOFORMAT, NOINIT,  NAME = N'PiecemealDB-Full Filegroup Backup',
    SKIP, NOREWIND, NOUNLOAD, COMPRESSION,  STATS = 10
GO
/********************************** RESTORE FILEGROUP PRIMARY  TO [PiecemealDB_NEW] **********************************/
use [master]
go

RESTORE DATABASE [PiecemealDB_NEW] READ_WRITE_FILEGROUPS
FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\PiecemealDB.bak' WITH  FILE = 2, RECOVERY, PARTIAL,
MOVE N'PiecemealDB' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PiecemealDB_NEW.mdf',
MOVE N'PiecemealDB_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\PiecemealDB_NEW_1.ldf',
NOUNLOAD,  REPLACE,  STATS = 10
GO

** After you exec this batch,Go to PiecemealDB_NEW properties >> Filegroup TAB :

Question: How can I drop/delete the Other Filegroup [ReadOnlyFG].
1. Is there any way to restore only the PRIMARY filegroup , without the ReadOnlyFG ?.
2. If not, How can I get rid of the ReadOnlyFG after the restore ?.

10X

Is there any way to restore only the [PRIMARY] filegroup?

That is what you have already done. The other filegroups exist only in metadata at this stage. Piecemeal restore allows you to restore filegroups in stages, or mark them defunct if they are no longer needed. There is no way to delete them from the system metadata though.

After the restore operation in the question, the data on the secondary filegroup is not accessible, because that filegroup has not been restored (yet). You can't see everything in the SSMS GUI only. For example:

SELECT
    [FilegroupName] = F.name,
    F.data_space_id,
    F.is_default,
    F.is_read_only,
    DF.[file_id],
    [FileName] = DF.name,
    DF.state_desc,
    DF.is_read_only
FROM sys.filegroups AS F
JOIN sys.database_files AS DF
    ON DF.data_space_id = F.data_space_id;

The results show recovery is pending for the secondary filegroup files:

  If not, How can I get rid of the ReadOnlyFG after the restore?

You can't delete it from the metadata, but you can make it defunct by dropping it (something that is not normally possible for a filegroup that still contains files):

ALTER DATABASE PiecemealDB_NEW
REMOVE FILEGROUP ReadOnlyFG;

The query above now shows the filegroup as defunct rather than recovery pending:

Objects that were stored on the defunct filegroup cannot be accessed, modified, or dropped. The only way to get to a database state as if the defunct filegroup had never existed is to create a new database and copy the data you want to keep across to it.


Scrubbing Names via SQL Query/Batch We have sensitive information (names of people) and we're looking to put into place a script to "scrub" of the names.  Has anybody come up with a good algorithm to do this, something other than just making all of the last names "TEST" or something?

Thanks.

I had to do this for NPI (non-public information) reasons at a previous employer.  Ended up scrambling fields in a script, since a first name / last name pair isn't NPI unless it's tied to something else.

Here's a rough version of the script.  Season to taste.  Also, this assumes you have first and last names in separate fields.

CREATE TABLE #RandomData
(
    DataID int not null,
    ID int not null,
    FirstName varchar(50),
    LastName varchar(50)
)

INSERT INTO #RandomData (ID, DataID)
SELECT ROW_NUMBER() OVER (ORDER BY NEWID()), DataId FROM DataTable

UPDATE r SET r.FirstName = x.FirstName
FROM #RandomData r
INNER JOIN (
    SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS ID, FirstName FROM DataTable
) x
    ON x.ID = r.ID

UPDATE r SET r.LastName = x.LastName
FROM #RandomData r
INNER JOIN (
    SELECT ROW_NUMBER() OVER (ORDER BY NEWID()) AS ID, LastName FROM DataTable
) x
    ON x.ID = r.ID

CREATE CLUSTERED INDEX PK_RandomData ON #RandomData
(
    DataID asc
)

ALTER TABLE DataTable DISABLE TRIGGER ALL

UPDATE dt
    SET dt.FirstName = r.FirstName,
    dt.LastName = r.LastName
FROM DataTable dt
INNER JOIN #RandomData r
    ON dt.DataId = r.DataId

ALTER TABLE DataTable ENABLE TRIGGER ALL

DROP TABLE #RandomData


TempDB file overflow This is my experience with TempDB. The Algorithm works better when
the TempDB File sizes are the same size.
We were seeing heavy TempDB contention on a TempDB file that was larger than the others. We needed that file larger in the case of run away queries (caused by people not logging off their machines properly - not the SQL Server) which
occasionally caused TempDB to fill.
We switched to 16 TempDB files (16 CPU's) on RamDisk all the same size with No Autogrowth.
We setup a job to kill the spid using the most TempDB resources, once TempDB was 80% full.
In working with Microsoft, their technician tested if a TempDB file was created on one TempDB file it would overflow to a second TermpDB file if it needed too (I was concerned about this).
Our SQL Server is a Read-Only AlwaysOn and we run some pretty big reports
that use large amounts of TempDB. Our new TempDB design has been in production for over 6 months, with no issues. Occasionally, we have a spid cancelled due to TempDB over 80% full, but not very often.
The question
Curious, is there some SQL code I can use to quickly determine if files are crossing multiple TempDB files? We are on SQL Server 2017 CU 12.

Jonathan's blog post describes quite nicely that the tables are striped across the data files in a filegroup and written to in a consistent manner.

Where the files are the same size and starting from empty, you should see the same amount of data for the table across each file.

You should be able to use the same xEvents scripts to see this for your own system.


gain admin access to sql express 2008 I have inherited a Windows 7 Professional pc with an existing instance of SQL Express 2008. I have local admin rights on the machine. I do not know the SA password. I am unable to create databases or logins, or modify the SA login's password. I would like to grant my domain account SYSAdmin privileges.

I have tried the following, in both situations I right clicked and ran SQL management studio as Administrator, and used Windows authentication to log in to the local SQL express.

I created a local admin account, with administrator privileges and logged in to my PC as that.
I have logged to my PC as a Domain Admin.

I understand that if I log in as an administrator using windows authentication I should have full privileges, what can can be stopping me from getting this access?

SQL Server manages its own security. Just because you have local Windows administrator rights doesn't automatically grant you access.

That said, there are two approaches to get back in, using local administrative rights:

Impersonate the NT AUTHORITY\SYSTEM account. There are two methods to do this, as I blogged about here. These methods allow you to leave the server running while recovering access. If there is no login for that system account, you have to use the second method (which is easier, and if you don't care about downtime, the one you should use anyway because it's safer).
Restart the database engine service in single user mode, which does grant local administrators sysadmin rights over the instance. Do what you need to do, then restart the instance in multi-user mode. This is the backdoor which was created precisely for the situation you're in; however, you do have to interrupt service to do it.


How to parse MongoDB int32 in node.js I am using MongoDB for a discord bot I am working on, and I want to retrieve a user's balance. My current code is
function findBalance(id){ MongoClient.connect(url, function(err, db) { if (err) throw err; var dbo = db.db("DiscordDB"); var query = { uuid: id }; dbo.collection("players").find(query).toArray(function(err, result) { if (err) throw err; console.log(result.balance); db.close(); }); }); }. When I call the function it returns undefined... any thoughts?

Note the db.collection("players").find(query).toArray() returns an array of documents - these are available as result array. Your code console.log(result.balance) returned undefined - as you are looking for a property called balance in the array. You could do that on an object (or document) which has a property with that name - not on the array.
Retrieve an individual property from an array of objects - do the following to print each document's balance property value:
result.forEach(doc => console.log(doc.balance));

Here is your function's code in a slightly different way:
const MongoClient = require('mongodb').MongoClient;
const url = 'mongodb://localhost:27017';

MongoClient.connect(url, { useUnifiedTopology: true }, function(err, client) {
    if (err) throw err;
    console.log("Connected successfully to server");
    const db = client.db("DiscordDB");
    const id = "some value"; // input value

    findBalance(db, id, function(result) {
        console.log(JSON.stringify(result));
        result.forEach(doc => console.log(doc.balance));
        console.log("Closing connection.");
        client.close();
    });
});

const findBalance = function(db, id, callback) {
    const query = { uuid: id };
    db.collection("players")
        .find(query)
        .toArray(function(err, result) {
            if (err) throw err;
            console.log("Query ran okay");
        callback(result);
    });
};


SQL Server 2008: What are the English Word Breakers? I'm trying to replicate what SQL Server 2008 is doing when generating full-text indexes, but I'm having a hard time finding a list of which characters SQL Server 2008 considers to be word breakers.

Is there a list out there specifying which characters SQL Server 2008 will break words on for full-text indexes for English?

The per language noise list (also referred to as stop list) and thesaurus files are in the MSSQL/FTData/ folder. The noise files are plain text, the thesaurus XML, so both can be inspected or changed easily.

Configuring Full-Text Linguistic Components in BOL has the details.


Database Backup - In Process - Power Outage I am using SQL Server 2008 R2.
What happens if a database backup (Full/Differential/Transaction Log) is in process and there is a sudden power outage or the system crashes? Does that corrupt the backup set?

If so, then what are the available options so that if and when a database restore needs to be done I don't end up with a corrupted backup set.

If not, then what will be the status of the backup that was in process?

Obviously the backup can't be completed if it was still in progress when electricity stopped flowing. That doesn't necessarily mean it is corrupt, but it is almost certainly going to be unusable.

In the unlucky (though unlikely) event that you lose power during a full backup, and the database itself was also corrupted as a result (this is not necessarily the case; often databases will recover from a power outage just fine), then you would have to resort to the previous full backup + any diff/log backups taken in the meantime. Similar if you lose power during a log backup, you would have to resort to the last successful log backup. So your recovery plan should account for this possibility (meaning you want your log backups to happen frequently enough that they cover your tolerance for data loss).

Once the database itself is recovered and confirmed to be fine, start the backup chain over by creating a fresh full backup.

BTW this is why people have redundant power supplies and/or UPS devices. This is certainly an avoidable problem at not a very high cost.


MySQL Database Schema for Volleyball I am developing a database for the recording of Volleyball results.  I have a fixture database that tells me who plays who in each round.

Teams Table, say IDs 1,2,3,4
Fixtures Table, Round No.1, Team 1 (Team.ID) v Team 2 (Team.ID)

Results is harder and I am stuck on the schema.  Each Volleyball game is best of 5 sets, so it can be 3, 4 or 5 sets long.  I need to record Fixtures.ID, Team1.Set1, Team1.Set2, Team1.Set3, Team1.Set4 and Team1.Set5 (and same for Team 2).  Is this the best way of doing it, or should some normalisation process have an entry like
Result.ID, Fixture.ID, Result.TeamID, Result.SetNo, Result.SetScore

Like Gaius said, review the answers at How-to implement an entity with an unknown maximum number of attributes?

Rewriting for MySQL Syntax from the answer by Eric Humphrey at above link:

You could do this. It would allow good performance for normal duration games, while allowing you to also store long running games.

CREATE TABLE InningRuns (
    GameId INT NOT NULL AUTO_INCREMENT,
    Team CHAR(4) NOT NULL, --'Home','Away'
    Inning1 TINYINT, --Seeing how more than 255 runs are not really possible in an inning
    Inning2 TINYINT,
    [...],
    Inning9 TINYINT,
    ExtraInnings VARCHAR(2000), --Use to hold any runs in extra innings, store multiple values using some delimiter like comma (,), pipe (|), semicolon (;), or anything that is not part of the data.
    PRIMARY KEY (GameId, Team)
)

You could further normalize and have a row for each unique combination of game, team, & inning. This would allow you as many innings as the InningId datatype would allow.

CREATE TABLE InningRuns (
    InningRunId INT NOT NULL AUTO_INCREMENT,
    GameId INT NOT NULL,
    Team CHAR(4) NOT NULL, --'Home','Away'
    InningId TINYINT, --Seeing how more than 255 innings might be excessive
    Runs TINYINT,
    PRIMARY KEY (InningRunId),
    UNIQUE (GameId, Team, InningId)
)


Format column length in SSMS output SQL Server 2012. Sample query at the bottom of this post.
I'm trying to create a simple report for when a given database was last backed up.
When executing the sample query with output to text in SSMS, the DB_NAME column is formatted to be the max possible size for data (same issue exists in DB2, btw). So, I've got a column that contains data that is never more than, say, 12 characters, but it's stored in a varchar(128), I get 128 characters of data no matter what. RTRIM has no effect on the output.
Is there an elegant way that you know of to make the formatted column length be the max size of actual data there, rather than the max potential size of data?
I guess there exists an xp_sprintf() function, but I'm not familiar with it, and it doesn't look terribly robust.
I've tried casting it like this:
DECLARE @Servername_Length int;
SELECT  @Servername_Length =        LEN(    CAST(   SERVERPROPERTY('Servername') AS VARCHAR(MAX)    )   ) ;

...
SELECT
   CONVERT(CHAR(@Servername_Length), SERVERPROPERTY('Servername')) AS Server,
...

But then SQL Server won't let me use the variable @database_name_Length in my varchar definition when casting.  SQL Server, apparently, demands a literal number when declaring the char or varchar variable.
I'm down to building the statement in a string and using something like sp_executesql, or building a temp table with the actual column lengths I need, both of which are really a bit more trouble than I was hoping to go to just to NOT get 100 spaces in my output on a 128 character column.
Have searched the interwebs and found bupkus.
Maybe I'm searching for the wrong thing, or Google is cross with me.
It seems that SSMS will format the column to be the maximum size allowed, even if the actual data is much smaller. I was hoping for an elegant way to "fix" this without jumping through hoops. I'm using SSMS 2012.
If I go to Results To Grid and then to Excel or something similar, the trailing space is eliminated. I was hoping to basically create a report that I email, though.
Sample query
--------------------------------------------------------------------------
QUERY:
--------------------------------------------------------------------------
SELECT
   CONVERT(CHAR(32), SERVERPROPERTY('Servername')) AS Server,
   '''' + msdb.dbo.backupset.database_name + '''',
   MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date
FROM   msdb.dbo.backupmediafamily
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
WHERE  msdb..backupset.type = 'D'
GROUP BY
   msdb.dbo.backupset.database_name
ORDER BY
   msdb.dbo.backupset.database_name

You need to use CONVERT(VARCHAR(xx), ColumnName) on all columns if you want the column to appear shorter in text output view.

Convert your query into something like:

SELECT [Server] = CONVERT(VARCHAR(30), SERVERPROPERTY('Servername'))
   , DatabaseName = CONVERT(VARCHAR(30), '''' + bs.database_name + '''')
   , LastDatabaseBackupDate = CONVERT(VARCHAR(30), MAX(bs.backup_finish_date))
FROM msdb.dbo.backupmediafamily  bmf
    INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
WHERE  bs.[type] = 'D'
GROUP BY bs.database_name
ORDER BY bs.database_name;

This will give output similar to:

Server                         DatabaseName                   LastDatabaseBackupDate
------------------------------ ------------------------------ ------------------------------
[ServerName]                   'A'                            Sep 25 2015 11:32AM
[ServerName]                   'B'                            Apr 21 2015 12:09PM
[ServerName]                   'C'                            Feb 24 2015  9:16PM
[ServerName]                   'D'                            Oct  8 2014 11:02AM
[ServerName]                   'E'                            May 14 2014  6:27PM

(5 row(s) affected)

If you want to be able to dynamically change the column widths without modifying the T-SQL code, you'll need to use Dynamic SQL:

DECLARE @ColumnWidth VARCHAR(4);
DECLARE @Cmd NVARCHAR(MAX);

SET @ColumnWidth = '24';
SET @Cmd = '
SELECT [Server] = CONVERT(VARCHAR(' + @ColumnWidth + '), SERVERPROPERTY(''Servername''))
   , DatabaseName = CONVERT(VARCHAR(' + @ColumnWidth + '), '''''''' + bs.database_name + '''''''')
   , LastDatabaseBackupDate = CONVERT(VARCHAR(' + @ColumnWidth + '), MAX(bs.backup_finish_date))
FROM msdb.dbo.backupmediafamily  bmf
    INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
WHERE  bs.[type] = ''D''
GROUP BY bs.database_name
ORDER BY bs.database_name;
';

EXEC (@cmd);

Here, I've set the width to 24 for all columns, and it comes out looking like:

Server                   DatabaseName             LastDatabaseBackupDate
------------------------ ------------------------ ------------------------
SERVERNAME               'A'                      Sep 25 2015 11:32AM
SERVERNAME               'A'                      Apr 21 2015 12:09PM
SERVERNAME               'A'                      Feb 24 2015  9:16PM
SERVERNAME               'A'                      Oct  8 2014 11:02AM
SERVERNAME               'A'                      May 14 2014  6:27PM

(5 row(s) affected)

If you really want to go crazy and have the columns automatically size themselves, you would do this:

DECLARE @ColumnWidthServer VARCHAR(4);
DECLARE @ColumnWidthDatabase VARCHAR(4);
DECLARE @ColumnWidthLastBackup VARCHAR(4);

DECLARE @Cmd NVARCHAR(MAX);

SELECT @ColumnWidthServer = 1 + LEN(CONVERT(VARCHAR(128), SERVERPROPERTY('Servername')))
   , @ColumnWidthDatabase = 1 + MAX(LEN('''' + bs.database_name + ''''))
   , @ColumnWidthLastBackup = 1 + MAX(LEN(CONVERT(VARCHAR(128), bs.backup_finish_date)))
FROM msdb.dbo.backupmediafamily  bmf
    INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
WHERE  bs.[type] = 'D';

SET @Cmd = '
SELECT [Server] = CONVERT(VARCHAR(' + @ColumnWidthServer + '), SERVERPROPERTY(''Servername''))
   , DatabaseName = CONVERT(VARCHAR(' + @ColumnWidthDatabase + '), '''''''' + bs.database_name + '''''''')
   , LastDatabaseBackupDate = CONVERT(VARCHAR(' + @ColumnWidthLastBackup + '), MAX(bs.backup_finish_date))
FROM msdb.dbo.backupmediafamily  bmf
    INNER JOIN msdb.dbo.backupset bs ON bmf.media_set_id = bs.media_set_id
WHERE  bs.[type] = ''D''
GROUP BY bs.database_name
ORDER BY bs.database_name;
';

EXEC (@cmd);


Many parallel queries hang but no deadlock The sql is a very long multi-query like select-join. All keys are indexed.  When I run a load test with 70 connections continuously querying they all at some point stop receiving any network response (was Exception before I set Command Timeout = 0). I see many AccessSharedLocks started 15 minutes ago! Also there are several ExclusiveLocks for the same queries (why?). In pgAdmin this query is executed in 12 ms (at the same time!). The disk and CPU activity are both low.

SELECT relation::regclass, * FROM pg_locks WHERE NOT GRANTED;

- returns 0 rows so they are not waiting for locks.

The all queries have waiting = 'f'

How can I fix it?

(Clickable)

select user0_.id as id73_0_, user0_.nickname as nickname73_0_, ..., challenges1_.start_at as start2_78_1_, challenges1_.counter as counter78_1_, challenges1_.user_id as user5_78_1_, challenges1_.challenge_id as challenge6_78_1_, challenges1_.user_id as user5_0__, challenges1_.id as id0__ from public.users user0_ left outer join public.user_challenges challenges1_ on user0_.id=challenges1_.user_id where user0_.id in ([single id]);

SELECT this_.id as id73_4_,..., characters2_.user_id as user28_6_, characters2_.id as id6_, characters2_.id as id1_0_, ..., smartchall3_.character_id as character3_7_, smartchall3_.id as id7_, smartchall3_.id as id79_1_, ..., userupdate4_.id as id99_2_, userupdate4_.coins as coins99_2_, userupdate4_.gold as gold99_2_, ..., userupdate4_.tournament_xp as tournament10_99_2_, userupdate4_.tournament_class_xp as tournament11_99_2_, userupdate4_.tournament_xp_in_current_guild as tournament12_99_2_, userupdate4_.last_login_guild_user_leave_counter as last13_99_2_, userupdate4_.last_login_guild_user_join_counter as last14_99_2_, ..., user5_.id as id73_3_, user5_.nickname as nickname73_3_, ... FROM public.users this_ left outer join public.characters characters2_ on this_.id=characters2_.user_id left outer join public.user_character_smart_challenge_trackers smartchall3_ on characters2_.id=smartchall3_.character_id left outer join public.users_updateable_data userupdate4_ on this_.id=userupdate4_.id left outer join public.users user5_ on userupdate4_.id=user5_.id WHERE this_.id in ([single id])

Note that adding SSD somehow postpones this issue until higher load.

I use Npgsql at the client side. All client connections are just waiting at Socket.Receive.

My log settings are:

log_min_duration_statement = 1000   # -1 is disabled, 0 logs all statements
log_checkpoints = on
log_connections = on
log_disconnections = on
log_duration = on
log_lock_waits = on         # log lock waits >= deadlock_timeout
#log_statement = 'none'         # none, ddl, mod, all
log_temp_files = 0 # 0 logs all temp files

The log file is here https://1drv.ms/u/s!AhAvFYRsqyUIg6tvjXHPxe8iabOFWA

The issue was caused by the bug in the Npgsql, it now works ok after updating to the latest version.


Postgres to Postgres foreign data wrapper, for tables based on queries I have a db with sensitive data in a secure server, and a set of anonymization queries that I'd like to create foreign tables based on in a less secure server.

The Postgres native wrapper only enables you to create foreign tables directly mapped to tables in the source db, but it seems there are other wrappers which allow you to base the foreign tables based on queries.

Are there any foreign data wrappers for Postgres to Postgres porting that let you do this? Ideally it'd be like so:

create foreign table foreign_table (id integer)
  server foreign_server
  options (query 'some query')

To do what you need to do, you should be able to create a view on your secure server and reference that as the table in your less secure server, like this:

On the secure server:

CREATE OR REPLACE VIEW redacted_view AS SELECT col1, col2 FROM sensitive_table;

On the less secure server:

CREATE FOREIGN TABLE sensitive_table (col1 int, col2 int)
     SERVER foreign_server
     OPTIONS (table_name, 'redacted_view');

It's not your ideal scenario, but from what you're describing it should work. Hope that helps. =)


SQL Server select columns which contain data I'm working for a client to import data out of a generic off the shelf application database.  If you can imagine a database that is designed by software developers that have no concept of database design, that is what i'm dealing with.  I'm selecting from tables that have 50+ columns, where half the columns contain null data for every row (i.e. not used).  My question is, how do i identify the columns that are null for EVERY row (i.e. useless) and exclude them from my queries? Thanks for your help!  (i want to avoid going column by column saying isnull etc. as that will take forever!)

You can use this stored procedure to the trick
You need to provide the table name you wish to query
note that if you'll pass to procedure the @exec parameter = 1 it will execute the select query

  SET ANSI_NULLS ON
   GO
  SET QUOTED_IDENTIFIER ON
  GO
 CREATE PROCEDURE [dbo].[SP_SELECT_NON_NULL_COLUMNS] ( @tablename varchar    (100)=null, @exec int =0)
  AS BEGIN
  SET NOCOUNT ON
        IF @tablename IS NULL
              RAISERROR('CANT EXECUTE THE PROC, TABLE NAME IS MISSING',16 ,1)
                          ELSE
        BEGIN
              IF OBJECT_ID('tempdb..#table') IS NOT NULL DROP TABLE #table
              DECLARE @i VARCHAR (max)=''
              DECLARE @sentence VARCHAR (max)=''
              DECLARE @SELECT VARCHAR (max)
              DECLARE @LocalTableName VARCHAR(50) = '['+@tablename+']'
              CREATE TABLE  #table  (ColumnName VARCHAR (max))
              SELECT @i+=
              ' IF EXISTS ( SELECT TOP 1 '+column_name+' FROM '  +@LocalTableName+' WHERE ' +column_name+
                   ' '+'IS NOT NULL) INSERT INTO #table VALUES ('''+column_name+''');'
                    FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name=@tablename
                    INSERT INTO #table
                    EXEC (@i)
                     SELECT @sentence = @sentence+' '+columnname+' ,' FROM #table
             DROP TABLE #table
                    IF @exec=0
                          BEGIN
                                SELECT 'SELECT '+ LTRIM (left (@sentence,NULLIF(LEN (@sentence)-1,-1)))+
                                            +' FROM ' +@LocalTableName
                    END
                    ELSE
                          BEGIN
                                SELECT @SELECT=  'SELECT '+ LTRIM (left (@sentence,NULLIF(LEN (@sentence)-1,-1)))+
                                                  +' FROM '+@LocalTableName
                        EXEC (@SELECT)
                    END
  END
  END


How to implement security model? I have installed Oracle 11g on fedora 14. I connected with SQL*Plus as 'SYS' and
created some tables, then created a user named "account" and a role, "payroll"
with the following privileges. I assigned the role to the "account" user but I
cannot access the created tables when I log-in as "account".

Here are my queries:

As SYS

create type pay_t as object(
id int,
name varchar(10))
/

create table pay of pay_t;
insert into pay values(pay_t(10,'p01'));

create user account identified by paccount;
grant create session to account;
grant connect to account;

create role pay_roll;
grant select on pay to pay_roll;
grant pay_roll to account;

As account:

select * from pay;

This gives me an error saying there is no such table or view.

The table you created as sys is created in the "sys" schema.

SELECT by default accesses the schema of the caller, so in your case, as SELECT is run as "account", it tries to access table "pay" in schema "account".

You'll need to either use a schema-prefixed query:

SELECT * FROM sys.pay

Or first create a synonym in "account" schema (as sys):

CREATE SYNONYM account.pay FOR sys.pay;

You could also use public synonyms, but I would rather not recommend them.


How to order fields returned by a PIVOT query I Have 3 tables names like companystructure,projectallocation,introducermaster

I have tried this

select *
from
(
select projectallocation.proAllocationID as Sno,
       introducermaster.intro_name as IntroLow,
       temp.intro_name as IntroTop,
       projectallocation.introtoplevelEmpid as TopEmp,
       (companystructure.csshortname) as Level,
       projectallocation.introlevelEmpid as EmpID
from projectallocation
inner join dbo.IntroducerMaster
ON dbo.introducermaster.empid=projectallocation.introLevelEmpid
inner join introducermaster as temp
on temp.empiD =projectallocation.introtopLevelEmpid
inner Join companyStructure
ON companyStructure.HLevel = projectallocation.introleveID
where projectallocation.projectID=1
group by IntroducerMaster.Intro_Name,
         temp.intro_name,
         companyStructure.CSShortName,
         projectallocation.proAllocationID,
         projectallocation.introlevelEmpid,
         projectallocation.introtoplevelEmpid
) b
PIVOT
(
    max(introlow) for level in(CH,ED,DIR,GM,AGM,BDM,SMM)
)PVT

Output
Sno         IntroTop                                           TopEmp                                             EmpID                                              CH                                                 ED                                                 DIR                                                GM                                                 AGM                                                BDM                                                SMM
----------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
177         NANDI RAMA                                         EMP00093                                           EMP00006                                           NULL                                               NULL                                               NULL                                               NULL                                               NULL                                               NULL                                               ALAGAPPAN
178         EDWART                                             EMP00031                                           EMP00012                                           NULL                                               NULL                                               NULL                                               NULL                                               NULL                                               NULL                                               B.MURUGESWARI

i want the empid will became last at the pivot table

You should order returned fields in your first SELECT statement in this way:
select Sno, IntroTop, TopEmp, [CH], [ED], [DIR], [GM], [AGM], [BDM], [SMM], EmpID
from
(
)
pivot
(
)

Example:

CREATE TABLE pvt (VendorID int, EmpID int, EmployeeID int, Orders int);

INSERT INTO pvt VALUES (1,1,10,5);
INSERT INTO pvt VALUES (2,1,11,5);
INSERT INTO pvt VALUES (3,2,10,5);
INSERT INTO pvt VALUES (4,2,11,5);
INSERT INTO pvt VALUES (5,3,10,5);

SELECT VendorID, COALESCE([10],0) [10], COALESCE([11], 0) [11], EmpID
FROM
(
    SELECT VendorID, EmpID, EmployeeID, Orders FROM pvt
) src
PIVOT
(
   SUM(Orders) FOR EmployeeID IN ([10],[11])
) pvt;

GO

VendorID | 10 | 11 | EmpID
-------: | -: | -: | ----:
       1 |  5 |  0 |     1
       2 |  0 |  5 |     1
       3 |  5 |  0 |     2
       4 |  0 |  5 |     2
       5 |  5 |  0 |     3

dbfiddle here


Need sample script for Cluster index scan Do we have any example which is behaving as cluster index scan when ever queried with range of values. I am trying to get the conclusion on optimizer behavior as when ever the select count increases to more than 10% even the table have cluster index optimizer will take cluster index scan.

The related Query asked in below url as well.
Clarification on Clustered index scan and Clustered Index Seek

Benjamic Navarez has an excellent article on how the cost-based optimizer in SQL Server works.

Essentially, when SQL Server is asked to read a table it looks at several methods of returning the requested data, and chooses the "best" path it can by assigning costs to each type of operation.  It then chooses the cheapest path, or plan.  This may or may not include either seeking the clustered index, scanning the clustered index, or perhaps doing seeks or scans on the non-clustered indexes, if they exist.

Since the plan is cost-based, there is no single piece of code that will always perform a clustered-index scan, except perhaps for the case of SELECT * FROM dbo.[table with a clustered index];.  Even adding an ORDER BY ... clause to that statement may make SQL Server choose a different plan.

In your question you mention that your query performs a clustered index scan whenever the selected number of rows climbs above 10% of the total rows in the table.  The number of rows requested versus the total number of rows in the table is part of the cost-based-optimization, and is likely causing SQL Server to think it will be faster to simply scan the entire clustered index (the clustered index is the table), rather than using a series of index seeks followed by lookups.

Look at point #2 in this article by Aaron Bertrand.  Rob Farley also has a good write-up about it.

Martin Smith's answer on this question also contains some excellent details on this topic.


Performance degraded after shrinking I was cleaning up database (SQL Server 2012 SE) by deleting old data and truncating some unnecessary tables. After deleting and truncating data performance improved and had 67% free spaces out of 16 GB database. Then run following command to shrink database with 10% free space.

DBCC SHRINKDATABASE (databasename, 10);
GO

After that database reduced to 7GB but having performance problem, see that attach screenshot. By the way, I also rebuild indexes but no luck. Getting low application performance index (Apdex) alert from New Relic app monitoring tool.

Look at the DMV query results, at least tweenty queries taking more then 1000ms! I had only 2 queries that exceed 1000ms limit.

edit:
Will delete this shortly or update based on answer to question. Realized OP mentioned he DID rebuild indexes already.

Shrinking a SQL Server database causes the data to get massively fragmented due to how the algorithm works.  You'll want to usually presize so you don't have to shrink but don't have to worry about growths too much either.  Always easier to grow a live DB with IFI than to shrink and reindex.

Paul Randal has a great post about this in depth here.  You will want to reindex and check your fragmentation prior and after using this script ripped off here:

SELECT dbschemas.[name] as 'Schema',
dbtables.[name] as 'Table',
dbindexes.[name] as 'Index',
indexstats.alloc_unit_type_desc,
indexstats.avg_fragmentation_in_percent,
indexstats.page_count
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats
INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id]
INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id]
INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats.[object_id]
AND indexstats.index_id = dbindexes.index_id
WHERE indexstats.database_id = DB_ID()
ORDER BY indexstats.avg_fragmentation_in_percent desc


Comparing 1 Database with Many Tables to Multiple Databases with Fewer Tables in Each What are the pros and cons to creating one huge database with numerous tables or better separating the data into many databases that each have fewer, more relevant tables?

Are there server performance issues associated with either design?
Are there memory issues with either design?
Are there connection issues with either design?

(FYI, I am designing my data store in MySQL, but general conceptual viewpoints are greatly appreciated)

Thanks.

In general, a good RDBMS will handle both solutions the same way and you should not see performance differences. However, you do need to test with your data load on your (R)DBMS. MySQL in particular does not always follow the relational rule/mind set and your results might therefor vary.

There are two main differences that I can think of:

You cannot declare referential integrity constraints across databases.
If you split the data in several databases, restore from a backup might require a smaller downtime if you can restore the most important database first and bring up the application partially functioning. (This requires the App to be written in a way that it can gracefully handle partial database availability.) You could for example keep one month worth of data in a small active database and move older data to a larger archive database that also could live on cheaper (read: slower) storage. However, depending on your RDBMS, you might be able to achive the same without spreading across multiple databases. With SQL Server for example you can implement a similar solution within a single database by separating tables in different file groups. SQL Server allows a restore to happen one file group at a time. Once a file group is restored you can access its data. Accessing a table living on a not-yet-restored file group will fail with an error comparable to the one you get when accessing a normal database that has not yet finished restoring.