Amazon RDS instance recovery during mysqldump I have a db.t2.small read replica, which crashes every time I run mysqldump.

The issues started after I made some table changes - my main suspicion is the use of InnoDB.

This replica is used as a backup (incase the main server was to fail); and allowed me to run mysqldump every night, so I can create an off-site backup without effecting the main server.

AWS now goes though the recovery process when I use mysqldump.

01:53:13 - Recovery of the DB instance has started. Recovery time will vary with the amount of data to be recovered.
01:57:11 - DB instance restarted
01:57:36 - Recovery of the DB instance is complete.

Annoying the error logs are pretty much empty, so I have no idea what's causing it to fail.

The largest tables are:

788 MB = report_data
410 MB = report_data_log
418 MB = email_log_boy

The report_data table used to contain 36 TEXT fields (an example of more fields being added over time), and used MyISAM.

I changed this to use a simpler 1 TEXT field per row (an report_id, field_name, field_value, created_date, author_id), and it's now setup to use InnoDB.

The overall table size has decreased a bit.

The report_data_log table is basically the same table structure, but with a date to record when each value was replaced (as in, on editing, the record is copied from the report_data table, so I have a history of what's changed in the last 3 months).

I export each table individually with:

/usr/bin/mysqldump --defaults-extra-file=/backup/database.cnf --skip-extended-insert --skip-dump-date db report_data_log

I have tried adding --single-transaction, but that hasn't changed anything.

The config file just contains the username, password, host, ssl-ca, and sets ssl-verify-server-cert to true.

The /etc/mysql/conf.d/mysqldump.cnf file contains:

[mysqldump]
quick
quote-names
max_allowed_packet = 1024M

The server configuration hasn't been changed recently, where it includes:

max_allowed_packet: 1073741824
net_read_timeout: 3600
net_write_timeout: 3600

Oddly, it's able to export the 788MB report_data table, but crashes after exporting ~200MB of the 410MB report_data_log table.

Using db.t2.medium will probably fix this issue, but it doubles the cost - which seems a little silly considering the main server is fine, and both are idle most of the time (never appears to exceed 25% CPU, average 1.5%; or running low on Freeable Memory).

I'm tempted to switch back to MyISAM, but was wondering if there are any better AWS RDS logs to find out what's happened, or if there is something different about InnoDB that I'm missing?

As an interesting aside, the "db.t2.small" MariaDB instance provides 2GB of RAM.

The parameter group sets "innodb_buffer_pool_size" to "{DBInstanceClassMemory*3/4}".

But when I run:

SHOW GLOBAL VARIABLES LIKE "innodb_buffer_pool_size";

It returns 2147483648... so it's being set to 2GB, not 1.5GB.

This is a confirmed bug with Amazon RDS.

Reply from Amazon on the 6th Jan 2020:
"Starting with MariaDB 10.2.2 resizing the buffer pool is performed in chunks determined by the size of the innodb_buffer_pool_chunk_size variable. Hence the buffer pool size must be a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances, since innodb_buffer_pool_size is set to 1.5 GB the behavior is to round off to 1GB as the "innodb_buffer_pool_instances" is set to 8."

Original Answer:
On the "db.t2.small" MariaDB instance, it has 2GB of RAM, but innodb_buffer_pool_size is being incorrectly set to 2GB.
This means MariaDB will crash when the InnoDB buffer pool fills with too much data.
The temporary solution is to change the Parameter Group so:
innodb_buffer_pool_instances = 12

If you want to get an ETA from Amazon, ask them to check Case 6411919871, from the 5th September 2019.
I did have to eventually pay them $29 to raise a "Technical" bug report, the original report was raised in August, both with Accounts, and on their Forum:
https://forums.aws.amazon.com/thread.jspa?messageID=913625


Is there a way to have SQL work in parallelism with two computers? Amateur here. I have a couple of the same old 4 core computers that I am trying to use to beef up some automatic query scripts that run every evening for my hobby. I recently found MPICH to have the two computers communicate with each other.  I also found documentstion that allows postresql to use more than the default number of cores (It was set at 2). I am hoping there is some way of being able to use all 8 cores to run some SQL queries.  Is this possible? Any thoughts or recommendations are appreciated.

If you want a solution using plain PostgreSQL, you will have to use sharding, that is, you will have to split the database into parts that you keep on different machines.

You can use PL/Proxy to have a single query run on all machines in parallel.


Combine multiple records into one, using name from only one record I've got a query that returns a table of technicians and the number of work orders they've filled. The problem is that sometimes technicians get entered using their full name, and sometimes they get entered using their userID. I want this table to use full names, and combine records where the userID is used with records corresponding to their full name. UserID comes as either 'FirstInitial' + 'LastName' or '$FirstName' + '$LastInitial', where + is string concatenation

Example of my table:

 Maddoc 74 - 229
 M74 - 21
 Maddoc7 - 14
 John Smith - 215
 jsmith - 47
 johns - 19
 Anna Kellog - 251
 akellog - 67
 annak - 14

What I want it to look like:

 Maddoc 74 - 264
 John Smith - 281
 Anna Kellog - 332

Here's my current query:

SELECT "RESPONDEDBY" AS Technician, COUNT("RESPONDEDBY") AS Tickets
FROM TRACKIT_DATA2.TrackItApp_1.vTASKS_BROWSE
WHERE DATEPART(m,"CLSDDATE") = DATEPART(m, {?reportingDate})
AND DATEPART(yyyy,"CLSDDATE") = DATEPART(yyyy, {?reportingDate})
AND "RESPONDEDBY" NOT IN (SELECT "RESPONDEDBY" FROM TRACKIT_DATA2.TrackItApp_1.vTASKS_BROWSE WHERE "RESPONDEDBY" = 'EmailMonitor')
GROUP BY "RESPONDEDBY"
ORDER BY COUNT("RESPONDEDBY") DESC

this query will get you the results you wanted for your example

CREATE TABLE tracking(name VARCHAR(50), cnt INT);
INSERT INTO tracking
VALUES( 'Maddoc 74',229),
      ('M74',21),
      ('Maddoc7',14),
      ('John Smith',215),
      ('jsmith',47),
      ('johns',19),
      ('Anna Kellog',251),
      ('akellog',67),
      ('annak',14);
WITH cte AS(
SELECT ROW_NUMBER() OVER(ORDER BY name) AS rn,name,SUBSTRING(name,1,1)+SUBSTRING(name,CHARINDEX(' ',name,1)+1,LEN(name)) AS firstl,SUBSTRING(name,1,CHARINDEX(' ',name,1)-1)+SUBSTRING(name,CHARINDEX(' ',name,1)+1,1) AS flast
FROM tracking
WHERE name LIKE '% %')
SELECT MIN(c.name),SUM(t.cnt)
FROM tracking t
INNER JOIN cte c
ON t.name = c.name
OR t.name = c.firstl
OR t.name = c.flast
GROUP BY rn;

You will have issues if you also have a Jane smith, John Sanders, etc. this also doesn't account for middle names in the full name


Best way to structure data I have been playing around with grib2 data from the National Weather Service lately, and have been databasing some model forecast data.
The current design model I have is probably bad design, but I have 87 columns of data per model per forecast hour I want to save. The long range forecast models have 52 forecast hour intervals I'm working off of, but I could increase that later. I'm collecting from 3 models... so right now that means I have 87 columns, 1 table per model per forecast hour - in other words, somewhere around 80 tables of data with 87 columns each.
Already, this is causing problems, as MySQL apparently has a hard limit for how many table joins you can do and I have hit this a few times. Also, I'm starting to get PHP errors trying to execute multiple queries at once and then joining the results in PHP to one data set - too many buffered connections, etc. - I currently have PHP doing the queries looping through forecast hours to auto-generate the table joins... but I want a better way to do this. It's very hacky sloppy.
Would it make more sense to:

Save the current table structure? (~80 tables per location - 1 right now)

Create a table just for hour intervals? (This could still hit the table limit if I increase the hour frequency - but would be a bit less tables. Each table would have ~240 columns.)

Create a huge table with all hour data per each model. Would have 3 tables, but one of those tables would have 6,880 columns of data. I understand there is a hard limit of around 4,000 columns? This would not work, probably.

Hard limits:

JOINs, including implicit ones in Views: 61.
Columns per table < 1000 (1017 in 5.6.9, InnoDB); 2598(?) for MyISAM -- But don't use MyISAM.
others

But why do you need a lot of JOINs?  Please elaborate on the schema (SHOW CREATE TABLE, leaving out many of the columns) and SELECT (leaving out all but a couple of representative JOINs).

Do you actually do arithmetic or searching in MySQL on the 240 readings?  If not, simply pack them in a JSON string and let the app break them apart.  Further, compress the JSON string (in the client) and store in a BLOB.  (Smaller --> faster)  PHP would have no problem unpacking 4000 fields with json_decode().  And it could be in the desired structure (array / hash / nested arrays / etc)

The main thing is to structure the schema for what works well in the database, then leave the rest for the application.  The database is a repository; the app is the compute engine.

One table per hour or one table per location -- this smells bad.  One table per location per hour would be really bad.  Once you have thousands of tables in a database (or thousands of databases), the OS gets sluggish in accessing them.

So... 3 (or 1?) tables with

location_id (normalized, to save space)
hour (probably a DATETIME)
one (or a few or maybe 80) JSON strings each with 87(?) numbers, structure for the convenience of the app.

(Sorry, I got lost in the dimensions -- 3 somethings * 80 something else * 87 * 52 * ???)

Plus, PRIMARY KEY(location_id, hour) (or possibly in the opposite order.  And (probably) a plain INDEX with those columns in the opposite order.  Pick the order in the PK based on which order is more important for the SELECTs.  (Don't worry about INSERTs.)


SQL Server Clustering - Active/Active or Active/Passive? What is the logic of the Active/Active cluster vs Active/Passive cluster?

I have searched a lot and read lots of documents but have not found the answer.

In active active cluster can I have 1 database which is in shared storage and that database have 2 instances?

What is the pros/cons of active active cluster?

In active passive cluster can I have one database which is in shared storage and it use active node. If active node is shutdown, will the passive node become the active node?

Active/Active refers, typically, to the idea of having multiple copies of a database available concurrently.  However, in SQL Server, there is no way to accomplish that for read-write databases, other than the somewhat edge case of sharding data across mutliple instances.  In SQL Server, Active/Active is normally used to denote a multi-node Windows Server Failover Cluster that has multiple SQL Server instances installed, where each node has a separate instance.  However, for this to work successfully, each node needs to be configured with enough memory and CPU resources to successfully serve all instances in case that node is the only node left running at some point.

Active/Passive refers to having a multi-node cluster where one node serves the database(s), while the other node is available to take the load if the primary node fails.

As an example of Active/Active, consider the following:

2 computers configured in a Windows Server Failover Cluster, Node "A" and Node "B".  The cluster has two instances installed, "X" and "Y".  Typically, "X" runs on node "A", and "Y" runs on node "B".  This means the horsepower of both machines can be combined to serve the needs of both instances.  If, however, node "A" goes down, then node "B" will host both "X" and "Y" instances.

MSSQLTips.com has a great article comparing the two types of clustering.

MSDN has a good blog post on how to create an Active/Active cluster using Hyper-V.

From the description in your question it sounds to me like you want a typical 2-node cluster with a single instance serving a single database.  The database resides on shared storage so that if the active node (the one currently serving the database) goes down, the passive node will automatically start up the instance and begin serving the database from there.  Once the original node is brought back online, the instance could be moved back over to it, although that is not strictly necessary.


SQL Server Connectivity Issues I am getting the below error on occasion. Maybe every 30mins or so. I am not sure how to track what is causing the issue. I think it could be a Network problem as there are disk backups running as well.

Any suggestions on where to begin? Thanks!

The underlying provider failed on Open. ---> System.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The wait operation timed out.) ---> System.ComponentModel.Win32Exception: The wait operation timed out

OK, so odds are it isn't link that Up_One posted.

SQL Server does all it's work via schedulers.  Each scheduler is attached to a CPU core on the server.  Usually when I've seen that error it's because the CPUs are very busy and can't properly respond to the request in the time needed.

What's the CPU workload on the SQL Server look like when the problem happens?  How many users are running queries at that time?  How many CPU cores does the server have?

The fix is to reduce the CPU workload on the server.  Typically this is done by doing index tuning on the system which will reduce CPU workload.


MySQL 5.6 performance_schema not showing queries from application I'm seeing a strange problem with perfromance_schema... it seems queries are not getting logged as I expect them to be. For example, this is what I get:

mysql> SELECT `SCHEMA_NAME`, `DIGEST_TEXT`, `COUNT_STAR`, `FIRST_SEEN`, `LAST_SEEN`
       FROM `performance_schema`.`events_statements_summary_by_digest`
       WHERE `DIGEST_TEXT` NOT IN ('COMMIT' , 'START TRANSACTION')
           AND `DIGEST_TEXT` NOT LIKE 'SHOW %'
           AND `SCHEMA_NAME` LIKE 'mydbase'
           AND `DIGEST_TEXT` NOT LIKE 'SET %'
           ORDER BY `LAST_SEEN` DESC LIMIT 5;
+-------------+--------------------------------------------------------------------------------------------------------------------------
| SCHEMA_NAME | DIGEST_TEXT                                                    | COUNT_STAR | FIRST_SEEN          | LAST_SEEN           |
+-------------+--------------------------------------------------------------------------------------------------------------------------
| mydbase     | SELECT `SCHEMA_NAME` , `DIGEST_TEXT` , `COUNT_STAR` , .......  |          6 | 2016-05-04 22:56:20 | 2016-05-04 22:56:42 |
| mydbase     | SELECT `st` . * FROM `performance_schema` . `events_statement`.|         39 | 2016-05-02 10:55:30 | 2016-05-04 16:06:48 |
| mydbase     | SELECT `st` . * FROM `performance_schema` . `events_stages_hi  |         39 | 2016-05-02 10:55:30 | 2016-05-04 16:06:48 |
| mydbase     | SELECT `st` . * FROM `performance_schema` . `events_waits_his  |         39 | 2016-05-02 10:55:30 | 2016-05-04 16:06:48 |
+-------------+--------------------------------------------------------------------------------------------------------------------------
5 rows in set (0.00 sec)

If I run the query inside the database from mysql client (CLI), I can see queries appearing in there, but those from the actual application are missing?

I'm running MySQL 5.6.30 from IUS repository (CentOS 6).

Any ideas?

P_S in MySQL versions below 5.7.4 doesn't collect information regarding executed prepared statements: https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-4.html.

Maybe your application is using prepared statements instead of simple ad-hoc statements (parameters concatenated). Some client libraries, like PHP's PDO can avoid this (MySQL's) issue by emulating prepared statements in software, instead of sending them to MySQL for preparation (PDO option PDO::ATTR_EMULATE_PREPARES).


Can SQL Server stop me from running an UPDATE statement without a WHERE clause? Whenever I have to run an update statement, I always get nervous that I might forget the WHERE clause and overwrite the entire column in the database.  Is there any setting or plugin or addon I can get for SQL Server Management Studio to block certain dangerous statements, unless you explicitly say its ok to run?

I'm working on a development server, so I can always roll back the changes, but still, I feel like I might have only the first two lines highlighted and click Execute or do something else stupid.

Create a trigger on update (another on delete is probably a good idea).

CREATE TRIGGER [Table].[uPreventUpdateOops]
ON [Table]
FOR UPDATE AS
BEGIN
     DECLARE @Count int
     SET @Count = @@ROWCOUNT;

     IF @Count >= (SELECT SUM(row_count)
         FROM sys.dm_db_partition_stats
         WHERE OBJECT_ID = OBJECT_ID('Table')
         AND index_id = 1)
     BEGIN
         RAISERROR('Cannot update all rows',16,1)
         ROLLBACK TRANSACTION
         RETURN;
     END
END
GO

Note: This only prevents the most obvious mistake. Please do not use this on a production server as you're bound to find a statement that will totally Bork your table but pass this simple test. Also, this runs quite slow on large tables, performance will be affected.


Deleting rows from big table: what then? I will be deleting 40% of rows from a table that currently has 2.5 millions rows, space reserved 17 GB, 150 MB index, unused space 3 MB.

The primary key is the identity, and records were inserted in order. There are 2 non-clustered indexes. The rows I delete are the oldest. There is a FK pointing to the table, but it is not a problem because it only refers to recent records (which will not be deleted). Columns are mostly nvarchar, including a few nvarchar(max).

The actual deletion will take less than 10 minutes, the database server will not be used by anyone else for a couple of hours, so I am not worried by the deletion itself.

To get the maximum advantage from the point of view of disk size and performances, which operations can I do after the deletion? I don't want to create a new table and move the data, but is there a way I can rearrange the data and reclaim the space?

Rebuild the table

ALTER TABLE yourtablename REBUILD


Calculate products count based on category tree table It is necessary to calculate how many products were created in each category, of course, that the products created in a child must be considered for the parent.
How can i do that SQL/Postgresql?

Second explanation if first is not clear - I should create a query that will calculate products count based on a categoryId and categoryIds childs too.

Database schema

Table Category
-------------
id
name
parentId
-------------
ROWS
1 rootCategory null
2 child1 1
3 child2 2

Table Product
------------
id
name
categoryId
------------
ROWS
1 productOne 1
2 productTwo 2
3 productOne 2
4 productOne 3

If categoryId = 1 is selected it should display products count from this category ant its children. In this example answer for root category count should be 4, because it has also two subcategories that have also three items in product table.

Use a recursive CTE:

WITH RECURSIVE cats AS (
   SELECT 1::bigint AS id
 UNION
   SELECT c.id
   FROM category AS c
      JOIN cats ON c.parentid = cats.id
)
SELECT count(*)
FROM product
   JOIN cats ON cats.id = product.categoryid;

This query uses category id number 1 as an example. Replace it with the appropriate category id or use a parameter instead.


Why its failed on linux server? I tried to mark disk and got Fail!!!

[root@oradbserver3 Server]# /etc/init.d/oracleasm createdisk DISK1 /dev/sda2

Marking disk "DISK1" as an ASM disk:                       [FAILED]

You just need to do:

/etc/init.d/oracleasm start

.... to start off with.

Look at your screenshot - you completely ignored the fact that the oracleasm command gave you the command-line usage back, rather than actually doing anything.


Recommended SQL Server transaction logs settings for ephemeral databases? I'm looking for recommendations on how should I set up transaction logs for databases that are used by a development team. These databases are ephemeral in the sense that we never care about data recovery in case of hardware/software failure. Instead, every time a developer starts a task, they will create a new database and fill it with data from scratch anyway, so they'd do so as well in case of hardware failure. Another use case is automated testing, where a new database is created for each test run.
Right now, due to the usage patterns of developers (testing different types of queries, frequent data bulk-loading), the logs grow a lot and are more of a hindrance than help. We've seen cases where the logs started taking half a terabyte after only an hour of the developer's work, forcing them to manually truncate the logs. As we don't want to manually truncate the logs during automated tests, we need to allocate larger machines for them. I suspect that more I/O is required, slowing down operation.
Any recommendations I could find in both the SQL Server documentation and other materials are for production server and focus on data recovery, which is exactly the opposite of what I'm looking for.
What are some good practices regarding configuration of transaction logs of SQL Server when data recovery is irrelevant, instead ease of operation, resource usage and raw speed are a bigger concern?

Simple recovery model doesn't mean that the transaction log isn't used.
It just means that SQL Server can empty the log (aka "truncate" the log), instead of having to do it yourself scheduling regular transaction log backups. I.e., the log need space for the largest/earliest open transaction, since the log cannot be truncated past the oldest transaction - regardless recovery model.
Simple does mean, however, that some operations can be performed in minimally logged form. Such include create/alter/drop index, SELECT INTO, bulk loading of data and if the stars are properly aligned also INSERT ... SELECT.
So, if simple doesn't cut it for you regarding space usage, then you'd have to look into how the operations are done - i.e., educate the developers about minimal logging as I mention above.
As for speed, minimally logging will help there as well, since you have less I/O. Larger transactions (within some reason) means less synchronous I/O to the LDF file. From a pure config aspect, I guess some RAM disk option for the ldf file could help - but that might not be doable if you need large ldf files.


Backup database using logical backup device in T-SQL I am new to SQL Server coming from a software development background.
I created a backup device which creates a backup with the date and time appended to the name to make it unique:
DECLARE @Current_Date DATETIME;
DECLARE @pname varchar(300);
SET @Current_Date = SYSDATETIME();
set @pname = (select 'C:\WEEKLY_EXPORTS\LEARNING_DB_Backup_' + replace(CONVERT(varchar,@Current_Date, 20), ':', '_') + '.bak' );
EXEC sp_addumpdevice @devtype = 'disk',
@logicalname = 'WEEKLY_EXPORTS',
@physicalname = @pname;
GO

When backing up the database using SSMS, I can use the backup device without having to specify a backup name everytime.

I was looking for an example on how to create a backup using the logical backup device in T-SQL but found none. Is there a way to create the backup in a simple way in T-SQL like the following:
BACKUP DATABASE test_student_db
TO DISK = 'WEEKLY_EXPORTS';

To use a backup device the proper T-SQL would be:
BACKUP DATABASE test_student_db
TO WEEKLY_EXPORTS;

Check the Syntax section of the Backup doc to better understand the backup options.

It seems you're trying to develop a backup solution from scratch, so I recommend you take a look at Ola Hallengren's solution as it might save you a lot of work.


Return batches of rows based on one row Say I have a table like this in SQL Server 2008:

id  |  name  |  qty
-------------------
1   |  john  |    1
2   |  bill  |    3
3   |  mary  |    2
4   |  jill  |    5

I would like to query this table and return 1 row for each batch of, at most, a quantity of 2. So, the result of the query would look like:

id  |  name  |  qty
-------------------
1   |  john  |    1
2   |  bill  |    2
2   |  bill  |    1
3   |  mary  |    2
4   |  jill  |    2
4   |  jill  |    2
4   |  jill  |    1

Can this be done neatly without using a cursor? Is this possible using unpivot?

By the way, the qty column can have a maximum value of 10.

It's easy with a numbers table. Since also qty cannot be more than 10, we only need a very small numbers table:

CREATE TABLE numbers
  ( i int NOT NULL PRIMARY KEY ) ;

INSERT INTO numbers (i)
VALUES (1), (3), (5), (7), (9) ;

We need only the odd numbers because the numbers of rows wanted in the result is half of qty (or about half).

The query:

SELECT
    t.id, t.name,
    qty = CASE WHEN n.i = 1 AND t.qty % 2 > 0 THEN t.qty % 2 ELSE 2 END
FROM
    tableX AS t
  JOIN
    numbers AS n
  ON
    n.i <= t.qty ;

Of course we could also do it without any auxiliary table:

SELECT
    t.id, t.name,
    qty = CASE WHEN n.i = 1 AND t.qty % 2 > 0 THEN t.qty % 2 ELSE 2 END
FROM
    tableX AS t
  JOIN
    (VALUES (1), (3), (5), (7), (9)) AS n (i)
  ON
    n.i <= t.qty ;

Tested at dbfiddle.uk.


Postgres very slow on server Restoring a single table on the server is very slow

From Fri Jul 22 13:54:48 CEST 2016 to Fri Jul 22 14:17:41 CEST 2016
Server
2016-07-22 13:55:11 CEST [88719-1] tester@test LOG:  duration: 22988.122 ms  statement: COPY testtable (id, updated, lang, q, results) FROM stdin;
2016-07-22 14:06:09 CEST [88719-2] tester@test LOG:  duration: 658189.147 ms  statement: ALTER TABLE ONLY testtable ADD CONSTRAINT testtable_lang_q_key UNIQUE (lang, q);
2016-07-22 14:06:10 CEST [88719-3] tester@test LOG:  duration: 1546.800 ms  statement: ALTER TABLE ONLY testtable ADD CONSTRAINT testtable_pkey PRIMARY KEY (id);
2016-07-22 14:06:13 CEST [88719-4] tester@test LOG:  duration: 2305.070 ms  statement: CREATE INDEX testtable_lang ON testtable USING btree (lang);
2016-07-22 14:06:15 CEST [88719-5] tester@test LOG:  duration: 2374.251 ms  statement: CREATE INDEX testtable_lang_like ON testtable USING btree (lang varchar_pattern_ops);
2016-07-22 14:17:38 CEST [88719-6] tester@test LOG:  duration: 683316.708 ms  statement: CREATE INDEX testtable_q ON testtable USING btree (q);
2016-07-22 14:17:41 CEST [88719-7] tester@test LOG:  duration: 2839.739 ms  statement: CREATE INDEX testtable_q_like ON testtable USING btree (q varchar_pattern_ops);

Local machine

From Fr 22. Jul 14:38:54 CEST 2016 to Fr 22. Jul 14:42:05 CEST 2016
Lokal
2016-07-22 14:39:32 CEST [23005-1] tester@test LOG:  duration: 37444.240 ms  statement: COPY testtable (id, updated, lang, q, results) FROM stdin;
2016-07-22 14:40:44 CEST [23005-2] tester@test LOG:  duration: 71899.607 ms  statement: ALTER TABLE ONLY testtable ADD CONSTRAINT testtable_lang_q_key UNIQUE (lang, q);
2016-07-22 14:40:46 CEST [23005-3] tester@test LOG:  duration: 2027.237 ms  statement: ALTER TABLE ONLY testtable ADD CONSTRAINT testtable_pkey PRIMARY KEY (id);
2016-07-22 14:40:49 CEST [23005-4] tester@test LOG:  duration: 3781.663 ms  statement: CREATE INDEX testtable_lang ON testtable USING btree (lang);
2016-07-22 14:40:52 CEST [23005-5] tester@test LOG:  duration: 2731.623 ms  statement: CREATE INDEX testtable_lang_like ON testtable USING btree (lang varchar_pattern_ops);
2016-07-22 14:42:02 CEST [23005-6] tester@test LOG:  duration: 69512.264 ms  statement: CREATE INDEX testtable_q ON testtable USING btree (q);
2016-07-22 14:42:05 CEST [23005-7] tester@test LOG:  duration: 3177.827 ms  statement: CREATE INDEX testtable_q_like ON testtable USING btree (q varchar_pattern_ops);

testtable

                                  Table "public.testtable"
 Column  |           Type           |                              Modifiers
---------+--------------------------+---------------------------------------------------------------------
 id      | integer                  | not null default nextval('testtable_id_seq'::regclass)
 updated | timestamp with time zone | not null
 lang    | character varying(2)     | not null
 q       | character varying(100)   | not null
 results | text                     | not null
Indexes:
    "testtable_pkey" PRIMARY KEY, btree (id)
    "testtable_lang_q_key" UNIQUE CONSTRAINT, btree (lang, q)
    "testtable_lang" btree (lang)
    "testtable_lang_like" btree (lang varchar_pattern_ops)
    "testtable_q" btree (q)
    "testtable_q_like" btree (q varchar_pattern_ops)

Tested with PG 9.4 and 9.5 on both machines with the same configuration. Restoring the dump takes even much longer on the server if auto vacuum is enabled...

What could be the problem? What's going wrong here? Any ideas how to debug?

Update: U 16.04 is running on server, U 14.04 LTS on local machine. Number of rows: 1574775, Size of plain SQL dump 1.8 GB.

Looking at your server times: 23 seconds to restore a table with 1,6 million rows from a 1.8 GB dump file. That does not seem too bad. Might be faster, but doesn't seem very slow either.

To speed up index creation (as well as UNIQUE and PK constraints) set maintenance_work_mem high enough temporarily (for the local session or transaction only). The manual:

  maintenance_work_mem (integer)

  ... Specifies the maximum amount of memory to be used by maintenance operations, such as VACUUM, CREATE INDEX, and ALTER TABLE ADD FOREIGN KEY.
  It defaults to 64 megabytes (64MB).

Set it just high enough for Postgres to process everything in RAM (If you have enough RAM to spare (temporarily). For instance:

BEGIN;
SET LOCAL maintenance_work_mem = 1GB;  -- adapt to your needs
ALTER TABLE testtable ADD CONSTRAINT testtable_lang_q_key UNIQUE (lang, q);
COMMIT;

The manual warns:

  Note that when autovacuum runs, up to autovacuum_max_workers times
  this memory may be allocated, so be careful not to set the default
  value too high.

Setting it high for your transaction only avoids this problem.

Related:

Is a clustered index on column A the same as creating a table ordered by A?


A UNION by default will filter out duplicate rows (essentially adding an implicit DISTINCT) which means comparing the rows output to filter them.

If you have duplicate rows that you need filtering out then you may need to cast the JSON type into something else (can you cast it to a string type? - I'm not familiar with postgres and JSON support).

If you do not have duplicate rows to worry about (or you in fact want to keep them) then use UNION ALL instead of a plain UNION and the comparisons will not be necessary. In fact you are likely to find that UNION ALL is more efficient, it is better to use UNION ALL at all times except when you explicitly need duplicate row removal.


Why doesn't nodetool work on my Cassandra Cluster? I have never been able to get nodetool to operate correctly to give me information about my cluster.  We have a simple 3-node cluster of Cassandra 3.11.1 nodes running under CentOS 7.3 -- using the gossip snitch and replication.  We have a special place on the file system where we're putting the node configurations, but these are appropriately set with environment variables.  A "generic" configuration is in the environment for client applications.

These are attempts at using nodetool (I have replaced the exact server name with /hostname/.)

  % nodetool -h /hostname/ status
  nodetool: Found unexpected parameters: [/hostname/, status]
  See 'nodetool help' or 'nodetool help '.

maybe the help is wrong and flags go at the end?  Running this:

  % nodetool  status -h /hostname/

gives me usage information.

My favorite is this:

  % nodetool help status
  nodetool: For input string: "help"
  See 'nodetool help' or 'nodetool help '.

I can connect with cqlsh.  I get the same response from nodetool whether I'm on a remote host or one of the cluster nodes.  Python clients connect fine and we've been able to create keyspaces and bulk load data from remote hosts.

I am sure there is some configuration I have overlooked but I can't find any information about what it might be in the docs.

Any suggestions on how to get nodetool working?

After some tinkering I figured it out; posting here to close the topic and to help others.

All the scripts in the conf/ directory work in concert and there are a number of environment variables you need to redirect if you install in a non-standard place (i.e. a tarball installation).

For each node in our cluster, we're (now) using:
  (1) cassandra-env.sh
  (2) cassandra-rackdc.properties
  (3) cassandra.yaml
  (4) jvm.options

All the other configuration files are pointed back to the conf/ directory from the tarball using an environment variable.

(2) and (3) set the properties of the node, such as its name, turning on gossip, etc:

(1) and (4) are used by the cassandra bash script when it launches -- the script goes looking for additional configuration files.  We've set it so that the cassandra.in.sh is found in the install area; but previously (1) and (4) were not being found, and these are required to activate JMX, port 7199, which nodetool uses to connect to the cluster.


Why are there empty transactions in the MySQL binlog? I am using Percona Server for MySQL 8.0.x. I have setup replication between two instances with GTID mode turned OFF. Almost all UPDATE and INSERTS are wrapped in the transaction from the application side.

On the slave server when looking at the binlog coming from master, I noticed there are lots of empty transactions. What are those and why are they present in the binlog when there is nothing to be executed in that transaction.

/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
# original_commit_timestamp=1587391567871055 (2020-04-20 14:06:07.871055 UTC)
# immediate_commit_timestamp=1587391567871055 (2020-04-20 14:06:07.871055 UTC)
/*!80001 SET @@session.original_commit_timestamp=1587391567871055*//*!*/;
/*!80014 SET @@session.original_server_version=80019*//*!*/;
/*!80014 SET @@session.immediate_server_version=80019*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
SET TIMESTAMP=1587391567/*!*/;
BEGIN
/*!*/;
COMMIT/*!*/;

After some research and further looking into MySQL documentation, it turned out to be it was an error on my part.

I was using mysqlbinlog -s option to view binlog. Because the replication statement is coming over as Row Based Replication, it is not possible to see the actual statement.

  You cannot see on the slave what statements were received from the master and executed.

Advantages and Disadvantages of Statement-Based and Row-Based Replication

So when trying to view the binlog using the mysqlbinlog -s util, it doesn't decode this blob of information and hence shows up as blank/empty commit.

Hope this helps someone else runs into to similar situation.


Statistics and Execution plan in SQL Server I have table Reg_Stats table without any indexes. Statistics are not updating automatically but I have enabled the autoupdate statistics (is_auto_create_stats_on, is_auto_update_stats_on are both are on).

My second problem: once I updated statistics manually, it is showing correctly but in execution plan it is not showing properly.

Statistics are being updated when the following number of rows have been affected in the said table : 20% * (table row count) + 500, unless the table contains less than 6 rows (in this case it is updated every time 6 rows have been affected), or less than 500 (same rule applies).

Now for the plan you displayed, it looks very weird. I can't repro it either.
Can you tell us how yo updated the statistics please ?

@++ ;)


MySQL - A Count with INNER JOINS - that multiplies the result by 8 The following SELECT with INNER JOINS works, but does not count it right.

I have a tests table:

Tests

TestNumber (int primary key)
TestComplete (int 0 = not complete, 1 = complete)
...other stuff

with values

(7, 0)
(8, 0)
(9, 0)

TestUsers

TestNumber (int - ties in with TestNumber in Tests)
UserId (int - the person in the test)
UserSlot (int - the seat they have in the test)
ResignedTimestamp (TIMESTAMP - when they resigned from their seat in the test or '1970-01-01 00:00:01' if they have not resigned)

with values

(7, 25, 5, 1970-01-01 00:00:01) this counts
(8, 25, 2, 2016-04-23 12:00:00) this should not count because the one below has filled the slot
(8, 32, 2, 1970-01-01 00:00:01)

What I need to do is count the number of tests a user is signed into and are not complete. It must include all Tests they have not resigned from. (This is easy).

BUT it must also include all the Tests they have resigned from and another User has not taken their place. (This is the tricky bit).

Another user will have been deemed to have taken the place of the User in question if they have an entry in TestUsers with the same TestNumber and the same UserSlot (note both), and they either have not resigned themselves (ResignedTimestamp='1970-01-01 00:00:01') or they have resigned but their ResignedTimestamp is later.

Here is my SQL.

SELECT COUNT(*) FROM testusers tu
    INNER JOIN tests t ON
        t.TestNumber=tu.TestNumber AND
        t.TestComplete=0 AND
        tu.UserId=25
    INNER JOIN testusers tu2 ON
        (tu.ResignedTimestamp='1970-01-01 00:00:01' OR
        (tu.ResignedTimestamp!='1970-01-01 00:00:01' AND
        tu.TestNumber=tu2.TestNumber AND tu.PlayerSlot=tu2.PlayerSlot AND
        (tu2.ResignedTimestamp='1970-01-01 00:00:01' OR tu.ResignedTimestamp < tu2.ResignedTimestamp)
        )
    )

The result counts row (7, 25, 5, 1970-01-01 00:00:01) eight times, and adds to that (8, 25, 2, 2016-04-23 12:00:00) once. So it is giving me an answer of 9 when the answer should just be 1.

Perhaps you want something like this?

SELECT tu.*
FROM tests t
JOIN testusers tu ON (tu.TestNumber = t.TestNumber)
WHERE t.TestComplete = 0 AND  -- below is your tricky bit
      (ResignedTimestamp='1970-01-01 00:00:01' OR
       NOT EXISTS (SELECT 1 FROM testusers WHERE TestNumber = tu.TestNumber AND UserSlot = tu.UserSlot AND UserId <> tu.UserId))
ORDER BY tu.UserId


Get members of a given group from N:M relationship I have this N:M relationship:

CREATE TABLE auth_user (
    id integer NOT NULL PRIMARY KEY,
    username character varying(150) NOT NULL UNIQUE
);

CREATE TABLE auth_group (
    id integer NOT NULL PRIMARY KEY,
    name character varying(80) NOT NULL UNIQUE
);

CREATE TABLE auth_user_groups (
    id integer NOT NULL PRIMARY KEY,
    user_id integer REFERENCES auth_user(id) NOT NULL,
    group_id integer REFERENCES auth_group(id) NOT NULL,
    CONSTRAINT user_groups UNIQUE(user_id, group_id)
);

INSERT INTO auth_user VALUES (1, 'user1');
INSERT INTO auth_user VALUES (2, 'user2');
INSERT INTO auth_group VALUES (1, 'group1');
INSERT INTO auth_group VALUES (2, 'group2');
INSERT INTO auth_user_groups VALUES (1, 1, 1);
INSERT INTO auth_user_groups VALUES (2, 2, 1);
INSERT INTO auth_user_groups VALUES (3, 2, 2);

How to select all usernames which are in the group 'group1'?

I use PostgreSQL, but SQL which works everywhere is preferred.

This EXISTS query returns unique users, regardless of whether there can be duplicate entries in auth_user_groups.
SELECT *
FROM   auth_user u
WHERE  EXISTS (
   SELECT 1
   FROM   auth_user_groups
   WHERE  user_id = u.id
   AND    group_id = (SELECT id FROM auth_group WHERE name = 'group1')
   );

Typically fast, too.
Notes
For Postgres - which you seem to be using.
The sequence of index columns matters. You defined UNIQUE(user_id, group_id), which is implemented with a corresponding unique index. For your particular query, an index on (group_id, user_id) would be preferable.
You can either switch the columns of the UNIQUE constraint, or create an additional (optionally unique) index with the columns reversed if you need both (which is the common case). Related:

Is a composite index also good for queries on the first field?

Also be aware that columns included in a UNIQUE constraint can still be NULL. (Unlike a PRIMARY KEY constraint which makes all member columns NOT NULL automatically!) You'd typically want user_id and group_id in auth_user_groups to be NOT NULL as well. Related:

How to implement a many-to-many relationship in PostgreSQL?


Practical way to temporarily shut down a database What would be the most practical way to shut down a database to access for any user?

I need to practically shut down a specific database on a SQL Server for test purposes, and would like to know the most practical common solution.

Thanx

You can take if offline or open as a single user.

To take it offline adapt the followin script, original at sqlauthority

-- Create Test DB
CREATE DATABASE [myDB]
GO
-- Take the Database Offline
ALTER DATABASE [myDB] SET OFFLINE WITH
ROLLBACK IMMEDIATE
GO
-- Take the Database Online
ALTER DATABASE [myDB] SET ONLINE
GO
-- Clean up
DROP DATABASE [myDB]
GO

To make it available to a single user account original at sqlauthority:

ALTER DATABASE [YourDbName]
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;


Is SELECT FOR ... UPDATE *necessary* to DELETE or UPDATE multiple rows without deadlock? Consider the following when the transaction isolation level is Read Committed (the default):

UPDATE example
  SET foo = 'bar'
WHERE ...

and

DELETE FROM example
WHERE ...

Neither DELETE nor UPDATE supports ORDER BY, nor do they have a defined order. After reading the PostgreSQL docs extensively, it would seem the most simple and reliable solution to avoid deadlock in a concurrent environment is

UPDATE example
  SET foo = 'bar'
WHERE ctid IN (
  SELECT ctid
  FROM example
  WHERE ...
  ORDER BY ctid
  FOR UPDATE
)

I'm not thrilled about doing this to every UPDATE and DELETE that affects multiple rows.

Is my assessment and solution correct?
Is this the best solution?
Is there a reason PostgreSQL doesn't already do this automatically?

Your assessment is spot on, and there is no better way of doing it.

As an alternative, you could do without the sort, but delete in smaller batches, each in its own transaction. That is still susceptible to deadlock, but the likelihood is smaller, and re-running the transaction doesn't hurt quite as much.


Postgres recovery.conf options I am setting up Postgres binary replication.

In my slave's recovery.conf, I am suggested to have 2 options.

I am wondering if the directory specified in restore_command and archive_cleanup_commands are referring to directory paths in the master OR the slave?

restore_command = 'cp -f /home/postgres/data/wals/%f %p </dev/null'
archive_cleanup_command = '/usr/pgsql-9.2/bin/pg_archivecleanup /home/postgres/data/wals/ %r'

For instance, in restore_command is /home/postgres/data/wals referring to the path in the master?
Likewise for archive_cleanup_command?

The reason I am asking is because the paths are not similar between the 2 servers.

I assume you realize that the two servers both need to have access to the WAL archive - so if you are using commands such as 'cp' then you would have to be using NFS or some other similar technology to achieve it.

The contents of recovery.conf on the slave refer to processes on the slave, so the path you should use is the path to the WAL archive dir as seen on the slave.

The path specified in archive_command in postgresql.conf on the master refers to the WAL archive dir as seen on the master.

I recommend if at all possible you align the machines so that the path is the same (which you can do via symlinks or by changing mount points). When you use pg_basebackup to create your slave, it copies across complete contents of the master including config files, so it makes life easier if the same paths are valid in both machines.


Prevent logins loss when restoring a database I'm backuping and restoring databases accross two sql server (v12.0.2000).
But it looks like whenever I restore a .bak file, the login I created on the database that was backuped is no more existent in the one that was restored. I expected it to be backuped to the bak file !

Is there any special manipulation for a login to be backuped in order to be easily restored on another server ?

I could not go on maintaining various script to restore the database on one hand and to recreate the logins on the other hand.

How could I create a consistent backup file that could easily be restored ?

The above issue which you are facing is commonly known as the case for Orphan users:

What commonly happens with SQL authenticated logins and database users on a restore is that the SIDS will be out of sync, thus breaking the relationship. This relationship must be repaired before you can connect to the database using that login, because in the eyes of SQL Server those principals are no longer connected.

On how to fix this you can refer Fixing Orphaned Users

Or as an alternative you can also make use of SP_help_revlogin

1) Run the sp_help_revlogins against the MASTER on SOURCE SERVER, and get the script of "creating logins".

2) During the restoring the backup to the DESTINATION SERVER, you can run the "creating logins" script against the MASTER database on the DESTINATION SERVER.

