How do I move or redistribute FILESTREAM files between containers? We have a FILESTREAM container with a few million files, and we believe it's the cause of our performance issues we're experiencing (abundant timeouts).

According to this blog about FILESTREAM best practices, each container shouldn't reach more than 300,000 files.

According to the accepted answer here, there is no way to accomplish it except by recreating the table to a new FILESTREAM location.

Is this true for my situation?
And if so, what is the recommended approach to ensuring that enough containers are created automatically to handle this without manual intervention every 300,000 files?

The structure of the table follows:

CREATE TABLE [dbo].[Documents](
    [ContentPath] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [FileContent] [varbinary](max) FILESTREAM  NULL,
 CONSTRAINT [UQ_IX_Documents_ContentPath] UNIQUE NONCLUSTERED
(
    [ContentPath] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY] FILESTREAM_ON [FSFileGroup_1]

We're using SQL Server 2012. Not Enterprise unfortunately (which we only recently realized supports multiple containers per filegroup).

Although we never do record updates, we do a lot of writes, probably as many as we do reads. The pattern is: just one at a time, reads by ContentPath, and writes in no specific container or order.

A good option for you, I would imagine, is to split table logically, so every section would be in its own folder and have under 300k files(=records).

In SQL Server logical table split is usually done with Partitioning (this is an Enterprise feature). Partitioning essentially maps sections of table (partitions) to physical storage (filegroups). Various partitions can be assigned to different filegroups. FILESTREAM is essentially a property of a filegroup.

Design

This makes up the spine of your solution:

Partitions reside on different filegroups
Each filegroup is a separate FILESTREEAM folder/container
Size of each partition under 300k records

Here is how to partition a table with FILESTREAM column.

Main advantage:  you will be able to manage your FILESTREAM data per Partition at a time, not whole table.

You'll need:

ID that would allow to track size of a partition or at least not allow random writes across partitions

[Id] [uniqueidentifier] ROWGUIDCOL NOT NULL DEFAULT (newsequentialid())

Maintenance job, that checks how full last partition is, creating new filegroups and partitions in advance, rebuilding indexes per partition etc.
To cater for Insert operations make you partitions smaller than 300k, so index is rebuild one partition at a time.

This design is for mostly read-only table with inserts happening at the end of it. Do consider writes and updates and impact of partitioning on them, if you have not done so already.

Migration

In order to make a table partitioned, you need to build an index. There 2 main ways:

Rebuild index for the whole table. It will requires x2-x3 times more space than the table. It is not suitable for big tables for a number of reasons.
Migrate data into new table, which is already partitioned.

My preferred method for this:

Create a table of the same structure as target partitioned table.
Insert data using DELETE... OUTPUT DELETED ... INTO <intermediary table> for data that fits 1 partition.
If number of rows is too big for one transaction, then wrap this into a loop
Create index on Partitioned scheme
Switch partition to Target table
Drop index on Intermediary table
Wrap in a loop and repeat for all partitions/data

Inserts

Newly inserted rows ideally should go into last partition. However, there is a catch about NEWSEQUENTIOALID() function:

  Creates a GUID that is greater than any GUID previously generated by
  this function on a specified computer since Windows was started. After
  restarting Windows, the GUID can start again from a lower range, but
  is still globally unique.

It means that new writes will go into last partition only until first reboot of the server. And this will eventually happen. New rows will be inserted in the middle of the table. But remember we have partitions now? Only one partition at a time will be affected. Still it will exceed 300k rows at some point in time.

Main option here is to split partition. Existing partition will split in 2, where right part will form a new partition. New partition should go onto new filegroup. It will not be a metadata only operation: files will be physically copied into another partition.

A work around would be moving (delete and insert rows into Intermediary table) rows on same PS; splitting partition and moving rows back. But it involves moving data once anyway. If intermediary table in on same PS, then split will involve moving data. If not, then data needs to be moved second time back into main table.

Multiple FILESTREAM containers

Multiple FILESTRAM containers are a feature of Enterprise version. A container is often referred to as a "folder" in FILESTREAM container. Actually it is a database file in a filegroup of FILESTREAM type.

It does help to stay under 300k files per NTFS folder, but it is very difficult to manage stored files across these folders explicitly.

Article Rebalancing data across files in a filegroup from 2011 by Paul Randall gives a hint on use of files in a filegroup:

  [SQL Server] also uses an algorithm called proportional fill that
  aims to allocate data from the files according to how much free space
  they have relative to the other files in the filegroup.

  ...

  This means that if you add a new data file to a filegroup that has mostly full
  data files, the proportional fill weightings will be such that the new
  file will be the one where allocations come from until it fills up to
  the same level as the older files. The new file in essence becomes an
  allocation hot spot.

SQL Server will be filling newly added files first until they fill up to the same %age level as others.

It may look like an easier alternative to Partitioning, however it is not:

You cannot do anything about files already stored in existing containers.
You still have one big table that you need to manage within single transaction

At the same time you can blend multiple FILESTREAM containers into Partitioning solution to make folders smaller and faster.

UPDATE for Standard Edition (SE)

SE does not have Partitioning nor Multiple FILESTREAM containers.
It means we can have only one partition and one container per table.

One option will be to have a number of tables with a UNION ALL view on top.

Number of tables will fixed for most of the time. You already have quite a number of documents, so you can look at distribution of GUIDs and creates tables for smaller intervals for higher density at the lower end.
Create a view that will UNION ALL all tables. Hint: in order to avoid scans of all tables add a WHERE clause for IDs belonging to that particular table. it will help Optimizer to exclude tables there a particular does not exist:

    Select * from Table1 where ID between 1 and 100
    UNION ALL
    Select * from Table2 where ID between 101 and 10000
    UNION ALL
    Select * from Table3 where ID between 10001 and 100000000

Make sure that you cover all possible values of GUID from 0000-...-0000 to FFFF-...-FFFF
 - This view will updatable, so you will be able to insert data into relevant table by using this view. There are imitations to this: Modifying Data Through a View.
 - You need to copy all data physically from existing table to new tables. it can be achieved via the master view.

P.S.
SQL Server is a RDMS first of all. Features to manage huge volumes of unstructured data like VARBINARY(MAX) are add-ons and relatively new.
Therefore there are other limitations that need to be taken into account:

Max number of partitions per table:       15 000
Max files groups OR files per database:   32 767
Max files per container:                 300 000
(=filegroup in my example, =database file in case of multiple containers per filegroup)

Size of transaction log is a limitation for one transaction as well as time that a transaction takes to complete. You need to keep individual operations manageable and possible to roll back in reasonable time.


DB2 developers can't create table in own schema but SQL and Oracle devs can. Justified? As a developer new to DB2, I was surprised when I discovered that I could not create my own tables in the development database environment. So, I posed a question to the db2 DBA. I don’t want to be too much of a nuisance by questioning policy and was hoping to get a better understanding of whether our policy of preventing developers from creating tables is justified for DB2.

Question:
I understand that you must follow policy, but do you know why our company might create a DB2-only policy to prevent DB2 developers from creating tables in their own schema while simultaneously allowing SQL Server and Oracle developers to do so?

Answer:
The mainframe is amore controlled environment than Oracle and SQL.   All applications within our company share the DB2 subsystems.   In Oracle or SQL, each application has its own instance and their testing would not impact another instance.
-- DB2 mainframe does not create multiple tables in the same file like Oracle.  So, on the mainframe if you were able to create tables in test, you could potentially use up all the dasd if you created a huge table and if you were to test against that huge table, it could blow out the subsystem sort space, edm pool,etc. that is sized for a test environment.
--DB2 Mainframe applications share subsystems and all system resources.  Each test db2 subsystem is sized according to the function of that DB2 subsystem…  DB2T – testing, DB2F – System testing, DB2B – Production Support.  They all have different sizing allowance.

Question to SO:
Re: “you could potentially use up all the dasd if you created a huge table”
Doesn’t DB2 have the ability to limit space usage by schema to prevent a developer from using up all of the DASD (which I presume is Disk Access Storage device/aka disk space)?
Looking up the acronym EDM and finding “Environmental Descriptor Manager” I presume that this is what contains all of the table definitions, like a Master database. Is this a valid concern that is specific to DB2?

From the DBA’s answer, I generally understand that our architecture is such that each environment, eg, TEST, UAT, etc, does not run in a separate instance and one instance can affect the other, but it sound like if this is the case, then we already have this issue given that developers have the ability to insert millions of rows into tables that were created for them. Isn’t the real threat the size of the tables that I create and rather than the ability to create a table? I would think that a DB2 DBA should be able to limit developers to a safe level.

They probably do this because storage allocation on the mainframe is much more complex than for unix or windows platforms.

zOS is basically a very modern sophisticated piece of harware and operating system that is emulating a 1960s mainframe. In much the same ways as the latest Intel chips are emulating a 1980s 386 chip.

The problem is that the very low level close to the wire disk I/O which was necessary to get high bandwidth and and quick access times on the original hardware is "baked in" to the OS and many applications. So all disk storage for legacy apps deals with channels and volumes. A volume is a "pretend" 3330 disk with 8GB or 32GB of storage usually allocated as a piece of a real nTerabyte raid system.

To define tables properly in zOS DB2 you need to create tables spaces which in turn requires you to allocate storage on specific volumes this is fairly messy as four or five entities need to be linked together TABLE -> TABLESPACE -> Volume Group -> Disk Volumes + Index -> IndexTablespace -> Volume Group -> Disk Volumes.

Basically your DBAs are saving you a lot of grief by not letting you play with the matches.

P.S. If you use the modern half of zOS (Unix Systems Services) this all becomes irrelevant but it's going to be a long slow migration!


How to find all views with invalid column names I am trying to refresh all my views using code I found on the web:

DECLARE @ViewName VARCHAR(256)
DECLARE cViews CURSOR READ_ONLY FOR SELECT name from sys.views
OPEN cViews
FETCH NEXT FROM cViews INTO @ViewName
WHILE @@FETCH_STATUS != -1
BEGIN
    EXEC SP_REFRESHVIEW @ViewName
    PRINT 'View ''' + @ViewName + ''' has been refreshed.'
    FETCH NEXT FROM cViews INTO @ViewName
END
CLOSE cViews
DEALLOCATE cViews

I am getting the error Error Message: Invalid column name 'CloseDate'., but it's not telling me which view has the error. How would I find the name of all views with an invalid column name with the name of the invalid column? Thank you.

You could try importing the database into an SSDT database project and building the project. It should report this type of thing.

Alternatively you could try

SELECT *
FROM   sys.views
       CROSS APPLY sys.dm_sql_referenced_entities (
                                        CONCAT(QUOTENAME(SCHEMA_NAME(SCHEMA_ID)), '.', QUOTENAME(NAME)),
                                        'OBJECT')

And then peruse the error output


Locating a schema that owns a role Let's say we've logged on under the A schema.

I am able to execute the following statement

grant select on wph_pden to LANDING_R;

However, the LANDING_R object can not be located in the schema A. It is absent from all the system tables and views.

How do I find out which schema owns the LANDING_R (which I know is a role, not user)???

Roles are not owned, but granted to users:

select grantee from dba_role_privs where granted_role = 'LANDING_R';

Object privileges granted to LANDING_R:

select * from dba_tab_privs where grantee = 'LANDING_R';

System privileges granted to the LANDING_R:

select * from dba_sys_privs where grantee = 'LANDING_R';


How to recover SA account password without restoring it? I forgot my SA password and resetting the password is out of the question because internal applications are using it and will will affect them. I also don't have an option to change the SA credentials for those applications.

This is why I must know what the password is WITHOUT restoring it.

Is there any method for that?

There is no way you can retrieve the lost sa password without resetting it. Relevant SO thread here


Where to store versioned/historized records? In our application we are using versioning records using triggers. The application is supposed to handle millions of records in the future and the history/versioning tables are there to ensure that there is an audit trail for various transactions.

Currently everything is stored into one database. My concern is that these tables will bloat the transactional database, and I really want to move all record history into another database mainly because:

transactional database can be backed up more frequently since it is smaller.
indices can be scheduled to be rebuilt more frequently on the transactional db.
The history can be archived more easily if needed.

Are there any performance advantages to choosing this strategy or is the extra database another moving part which is not needed? What is the prescribed solution for this sort of thing?

We use Change Data Capture. SQL Server essentially creates journal tables and then automatically reads from the transaction log and fills in the journal tables with information about DML operations on the original table.

To save space, we compress the journal tables. This means your journal tables are stored in the same database, but if you are having space issues, you could offload them to a different file/filegroup and store that on a different disk.

The journal tables are in a separate schema so you could exclude that entire schema from your short-term backups and only include them in your long-term backups.


PostgreSQL migrate column to array I got a table in PostgreSQL containing a column 'X' of type character (255). Now I would like to migrate that table so that 'X' changes to type character[] (255), but I want the columns original character (255) value to be in the migrated column as an element of the new array in 'X'.

Using sequelize, is there a way to do that through a migration script? Or is there a way to do it in JavaScript with transitions and stuff?

What would be the best (or maybe only) way to go?

The following DDL will convert the column to an array and make the existing value be the first array element:

alter table the_table
   alter column x type varchar(255)[] using array[x];

varchar is a synonym for character varying

To reverse this change, you can apply the same logic:

alter table the_table
   alter column x type varchar(255) using coalesce(x[1],'');

x[1] returns the first element, but will only return null if the array is empty. With coalesce() this can then be turned into an empty string ''


Postgresql streaming replication and wal archive? We have postgresql 9.1 configured for streaming replication with a WAL archive, and this works great. We would like to use a shared WAL archive so we can restore from periodic base backups. The postgresql docs refer to this as a "long-term WAL archive area."

However, we archive to a shared folder (using archive_command), and after a failover, the new master tries to archive the same WAL segment filename as the old master. The contents are different. What is the right way to handle this?

We have tried to allow the new master to overwrite the old file, but then when we test restore from the base backup with a configured restore_command that restores from the shared WAL archive, we get various errors when the restoring server hits the overwritten log files. (errors like "record with zero length at 0/E5003128") So it looks like overwriting is the wrong approach. What is the right approach?

In a restore situation, there is no master and no slave. There is a fresh clean database that is restoring from a basebackup. It also has a restore_command. How are the archive_command and restore_command supposed to work together to let the restore work? (i.e. where does the archive command write after a failover, and where does the restore_command read from?)

A bit more information so this is perfectly clear:

Let's say we have 4 servers. A, B, X, and R. A and B are identical, and are a master/slave pair using streaming replication. They have the same configuration, except as far as recovery.conf v. recovery.done on the slave. X is a separate reliable host where we store backups and archived WAL files. archive_command (on either A or B, whoever is master) archives WAL files to a shared folder on X. restore_command (on either A or B, whoever is slave) restores from that shared folder on X. Periodically, we take a basebackup of the master and store it on X. This works, with one caveat below.

In a disaster situation (or for testing) we want to restore a full copy of the database onto R. We restore one of the basebackups from X on R. R now has the restore_command to read from the shared folder on X. It can easily restore all the WAL files made from the same source as the basebackup, but it fails as soon as it hits a WAL file from the new master after a failover. (see Caveat).

Caveat: if archive_command does as the specs says, and refuses to overwrite a file with the same name, then the new master will fail to archive any WAL file segments. On promotion, it tries to archive everything it has locally, including segments from the old timeline. In fact, it has segments that were never archived by the old master, because they were streamed before the xlog segment was closed. So currently, we allow the archive command to overwrite archived WAL files in this situation. It's these WAL files that fail to restore in a recovery. We know this is wrong, somehow, but don't know what the right behavior should be. With this behavior, though, failover and later fail back work.

Further testing revealed that the different WAL files were mostly those that had been rotated but not yet written to. The new slave would try to archive these (possibly fixed in 9.2) but they wouldn't be used for recovery.

The other problem is the overwriting. We disabled the overwrite (instead storing with a new filename so we at least keep the WAL just in case) and everything works... both failover and later restore.

NOTE: my earlier answer here that was completely wrong. See the edit log if you need to see how wrong.


Multiple versions of PostGIS and PgRouting on CentOS 7 I'm trying to install multiple versions of PostGis and PgRouting in the same instance of PostgreSQL v9.6, using CentOS 7.5.

I've been fighting with dependencies and shared libraries, to make all working.

So the question is, which is the best way to manage de dependencies versions? If I install a major dependency version, old Postgis version still working?

Some dependencies are: GDAL, GEOS, Proj4, etc.

I've been organizing them in folders, with contains subfolders with the version of the dependency, using --prefix=path/v3.0.0 on ./configure script during the depency installation.

Example: Folder -> GEOS - Subfolders: v3.0.0, v3.4.0, etc.

After that, when i want to install any PostGIS version, I use ./configure to use any dependency absolute path.

Example: ./configure --with-pgconfig=path --with-gdal=path/version --with-proj4=path/version ...

But I'm not sure that is a right way.

Any suggestion is welcome to clear my mind.

It won't work to install several versions of PostGIS in the same PostgreSQL software directory, since the resulting shared objects will have the same name. Using a different --prefix is not enough, since most of PostGIS is not installed there, but in the PostgreSQL software directory.

So you should make a copy of your PostgreSQL software directory and install the different PostGIS versions in different directories.

If you really need different versions of Proj, GDAL etc. (why?), things become difficult, and you'd have to use a different LD_LIBRARY_PATH for each instance.

But perhaps it is much simpler, since the recent versions of PostGIS are binary compatible and come with installation scripts for several versions.

So maybe you can do with just installing the most recent PostGIS version and using different versions in different databases:

CREATE EXTENSION postgis VERSION 2.4.0;


MSQL state "closing tables" taking double the time of a process I have a delete query that is taking a long time. When I check in processlist that status is showing as "closing tables". It is taking a long time to close tables.

Example:
I run a query and the total time is 1:42 seconds and in that 80-90 seconds are for closing table.

This is happening for all queries like load data , select and insert.

Below is my.cnf settings:

key_buffer_size = 2G
sort_buffer_size = 8M
read_buffer_size = 10M
read_rnd_buffer_size = 10M
join_buffer_size = 2M
bulk_insert_buffer_size = 100M
myisam_sort_buffer_size = 64M

#tmp_table_size = 100M
#max_heap_table_size = 64M
#max_allowed_packet = 64M

table_cache=1024

My meminfo

[root@localhost ~]# free -m
             total       used       free     shared    buffers     cached
Mem:          7862       6686       1175          0         11       4091
-/+ buffers/cache:       2583       5278
Swap:        15998         18      15980

Please tell me what kind of changes I need to do in my.cnf file?

Closing tables this thread is flushing the changed table data to disk and closing the used tables. This should be a fast operation. If not, you should verify that you do not have a full disk and that the disk is not in very heavy use.

This bug report can be related as well.

Further,

MySQL closes an unused table and removes it from the table cache under the following circumstances:

When the cache is full and a thread tries to open a table that is not in the cache.
When the cache contains more than table_cache entries and a table in the cache is no longer being used by any threads.
When a table flushing operation occurs. This happens when someone issues a FLUSH TABLES statement or executes a mysqladmin flush-tables or mysqladmin refresh command.

You can determine whether your table cache is too small by checking the mysqld status variable Opened_tables, which indicates the number of table-opening operations since the server started:

mysql> SHOW GLOBAL STATUS LIKE 'Opened_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Opened_tables | 2741  |
+---------------+-------+

If the value is very large or increases rapidly, even when you have not issued many FLUSH TABLES statements, you should increase the table cache size
For more information refer how MySQL opens and closes a tables here:

http://dev.mysql.com/doc/refman/5.0/en/table-cache.html


Table with contacts: how to differ between people and organizations? In several projects, I've seen the same problem pop up. But so far, I have not found a satisfying solution. Here's the issue. I need a table with contacts. These 'contacts' could be individual people, or organizations. From one point of view, I prefer to have these contacts mixed (one table with both types), but from another point of view, I prefer to have them separated. Here's some arguments:

A company that sells products to the general public has 'clients' of both types. When relating a sale, it is most convenient to refer to a 'client ID' without having to specify what type of client this is (for each sale).
A company can have many providers that it uses. Some are individual people (like a plummer or a consultant), others are companies. When relating a 'purchase order' I again prefer to relate to just one provider ID, without having to differentiate between provider types.
When managing the actual contacts, I do want to differentiate between the two types. That way, I can have the right fields for each. For instance, "First Name" and "Last Name" are not useful for companies, but they do are for people. Also, if I have a separate table, I can actually link people working at specific organizations.

Being pretty common - I guess - I hope that others have tackled this problem before. What is the way you set this up?

I suggest you create them as separate tables.

Create an Individuals table and an Organizations table so that each
can have the proper fields for their respective types.
Draw from the same sequence to populate the ClientID field in both
tables.
Create a view to UNION ALL both tables for code that needs
information about both.
Create foreign keys to the tables as necessary.


Set up new publication on SQL Server 2014 database which is part of an availability group I like to setup a publication (transactional replication) on a SQL Server 2014 database which is part of an availability group. We have a primary and one secondary (non-readable) replica.

On the instance I have already another running publication. The distributor is running outside of the availability group, even on a different domain.

Problem

Initially the set up of the distributor was done on the other replica (which is currently the secondary). Also the other existing publication was initially created on the other hosts. That's the first publication I have to create after I did a failover of the availability group to the secondary.

Error

Running the following command on the database to publish

EXEC sp_replicationdboption @dbname = 'mydatabase',
                            @optname = N'publish',
                            @value = N'true';

I received the following error:

  Msg 20028, Level 16, State 1, Procedure sp_MSpublishdb, Line 56 [Batch Start Line 0]
  The Distributor has not been installed correctly. Could not enable database for publishing.

I wonder why that occur as I have already on publication up and running on another database without any error and also working properly.

Ideas

Checked the management logs doesn't help as nothing was logged. After cross checking the distributor I saw also that the publisher is registered properly.

Any ideas what could be issues here?

I found a solution for my problem and like to share it.
Solution
By using the fourth parameter ignore_distributor I could get around the problem and was able to set option publish for my db.
exec sp_replicationdboption @dbname = N'mydatabase',
                            @optname = N'publish',
                            @value = N'true',
                            @ignore_distributor=1

But unfortunately it wasn't solving my overall issue on creating a new publication on this existing publisher.
New (second) Problem
The next step was to create the log reader agent job by executing the following command.
EXEC sys.sp_addlogreader_agent @job_login = N'<domain user>',
                               @job_password = N'<password>',
                               @publisher_security_mode = 0,
                               @publisher_login = N'<username>',
                               @publisher_password = N'<password>';

It resulted into the following error

The Publisher 'ServerB' does not exist. To view a list of Publishers, use the stored procedure sp_helpdistpublisher.

Ok, strange as I had a running publication from this server. So, I was checking it by executing the mentioned SP on my distributor. The result was showing me my publisher correctly. So far so good. Where is now the problem? Even after checking a couple of other thinks on my publisher I wasn't able to find any think wrong.
Let's check the distributor and run a trace when I executed my above command.
At the end the issue was created by the system procedure sp_MSreplagentjobexists. It was checking the publisher configured in the table dbo.MSreplservers within the distribution db. Bingo! My publisher was missing in there. I only found my original publisher in there. So, getting the server id from sys.server and insert the current primary node of my availability into this table worked like a charm.
The last think I had to do was to redirect my publisher which are running on an availability group to my AG listener.
-- redirect ServerA
exec sys.sp_redirect_publisher
       @Original_publisher = 'ServerA_primary_node',
       @Publisher_db = 'mydatabase_to_publish',
       @Redirected_Publisher = 'AGListener';
GO

-- redirect ServerB
exec sys.sp_redirect_publisher
       @Original_publisher = 'ServerB_seconary_replica',
       @Publisher_db = 'mydatabase_to_publish',
       @Redirected_Publisher = 'AGListener';
GO

Finally my new publication was working :)
So, I am not sure if this a bug and configuration issue made in the past. As I have no idea how and when the entries are made in the table MSreplservers of the distribution.
Any thoughts?


How to model a database to retain driver related information? I am modelling a chauffeur-like system, where for each driver that registers in the platform, he/she can either be a "solo" driver or belong to an already associated company.

Independently of the type of driver, the following information is registered:

Driver ID (Primary Key - I considered using this parameter in order to have the system more "ordered")
Name
Email (UNIQUE)
Phone Number (UNIQUE)
Registration Date (Time Stamp)

Now, if it's a "solo" driver, the following additional information will also be asked:

Vehicle type (for example: 'car', 'motorbike', etc.)
Plate number (UNIQUE)
Fiscal number (UNIQUE)

If he/she belongs to an associated company, only the name of the company will be asked (since all the vehicles will be associated to the car company).

The company table will have the following attributes:

Company ID (Primary Key - I considered using this parameter in order to have the system more "ordered")
Name
Address
Contact (UNIQUE)
Email (UNIQUE)
IBAN (UNIQUE)
Fiscal Number (UNIQUE)

Lastly, the vehicle table will have the following attributes:

Vehicle ID (Primary Key - I considered using this parameter in order to have the system more "ordered")
Company ID (Foreign Key)
Vehicle Type
Plate Number (UNIQUE)

A vehicle cannot exist as a standalone entity, i.e., it must be associated to a driver or to an existing company.

I would like to model this scenario using a relational DB since this info will be coupled with a routing algorithm. I'm having a difficult time finding a "clean" solution for this problem (specially considering that we are storing related-entity information, vehicle, in two different tables)... so, what's the best way to model this situation?

Responses to clarification requests

  In your business domain, is it possible for a solo driver to eventually work for a car company?
  Is it possible for a driver that works for a car company to become a solo driver only?
  Can a driver be working for a car company and, at the same time, be working as well as a solo dirver?

Regarding questions 1 and 2, those are cases I haven't quite considered, but they seem plausible possibilities, so we can consider that those are possible scenarios.

Regarding question 3, for the moment, let's consider that a person can only be a solo driver or a company driver.

Assuming there is a 'company' table as well that drivers can be associated to?

If so, one option is as follows:

Driver:

Driver_ID (PK)
Vehicle_ID (FK to Vehicle... nullable)
Company_ID (FK to Company... nullable)
solo_driver (boolean value [kind of optional, but could be useful])
other Attributes...

Vehicle:

Vehicle_ID (PK)
Company_ID (FK to Company... nullable)
Other Attributes...

Company:

Company_ID (PK)
Other Attributes...

If the driver is a solo-driver, then they have an associated Vehicle_ID
If the driver is a company-driver, then they have an associated Company_ID
- The company_ID would have associated (one-to-many) vehicle_ID's


initial and last date of change I have a table with ID's, dates and values witch are the results of periodic tests.
I can find the latest test with the according result.
But I need also the initial date since when the same result is marked.

ID  Date       Value
01  2015-01-01  2
01  2015-02-01  3
01  2015-03-01  3
01  2015-04-01  2
01  2015-05-01  2
01  2015-06-01  1
01  2015-07-01  3
01  2015-08-01  3
01  2015-09-01  3

So here the last date and result for ID 01 are 2015-09-01 and 3
The initial date that I also need is 2015-07-01 as that is the initial dat of the last serie with value 3

How can I write this query?

I get the last date and score by ID using this query:

select * from tblScores s
join (
    select id, max(Date) as MaxDate
    from tblScores group by id
) Q on s.id = q.id s.date = q.maxdate

These queries with variables simulates the ROW_NUMBER() Window Function (not supported by MySQL).

Query 1 ID

If you are working with only 1 ID at a time like in you sample, this query can be used:

SELECT ID, MIN(Date), MAX(Date)
FROM (
    SELECT *
        , @n := CASE WHEN @value <> Value THEN @n +1 ELSE @n END as n
        , @value := Value
    FROM data1
    CROSS JOIN (SELECT @value, @n := 0) v
    ORDER BY Date DESC
) as x
WHERE n = 0
GROUP BY ID
;

Output:

ID |              MIN(Date) |                   MAX(Date)
 1 | July, 01 2015 00:00:00 | September, 01 2015 00:00:00

Query several IDs

If you want the same result for several IDs, this query can be used:

SELECT ID, MIN(Date), MAX(Date)
FROM (
    SELECT *
        , @n := CASE
            WHEN @ID <> ID THEN 1
            WHEN @value <> Value THEN @n +1
            ELSE @n END as n
        , @value := Value
        , @ID := ID
    FROM data2
    CROSS JOIN (SELECT @id, @value, @n := 0) v
    ORDER BY ID, Date DESC
) as x
WHERE n = 1
GROUP BY ID
;

Output:

ID |              MIN(Date) |                   MAX(Date)
 1 | July, 01 2015 00:00:00 | September, 01 2015 00:00:00
 2 | July, 01 2015 00:00:00 |      July, 03 2015 00:00:00

This SQL Fiddle contains both samples and queries.

Data used:

INSERT INTO data2
    (`ID`, `Date`, `Value`)
VALUES
    (01, '2015-01-01', 2),
    (01, '2015-02-01', 3),
    (01, '2015-03-01', 3),
    (01, '2015-04-01', 2),
    (01, '2015-05-01', 2),
    (01, '2015-06-01', 1),
    (01, '2015-07-01', 3),
    (01, '2015-08-01', 3),
    (01, '2015-09-01', 3),
    (02, '2015-01-01', 2),
    (02, '2015-02-01', 3),
    (02, '2015-03-01', 3),
    (02, '2015-04-01', 2),
    (02, '2015-05-01', 2),
    (02, '2015-06-01', 1),
    (02, '2015-07-01', 3),
    (02, '2015-07-02', 3),
    (02, '2015-07-03', 3)
;

Table data1 only contains the first rows where ID=1 (see SQL Fiddle).


Moving Clustered Index to new filegroup results in excessive filegrowth I've Moved the clustered index on large tables to new filegroups to defrag, compress, and to recover space more easily from the original "primary" filegroup.

Problem:

A 315GB compressed Clustered Index (page compression) resulted in a filegroup that expanded to over 650GB.  (Uncompressed, this table is north of 2TB.)  The free space is not limited to the end of the file, so it cannot be truncated - to shrink this file will be slow and likely result in increased fragmentation.

Let me know if you know how I can create this index without the excessive growth.

I believe I've found my own answer to this question.  When setting SORT_IN_TEMPDB on, the filegroup no longer expands unpredictably, and a shrink with truncate only is successful in recovering space which is all trailing space.  If trying this, note that this could result in additional space requirements for TempDB.


SQL query avg function not working This query produces the correct data :

select
       extract (hour from (tr.eventts-pr.eventts))*3600+
       extract (minute from (tr.eventts-pr.eventts))*60+
       extract (second from(tr.eventts-pr.eventts))*1000 ms
,pr.pid
from (      select max((FROM_TZ(CAST(eventts AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific')) eventts, pid
            from WC_PACKAGEREPORT
            where DESTINATION=154
             AND PID in(SELECT unique pid FROM WC_PACKAGEINFO
                        WHERE EXECUTEDTASK='ManualScan'
                        and (FROM_TZ(CAST(eventts AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') >= (FROM_TZ(CAST(trunc(SYSDATE) AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific')
          )
group by pid) pr

            inner join (

select max((FROM_TZ(CAST(eventts AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific')) eventts, pid
from WC_TRACKINGREPORT
where areaid=1444
and zoneid=68
and equipmentid=03
group by pid) tr

on tr.pid=pr.pid;

I now want to get the average ms taken so i try :

select
       avg(extract (hour from (tr.eventts-pr.eventts))*3600+
       extract (minute from (tr.eventts-pr.eventts))*60+
       extract (second from(tr.eventts-pr.eventts))*1000) ms
,pr.pid
from (      select max((FROM_TZ(CAST(eventts AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific')) eventts, pid
            from WC_PACKAGEREPORT
            where DESTINATION=154
             AND PID in(SELECT unique pid FROM WC_PACKAGEINFO
                        WHERE EXECUTEDTASK='ManualScan'
                        and (FROM_TZ(CAST(eventts AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific') >= (FROM_TZ(CAST(trunc(SYSDATE) AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific')
          )
group by pid) pr

            inner join (

select max((FROM_TZ(CAST(eventts AS TIMESTAMP),'+00:00') AT TIME ZONE 'US/Pacific')) eventts, pid
from WC_TRACKINGREPORT
where areaid=1444
and zoneid=68
and equipmentid=03
group by pid) tr

on tr.pid=pr.pid;

but i get a "not a single-group group function".

I believe I need to wrap the sql somewhere but I'm not sure where. Can any of you advise?

Tom

At the outermost level, you are returning the average values along with pr.pid. If you want averages per pid, then you need to group your rows by pid.

You are doing that in the nested queries but not in the main one. So, after on tr.pid=pr.pid just add group by pr.pid.

But if you want the average value across the entire row set, remove pr.pid from the (main) select list.


Changing Data Directory on MySQL DB Server I have installed mysql server 5.6.24 on Windows Server 2012 R2.

I imported dump files and the data files are stored in C:\ProgramData\MySQL\MySQL Server 5.6\data

I want to change directory like D:\ProgramData\Data.

Can you please help me, anyone ?

Open Windows CLI as Administrator and Shutdown MySQL

net stop mysql

Copy the Data to D:\ProgramData\Data

xcopy /s C:\ProgramData\MySQL\MySQL Server 5.6\data D:\ProgramData\Data

Create or edit C:\ProgramData\MySQL\MySQL Server 5.6\my.ini

Add this to the my.ini

[mysqld]
datadir = D:/ProgramData/Data

Startup MySQL

net start mysql

If you can not execute net start mysql, try the Windows control panel.
Login to MySQL and verify everything is good

When you login to MySQL, run this

mysql> SHOW GLOBAL VARIABLES LIKE 'datadir';

Remember to give your new data directory the same full permissions for users Network Service and Administrator that the default data dir was using, otherwise it'll cause the dreaded "started and then stopped" error.  Don't delete this dir until you're sure your new one is working.

If this does not work, here is the rollback plan:

net stop mysql
del "C:\ProgramData\MySQL\MySQL Server 5.6\my.ini"
net start mysql


Install PostgreSQL using source code or using apt-get? I want to install PostgreSQL on a server that runs Ubuntu 14.04.2 LTS.

I understand that I have two options:

Install PostgreSQL using the source code distribution
Install PostgreSQL using apt-get install postgresql postgresql-contrib with these instructions.

Which option should I use?

Will there be a difference in the outcome?
Will both options result in the same set up and configuration (assuming I choose the default installation with option #1)?
Is there a situation where I would prefer option #1 over option #2 and vice versa?

If you install from apt-get you will be able to use apt-get upgrade option later and other apt-get build in options (available from Ubuntu).
Furthermore apt-get installs binaries and manages their versions. You just install and nothing more.

Installing from source gives you the ability to do a more detailed installation. (You can customize the build and installation process with command line options connected to ./configure PARAMETERS LIST.) It's useful if you need a feature that can be enabled only by compiling the package yourself. Next thing is that installation from source may not be visible to apt-get commands so you may not be able to use them.

But if you need to install Postgres stable version without any specialized functionalities use apt-get option.


What is the fastest way to compare polygon geometry data? We have two tables with polygons stored in geometry type columns.

I want to fetch the polygons present in one table that are not present in another table.

As of now I am doing a left outer join and using STAsText(), however that is taking a very long time.

FYI, we have approximately 120 million polygons in both tables.

Is there a fast way of comparing geometry type data?  Maybe I need to use the spatial indexing, however I am not aware of this.

SELECT newPolygon.*
FROM table1 newPolygon
    LEFT JOIN table2 oldPolygon
        ON newPolygon.Shape.STAsText() = oldPolygon.Shape.STAsText()
WHERE oldPolygon.Shape IS NULL

Building on the comments provided by @John M, I've contrived the following example of how to build a geometric index, and then query the index using the STEquals function:

USE tempdb;

DROP TABLE dbo.G1;
DROP TABLE dbo.G2;

CREATE TABLE dbo.G1
(
    G1ID INT NOT NULL CONSTRAINT PK_G1 PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , Shape1 GEOMETRY
);

CREATE SPATIAL INDEX SIndx_G1_Shape1
ON dbo.G1(Shape1)
WITH (BOUNDING_BOX = (0, 0, 9999, 9999));

CREATE TABLE dbo.G2
(
    G2ID INT NOT NULL CONSTRAINT PK_G2 PRIMARY KEY CLUSTERED IDENTITY(1,1)
    , Shape2 GEOMETRY
);

CREATE SPATIAL INDEX SIndx_G2_Shape2
ON dbo.G2(Shape2)
WITH (BOUNDING_BOX = (0, 0, 9999, 9999));

DECLARE @c NVARCHAR(50);
SET @c = '';
DECLARE @LoopCount INT;
SET @LoopCount = 0;
WHILE @LoopCount < 5000
BEGIN

    SET @c = 'LINESTRING(2 ' + CONVERT(NVARCHAR(10), @LoopCount) + ', '
        + CONVERT(NVARCHAR(10), @LoopCount) + ' 2, 4 '
        + CONVERT(NVARCHAR(10), @LoopCount) + ', 2 4, 2 2)';
    IF @LoopCount < 10 PRINT (@c);
    INSERT INTO dbo.G1 (Shape1)
    SELECT geometry::Parse(@c);

    SET @c = 'LINESTRING(' + CONVERT(NVARCHAR(10), @LoopCount) + ' '
        + CONVERT(NVARCHAR(10), @LoopCount) + ', '
        + CONVERT(NVARCHAR(10), @LoopCount) + ' 2, '
        + CONVERT(NVARCHAR(10), @LoopCount + 2) + ' '
        + CONVERT(NVARCHAR(10), @LoopCount) + ', 2 4, 2 2)';
    IF @LoopCount < 10 PRINT (@c);
    INSERT INTO dbo.G2 (Shape2)
    SELECT geometry::Parse(@c);

    SET @LoopCount = @LoopCount + 1;
END

SELECT TOP(10) *
FROM dbo.G1;
SELECT TOP(10) *
FROM dbo.G2;

SELECT *
FROM dbo.G1
    INNER JOIN dbo.G2 ON G1.Shape1.STEquals(G2.Shape2) = 1;


MS-SQL 2014 Log Shipping: Access permission question Wonder if someone could provide some advice or point me in the right direction towards achieving the below objective please. Unfortunately I have not been very successful in finding a solution online.

We are at the process of implementing Log shipping between 2 SQL-2014 servers. The user access are enabled on our SQL servers via Windows Security Groups.

We would like one Windows Security Group (eg: WSG-1) of users running expensive queries be denied access only to say a single database (eg: database-A) on the primary server but be allowed access to:

1). The rest of the databases on the primary server and

2). The same database-A (standby/read-only) on the secondary server.

My initial plan was to deny SQL connect access to the WSG-1 on the primary server but grant WSG-1 all access on the secondary server. However, this did not work as it denied WSG-1 access to SQL altogether on the primary server. I used:

DENY CONNECT SQL TO [DOMAIN\WSG-1]

Wonder if a solution could be accomplished this way or if there is a different way to a solution.

The biggest challenge I find with Log-shipping is, any grant/deny applied at database level on the primary server is mirrored exactly over to the secondary server with the subject database left in non recovery mode. But we would like to vary the db level access permission between the primary and the secondary server for the subject database.

I find that the database role denied access on the primary server remain denied on secondary too for the group WSG-1. Not sure if my problem could be approached in a different way.

The people or security contexts running the expensive queries on the wrong instance should be identified and then notified to change their processes on the instance and DB to start running their stuff on the instance you want those run on. Since the secondary is read-only/standby, we would assume these expensive queries are SELECT statements only where the primary DB that's not in standby would have to run queries that update data or modify objects regardless of the expense.  You could also look into why some of these queries are so expensive in the first place, see if adding indexes would help, rewriting queries for performance tuning, etc.  I think that'd be a good root cause type solution.

It seems like it'd be tricky to do this with one of more AD groups though as the changes are replicated from primary to secondary and secondary would be in standby to accept transaction log changes from primary when those are applied so I'm not sure how you would accomplish this in that sort of configuration with log shipping.

I'm also not certain how often your tran logs are being restored to secondary as I thought when LSRestore jobs run, it disconnects all session on secondary until those transactions are committed, so I assume it's once a day or not too often or people would be screaming about their queries, etc. getting disconnected during normal hours.  If once-a-day restores are occurring for LSRestore jobs on secondary, then this would mean the  data gotten from that server is 24 hours old or however long between your LSBackup, LSCopy, and LSRestore jobs.  So who can use which DB with this regard may depend on how fresh their query results need to be from the business side, etc.

Lots of factors to consider here but getting to the root cause and having bad performing queries tuned, adding indexes, etc. may be the best solution as well as having the people with access take responsibility with their processes to not hose up the performance for others when they run their stuff.


Why do my RDS read IOPS drop after a certain period of time? I am trying to add non-null constraints on several columns in a table with about 70,000,000 rows. Each statement is just ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL;, and each statement takes about 4.5 minutes to complete.
During this time, the read IOPS spike to about 8,000, and stay there until the statement completes. However, after executing several of these statements, the IOPS drop to 2,000 and are capped there. At this point each statement takes four times as long. If I wait a while, I can get another 30 minutes or so at 8,000 IOPS.
At first I thought this was the burst credits being used and then regenerated, but

The burst balance never drops below 98% during this entire time.
According to the documentation, I should have a balance of about 2,970, not 8,000.

The database details are

Postgres 11.8 on AWS RDS.
Instance size is db.t3.large.
990 GB of GP storage.
No provisioned IOPS.

My questions are

What is going on here? What is the significance of the 8,000 and 2,000 numbers? I can't find documentation about this anywhere.
Why isn't burst balance being used here?
Is 4.5 minutes to add the constraint on a 70 million-row table reasonable? And in general, is there a more efficient way I can be doing this? I still have over 60 columns to alter.

Read IOPS

Write IOPS

Burst balance

CPU

Memory

I've not found the IO burst balance to work the way the documents state nor the monitoring of the burst balance to reflect reality, for t* class instances.  So while I can't explain your observation, it does not surprise me.

And in general, is there a more efficient way I can be doing this?

A single ALTER TABLE can implement a number of actions, not just a single action.  And it should generally scan the table only once, not once for each action.
ALTER TABLE table_name
   ALTER COLUMN column_name1 SET NOT NULL,
   ALTER COLUMN column_name2 SET NOT NULL,
   ALTER COLUMN column_name3 SET NOT NULL,
   ALTER COLUMN column_name4 SET NOT NULL,
   ALTER COLUMN column_name5 SET NOT NULL...;


Convert existing table to partitioned table - SQL Server 2017 I have a large busy database where 99.9% of data lives in one table. IOT devices report their time series data into this table.

This table has 7 billion rows and takes up around 3.5 TB of space. The database is running out of space on its current drive.

I have another drive available that I would like to start saving new data for this one table.

The challenge here is doing all this with no, or very little down time (max 30 minutes). I need a strategy to save this new data.

Here is idea 1:

Create new table called IotSensorData_New which is same as the main table, but has partitioning turned on and lives on new drive (the ID identity seed is a concern here)
Rename current large table to IotSensorData_Old, then rename empty IotSensorData_New to IotSensorData
Turn on data importers so data starts ingesting into this new table (again ID is concern here as it will start from 1)
Convert the old IotSensorData_Old data into the current table - I'm unsure exactly how to do this bit

Here is idea 2:

Turn off data importers
Convert the current table to partitioning (I understand indexes need to be rebuilt if I do this which would probably take over 24 hours)
Turn on importers

I'm looking for exact steps on how to do this with the least downtime

I'm using SQL Server 2017 Enterprise edition.

You are totally on the wrong idea here. PArtitioning is not really how to do that. Not saying that thee is no sense in it, but generally partitioning is about deleting data fast (i.e. a partition per year MAY bea good idea to get rid of 10 year old data in milliseconds).

All you need to do as simplest solution for your case is:

Add other drive
Make sure it is writable (partition, format etc.
Go into the db setup and add another db file to the filegroup.

You do not gain most of the advantages of multiple drives, but you would not with your solution either. This would require serious reformatting which I doubt you have the space for.

But a file group can have multiple files and SQL Server will automatically use them all. There is no need to partition for your particular problem.


How to setup a script that automatically clones the DB every 2 hours? I'm planning to build a system that will have 30+ tables and 100+ million rows in a few of those. Going to use MySQL - InnoDB (any better alternative for this?)

My scripts are going to add a couple of hundreds of thousands of clicks to the database every day. On the other hand, I'd like to do heavy database queries during the day as well.

What I came us with is to have two different servers. Server A would take all the clicks and store them and Server B would work on retrieving the results.

Question A: Is this the right approach to do?
Question B: Is it possible to set up a script that's cloning the database over from Server A to Server B - so the data is semi-up to date?

You can't insert into one MySQL instance and expect the data to show up on another instance -- at least not without somehow inserting on the second instance.  But this defeats your original goal -- a performance problem caused by battling inserts and selects.

A Master-Slave setup does what I just described -- You insert on the Master; that insert is replicated to the Slave, where it is again executed.  Then you read from the Slave.  So, problem not solved.

First of all, you must use InnoDB, not MyISAM.

Are your "clicks" counters?  Or are they rows containing more information than just "1 more click for X"?  Assuming just a counter, such as INT UNSIGNED, ...

Second, keep the click counters in a table by themselves.  This table would have a PRIMARY KEY that is the same as the main table (with the possible exception of AUTO_INCREMENT.

But if that is not enough, then consider ways of collecting the clicks and applying them "after a while".  One ways would be a variant on http://mysql.rjweb.org/doc.php/staging_table .  This link would also provide a good way to gather whole rows instead of just a counter.

How many clicks per second do you get?  "Hundreds of thousands per day is only a few per second.  Do you really have a problem?


Update database table I'm developing a website using PHP. I have a MySQL database and it has three columns called name, latitude and longitude. It is as follows.

Name | latitude | longitude
----------------------------
AAAA| 6.9270974 | 79.8612478
BBBB| 7.2667576 | 80.380611

My interface has three text boxes called txtname, txtlatitude, txtlongitude and they return values just like this.

txtname - CCCC
txtlatitude - 6.9271786
txtlongitude - 79.86224300000006

I want to update the “Name” column of the database table which is approximately similar to the latitudes and longitudes values of these text boxes.

(For example, according to the above database table,  I want to replace “AAAA“ with “CCCC”)

Is it possible? Any help would be appreciated.

Thanks.

Yes you can do that. The easiest way to do if your amount of rows is relatively small is:

update vertex set name = 'CCCC'
where latitude between 6.9271786 * 0.99 and 6.9271786 * 1.01 and
      longitude between 79.86224300000006 * 0.99 and 79.86224300000006 * 1.01;

With of course setting your threshold to the right level. The above example assumes 1% which is huge in terms of geo coordinates but you get the idea.

Since MySQL can only use one column for range queries from a composite key having a combined index won't help but separate index on latitude and longitude will. Then MySQL can choose the one which is more distinctive for the specific case.

create index lat_idx on vertex (latitude);
create index lat_idx on vertex (longitude);

For further optimizations if necessary you can look into spatial indexes (you haven't mentioned which MySQL version and engine you're using).

Also you can geohash the coordinates into a single numerical column and have proximity match on that then filter by the coordinates which I use on bigger database and works like a charm.

https://en.wikipedia.org/wiki/Z-order_curve
https://en.wikipedia.org/wiki/Geohash


Is it ok if I use only the latest binary log file when restoring MySQL db? mysqlbinlog --verbose --stop-datetime="2019-12-28 10:00:00"  bin.000009 > backup.sql

mysql -u root -p < backup.sql

(The stop-datetime parameter here is just for testing)

I did the recovery job like that. Everything seems to be working fine. I don't know why the other numbers are required. Is that due to "max_binlog_size"?

My DB env is MYSQL 8.0, innodb

log-bin=/var/www/dbenv/log/bin/bin.log
binlog_cache_size=2M
max_binlog_size=500M
binlog_expire_logs_seconds=120000

Binlog itself isn't intended for DB restoration. It contains the changes in the DB since some point in time. Bin files are the chunks of the whole log where the max_binlog_size defines how big the single chunk can be.

To perform the restoration in the proper way you need two things:

the dump created by mysqldump utility at the some point of time
all the binlog chunks since DB was dumped

Binlog events can be applied to the some initial state only. They are not idempotent - it can't be applied again and again. Let me illustrate the principle: you want to bake a cake.

The initial state is all the ingredients apart. Then you mix the water, flour, add the yeаst, then you kneed the dough and set it into the owen. All your operations are written to the binlog. If something went wrong you can't start from the arbitrary point. Say, if the cake was burned you can't start from the kneeding of burned cake. You have to revert to the initial state and perform all the steps from the very beginning.

Dump + full binlog allows to restore the final state of DB. Last state + binlog (full or partial) doesn't.


Return total number of rows and selected (aggregated) data I have a function which is selecting some data from a table. I would like to return the selected data and the total number of rows in that table.

How can I do that or how can I get the same result in the most efficient way?

I have tried few things and I ended up with the code below, now this is the format that I want, but count(*) over () as total_count will return 1 all the time, what I need it to return is the total number of rows from that records select.

SELECT
row_to_json(selected_records) as data
FROM
(
    SELECT
    count(*) over () as total_count,
    array_to_json(array_agg(row_to_json(records))) as data
    FROM (
        SELECT
            sum(entrances) as entrances
        FROM report_la
        WHERE profile_id = 3777614
        GROUP BY landing_path_id
        limit 10 offset 0
    ) records
) as selected_records

Updated, the code below is producing the result that I want, it will be nice if I can hide that total_count column from the records select

SELECT
row_to_json(selected_records) as data
FROM
(
    SELECT
    min(total_count) as total_count
    ,array_to_json(array_agg(row_to_json(records))) as data
    FROM (
        SELECT
            sum(entrances) as entrances
            ,count(*) over () as total_count
        FROM ga.report_la
        WHERE ga_profile_id = 3777614
        GROUP BY landing_path_id
        limit 10
    ) records
) as selected_records

As I understand the question, you don't need a window function. Aggregate functions do the job:

count() in the lowest level (-> row_ct).
sum() the resulting row_ct in the next level (-> total_row_ct).

SELECT row_to_json(selected_records)::text AS data
FROM  (
   SELECT array_to_json(array_agg(row_to_json(records))) AS data
        , sum(row_ct) AS total_row_ct
   FROM (
      SELECT landing_path_id
           , sum(entrances) AS entrances
           , count(*) AS row_ct
      FROM   report_la
      WHERE  profile_id = 3777614
      GROUP  BY landing_path_id
      LIMIT  10
      ) records
   ) selected_records;
I also included landing_path_id so the resulting data makes sense.
SQL Fiddle.
Window function?
A window function (count(*) over ()) does not seem to be what you want, since you don't have unaggregated rows.
You could add to the inner subquery:
count(*) OVER ()

.. to get the count of distinct landing_path_id, which is one other possible number that might be of interest. But that doesn't seem to be what you meant by "the total number of rows from that records select".
Or you could add  to the inner subquery:
sum(count(*)) OVER ()

.. to get the total count with every landing_path_id redundantly, but that would seem pointless. Just mentioning that to demonstrate it's possible to run a window function over the result of an aggregate function in a single pass. Details for that:

How will the order of operations in this query affect my results?
How do I get the aggregate of a window function in Postgres?
Need separate columns that calculate percentage

Updated question
Your result, just without total_count in the records subquery. Now accounting for the LIMIT in the inner SELECT. Even though a maximum of 10 distinct landing_path_id is selected, all qualifying landing_path_id are counted.
To get both in one scan and reuse count and sum separately I introduce a CTE:
WITH cte AS (
  SELECT sum(entrances) AS entrances
       , count(*) over () AS total_count
  FROM   report_la
  WHERE  profile_id = 3777614
  GROUP  BY landing_path_id
  LIMIT  10
  )
SELECT row_to_json(selected_records)::text AS data
FROM  (
   SELECT (SELECT total_count FROM cte LIMIT 1) AS total_count
        , array_to_json(array_agg(row_to_json(records))) AS data
   FROM  (SELECT entrances FROM cte) records
   ) selected_records;
If you don't care about the attribute name, you can have that cheaper with a subquery:
SELECT row_to_json(selected_records)::text AS data
FROM  (
   SELECT min(total_count) AS total_count
        , array_to_json(array_agg(row_to_json(ROW(entrances)))) AS data
   FROM (
      SELECT sum(entrances) AS entrances
           , count(*) over () AS total_count  -- shouldn't show up in result
      FROM   report_la
      WHERE  profile_id = 3777614
      GROUP  BY landing_path_id
      LIMIT  1
      ) records
   ) selected_records;
You get the default attribute name f1 instead of entrances, since the ROW expression does not preserve the column name.
If you need a certain attribute name, you could cast the row to a registered type. (Ab-)using a TEMP TABLE to register my row type for the session:
CREATE TEMP TABLE rec1 (entrances bigint);

...
        , array_to_json(array_agg(row_to_json(ROW(entrances)::rec1))) AS data
...
This would be a bit faster than the CTE. Or, more verbose but without cast:
...
        , array_to_json(array_agg(row_to_json(
                   (SELECT x FROM (SELECT records.entrances) x)))) AS data
...
Detailed explanation in this related answer:

Select columns inside json_agg

SQL Fiddle.


Pass the unix variable to the ddl command inside a pl/sql block I would like to create a db link dynamically, using the input given in the unix session. i.e. the remote db name and db instance would be given by the user, in unix, which are VAR1 and VAR2. Now, from the current unix session, I want to connect to one sql database and then to create the db link to the another database using the input values. When I tried the below script, this is creating the db link successfully, but the values of VAR1 & VAR2 are not taken as 'xxx' and 'yyy', instead these are read as it is, i.e. the username is 'VAR1' and host name is 'VAR2'. How do I make sure that the values of these variables are passed instead of these? Thanks in advance.

VAR1="xxx"
VAR2="yyy"
sqlplus aaa/bbb @try.sql $VAR1 $VAR2

--Then below is my SQL (try.sql)

SET SERVEROUTPUT ON
DECLARE
VAR1 varchar2(100):='&&1';
VAR2 varchar2(100):='&&2';
BEGIN
execute immediate 'CREATE DATABASE LINK  try_lnk CONNECT TO VAR1 IDENTIFIED BY VAR1 USING ''VAR2''';
END;
/
exit

Instead of the string literal VAR1 and VAR2, use the variables in your EXECUTE IMMEDIATE statement:

execute immediate 'CREATE DATABASE LINK  try_lnk CONNECT TO ' || VAR1 || ' IDENTIFIED BY ' || VAR1 || ' USING ''' || VAR2 || '''';


Nonclustered Index Rebuild Online on Heap Table Recently read that rebuilding a heap online in SQL Server Enterprise is single threaded and does not go parallel.
My question is: If you try to rebuild online just non-clustered indexes on a heap table with those also be single threaded or can those go parallel?
Background
I am trying to find the best place to actually test it, but right now I have limited hardware available to me.  I am new to the company and I am not advocating a heap, I am trying to steer them towards clustered indexes, but they have random GUIDs everywhere and lots of the developers think clustered indexes will hurt performance.  I am trying to educate them on the advantages of using clustered indexes rather than heaps and one of those components is index maintenance.
We had one stored procedure that was doing over 27 million logical reads.  I went in and looked at fragmentation on one of the key table scans, rebuilt the heap and logical reads went down to 560,000.  Just looking at forwarded records and fragmentation I would not have even rebuilt that table through a scripted job.  Trying to make them understand if we are gonna keep heaps we need to perform regular maintenance on these things.
The cost of this maintenance is higher for rebuilding heaps than having clustered indexes.  That is really where my question about single threaded is coming from.  Sorry to be long winded but thought would help explain question.

My question is: If you try to rebuild online just non-clustered indexes on a heap table with those also be single threaded or can those go parallel?

It can be multi threaded, the restriction is ONLY to alter table rebuild command when done with ONLINE option. I would like to quote from Enabling Compression on Heap MSDN Blog

(1) If you use ONLINE option then HEAP rebuild is single threaded. Note, this restriction is not there when you are rebuilding an index which supports multi-threaded ONLINE build.  However, in both cases, OFFLINE build does support multi-threading.

A non-clustered index rebuild can be multi threaded. Please note for index rebuild to use multiple threads the edition should be Enterprise edition or above( if there is such a edition). This is still true from SQL Server 2005 to SQL Server 2019.

For example, rebuilding a heap with three nonclustered indexes online:
ALTER TABLE dbo.Fish REBUILD WITH (ONLINE = ON);

The heap rebuild is serial:

...while the associated nonclustered index rebuilds are parallel:


Log slow SELECT queries only I'm enabling slow log query for MySQL but I notice that CREATE, INSERT, UPDATE, et. al are also included.
I'm generally not interested in those.
Is there any way to specify that only slow SELECTs are to be logged?

No.
Instead, I suggest that the most important information comes from summarizing the log.  This can be done via either of these

pt-query-digest
mysqdumpslow -s t

Then focus on the first few entries.


Innodb cluster options missing I have successfully installed and run an INNODB Cluster on mysql 8.0.19

However I cannot set options for the cluster

when we run

cluster=dba.getCluster();
cluster.options();

We get the following error

Invalid object member options (AttributeError)

Please advise?

What MySQL Shell version are you using?

<Cluster>.options() was introduced in version 8.0.14:

https://mysqlserverteam.com/mysql-innodb-cluster-changing-cluster-options-live/


Handling growing number of Tenants in Multi-tenant Database Architecture Handling a modest number of customers (tenants) in a common server with separate databases for each tenant's instance of the application is relatively straightforward and is normally the correct way to do this.  Currently I am looking at the architecture for an application where each tenant has their own database instance.

However, the problem is that this application will have a large number of tenants (5,000-10,000) with a substantial number of users, perhaps 2,000 for a single tenant.  We will need to support growing the system by several tenants every week.

In addition, all tenants and their users will be presented with a common login process (i.e. each tenant cannot have their own URL).  To do this, I need a centralised login process and a means to dynamically add databases to the system and register users.

How could the registration and database creation process be automated robustly?
Is the process of creating and registering tenants' databases on the system likely to cause performance or locking issues.  If you think this could be an issue, can anyone suggest ways to mitigate it?
How can I manage central authentication in a way where user credentials will be associated with a particular tenant's database but the user can log in through a common page (i.e. all through the same login URL, but their home application will be on some specific tenant's database).  The tenants will have to be able to maintain their own logins and permissions, but a central login system must be aware of these.  Can anyone suggest a way to do this?
If I need to 'scale out' by adding multiple database servers, can anyone suggest what issues I might have to deal with in managing user identies across servers (impersonation etc.) and some way to mitigate those issues?

At the lower end (500 tenants / 10000 users) this is how I did it. First, you have a "control" database that is global, central and contains all of the information about tenants and users (I really don't think you want to manage these as SQL auth logins). So imagine a database called "Control" with the following tables:

CREATE TABLE dbo.Instances
(
  InstanceID INT PRIMARY KEY,
  Connection VARCHAR(255)
  --, ...
);

INSERT dbo.Instances SELECT 1, 'PROD1\Instance1';
INSERT dbo.Instances SELECT 1, 'PROD2\Instance1';
-- ...

CREATE TABLE dbo.Tenants
(
  TenantID INT PRIMARY KEY,
  Name NVARCHAR(255) NOT NULL UNIQUE,
  InstanceID INT -- Foreign key tells which instance this tenant's DB is on
  --, ...
);

INSERT dbo.Tenants SELECT 1, 'MyTenant', 1;
-- ...

CREATE TABLE dbo.Users
(
  UserID INT PRIMARY KEY,
  Username VARCHAR(320) NOT NULL UNIQUE,
  PasswordHash VARBINARY(64), -- because you never store plain text, right?
  TenantID INT -- foreign key
  --, ...
);

INSERT dbo.Users SELECT 1, 'foo@bar.com', 0x43..., 1;

In our case when we added a new tenant we would build the database dynamically, but not when the admin user clicked OK in the UI... we had a background job that pulled new databases off a queue every 5 minutes, set model to single_user, and then created each new database serially. We did this to (a) prevent the admin user from waiting for database creation and (b) to avoid two admin users trying to create a database at the same time or otherwise getting denied the ability to lock model (required when creating a new database).

Databases were created with the name scheme Tenant000000xx where xx represented Tenants.TenantID. This made maintenance jobs quite easy, instead of having all kinds of databases named BurgerKing, McDonalds, KFC etc. Not that we were in fast food, just using that as an example.

The reason we didn't pre-allocate thousands of databases as the comment suggested is that our admin users usually had some idea of how big the tenant would become, whether they were high priority, etc. So they had basic choices in the UI that would dictate their initial size and autogrowth settings, which disk subsystem their data/log files would go to, their recovery settings, backup schedule to hinge off of, and even smarts about which instance to deploy the database to in order to best balance usage (though our admins could override this). Once the database is created, the tenant table was updated with the chosen instance, an admin user was created for the tenant, and our admins were e-mailed the credentials to pass along to the new tenant.

If you're using a single point of entry, it is not feasible to allow multiple tenants to have users with the same username. We opted to use e-mail address, which - if all users work for the company and use their corporate e-mail address - should be fine. Though our solution eventually became more complex for two reasons:

We had consultants that worked for more than one of our clients, and needed access to multiple
We had tenants who themselves were actually comprised of multiple tenants

So, we ended up with a TenantUsers table that allowed one user to be associated with multiple tenants.

Initially when a user logs in, the app will know the connection string for the control database only. When a login is successful, it can then build a connection string based on the information it found. E.g.

SELECT i.Connection
  FROM dbo.Instances AS i
  INNER JOIN dbo.Tenants AS t
  ON i.InstanceID = t.InstanceID
  INNER JOIN dbo.TenantUsers AS u
  ON i.TenantID = u.TenantID
  WHERE u.UserID = @UserID;

Now the app could connect to the user's database (each user had a default tenant) or the user could select from any of the tenants they could access. The app would then simply retrieve the new connection string, and redirect to the home page for that tenant.

If you get into this 10MM user area you propose, you'll definitely need this to be balanced better. You may want to federate the application so that they have different points of entry connecting to different control databases. If you give each tenant a subdomain (e.g. TenantName.YourApplicationDomain.com) then you can do this behind the scenes with DNS/routing without interrupting them when you need to scale out further.

There is a lot more to this - like @Darin I am only scratching the surface here. Let me know if you need a non-free consult. :-)


Disk errors in one of the SQL cluster nodes and SQL patching was failing with a lot of errors related to disk and permission issues We have two cluster nodes in a SQL cluster and the application use this cluster is Microsoft Identity Manager.

Since few month both nodes ( Node 1 and Node2) are throwing disk error and SQL team is tried fixing that multiple time by running CHKDSK. Recently noticed that, resources are unable to move to primary node(Node1) and they did patching that also failed.But somehow they are able to move the resources from Node 2 to primary Node1.. But SQL team is suggesting to rebuild the server( Node1) as the server still has Disk error and patch failures.

So my question here would be, wont that disk replacement work here in this case and copy all the data from old to new disk ?

There is possibility of disk replacement, for detailed steps..

Seems your team decision is correct as you have been tried to apply patches that been ran through failures.

Edit (Including disk replacement steps summary from same source - referral link):

Take backups of all the databases including System DBs. [ONLY for disaster recovery]
Present all the drives from the new storage to the Windows Cluster. These disks must be visible under “Available Storage” Failover Cluster Manager
Now add these disks to the SQL Cluster Role/Group.
From the SQL Server dependencies, remove the drive letters pointing to the old storage
Take SQL Server Resource to an OFFLINE State
Make sure that the old disks should be online. Now start copying all the folders as is from old storage to the new storage. DO NOT change any files or folder structures.
Once all the files are copied, you need to swap the drive letters. Such that the new drives will get the old drive letters and vice-versa [This can be directly from Failover Cluster Manager] - This might not applicable in your case
Add the disks to the SQL Server dependencies
Make sure the SQL server service account has appropriate permissions on new disk/folder especially for the folders that are pointed in SQL Server - startup parameters
Bring the SQL Resource ONLINE.
Test connectivity from applications.
Once testing is completed thoroughly, disks from old storage can be removed from the Failover Cluster Manager


a SELECT or set-returning version of MERGE/UPSERT? I'm learning MERGE or UPSERT (UPDATE + INSERT), which according to wikipeda, has the form:

 MERGE INTO tablename USING table_reference ON (condition)
   WHEN MATCHED THEN
   UPDATE SET column1 = value1 [, column2 = value2 ...]
   WHEN NOT MATCHED THEN
   INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...]);

, and:

  If the ON field(s) in the Source matches the ON field(s) in the
  Target, then UPDATE

  If the ON field(s) in the Source does not match the ON field(s) in the
  Target, then INSERT

I was wondering if there is a SELECT version of it (without side effects) that returns the would-be result of the updated target table without actually changing the target table?

Using PostgreSQL 9.5's INSERT INTO ... ON CONFLICT [ conflict_target ] conflict_action statement (pointed out in wikipedia) as an example. Given a target table distributors and an update table distributors_upd as follows:

DROP TABLE IF EXISTS distributors;
DROP TABLE IF EXISTS distributors_upd;

CREATE TABLE distributors (did INTEGER PRIMARY KEY, dname VARCHAR);
INSERT INTO distributors
VALUES (5, 'Gizmo Transglobal'), (6, 'Associated Computing, Inc');

CREATE TABLE distributors_upd (did INTEGER PRIMARY KEY, dname VARCHAR);
INSERT INTO distributors_upd
VALUES (5, 'Gizmo Transglobal*');

Is there an effective query to get the result of a UPSERT without actually changing the target table distributors?

I tried to mimic the effect with:

WITH distributors_updated AS (
INSERT INTO distributors (did, dname)
    (TABLE distributors_upd)
    ON CONFLICT (did) DO UPDATE SET dname = EXCLUDED.dname
    RETURNING *
)
--SELECT * FROM distributors;
SELECT * FROM distributors_updated;

Neither one of the last two lines gives the would-be result:

 did |           dname
-----+---------------------------
   6 | Associated Computing, Inc
   5 | Gizmo Transglobal*

The first ... SELECT * FROM distributors shows the original table (by design).

-----+---------------------------
   5 | Gizmo Transglobal
   6 | Associated Computing, Inc
(2 rows)

The second ... SELECT * FROM distributors_updated shows the change rows only.

-----+--------------------
   5 | Gizmo Transglobal*
(1 row)

The content of distributors is changed either way. Is there an effective query returning the "merged"/"unioned" without actually changing the target distributors?

If you want to find the results of the "UPSERT" without actually performing it, you can run a query with FULL JOIN:

SELECT
    did,
    COALESCE(s.dname, t.dname) AS dname   -- in case of conflict,
                                          -- give precedence to source table
FROM
    distributors AS t
    FULL JOIN distributors_upd AS s
    USING (did) ;

Test at dbfiddle.uk (thnx to McNets for setting it up)

The columns that would be updated on the conflict by the merge will have the source column first in the COALESCE() function and the target column second. If the update is more complex, you'll have to use a more complex COALESCE() or CASE expression. Example:

ON CONFLICT UPDATE
    SET counter = counter + EXCLUDED.counter

will become

SELECT
    COALESCE(t.counter + s.counter, t.counter, s.counter) AS counter


When exactly does pg_stat_all_tables.last_autovacuum get updated? I'm looking into issues with our autovacuum settings and trying to understand last_autoanalyze and last_autovacuum from the pg_stat_all_tables.

I understand autovacuum to work in an incremental way, compacting and cleaning up dead tuples in batches, then sleeping for a moment, etc. So what does last_autovacuum mean? Is it possible that autovacuum could be mostly working but that that timestamp might never get updated (e.g. because a tuple could not be removed)?

Many of the timestamps I see for our tables are old, even though I'm fairly sure I observed a running autovacuum thread on some of the tables.

EDIT:
If I'm unclear, my question is: does last_autovacuum mean...

the time at which an incremental autovacuum process last finished up a chunk of work on this table and slept
the time an autovacuum process worked its way to the very end of a table
the above, but only if there were no tuples that could not be removed
something else

I also wanted to know this, so I dug through the Postgres src code a bit to try and find out...
https://github.com/postgres/postgres/blob/ca3b37487be333a1d241dab1bbdd17a211a88f43/src/backend/catalog/system_views.sql#L584
pg_stat_get_last_autovacuum_time(C.oid) as last_autovacuum,

https://github.com/postgres/postgres/blob/ca3b37487be333a1d241dab1bbdd17a211a88f43/src/backend/utils/adt/pgstatfuncs.c#L267
result = tabentry->autovac_vacuum_timestamp;

https://github.com/postgres/postgres/blob/ca3b37487be333a1d241dab1bbdd17a211a88f43/src/include/pgstat.h#L740
TimestampTz autovac_vacuum_timestamp;   /* autovacuum initiated */

So, if I followed the code correctly, it appears that last_autovacuum timestamp is when the process started, not when it finished.


Installation of SQL Server RAID 10 vs RAID 5 I am new to setting up SQL Servers and Vms. My predecessor used Raid 5 for C Drive where the SQL installation resided and RAID 10 for both database files and database logs.

I was wondering whether that is correct or should RAID 5 be utilized for SQL database files instead?

Any help?

I did a series of TCP-C tests against several of the common RAID levels and was surprised that in a write-heavy scenario RAID5 was over 3x slower than RAID10 (it was 319% slower, to be specific).  Your mileage may vary according to the actual ratio of reads versus writes in your scenarios.

-Kev


How to update the datetime difference between all of the rows of a table? I've got the following table with some data in it:

CREATE TABLE schedules
(
    ID        INT PRIMARY KEY,
    StartTime DATETIMEOFFSET(4)
);

INSERT INTO schedules
VALUES (1, '2020-03-27 08:00:00.000 -05:00'),
       (2, '2020-03-27 09:00:00.000 -05:00'),
       (3, '2020-03-27 10:00:00.000 -05:00'),
       (4, '2020-03-27 11:00:00.000 -05:00');

Currently, the interval of start times is every one hour. I want to run a query to change this interval to every 45 minutes. I tried the following query but it didn't work.

UPDATE r2
SET StartTime = DATEADD(MINUTE, 45, r1.StartTime)
FROM schedules r1 JOIN schedules r2 ON r1.ID + 1 = r2.ID;

Any advice would be appriciated!

WITH cte AS ( SELECT *, ROW_NUMBER() OVER (ORDER BY StartTime) rn FROM schedules)
UPDATE cte SET StartTime = DATEADD(minute, 15*(1-rn), StartTime);

fiddle


IMPORT TABLESPACE is hanging in the 'System lock' state We have a development database server with Percona-server 5.7.15-9 on it. It replicates two schemas from two different production servers using multi-source GTID replication. Lets call these schemas alice and bob.

On the dev server we clone this replicas of production databases to get databases for development. They are called 1_alice, 1_bob, 2_alice, 2_bob, etc. All of them use the same instance of MySQL.
For fast cloning we use Percona XtraBackup as described here https://www.percona.com/doc/percona-xtrabackup/2.4/innobackupex/restoring_individual_tables_ibk.html

In the past there were only one replica (alice) and we used binary log position for replication instead of GTID. These times everything worked fine and fast. One day (I'm not sure when exactly) it became broken.

Now when I perform ALTER TABLE 2_alice.access_group IMPORT TABLESPACE query, it hangs in the 'System lock' state. And could be hanging in this state from 1 min 'till 1 hour and more (then it works). There are no more active connections instead of two replicas, but they doesn't use 2_alice schema.

Why is IMPORT TABLESPACE query hanging and how could I debug this case?

I think your table must be very big. I have recently encountered the same problem when I trying to import a 700G table.

Check the error log, I found that the import is in progress stage 1

InnoDB: Phase I - Update all pages

Check the official documentation.
https://dev.mysql.com/doc/refman/8.0/en/tablespace-copying.html

When ALTER TABLE ... IMPORT TABLESPACE is run on the destination instance, the import algorithm performs the following operations for each tablespace being imported:

Each tablespace page is checked for corruption.
The space ID and log sequence numbers (LSNs) on each page are updated
Flags are validated and LSN updated for the header page.
Btree pages are updated.
The page state is set to dirty so that it is written to disk.

I think it takes a lot of time to update the LSN of each page.

check the MySQL work log
https://dev.mysql.com/worklog/task/?id=5522

Import algorithm
================
We scan the blocks in extents and modify individual blocks rather than using
logical index structure.

foreach page in tablespace {
  1. Check each page for corruption.

  2. Update the space id and LSN on every page  --I think this is what "InnoDB: Phase I - Update all pages" does
     * For the header page
       - Validate the flags
       - Update the LSN

  3. On Btree pages
     * Set the index id
     * Update the max trx id
     * In a cluster index, update the system columns
     * In a cluster index, update the BLOB ptr, set the space id
     * Purge delete marked records, but only if they can be easily
       removed from the page
     * Keep a counter of number of rows, ie. non-delete-marked rows
     * Keep a counter of number of delete marked rows
     * Keep a counter of number of purge failure
     * If a page is stamped with an index id that isn't in the .cfg file
       we assume it is deleted and the page can be ignored.
     * We can't tell free pages from allocated paes (for now). Therefore
       the assumption is that the free pages are either empty or are logically
       consistent. TODO: Cache the extent bitmap and check free pages.

   4. Set the page state to dirty so that it will be written to disk.
}


What consequence deleting idb file will have on my mysql database? I tried to check what is consumming my Disk Space on Centos 7, I found these 2 database file:

  /var/lib/mysql/pub/vues.ibd: 8.1G
  /var/lib/mysql/pub/log.ibd: 8.6G

I would like to delete them. pub is the name of my database.
And log and vues are  tables.

That would be most unwise. Never fiddle with MySQL files directly.

If you want to delete those tables use SQL:

DROP TABLE pub.vues, pub.log


recover oracle db from datafile We have oracle db which unfortunately we did'n make backup and all control files and other files except all data-files are damaged. Is there any solution to recover db from all of these intact data-files? Thank you for helping me.

Here's a link describing and solving the same problem, do note that you still have to make your own pfile, but that isn't hard.
http://myracle.wordpress.com/2008/01/11/recover-database-without-control-files-and-redo-log-files/


Mysql query to get count result and group by I've got three tables with different records for users,

User

username |        realname   |         date
evn-az-3ju           john      11/2012 03:09:40 p.m.
jwyvm_rdyt           steve      12/2012 03:09:40 p.m.
bsMIAtWkhi           mahesh      01/2013 03:09:40 p.m.
zrObzh4um0           santa      01/2013 03:09:40 p.m.
WyVm_rDYt           grolsch      11/2012 03:09:40 p.m.

offline

username |       messageID |    message
jwyvm_rdyt           54      <message to="jwyvm_rdyt" id="t4Wa4-291" type="chat" from="evn-az-3ju">test1<thread>1a327531-5a1c-4d6b-8b66-1209cdabb77d</thread></message>
jwyvm_rdyt           78      <message to="jwyvm_rdyt" id="t4Wa4-290" type="chat" from="evn-az-3ju">Happy birthday<thread>1a327531-5a1c-4d6b-8b66-1209cdabb77d</thread></message>
evn-az-3ju           89      <message to="evn-az-3ju" id="t4Wa4-290" type="chat" from="evn-az-3ju">Happy birthday<thread>1a327531-5a1c-4d6b-8b66-1209cdabb77d</thread></message>
zrObzh4um0           98      <message to="zrObzh4um0" id="t4Wa4-290" type="chat" from="evn-az-3ju">Happy birthday<thread>1a327531-5a1c-4d6b-8b66-1209cdabb77d</thread></message>
WyVm_rDYt           45      <message to="WyVm_rDYt" id="t4Wa4-290" type="chat" from="evn-az-3ju">Happy birthday<thread>1a327531-5a1c-4d6b-8b66-1209cdabb77d</thread></message>

roster

username |             jid       |     date
jwyvm_rdyt           evn-az-3ju      11/2012 03:09:40 p.m.
jwyvm_rdyt           zrObzh4um0      12/2012 03:09:40 p.m.
jwyvm_rdyt           bsMIAtWkhi      01/2013 03:09:40 p.m.
zrObzh4um0           WyVm_rDYt      01/2013 03:09:40 p.m.
WyVm_rDYt           zrObzh4um0      11/2012 03:09:40 p.m.

These are the tables from which i want result set like this,

 username |             realname       |     count
evn-az-3ju           john                     2
zrObzh4um0           santa                    0
bsMIAtWkhi           mahesh                   0

I want to know all the roster jid which username 'jwyvm_rdyt' has along with their realname and count of message if that message contains any roster jid in that. Like 'evn-az-3ju' is in message 54 and 78, so count 2 for that roster jid.

Any help would be greatly appreciated.

SELECT r.username,
       u.realname,
       ( SELECT  COUNT(*)
           FROM offline
           WHERE username = r.username
             AND message LIKE CONCAT('%', r.jid, '%')
       ) as "count"
    FROM roster AS r
    JOIN User AS u  ON r.username = u.username
    WHERE r.username = 'jwyvm_rdyt'

Sometimes it is best to do a COUNT in a subquery in the SELECT clause - to avoid over-counting when doing a JOIN.  And, I think, GROUP BY is avoided.


How to normalize data based only on the repetitions Given a non-normalized data table, is there a good a strategy to attempt to normalize based on repeated data?  Assume we have no information about the significance of the columns.

In a small example

Col1            Col2         Col3
========================================
Europe          France       Paris
Europe          France       Marseilles
Europe          England      London
Europe          England      Manchester
N.America       USA          St. Louis
N.America       USA          Washington D.C.

I can see that there are three source tables, probably called, Continents, Countries, and Cities.  What about for much larger data sets.  Say 45 columns and several hundred thousand rows.  Looking at the data is obviously not going to help much.

I've had to reverse engineer several existing complex data sets.  The most important thing to establish are the keys and dependencies in the data.  The problem is frankly NP hard, so some intuition and inspection will go a long way to getting you to a sensible answer, so don't count on a simple "turn the crank" solution unless you've got a lot of time on your hands.

What you need to do is to query the data a column at a time and by combinations of columns.  You want frequency distributions for column values (and column combination values).  Columns (or combos) with maximum frequencies of 1 are candidate keys.

You can also look at frequency distributions of combinations of columns to find potential hierarchies.  In your example each value in Col2 only ever has one value in Col1 and so forth.

When you identify candidate keys and dependencies between columns you can apply normalization.

EDIT: In response to OP's quesiton in comments:

This is a query that would tell you whether or not one column may have a functional dependency on another column:

select COL2
from MYSTERY
group by COL2
having count(distinct(COL1)) <> 1

If COL2 has a functional dependency on COL1 then this query would return 0 rows.  This is because every value of COL2 has exactly 1 corresponding value of COL1.


Aggregate all joined rows with where clause Three nested tables exist: user_groups, users, projects. Each user_group has many users and, in turn, each user has many projects.

Each project has an ordinal assigned, 1-5 (step_number).

I would like to provide a summary for each user group. Each user group summary should include the users' projects and their unique progress of the users' projects and the steps they are at. Importantly, I would like to also like to select users projects with projects for a specific ordinal step. In other words, I would like to include the sibling projects for a users' steps.

For example, below, I would like all users aggregated by user_groups.id with projects that are step 3.

+------------+---------------------------------+
| User Group | Summary (Project Name: 1, 2, 5) |
+------------+---------------------------------+
|          1 | proj1: 2, 3, 5; proj2: 3, 5…    |
|          2 | proj33: 2, 3; proj 44: 1, 3     |
|          3 | proj55: 2, 3, 4; proj 66: 1, 3  |
+------------+---------------------------------+

The initial instinct was to write a query like this:

SELECT
  user_groups.id,
  STRING_AGG( DISTINCT(projects.step_number), ', ') AS summary
FROM user_groups
INNER JOIN users ON user_groups.id = users.user_group_id
LEFT JOIN projects ON users.id = projects.user_id
WHERE step_number = 3
GROUP BY user_group.id

However, that only returns projects where the step number is 3 and not the other steps for that user's other project. That makes sense, but how do I include all the sibling projects for that user_id?

I also tried different joins, but I am guessing and clearly unclear on how to solve this problem with SQL.

Here are some simplified table definitions

                                Table "public.user_groups"
  Column    |            Type             |                       Modifiers
------------+-----------------------------+-------------------------------------------------------
 id         | integer                     | not null default nextval('user_groups_id_seq'::regclass)
 created_at | timestamp without time zone |

                           Table "public.users"
   Column      |            Type             |                       Modifiers
---------------+-----------------------------+-------------------------------------------------------
 id            | integer                     | not null default nextval('users_id_seq'::regclass)
 user_group_id | integer                     |
 gender        | character varying(255)      |
 created_at    | timestamp without time zone |
 first_name    | character varying(255)      |
 last_name     | character varying(255)      |

                                  Table "public.projects"
   Column     |            Type             |                           Modifiers
--------------+-----------------------------+-------------------------------------------------------
 id           | integer                     | not null default nextval('projects_id_seq'::regclass)
 user_id      | integer                     |
 step_number  | integer                     |
 created_at   | timestamp without time zone |
 updated_at   | timestamp without time zone |
 start_dt     | date                        |
 end_dt       | date                        |
 project_name | text                        |

If I understand your requirements, this consists of selecting all the users that have a project in step 3, and using that list to select all the projects for just those users.
Something like:
SELECT ug.id
     , STRING_AGG(DISTINCT p.step_number, ', ') AS summary
FROM user_groups ug
    INNER JOIN (
            SELECT u.user_group_id
                 , u.user_id
            FROM users u
                INNER JOIN projects p ON u.user_id = p.user_id
            WHERE p.step_number = 3
            GROUP BY u.user_group_id, u.user_id
        ) Step3Projects ON ug.user_group_id = Step3Projects.user_group_id
    INNER JOIN projects p ON Step3Projects.user_id = p.user_id
GROUP BY ug.id;