3) After the restoration DONE, please EXEC sp_change_users_login 'REPORT' against the RESTORED database.


Configuring Transactional Replication for an OLTP Database We conduct backup/restore (every 24 hours) on an OLTP database for reporting and SSRS queries. This prevents contention from simultaneous writes and reads; preventing locking, blocking, writer-reader deadlocks.

Now we want to apply transactional replication (to obtain more real time data). However, would the same issue occur?

If the reporting subscription database is being written into, updated/deleted, and we have read queries, would we still get the locking, blocking issue?

Although at least it would not be on a live OLTP system.

Using RCSI on the subscriber(s) is a common way to design this type of a scenario to avoid blocking. RCSI will allow readers and writes to play nice together but won't solve writers blocking writers. Since the report queries are readers and the transactional replication is a writer, this feature is a good fit for this.

You just need to make sure that your TempDB is configured to support your workload for the versioning. Also remember that enabling RCSI adds 14 bytes to each inserted/updated row which might cause internal fragmentation.


InnoDB table size increased substantially after ALTER I'm trying to understand why the data size of my table increased so much after performing this ALTER TABLE statement on an InnoDB table:

ALTER TABLE alert ADD COLUMN receiver_id INT(10) UNSIGNED

Note that I used gh-ost to perform the ALTER because the size of the table was rather large. I know that this will create a net new table, copy the rows and rebuild the indexes as data is copied. Based on what I know, I would expect the outcome to be very similar to how OPTIMIZE TABLE works.

Here's some stats from the old table (I got these from SQL Workbench, but they should be calculated from information_schema):

Table rows: 40037398
AVG row length: 8800
Data length: 328 GiB
Index length: 12.9 GiB
Table size (estimate): 341 GiB
Autoincrement: 65447094

And here's a screenshot of the column data from information_schema.COLUMNS, including the data types and encodings:

And here are the same stats for the new table which has the receiver_id column applied to it:

Table rows: 24763900
AVG row length: 18735
Data length: 432.1 GiB
Index length: 12.2 GiB
Table size (estimate): 444.3 GiB
Autoincrement: 65472610

And here's a screenshot of the column data from information_schema.COLUMNS, including the data types and encodings. You should see the additional INT column at the bottom:

The difference in autoincrement makes sense because after the tables had been swapped, new writes were being applied to the latter. The decrease in index length also makes some sense to me, as rebuilding indexes can optimize the storage underneath. Can anyone provide any insight into why Table rows, Data length would change so dramatically?

EDIT 10/4 adds COLUMN data screenshots.

It's difficult to answer without knowing the nature of the data being copied and maybe of the integration software that is used.

But I decided to share my guesses hoping it may lead to better diagnostic:

Row length increase may be because texts were converted into Unicode during migration.
Lower row count maybe due to some kind of deduplication that also removed records with less text data thus increasing average row length. Or it might be just that the migration process was aborted in the middle.

It might help to make a more educated guess if you supplement the question with SHOW CREATE TABLE output for both source and target tables.


Inserting data to a table which is being cleaned up by SQL job at the same time Our app uses a table in a database and we have a Sql job set up to run overnight which executes a stored procedure which just deletes data older that from the table.

DELETE FROM
        MyTable
    WHERE
        CreatedOn < DATEADD(D, -1 * 1, GETUTCDATE());

Now depending on the data stored on that table (on the previous day), the job completes fast or very long. When It is long, the app throws timeout error inserting into the table. Obviously this is reported by the users using the app overnight.

I have following options :-

a) Change the time of the job so that minimum number of users get affected.

b) Delete certain number of rows at a time rather than delete all of them together.

delete top (10) MyTable where CreatedOn < DATEADD(D, -1 * 1, GETUTCDATE());
while @@rowcount > 0
    begin
    delete top (10) MyTable where CreatedOn < DATEADD(D, -1 * 1, GETUTCDATE());
    end

Could anyone think of a better option?

If CreatedOn is not the clustered index (and especially if it is not indexed at all), and you are inserting new data sequentially (e.g. new rows have a newer CreatedOn), you should consider making that the clustered index. Then you shouldn't have any contention when you are deleting old data and inserting new data, since it will be on a completely different set of pages. Unless for some reason your inserts or the delete are escalating locks. If that is the case then instead of one big transaction:

DELETE [massive amount of rows]
  WHERE CreatedOn < [somedate];

You should consider breaking that up into chunks, as you suggested; but, just doing that in a loop and not adding any other changes won't have that much of an effect, since that may still be operating as a single transaction (and your problem might be exasperated by transaction log writes, and perhaps poor autogrowth settings for the transaction log). My method is typically as follows (with no "outer" transaction at play) - commit each set in its own transaction, and checkpoint or backup the log in between transactions. This lets user queries get in between your transactions and also reduces the impact on the log. Note that I picked 1000 arbitrarily; that might not be the right number for your scenario, but I'm pretty sure 10 is not.

DECLARE @somedate DATE = DATEADD(DAY, -1, GETUTCDATE());

BEGIN TRANSACTION;

SELECT 1;

WHILE @@ROWCOUNT > 0
BEGIN
  COMMIT TRANSACTION;

  -- if in simple recovery: CHECKPOINT;
  -- otherwise: BACKUP LOG ...;

  BEGIN TRANSACTION;

  DELETE TOP (1000) dbo.MyTable
    WHERE CreatedOn < @SomeDate;
END

But again, this will help your process most if CreatedOn is clustered, or at least indexed (having no context I don't know if your overall workload would be better off with that as clustered, but I do know that this query will work much better if it is).


Mysql And Galera cluster mariaDB , Private and Public IP Addresses multisite I am just wondering has anyone come accross this before and the best method to go about doing it.

We have a customer that wants multi site replication over a galera cluster.

I am familiar with setting this up but not in the way they are asking for.

First of all the client has a dedicated L2 connection between the datacentres so I have set up the cluster with Private IP addresses going across the dedicated L2 link, the issue is if the L2 link goes down they loose the replication between sites, So I was thinking of natting the nodes private IP addresses to Public IPs.

My question is: Is there a way I can set the galera cluster so that if it cant reach the private IPs it will go out over the internet through the firewall and hit the public IPs?

Could I maybe put the private and public IPs of each site within this setting:

wsrep_cluster_address=gcomm://server1private_IP1,server1public_IP1,server2private_IP1,server2public_ip1 etc

Saying that server1 is local in my datacentre and server2 is in another datacentre.

Or would that write the data twice over the 2 links?

Thanks in advance for any help that is thrown my way.

Maybe you can reach what you want changing gcomm ip addresses to custom non-FQDN hostnames (db1, db2, dbN). Then you can play a little bit with DNS resolution just updating your hosts file on each db node.
This is not official nor valuable solution anyway ;)


I changed the max memory setting but I don't see memory usage rise at OS level We have increased the RAM capacity on the OS to 40G and I have changed the max memory parameter to 36G in SSMS.

When I select "current value", it shows 36G.

However, monitoring memory usage at OS level doesn't show more memory being consumed.

I know that, in theory, it's possible to change the max memory setting while SQL Server is still running, but does it really work? Do I need to restart the server? Or am I being impatient?

Thanks

That setting is not "go use a bunch of memory for whatever." It is a setting that dictates the maximum usage of memory for the buffer pool (data cache, plan cache, and a few other areas of memory reservation).

In order to actually use 36GB of memory, you need to run queries that will force the buffer manager to pull data off disk and put it into memory. Run the following query and you will see memory usage rise (also, in case it is where you are looking, do not use Task Manager for this).

SELECT * FROM dbo.YourBiggestTable AS ybt1
  CROSS JOIN dbo.YourBiggestTable AS ybt2;


Change clustered index without dropping the primary key In the past, I have chosen the datetime column created as inappropriate clustered index in the table.

Now I concluded (base on the execution plan) that it would be better to choose the ID identity primary key as the clustered key, because it is referenced as foreign key very often.

I would like to drop the current clustered key and create a new, but I cannot drop the primary key, because that the full-text index is dependent on that primary key.

Can I just switch primary key to clustered index or do I need to drop the primary key and the chain of all dependent objects?

Bellow you will find table definition and clustered index definition.

CREATE TABLE [dbo].[Realty](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Created] [datetime] NOT NULL,
    ....

 CONSTRAINT [PK_Realty] PRIMARY KEY NONCLUSTERED
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
...

CREATE CLUSTERED INDEX [Created] ON [dbo].[Realty]
(
    [Created] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

Just drop the existing clustered index, then create a new one on the ID column.

DROP INDEX [Created] ON dbo.Realty;
GO

CREATE UNIQUE CLUSTERED INDEX CX_Realty ON dbo.Realty (ID);
GO

Of course, you'll want to do this during a maintenance window so you don't cause too much blocking.  If you have Enterprise Edition you can do the CREATE INDEX operation online by adding WITH (ONLINE=ON) to the statement.


Very weird result of CTEs and subqueries WITH t1 AS (SELECT a.id,
                   AVG(standard_qty) std_avg,
                   AVG(poster_qty) pos_avg,
                   AVG(glossy_qty) gloss_avg
            FROM accounts a
            JOIN orders o
            ON a.id = o.account_id
            GROUP BY 1),

     t2 AS (SELECT MAX(std_avg) max_std_avg,
                   MAX(pos_avg) max_pos_avg ,
                   MAX(gloss_avg) max_gloss_avg
            FROM t1)

SELECT std_id , max_std_avg, pos_id , max_pos_avg, glos_id , max_gloss_avg
FROM(SELECT
        (SELECT id std_id  FROM t1,t2 WHERE std_avg = max_std_avg),
        (SELECT id pos_id  FROM t1,t2 WHERE pos_avg = max_pos_avg),
        (SELECT id glos_id FROM t1,t2 WHERE gloss_avg =max_gloss_avg)
    )foo ,t1,t2

RESULT:

std_id  | max_std_avg| pos_id | max_pos_avg | glos_id |  max_gloss_avg
-----------------------------------------------------------------------
   1341 | 1891.7777  |   4251 | 2184.4615   |    4211 | 523.258
   1341 | 1891.7777  |   4251 | 2184.4615   |    4211 | 523.258
'
'
'
'
'
   1341 | 1891.7777  |   4251 | 2184.4615   |    4211 | 523.258
(350 ROWS)

However, the output should be only 1 single row:

std_id  | max_std_avg| pos_id | max_pos_avg | glos_id |  max_gloss_avg
-----------------------------------------------------------------------
   1341 | 1891.7777  |   4251 | 2184.4615   |    4211 | 523.258

I would really appreciate if anyone can explain why?
Thanks.

In the last, external query you cross join foo with t1 and t2, while you only need (and use) columns from foo and t2.

So remove the t1 join:

WITH
    ...
SELECT
FROM (
     ...
     ) foo, t2 ;

The whole external query could be simplified further, as you don't really need the t2 used four times, in the three subqueries plus the main query. You can use it once and still join it to t1 three times:

WITH t1 AS ...,
     t2 AS ...
SELECT std.id   AS std_id,   t2.max_std_avg,
       pos.id   AS pos_id,   t2.max_pos_avg,
       gloss.id AS gloss_id, t2.max_gloss_avg
FROM t2
     JOIN t1 AS std   ON std  .std_avg   = t2.max_std_avg
     JOIN t1 AS pos   ON pos  .pos_avg   = t2.max_pos_avg
     JOIN t1 AS gloss ON gloss.gloss_avg = t2.max_gloss_avg
;

Another way to get the same results but in a slightly different format (3 rows instead of 1), would be to not use joins at all but more window functions. You could also:

combine the two CTEs into one.
remove the accounts from the first CTE. If there is a FOREIGN KEY from orders to accounts, the results will be the same.

The new query:

WITH t1 AS (SELECT account_id,
                   AVG(standard_qty) AS std_avg,
                   AVG(poster_qty)   AS pos_avg,
                   AVG(glossy_qty)   AS gloss_avg,

                   MAX(AVG(standard_qty)) OVER () AS max_std_avg,
                   MAX(AVG(poster_qty))   OVER () AS max_pos_avg ,
                   MAX(AVG(glossy_qty))   OVER () AS max_gloss_avg
            FROM orders o
            GROUP BY account_id)

SELECT t1.*,
       CASE WHEN std_avg   = max_std_avg
           THEN 'max_std_avg'   ELSE NULL
       END AS std_result,
       CASE WHEN pos_avg   = max_pos_avg
           THEN 'max_pos_avg'   ELSE NULL
       END AS pos_result,
       CASE WHEN gloss_avg = max_gloss_avg
           THEN 'max_gloss_avg' ELSE NULL
       END AS gloss_result
FROM t1
WHERE std_avg   = max_std_avg
   OR pos_avg   = max_pos_avg
   OR gloss_avg = max_gloss_avg ;


Default user Accounts in Mysql I'm new to MySQL and installed MySQL in Ubuntu 16.04. At the time of installation I have given password for root.

In the terminal I can access only root user and created user only.

bharath@bharath-desktop:~$ mysql -uroot -pbharath

  mysql: [Warning] Using a password on the command line interface can be
  insecure. Welcome to the MySQL monitor.  Commands end with ; or \g.
  Your MySQL connection id is 7 Server version: 5.7.16-0ubuntu0.16.04.1
  (Ubuntu)

  Copyright (c) 2000, 2016, 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>

But I am not able to access the default MySQL user in the terminal. It shows like this:

bharath@bharath-desktop:~$ mysql

  ERROR 1045 (28000): Access denied for user 'bharath'@'localhost'
  (using password: NO)**

If I hit enter after MySQL, it's not authenticated. How to access this?

As far as I can tell, the default users for a fresh mysql server is root@localhost and mysql.sys@localhost, you'll need to create a bharath@localhost user and create a .my.cnf under your $HOME folder with the following contents:

[mysql]
user=bharath
password=<yourpreferredpassword>
host=localhost


How to Query Which Table Is Inserted Mostly In SQL Server? I have a database and there is not any trigger which follows the transactions in said database.

Now, I have to make a report that shows which table has mostly inserted so far. How can I do that?

To know mostly inserted table you can simply retrieve number of rows/records in the table.

You can use following query to find it. Mostly inserted tables will be displayed on top of the records.

SELECT
   o.Name TableName,SUM(ps.row_count) Total_Rows
FROM
            sys.dm_db_partition_stats ps
inner join  sys.objects o
on ps.object_id=o.object_id
WHERE o.type='U'
GROUP BY
    o.Name
ORDER BY 2 DESC

Edit:1

As you described you requirement in comments, "you need find mostly inserted tables between a date range. It can be done using several method.

Using AFTER TRIGGER on the tables.
Using IDENTITY type column along with a column which records insert date & time (like Amina commented).
Using CDC you can record date & time of every changes in the table.
enabling Temporal Table (SQL Server 2016 +) feature on a table to record every changes.
Track Data Changes (SQL Server 2017)

Thanks!


What is difference between Oracle Apps and Oracle Application Server? I am very new to oracle, could anybody tell me what Oracle Apps and Oracle Application Server are.

Oracle Applications means software that helps you runs your enterprise businesses such as Accounting, Humans Resource, or Financials. But the Oracle Applications Server is the technology that, mostly installs on the server, integrates others software to work together. It can be called Middleware.

The first example is like Peoplesoft or SAP ERP. The latter is like Apache HTTP Server, Tomcat, Glassfish.


Sudden Query Performance Degradation On A Linked Server I have an application that uses a view on a SQL Server 2008 R2 instance. This view actually queries a linked server (SQL Server 2008) that has replicated data from our main ERP database. Yesterday, we began experiencing some timeout on the application that uses this view and we discovered that the view was taking over 1 minute to complete (before this it was a matter of seconds).

Here's what we know:

Running the query on the linked server directly returns results in a
matter of milliseconds.
Creating a view on the linked server, and then having the view on the
first server simply query the view on the linked server restores
performance to its original state. (view source), (workaround)
When running the query on the first server, I notice that there are quite a few OLEDB waits (using sp_WhoIsActive). Because of this, I decided to check the linked server while the query was running (on the first server). I noticed that the primary wait was ASYNC_NETWORK_IO.

Because of this, I am inclined to believe that there is an issue with either the network, or the configuration on my linked server. I am not sure that there is anything wrong with it, but I am posting it too.

What other things should I be looking at to help me to get to the root cause of this?

EDIT

Query Plan: http://i.imgur.com/yn6ZC.png

IO Stats: http://i.imgur.com/JNX2K.png

Query Columns: http://i.imgur.com/Coxx5.png

Top Operations: http://i.imgur.com/IROrA.png

Plan Tree: http://i.imgur.com/Wco4K.png

If I'm reading the plan and operations correctly, it looks like those nested loops joins with the inner remote operations are the problem.

They were estimated to execute ~750 times apiece, but actually executed almost 6,000 times each. Normally that might not be such an issue, but if network latency is involved with every call, the query becomes extremely sensitive to network conditions. This would certainly explain the ASYNC_NETWORK_IO waits you're seeing.

In any event, I would stick with your workaround -- it's probably better to protect the tables behind a view anyway instead of trying to do all the joins remotely.


Unable to reset the password of users in postgres Evrytime i run, su - postgres it asks me for a password which I did not set. I simply installed the postgres. I have gone to the pg_hba.conf.

# Database administrative login by Unix domain socket
local   all             postgres                               trust

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD

# "local" is for Unix domain socket connections only
local   all         all                               ident
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
# IPv6 local connections:
host    all         all         ::1/128               md5

I changed the postgres method from ident to trust and then ran sudo /etc/init.d/postgresql restart. Even then it continues to ask for password. i am not sure where am I going wrong?

What should I do?

I was finally able to solve the issue thanks to this site,

http://scratching.psybermonkey.net/2009/06/postgresql-how-to-reset-user-name.html


Failed installation of SQL 2012 on Windows Server Core I'm in the process of building the lab for 70-462 exam.  On page 49 of the text, it provides the command to install SQL 2012 on Windows Server Core:

Setup.exe /qs /Action=install /Features=SQLEngine,IS,Conn
/InstanceName=MSSQLServer /SQLSYSADMINACCOUNTS="Contoso\kim_akers"
/IAcceptSQLServerLicenseTerms

When I run this code, it appears things are running fine, the GUI window comes up and shows progress, then all the sudden it quits, the GUI goes away, and then I'm left with the following at the CLI:

The following error occurred:
Object reference not set to an instance of an object.

Error restult: -2147467261
Result facitlity code: 0
Result error code: 16387

Please review the summary.txt log for further details

I've done some searching and haven't come up with much.  Further, I don't know how to access this "summary.txt" file as it isn't anywhere to be found on the C:\ drive, so I can't see what file is stating.

Other details:

This is a lab built on top of Windows 8.1 (x64) using the Hyper-V feature.
16 GB of RAM, with 2GB installed for each
Huge hard drive
8-core CPU
The same ISO/DVD was used to install all the graphical versions of
SQL server, so I don't think it is corrupt download
All OSes are x64 Windows Server 2008 R2
SQL Server 2012 x64 SP1 (I've also tried with SQL 2012 minus SP1)

If anything else is needed, let me know.

Edit #1 5-10-15 1:15pm CST

When I execute the dir command in the folder suggested by Shanky, I get:

(DIR)    Datastore_ComponentUpdate
(DIR)    Datastore_GlobalRules
(DIR)    resources
(DIR)    Watson_1
Detail_ComponentUpdate.txt
Detail_GlobalRules.txt
Registry_SOFTWARE_Microsoft_MSSQLServer.reg_
Settings.xml
Setting_GlobalRules.xml
SQL-CORE_20150510_091104_Watson_ComponentUpdate.txt
Summary_SQL-CORE_20150510_091104_ComponentUpdate.txt
Summary_SQL-CORE-20150510_091104_GlobalRles.txt
SystemConfigurationCheck_Report.htm

I figured out the issue after doing some more searching and trial & error.

The process I used for finding the issue:

Started with the CLI and ran the following command:

This will provide a list of all features that are enabled and disabled

Dism /online /get-features /format:table

Next you use the following command to enable needed features

Dism /online /enable-feature /featurename:<featurename>

Example:

Dism /online /enable-feature /featurename:MicrosoftWindowsPowerShell

ALL of the following features need to be enabled for the installation to be successful:

NetFx2-ServerCore
NetFx2-ServerCore-WOW64
NetFx3-ServerCore
NetFx3-ServerCore-WOW64
MicrosoftWindowsPowerShell
MicrosoftWindowsPowerShell-WOW64

When I posted this question, I had all, but “NetFx3-ServerCore-WOW64” enabled.  Further, my problem was an assumption on my part.  I assumed that these features would be installed along with SQL setup, this wasn’t the case from the CORE installation, you need to make sure these features are installed/enabled BEFORE the SQL 2012 installation begins.  When I performed the GUI-based installations, I didn’t have worry about enabling the aforementioned features.

Moral of the story…DON’T ASSUME ANYTHING!  ALWAYS check prerequisites if you’re having installation problems for any software/hardware.


How to maintain referential integrity of an object graph when a node is deleted In a relational database, if a node in an object graph is deleted, how can the object graph's referential integrity be maintained?

For example:

A product is deleted, how is the invoice containing the order which had a reference to the product kept relevant, in that the order may no longer be relevant. Moreover, how is the order kept relevant?

Suggestions:

Disallow deletion of a product that has been ordered.
Move the order detail row to an archive table that has no foreign key to products (perhaps denormalized to store product name etc that would otherwise be lost) then delete the product.
Move the product to an archive table and the order detail to another archive table with a foreign key reference to the product archive table.


dokku-mongo Export Database Error dokku-mongo export Database command is showing error "This does not look like a tar archive"

$ ssh svuser@dbexample.com
$ dokku mongo:export database > backup.tar
$ tar -xfv backup.tar
tar: This does not look like a tar archive
tar: Skipping to next header
tar: Exiting with failure status due to previous errors

$ ssh svuser@dbexample.com
$ dokku mongo:export database > backup.tar
$ tar -xfv backup.tar
tar: This does not look like a tar archive
tar: Skipping to next header
tar: Exiting with failure status due to previous errors

As per github dukku-mongodb documentation by @josegonzalez here I don't think the export is actually a tar. The readme we have is wrong, but if you run dokku mongo:help export, you'll see the correct usage.

Here's what dokku mongo:help export says:

export a dump of the MongoDB service database

arguments:

service   service to run command against

examples:
by default, datastore output is exported to stdout

dokku mongo:export lolipop

you can redirect this output to a file

dokku mongo:export lolipop > lolipop.dump

export a dump of the MongoDB service database
arguments:

service   service to run command against

examples:
by default, datastore output is exported to stdout

dokku mongo:export lolipop

you can redirect this output to a file

dokku mongo:export lolipop > lolipop.dump

For further your ref here


MySQL slow log configuration Is there a way of making MySQL slow logs to start new log file every day ?
At the moment its just a single large file, and have to grep lines for every day. It would be much more convenient to have separate files for every days slow logs.

Do I have to configure a my.cnf or some linux feature ?

Update
As Aaron points out, there is the chance the copy-and-truncate can miss some entries. So the safer method is to move and FLUSH.

Original

This article has the basic principle to rotating the slow query log that I use. Basically you need to copy the slow log to a new file, then truncate the contents of the slow.log:

cp log/slow.log log/slow.log.`date +%M`; > log/slow.log

If you just move the slow log to a new file and creating a new  'slow.log', it won't work because the moved file still has the same inode, and mysql still has it open. I suppose moving the file and then issuing a FLUSH SLOW LOGS command would work, as that closes the file and reopens, but I find the copy-and-truncate to be just as effective and doesn't require logging into mysql.

His article mentions using logrotate in Linux, but I just made a cronjob to run once a day at midnight to do this for me.

Also, to address the issue of replication on FLUSH LOGS:

  FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK (with or without a table list) are not written to the binary log in any case because they would cause problems if replicated to a slave. [src]

So no, since those statements are not written to the binary log, it will not interfere with replication. For your purposes I would specify FLUSH SLOW LOGS to only close/open the slow query log.


What's the difference between equality (=) and inequality () on BIT field in SQL Server? Is there difference between WHERE [BitField] = 1 and WHERE [BitField] <> 0?
They seem logically equivalent.
SQL Server, however, disagrees and creates slightly different execution plans. (Which is bad because the second form doesn't use existing indexes very effectively.)
Am I missing something, or it is just the way inequality operators behave?

They seem logically equivalent.

They are.  Even with ANSI_NULLS OFF "a SELECT statement that uses WHERE column_name <> XYZ_value returns all rows that are not XYZ_value and that are not NULL."
I found an old feedback item for this here, you can go vote for it.


SQL Server Failover Cluster setup shared drive issue I'm trying to complete an SQL install on a two node cluster which has three LUNs  assigned from Filer storage present and currently owned by the first node.

I've selected the "New SQL Server failover cluster installation" option from the setup media and got to the "Instance Configuration" step.

Here I've named the SQL Server Network Name and instance and chosen the LUN I;ve dedicated to the DB Data (H:\MSSQL10_50.SQL_DevInst1), however, when I click on next I get the following error:

  Instance directory (or command line option /INSTANCEDIR) value for a cluster installation cannot be a directory on shared drive.

I'm baffled by this as the LUN is not a shared drive!

Choosing an actual local drive (C:) does allow this to continue but surely should accept my first choice?

Found the following on another forum which confirmed my hesitation. Should have just gone with my gut.....:

  The root directory has to be on the local drive on each node. There will be
  options to specify data and log directories later on in the installation process.

This was also confirmed by Krismorte above.


how to override a table's cardinality or statistics histogram I remember reading once that it is possible to force SQL Server to assume a table is a certain size, or perhaps even override a statistics histogram, when compiling a query.  Can someone explain how to do that, if it's possible?

To be clear: the purpose of this is to do what-if scenario analysis, outside of production, when writing queries.  I am not talking about modifying the query itself or using query hints.  (Examples of modifying the query and using query hints can be found in the answers to this question.)

You can use the UPDATE STATISTICS  WITH ROWCOUNT and PAGECOUNT option

See here some examples :
 https://www.mssqltips.com/sqlservertip/2870/using-with-rowcount-and-pagecount-option-of-sql-server-update-statistics/
or
https://sqlsunday.com/2016/02/27/update-statistics-with-rowcount/


DB2 on Windows: Fix encoding in db2cmd and for JDBC I'm running a DB2 v10.5 FP 7 on a Windows Server 2013 and there is a problem with the encoding setup:

The windows is set to use German(Switzerland) as region setting and input language.

chcp: Active code page: 850

My database is set to UTF-8, see this output from db2 get cfg

 Datenbankgebiet = CH
 Codepage für Datenbank = 1208
 Codierter Zeichensatz der Datenbank = UTF-8
 Landescode der Datenbank = 41
 Sortierfolge der Datenbank = SYSTEM_1252
 Alternative Sortierfolge (ALT_COLLATE)  =

UTF-8 Strings with umlauts are stored correctly in the database (checked by looking at the effective hex-values). However on retrieval, they are mixed up:

The correct value would be '87100 - Überbauung Hinterroos'. Notice how also the default db2 output is shown as 'Sõtze' instead of 'Sätze'.

The incorrect output can also be reproduced by selecting the same rows via JDBC/Java.

IBM Documentation says this behaviour is linked to the DB2CODEPAGE registry variable, which is not set explicitly on the server.

Any ideas how to fix this?

EDIT: (More info as requested by @mao):

The windows default code page is 850 too.
In the registry, codepages are as follows:

ACP 1252
OEMCP 850
MACCP 10000

To make sure I'm not following any wrong leads, I verified the stored string in the table by SELECT HEX(DISPLAY_NAME)... which outputs

3837313030202D20C39C626572626175756E672048696E746572726F6F73

The letter with umlaut is 0xC39C which is CAPITAL LETTER U WITH DIARESIS

Setting the DB2CODEPAGE variable to 1208 changes the output to a different wrong output:

After opening a support case with IBM, they gave me the clue that lead to a solution:

  -> Could you first right click in the title bar of the Command Window and take "Properties".

  Is it using a TrueType font for displaying characters?

As it turns out, changing the font (wtf?) changes the display of said characters.

This is some specific behaviour for the DB2 CLI window: Opening a regular cmd with a non TrueType font works as expected.


PostgreSQL kill - Sighup pid To reload the configuration files, we send the SIGHUP signal to the
  postmaster, which then passes that on to all connected backends.
  That's why some people call reloading the server "sigh-up-ing".

  As reloading the configuration file is achieved by sending the SIGHUP
  signal, we can reload the configuration file just for a single
  backend using the kill command.

  First, find out the pid of the backend using pg_stat_activity. Then,
  from the OS prompt, issue the following:

kill  -SIGHUP pid

I dont understand bold words. Because we have many PIDs for backend  and  if we kill a PID, how can it get changes from reload configuration file (postgresql.conf)?

Probably this will help:

  The Linux operating system normally terminates, or kills, a task by
  sending it a SIGHUP command. This stands for "Signal Hangup" and
  terminates a process on the system. If a programmer has not created a
  program restart command, or if you are having difficulties with an
  unresponsive program and wish to restart it, sending it a SIGHUP
  command will terminate the program and cause it to automatically
  restart.


MariaDB 10.2 custom install using mysql_install_db.exe In the past I used to setup MySQL/MariaDB manually on Windows boxes but don't remember exactly how. I unzip the .msi file and put every binary file into the desired place. I write a custom my.ini file including required InnoDB settings like autoextend but also log directory. Now I want to generate the mysql defaults database. According to the documentation it's not possible to provide the mysql_install_db.exe tool with a path to the my.ini file.
All files are generated into a single folder including a my.ini file I don't need.

How do I generate the database manually into my individual folder hierarchy?

I call mysql_install_db.exe with the correct --datadir argument and root password and have all files generated. I call mysqld --remove to delete the windows service. I call mysqld --install-manual [ServiceName] --defaults-extra-file=my.ini to finally set up the service using my very own my.ini file.
When this service is started up for the first time using net start the innodb-files are created freshly in the folders defined in the my.ini file. The system database mysql uses myisam table format and had not touched the innodb files.


Where can I find a drawing of the physical structure of a MySQL Server? I'm new to MySQL but want to learn more about it.  I know Oracle and Rdb quite well.  Can you show me where I can locate some type of drawing of the physical structure of a MySQL Server?

Below screenshot shows MySQL architecture at detailed level.


Restoring MySQL database from small ibdata1 I've got a database from a dead Rasperry Pi, which I want to restore. As it doesn't boot anymore because of a damaged SD card, I only could recover the data files, but not do a normal dump. The server which ran before on the Pi is now supposed to run on a Windows PC here. Therefore I installed XAMPP and tried to use the old data files, but with

    ...
    innodb_data_file_path = ibdata1:10M:autoextend
    ...

I get only

    [ERROR] InnoDB: auto-extending data file D:\xampp\mysql\data\ibdata1
    is of a different size 512 pages (rounded down to MB) than specified
    in the .cnf file: initial 768 pages, max 0 (relevant if non-zero) pages!

The size of the ibdata1 file is only 8MB, so I tried setting the size to this, but then MySQL complains, that the tablespace should be at least 10MB.

Is there any way to recover that data? E.g. extending the ibdata1 file manually?

The problem was not that the file was too small, but that it was incomplete. The original file has 90MB, but the copying failed because of the SD card failures.


How to flatten results of a table with two related "many" tables? I've reorganized some tables in my database to be more flexible but I'm not really sure how to write the SQL to extract meaningful data from them.

I have the following tables (somewhat abbreviated for a clearer example):

CREATE TABLE Loans(
    Id int,
    SchemaId int,
    LoanNumber nvarchar(100)
);

CREATE TABLE SchemaFields(
    Id int,
    SchemaId int,
    FieldName nvarchar(255)
);

CREATE TABLE LoanFields(
    Id int,
    LoanId int,
    SchemaFieldId int,
    FieldValue nvarchar(4000)
);

With the following data:

INSERT INTO Loans (Id, SchemaId, LoanNumber) VALUES (1, 1, 'ABC123');

INSERT INTO SchemaFields (Id, SchemaId, FieldName) VALUES (1, 1, 'First Name');
INSERT INTO SchemaFields (Id, SchemaId, FieldName) VALUES (2, 1, 'Last Name');

INSERT INTO LoanFields (Id, LoanId, SchemaFieldId, FieldValue) VALUES (1, 1, 1, 'John');
INSERT INTO LoanFields (Id, LoanId, SchemaFieldId, FieldValue) VALUES (2, 1, 2, 'Doe');

The objective is to get a query that is flat for a loan with all its fields. (In the real world there will likely be between 20-30 fields for the same schema, but we just have 2 in the example):

LoanNumber   First Name    Last Name
----------   -----------   ----------
ABC123       John          Doe

I cannot use a pivot that references the 'First Name' and 'Last Name' because I'll have no idea what will actually be there.

I have a SQL Fiddle here with schema already in place.

How can I get the desired result?

This can be done using the PIVOT function, but since it sounds like you want to change the query based on the schemaId, then you will want to use dynamic SQL.

If you had a known number of values or knew the columns for a specific schemaID, then you could hard-coded the query. A static query would be:

select loannumber,
  [First Name],
  [Middle Name],
  [Last Name]
from
(
  select
    l.loannumber,
    sf.fieldname,
    lf.fieldvalue
  from loans l
  left join loanfields lf
    on l.id = lf.loanid
  left join schemafields sf
    on lf.schemafieldid = sf.id
    and l.schemaid = sf.schemaid
) src
pivot
(
  max(fieldvalue)
  for fieldname in ([First Name], [Middle Name], [Last Name])
)piv;

See SQL Fiddle with Demo.

If you had an unknown number or you want the columns to change based on a SchemaId that you are passing into a procedure, then you will use dynamic SQL to generate the SQL string:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @schemaId int = 1

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(FieldName)
                    from SchemaFields
                    where schemaid = @schemaid
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        ,1,1,'')

set @query = 'SELECT loannumber,' + @cols + '
            from
            (
              select
                l.loannumber,
                sf.fieldname,
                lf.fieldvalue
              from loans l
              left join loanfields lf
                on l.id = lf.loanid
              left join schemafields sf
                on lf.schemafieldid = sf.id
                and l.schemaid = sf.schemaid
              where sf.schemaid = '+cast(@schemaid as varchar(10))+'
            ) x
            pivot
            (
                max(fieldvalue)
                for fieldname in (' + @cols + ')
            ) p '

execute(@query);

See SQL Fiddle with Demo. Both of these queries will generate the result:

| LOANNUMBER | FIRST NAME | LAST NAME | MIDDLE NAME |
-----------------------------------------------------
|     ABC123 |       John |       Doe |      (null) |
|     XYZ789 |    Charles |     Smith |         Lee |


Is GUID(uniqueidentifier) not suitable for a clustered index? When i use uniqueidentifier datatype for primary key column then it will automatically create clustered index...
So, will it slow down my queries or not ?

I am explaining some description of my problem..
I have two publisher which is replicating data into one subscriber(i.e primary Alwayson) then how to maintain primary key value in this scenario ?

So,Please give me a idea to maintain primary key value ?

It will slow down inserts rather than queries. That's because it is essentially inserting a record somewhere randomly inside the existing records, instead of just whacking it on the end (as it would if you were using an identity or sequential dates).

You can somewhat reduce this by using NEWSEQUENTIALID instead of NEWID (assuming the GUID is generated by the database rather than your application).


Restoring to modified database If i restore a DB to a DB that has beed modified vill it work? In case the modification and the backup could be compatible like if a new table has been added without required connections to it, or an existing table has a new column that can be null.

When you restore a database backup, it completely overwrites the prior database regardless of its contents. There is no merging functionality built into the SQL Server restore process.

If you need to merge the contents of two databases, or restore a particular table by itself, your best bet is to:

Restore the backup to a different database name, like Production_Restored
Copy the objects you want from Production_Restored into the Production database
Drop the Production_Restored backup when you're done

As you copy objects from one database to another, keep in mind that transaction logging is involved. If you copy a 50GB table from one of these databases to the other, the target database's transaction log will grow, fast. Your best bet there is to move the data across in batches. (And of course, there will be blocking involved as data is changed.)


How to observe output from query in Workbench 6.3? I am just starting to use MySQL Workbench 6.3, and I want to just run simple queries.  I've created a table with the appropriate schema and everything, but I can't seem to observe the output from my script.  Here's what it looks like:

I click on "run script file" in the Scripting drop down menu, but it doesn't seem to do anything at all?  Even if there's a syntax error, I'd like to just see the output and debug my script.  Am I missing something obvious?

Are there any records in table_A (not a good name by the way)? It should at least give you field names though.

For problems like this, it's always best to go back to the good-ol CLI (Command Line Interface) to make sure that nothing's wrong somewhere.

You could also open a new Query (interactive) window and copy, paste and run your code and check that way.

As @MikeLischke points out, the system wasn't designed for interactive scripting in modelling mode (unsurprising really).

However, when you open up MySQL Workbench, you have to choose the modelling option. You then get a new tab with that functionality. You can also open a new Query tab (File-> New Query tab...). You have to connect to an (already existing) schema to actually run any SQL (logically enough).

In the interests of fairness, I think you could maybe mark Mike's post as the correct answer (and give me a +1 if you think I deserve it! :-) ).


SSD vs HDD for databases I am trying to purchase a new Server to run MySQL Server on. This new server will be a slave of my main machine. However, this server will be dedicated for reporting only "Lots of reads and complex queries."

Now I am looking into investing into solid state hard drives but was wondering if it's really worth the price. The difference between an SSD and a SATA 7200 hard drive is about $1500 and the SSD has less disk space. If I do invest into SSD will the speed be noticeable?

I can purchase 4 (500GB SATA 7200) for $1500 less than purchasing 2 (500GB SSD)

Can you please help me making the decision to see if it is worth the upgrade or not?

Once more thing that I would like to mention is that I am not using query_cache so there will be lots of disk reads.

This server will have 32GB of RAM and will run Ubuntu 12.04

Yes with a lot of reads and reporting an SSD will make a huge difference. From 7200 RPM drive you could expect no more than ~100 IOPS while cheapest SSD can be as minimum 5x as fast as that. With good SSD you can get to 20000 IOPS or even more.

Also random writes in SSD are a lot faster as disk does not have to move every time.


SQL Server count value of column I call the table is [vDemo]
[]1

i want count MaLC by TenNV, result is

if record 13 MaLC is L then Count(x) = 16, Count(L) = 1

You can use SUM or COUNT functions to do this, with a case expression:

select
   TenNV
   ,SUM(count_x) as count_x
   ,SUM(count_L) as count_l
from
(
select
  TenNV
  ,Case when MaLC = 'x' then 1 else 0 end as count_x
  ,Case when MaLC = 'L' then 1 else 0 end as count_L
from vDemo
) A
group by TenNV

or with COUNT:

select
   TenNV
   ,COUNT(count_x) as count_x
   ,COUNT(count_L) as count_l
from
(
select
  TenNV
  ,Case when MaLC = 'x' then MaLC else NULL end as count_x
  ,Case when MaLC = 'L' then MaLC else NULL end as count_L
from vDemo
) A
group by TenNV

output of this:

TenNV   count_x count_l
Cam     5       0
Ngo     17      0

dbfiddle here


Unexpected SELECT permissions My user WebApp seems to have SELECT permissions on every table in every schema and I can't figure out why.

WebApp is only in the Public role.
Public doesn't own any schema.
Explicit permissions haven't been set on the tables in question
Effective permissions, as reported by SSMS, show it has SELECT permission

Figured it out. A bad script granted database-wide SELECT permissions to the user. Found it using this script:

WITH    perms_cte as
(
        select USER_NAME(p.grantee_principal_id) AS principal_name,
                dp.principal_id,
                dp.type_desc AS principal_type_desc,
                p.class_desc,
                OBJECT_NAME(p.major_id) AS object_name,
                p.permission_name,
                p.state_desc AS permission_state_desc
        from    sys.database_permissions p
        inner   JOIN sys.database_principals dp
        on     p.grantee_principal_id = dp.principal_id
)
--users
SELECT p.principal_name,  p.principal_type_desc, p.class_desc, p.[object_name], p.permission_name, p.permission_state_desc, cast(NULL as sysname) as role_name
FROM    perms_cte p
WHERE   principal_type_desc <> 'DATABASE_ROLE'
UNION
--role members
SELECT rm.member_principal_name, rm.principal_type_desc, p.class_desc, p.object_name, p.permission_name, p.permission_state_desc,rm.role_name
FROM    perms_cte p
right outer JOIN (
    select role_principal_id, dp.type_desc as principal_type_desc, member_principal_id,user_name(member_principal_id) as member_principal_name,user_name(role_principal_id) as role_name--,*
    from    sys.database_role_members rm
    INNER   JOIN sys.database_principals dp
    ON     rm.member_principal_id = dp.principal_id
) rm
ON     rm.role_principal_id = p.principal_id
order by 1

https://web.archive.org/web/20141128165740/http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions--_2800_2_2900_.aspx


Why does the PG manual state to modify the "pg_hba.conf" file before starting the server the first time? The following is from https://www.postgresql.org/docs/current/static/creating-cluster.html with original emphasis on before:

  or modify the generated pg_hba.conf file after running initdb, but before you start the server for the first time.

Is the manual saying this to prevent unauthorized users from accessing a poorly secured PG server? Or is there some other reason why the pg_hba.conf file should be edited before starting the server for the first time?

Yes, it is purely a security measure to prevent even momentary unauthorized access.  If a hostile party on the same machine knows you are in the process of setting up a new cluster, they could easily test in a loop for it to become connectable with trust authentication.  So even if you leave it open for just a few seconds, it could be exploited in that time.

If you start the server first and then edit pg_hba.conf to make it more secure but then forget to do a pg_ctl reload or equivalent, it will remain vulnerable.   If you do do a reload, existing exploit connections will remain live even though new ones can't be made.  If you do a pg_ctl restart or equivalent, existing exploit connections will be severed.  But it only takes a few milliseconds to do serious damage.  For example, they could create a new postgres superuser account with a password of their choosing.  Or they can perhaps leverage the existing default PostgreSQL superuser account to manipulate the home directory of the OS user running the PostgreSQL server in arbitrary ways.  Including things like adding an entry to ~/.ssh/authorized_keys.