How can I compare each single day of last two weeks in MySQL I'm stuck, this job seems too complicated for me I hope in some goodness help here!

So this is my table views:

+---+--------------------+
| id|                time|
+---+--------------------+
| 1 | 2016-10-06 10:13:11|
| 2 | 2016-10-07 15:33:31|
| 3 | 2016-10-08 20:13:13|
| 4 | 2016-10-09 09:16:21|
| 5 | 2016-10-10 18:23:17|
| . |                 ...|
+---+--------------------+

I need to select in array the rows count of each day for the last 2 weeks and compare them (so I need to compare monday of current week and previous week).

So I tried:

SELECT 'last_week' AS week, COUNT(*) AS rows
    FROM `views`
    WHERE `time` >= CURDATE() - INTERVAL  6 DAY
      AND `time` < CURDATE() + INTERVAL  1 DAY
  UNION ALL
    SELECT 'previous_week' AS week, COUNT(*) AS rows
    FROM `views`
    WHERE `time` >= CURDATE() - INTERVAL 13 DAY
      AND `time` < CURDATE() - INTERVAL  6 DAY;

But it counts rows of each weeks and not single days.
Yes, I can add eg. AND DAYOFWEEK(time) = 7; to the query, but in this case I have to declared it for seven times.

Basically I need something like:

+---------------+--------------------+
|          week | 1| 2| 3| 4| 5| 6| 7|
+---------------+--+--+--+--+--+--+--+
|     last_week | 1| 5| 3|15|12| 1| 3|
| previous_week |10|15|34|22|55|78|99|
+---------------+--+--+--+--+--+--+--+

Where 1 is Monday, 7 Sunday.

PRELIMINARY DATE MATH

Here are two expressions you need to understand

mysql> SET @this_past_monday = CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND;
Query OK, 0 rows affected (0.01 sec)

mysql> SET @prev_two_mondays = @this_monday - INTERVAL 2 WEEK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT CURDATE(),@this_past_monday,@prev_two_mondays;
+------------+---------------------+---------------------+
| CURDATE()  | @this_past_monday   | @prev_two_mondays   |
+------------+---------------------+---------------------+
| 2016-10-11 | 2016-10-10 00:00:00 | 2016-09-26 00:00:00 |
+------------+---------------------+---------------------+
1 row in set (0.00 sec)

mysql>

GOAL OF THIS ANSWER

You need to combine two queries

generate a temp table with the dates based on these two Mondays
retrieve counts from views table based on these two Mondays

QUERY #1

Generate all dates from Jan 1st last year to 1000 days later, extracting all dates spanning 2 weeks before this past Monday

SELECT dt FROM
(SELECT MAKEDATE(YEAR(NOW()) - 1,H*100+T*10+U) dt FROM
(SELECT 0 H UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) H,
(SELECT 0 T UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T,
(SELECT 0 U UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) U
ORDER BY dt) A
WHERE dt >= CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND - INTERVAL 2 WEEK
AND   dt <  CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND;

QUERY #1 EXECUTED

mysql> SELECT dt FROM
    -> (SELECT MAKEDATE(YEAR(NOW()) - 1,H*100+T*10+U) dt FROM
    -> (SELECT 0 H UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) H,
    -> (SELECT 0 T UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T,
    -> (SELECT 0 U UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
    -> UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) U
    -> ORDER BY dt) A
    -> WHERE dt >= CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND  - INTERVAL 2 WEEK
    -> AND   dt <  CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND;
+------------+
| dt         |
+------------+
| 2016-09-26 |
| 2016-09-27 |
| 2016-09-28 |
| 2016-09-29 |
| 2016-09-30 |
| 2016-10-01 |
| 2016-10-02 |
| 2016-10-03 |
| 2016-10-04 |
| 2016-10-05 |
| 2016-10-06 |
| 2016-10-07 |
| 2016-10-08 |
| 2016-10-09 |
+------------+
14 rows in set (0.00 sec)

mysql> SELECT CURDATE();
+------------+
| CURDATE()  |
+------------+
| 2016-10-11 |
+------------+
1 row in set (0.00 sec)

mysql>

QUERY #2

SELECT DATE(`time`) dt,COUNT(1) RowCount FROM views
WHERE `time` >= CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND - INTERVAL 2 WEEK
AND   `time` <  CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND
GROUP BY DATE(`time`);

COMBINATION

LEFT JOIN Query #1 and Query #2

SELECT A.dt,IFNULL(B.RowCount,0) RowsCounted FROM
(SELECT dt FROM
(SELECT MAKEDATE(YEAR(NOW()) - 1,H*100+T*10+U) dt FROM
(SELECT 0 H UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) H,
(SELECT 0 T UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) T,
(SELECT 0 U UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) U
ORDER BY dt) A
WHERE dt >= CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND  - INTERVAL 2 WEEK
AND   dt <  CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND) A
LEFT JOIN
(SELECT DATE(`time`) dt,COUNT(1) RowCount FROM views
WHERE `time` >= CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND - INTERVAL 2 WEEK
AND   `time` <  CURDATE() - INTERVAL WEEKDAY(NOW()) DAY + INTERVAL 0 SECOND
GROUP BY DATE(`time`)) B
USING (dt);

REASON FOR COMBINATION / LEFT JOIN

In the event there are no rows for a particular date, you want Query #1 to still show a date but present 0 as the number of rows

GIVE IT A TRY !!!

NOTE : I will leave the gory details of presenting the data in your capable hands


Galera cluster: change the size of InnoDB Redo log files online? We have a 3 node Galera cluster. I need to change the mysqld innodb_log_file_size parameter (see Changing the Number or Size of InnoDB Redo Log Files).

I do this by stopping all 3 nodes and disabling Puppet. Can I do this change online with Galera? How?

> select VERSION();
+----------------------+
| VERSION()            |
+----------------------+
| 5.5.41-MariaDB-wsrep |
+----------------------+
1 row in set (0.00 sec)

The InnoDB redo log files are independent of galera.  Because of this, you should be able to change the redo log configuration by doing a rolling restart and following the procedure in the link you referenced.


How do I determine if FORCE LOGGING is on for the entire database? How can I determine if force logging is on, at the database level. (Not asking about finding out if force logging is on for particular tablespaces.)

What rights are needed to perform the check of the above. (I have SELECT_CATALOG_ROLE, but other than that, pretty limited rights.)

I've tried to find out how to query from the Reference and the Administrator's Guide manuals.

Version:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0  Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SELECT force_logging FROM v$database;

The SELECT_CATALOG_ROLE gives you permission to run this statement.


How does normalization fix the three types of update anomalies? I have been reading 'Database Systems: A practical approach to design, implementation and management' by Connolly and Begg in order to understand database normalization (chapter 14). I now better understand the three database normalization forms:

First normal form (1NF)
Second normal form (2NF)
Third normal form (3NF)

I also understand the three update anomalies:

Insertion anomaly
Deletion anomaly
Update / Modification anomaly

What I am struggling to understand now is how to link the two together. For example, which of the different forms help(s) to fix the insertion anomaly? Similarly for the other anomalies. I would ideally like to know the mapping relationship between the two groups and why those forms fix the certain anomaly.

This is my first question on here and I searched on Google and this site to try and find an answer first, but to no avail.

Thank you.

1NF is basically just "don't keep too much data in a single column", so I think that 2NF and 3NF are the primary fix for all 3 database anomalies, since both 2NF and 3NF involve breaking out items into their own tables:

Insertion anomaly: If you have one big enrollment table that includes both "class" and "student" data (neither of which exists elsewhere), then you can't enter a new (empty) course without at least one corresponding student (because the table is a record of your enrollments). So, apply 2NF and create separate tables for classes, students, and make your original enrollment table link to both by ClassID and StudentID. Now you can enter new classes with no students, and new students with no classes.
Deletion anomaly: Same as above, if each row of your original enrollment table contains the full details of the student and the full details of the class they are enrolled in, then removing the last enrolled student for a class removes the last bit of information about that class. The solution is the same, apply 2NF to make separate tables, so that students can be enrolled or unenrolled without losing class information.
Update anomaly: Same as above, using the single-table method, updating information (say, the room number) for a class with multiple students enrolled might lead to a situation where some rows have been new information and other rows have the old. Applying 2NF as above is again the solution, so that class data is changed in only one place (the classes table).

Note that 1NF still plays some role in your process, as it means you can't try to solve the problem by cramming an entire list of enrolled students into a single field, or add student1, student2, student3 fields or something like that.

We could come up with similar examples where the factor in play is 3NF instead of 2NF: If each "student" has a faculty advisor (and some advisors are assigned to multiple students), that might not be part of the key for our student table, but it is "dependent attribute", and could lead to some of the same problems as above. So faculty advisor could be broken out into its own table.

A few resources I found helpful:

Normalization in DBMS: 1NF, 2NF, 3NF and BCNF in Database
Fundamentals of Database Design: Anomalies


How to configure data collection in SQL Server 2012? In SSMS of SQL Server 2012 Enterprise on Windows Server R2 Standard, each time I am trying to set sub-option "On demand" of option "Non-cached - Collect and upload data on the same schedule" of Data Collection Set, I am getting the error:

  A  collection set cannot start without a schedule. Specify a schedule for the collection set

  TITLE: Microsoft SQL Server Management Studio

  Operation 'Alter' on object 'Disk Usage' failed during execution.
  (Microsoft.SqlServer.Management.Sdk.Sfc)

  An exception occurred while executing a Transact-SQL statement or
  batch. (Microsoft.SqlServer.ConnectionInfo)

  Caught error#: 14684, Level: 16, State: 1, in Procedure:
  sp_syscollector_start_collection_set, Line: 203, with Message: Caught
  error#: 14693, Level: 16, State: 1, in Procedure:
  sp_syscollector_start_collection_set, Line: 108, with Message: A
  collection set cannot start without a schedule. Specify a schedule for
  the collection set. (Microsoft SQL Server, Error: 14684)

Now, I do not see how to re-setup or reconfigure the Data Collection in any way other than to enable and disable already existing malfunctioning one.

Any ideas how to make it working?

First of all, I'd like to understand whether it is me who screwed it (though I tried on different machines) or it is someone else ?

Update:
Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64)
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1  (Build 7601: Service Pack 1) (Hypervisor)

It's a bug.  This Connect ticket has a workaround for it from Kendra Little:

  I was able to work around this issue by temporarily adding a scheduled
  to the Utility Information collection (@collection_set_id=4) with the
  SQL command below. After doing this I was able to complete the data
  collection configuration via the GUI successfully. Afterwards I went
  back and set the Utility Information collection to manual start,
  thereby removing the schedule.

declare @schedule_uid uniqueidentifier
select @schedule_uid = schedule_uid from dbo.syscollector_collection_sets where collection_set_id=3

exec dbo.sp_syscollector_update_collection_set
@collection_set_id=4
, @schedule_uid=@schedule_uid


Deleted config.ini in MySQL cluster by mistake I was trying to delete cache file of MySQL cluster for rolling restart, but by mistake I deleted the directory /var/lib/mysql-cluster from both the nodes.

I have ndb_1_config.bin.1 file, but how can I recover config.ini in text format.
NDB version is ndb-7.4.12

The file on the FS is only marked as deleted, with the actual deletion happening once all open FDs to it are closed.

This means that if you haven't restarted the management server (or any process that had the config.ini file open), then you can copy the config.ini file contents from that FD which the process still has open.

You can see the open file descriptor(s) with:

lsof | grep config.ini

And here's a one-liner to copy the file contents:

lsof | awk '/config.ini/{print $2 ":" $4}' | tr -d [:alpha:] | sed s/:/\\/fd\\// | xargs -Ifile cat /proc/file > /tmp/config.ini

Alternatively you can try and use ndb_config to dump the in-memory configuration from the management server.

And in the future, you probably should backup the file. :)


MongoDB - handling embedded documents and relations I am researching MongoDB for a web app that I am bulding.  Coming from a MySQL background, the concept of embedded documents is not so easy to fully understand.

Let's say I have a document called blogpost and it looks something like this:

db.posts.save({
    _id: 1
    title: "first post!",
    body: "post content",
    author: {
        _id: 1,
        name: 'John'
        email: 'jonh@doe.com'
    },
    comments: [
        {
            _id: 20,
            author: 'mary',
            content: 'This blog post is cool!'
        }
    ]
});

Each author would actually be stored in the authors collection, when I save the blogpost, I would merely copy the data from the author document and paste it so it is embedded in the blogpost document.  Is this a good way to do that?

My concern is that when John updates his e-mail address, it would only be updated in the authors collection. Some of his older blogposts would then show an outdated e-mail address for him.

Does MongoDB have a method for dealing with that issue, or would I need to do it myself in my app code?

If I do that in my app code then what is the point of embedding the author into the blogpost in the first place? I could just store the reference, author _id, and look up the author in a separate query.

On the other hand, if I need to store historical data, for example an invoice with customer information, then it would make sense to embed the customer document inside the invoice document since invoices need to show the customer data that existed when the invoice was created.

For the comments part, I have already read about Multiple Collections vs Embedded Documents and when it comes to comments, Multiple Collections seems to be the way to go. http://mongly.com/Multiple-Collections-Versus-Embedded-Documents/

So, in general - am I completely missing the point of embedded documents?

You are not missing the point :)

The key is how many reads and how many writes do you have to do when you store/update/fetch various entities in your collections?

You are presumably constantly (daily?) creating new posts and frequently creating new comments, and very frequently querying posts.

Actions like updating authors' email are very rare compared to the above.  You want to optimize the performance of reads and writes that happen hundreds or thousands of times per  in your application, and worry less about performance of very infrequent actions.

Having said that, I would store the basic author information embedded in the post, but I would only have information there that must be displayed with every post.  If there are additional details about the author I want to keep in its own collection, I would expect that would be details that the user needs to click additional link to see (allowing for time to do another read).


Calculate Percentage with JOINS and original value I need your help. I'm trying to calculate percentages by using results from JOINING divided by the original value. I do not know how to combine both values. I try to find trips with station id which do not exist in the table station and then find the percentage of that trips.
I only can write the query to this extend:
SELECT COUNT(*)
FROM (SELECT a.start_station_id, b.station_id, a.end_station_id,a.bikeid
FROM `bigquery-public-data.new_york_citibike.citibike_trips` as a
LEFT OUTER JOIN  `bigquery-public-data.new_york_citibike.citibike_stations` as b
ON a.start_station_id = b.station_id
WHERE b.station_id IS NULL)

I need to divide the result COUNT from the query above with COUNT(*) before JOIN.
Kindly help me with this.
Thank you!!

One way to get expected output can be
select case when total_count = 0 then 0.0 else count_1*100.0/total_count end as pcnt
from
(
SELECT count(case when b.statition_id is null then 1 end) as count_1,
count(*) as total_count
FROM `bigquery-public-data.new_york_citibike.citibike_trips` as a
LEFT OUTER JOIN  `bigquery-public-data.new_york_citibike.citibike_stations` as b
ON a.start_station_id = b.station_id
)x

Note, it returns 0.0 if total_count is 0 to avoid division by zero.  You may want to return null(casted to proper decimal type ) in such case instead.


Does MySQL compress the stored data? When I export a database in MySQL, it gives me a .sql file. Let's say this .sql file is 80 MB.

Are they compressed in order to reduce disk space usage?

Is that 80MB real physical-storage size?

It depends on how you export your database:

if you use mysqldump, then the result is a flat text file containing the sql commands.

you can compress it manually or in one commandline like this:

mysqldump < mysqldump options> | gzip > outputfile.sql.gz

see: http://www.ducea.com/2006/10/28/compressing-mysqldump-output/

if your question is, if the tables are stored compressed in the working directory, there are some possibilities:

if you have MyISAM tables that would work if they are read-only, you can Generate Compressed, Read-Only MyISAM Tables with myisampack

And by setting InnoDB configuration options, you can create tables where the data is stored in compressed form.

see: dev.mysql.com/doc/refman/5.5/en/innodb-compression.html

just alter the table to add the compressed option:

ALTER TABLE `tablename` ENGINE = InnoDB ROW_FORMAT = COMPRESSED KEY_BLOCK_SIZE =4


NEWB Getting to KNow New Database and Understand Where Relationships Are I'm teaching myself SQL using MYSQL and a textbook.

While working through the textbook I'm using a database I'm kinda familiar with - an exported copy of my CMS driven websites database.

My website collects and sends emails to people who subscribe each time a new article is added. When I log in to the back end of my CMS website I can see the number of emails sent to each email address. People sign up via the "get alerts" page.

While reading through my text book I am currently covering functions in SQL. Since I am already familiar with my data I thought that it would be a good opportunity to try to recreate the data described above - the count of emails sent to each recipient.

There are many tables in my database but I'm pretty sure that the ones of relevance are called "alert" and "alert_sent".

I have used SELECT to view the top 5 records and try to determine what a common field between the two would be but cannot see any. I then wondered what the standard procedure here was?

When one is given a new database, is there a rule of thumb in figuring out what fields are what? Is there typically a "key" of some sort that defines what tables/fields are showing? Or do DBAs typically have to just browse the data and figure it out?

Apologies if this question is vague. I'm trying to teach myself by example in using a database I'm moderatley familiar with.

If it's only help, here is the results of DESCRIBE on the two mentioned tables:

Table "alert"

Field              | Type                | Null | Key | Default | Extra          |
+--------------------+---------------------+------+-----+---------+----------------+
| id                 | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id            | int(11) unsigned    | YES  | MUL | 0       |                |
| alert_type         | tinyint(4)          | NO   |     | NULL    |                |
| alert_recipient    | varchar(200)        | YES  | MUL | NULL    |                |
| alert_code         | varchar(30)         | YES  | UNI | NULL    |                |
| alert_confirmed    | tinyint(4)          | NO   |     | 0       |                |
| alert_lat          | varchar(150)        | YES  |     | NULL    |                |
| alert_lon          | varchar(150)        | YES  |     | NULL    |                |
| alert_radius       | tinyint(4)          | NO   |     | 20      |                |
| alert_ip           | varchar(100)        | YES  |     | NULL    |                |
| previous_noconfirm | tinyint(4)          | YES  |     | NULL    |

and table "alert_sent"

Field       | Type                | Null | Key | Default | Extra          |
+-------------+---------------------+------+-----+---------+----------------+
| id          | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| incident_id | bigint(20) unsigned | NO   | MUL | NULL    |                |
| alert_id    | bigint(20) unsigned | NO   | MUL | NULL    |                |
| alert_date  | datetime            | YES  |     | NULL    |

I'm not a DBA but I've looked at many "new to me" databases.

I don't know if there is a trick to understanding a database.  What has worked for me is just to explore the db and query it to see what it returns.

Many times as a new developer to a project I'm given a task to fix or update something.  I usually look at the table like you did with alert and look at any other id columns that might be in the table (i.e user_id & incident_id).  That starts the bread crumb trail. Then I look at the foriegnkeys to see what they are doing (i.e. cascade delete).  The FK usually point to another table that could also lead to other tables.

If the bread crumb trail of id columns gets bigger than 2 or 3, then I usually try to diagram the db if possible.  Sometimes that helps me to see the tables in a different perspective.

Other than that, it just takes time.  As you get more tasks under your belt with the db you will understand more of it.


How to efficiently clone database records together with all child elements I have an insert-only PostgreSQL database with a Book table, a Page table and an Element table.
Books can have multiple pages and pages can have multiple elements.
The database is insert-only because any time a page is modified, a new version of that page is created in a new record for auditing reasons.

-------------
Book
-------------
id [PK]

-------------
Page
-------------
id [PK]
book_id [FK]
version
created

-------------
Element
-------------
id [PK]
page_id [FK]
version
created

Currently, Element has a Foreign Key pointing to Page.id and Page has a Foreign Key pointing to Book.id.

A requirement is to have independent page drafts (versions) that can contain any number of elements. Every time a new page draft (version) is created, the new draft should be a clone of the previous version and it should contain all elements in the previous version. Elements can be added to or removed from a specific version of the Page, although Elements records are never deleted.

For scalibility reasons, I would avoid cloning all the elements together with the page, since the elements could be a very large number. I would also what to avoid many-to-many relationships between pages and elements.

I was thinking about replacing the Element.page_id FK with an Element.book_id FK so to have a single bucket of elements and pages more loosely coupled with each other. This way, when cloning a page, I wouldn't have to clone all the elements, but I would need a way to associate each element with a particular version of a Page.

Ideally, I should be able to retrieve a page version with all the elements associated to that version with a single SQL query.

I am still in a design/prototyping phase, so any aspect of the databse can potentially be changed to fulfill the requirements.

What could be the best way to associate Pages and Elements according to the requirements above? I hope the requirements are clear, if not, please ask any questions and I'll try to clarify.

a new version of that page is created in a new record for auditing reasons.

How do you know which "version" is the latest, you're going to process on version and select the last one? That'll work, but I think you're barking up a pretty steep tree. Essentially you're building your own revision-control system in a database. Not saying everyone hasn't tackled that task before, but issues likely to come up are

Merging (other users)
Rollbacks (without which, why would you keep old versions)
Diff-views
Delta stores (why store the whole element when you only change one character)
etc, etc.

This gets super complex very fast. If you're wanting an out of the box solution, checkout gitlab. If you have speced to just stop here and not feature creep -- I think this will work just fine.


Run MySQL replication by schedule or on demand I have a master-slave replication currently configured. I know this setup runs real time. Is it possible to sync only the databases on schedule (daily or weekly) or by demand?

Thanks!

On your Slave issue the STOP SLAVE, START SLAVE commands to stop / start replication on Demand. If you want to run it to a schedule write a batch file / cron to STOP SLAVE and START SLAVE at the required times. I'm assuming you have a good reason for doing this?


Access: How to Combine Data from Two Rows in a Query I have a base dataset formatted like the one below.

Sex1    | Sex2    | Number|
F       |         | 5     |
M       |         | 8     |
M       | F       | 5     |
F       | F       | 3     |
M       | F       | 2     |
F       | M       | 1     |
F       | F       | 9     |
M       | M       | 3     |

I'd like to use SQL to create a query that produces totals from the price by Gender composition like so:

Sex | Total Number
F   |    5
M   |    8
MF  |    8
FF  |    12
MM  |    3

However, I'm struggling to find a way to pull the FM and MF categories into the same row. The code I have that leaves them distinct is of the form

SELECT Sex1 & "" & Sex2 AS Sex, Sum(Number) AS [Total Number]
FROM TableName
GROUP BY Sex1 & "" & Sex2

Is there a simple way to combine the rows?

You could use a CASE expression or the IIf() function to first order the two values and then group by them:

with CASE expression:

SELECT CASE WHEN Sex1 < Sex2 THEN Sex1 ELSE Sex2 END
       & CASE WHEN Sex1 < Sex2 THEN Sex2 ELSE Sex1 END AS Sex,
       Sum(Number) AS [Total Number]
FROM TableName
GROUP BY CASE WHEN Sex1 < Sex2 THEN Sex1 ELSE Sex2 END,
         CASE WHEN Sex1 < Sex2 THEN Sex2 ELSE Sex1 END ;

with IIf() function:

SELECT IIf(Sex1 < Sex2, Sex1, Sex2) & IIf(Sex1 < Sex2, Sex2, Sex1) AS Sex,
       Sum(Number) AS [Total Number]
FROM TableName
GROUP BY IIf(Sex1 < Sex2, Sex1, Sex2),
         IIf(Sex1 < Sex2, Sex2, Sex1) ;


Using AS OF TIMESTAMP or DBMS_FLASHBACK.ENABLE_AT_TIME across multiple schemas We have a requirement to read data as of certain time (say T1) from, say, schema 1, fill some staging tables in schema 2 with that data. And then read data from some other tables of schema 1 as of T1 based on the current data in the schema 2 staging tables (i.e. schema 1 and schema 2 are joined in the subsequent queries).

Is there a way of achieving this without having to use AS OF in each of the (500+) queries in the stored procedure, wherever schema 1 tables appear? Also there are lots of commits in the stored procedure due to the large amount of being inserted into schema 2.

Business need is to have read consistency across all the queries on Schema 1 so it can be as of the start of the stored procedure execution. All the tables in the schema 1 are enabled for flashback queries. Issue we are facing is having to read the tables of schema 1 as of certain time, simultaneously with reading current data from tables in schema 2.

Yes you can use dbms_flashback.enable_at_time. This changes your current sessions perception of the whole database (all schema).

But keep in mind that you would have to write changes in a different session, so you cannot use insert from those statements.

There is a current video from Connor as of AskTom fame on this: https://youtu.be/lmrWmH1vMPc

Another option is, if you want to have a consistent but current view you can set your transaction isolation to read only and begin a new transaction to get read consistency across multiple queries.

If you want to mix both read and write then you would need to use ‚as of` for the read subquery. The problem you have with that (changing  many statements) can be easily solved by actually generating those statements automated.


How to split field and run sub query using it? Given two tables T1 and T2 below, is there a way in MySQL 5.7.x to get such a list field from T1, so that none of its entries are present in T2?
T1.list is a regular VARCHAR, containing T2.vals delimited by ':'

From the example, I would like to get D:E field.

T1

id list
0  A:B:C
1  D:E
2  F:G

T2

vals
A
B
C
F
G

select t1.id, t1.list
from t1
left join t2 on locate(t2.vals, t1.list)
group by t1.id, t1.list
having 0 = count(t2.vals);

fiddle

In more complex case (when a token in list may contain a token as a substring) you may use

select t1.id, t1.list
from t1
left join t2 on locate(concat(':',t2.vals,':'), concat(':',t1.list,':'))
group by t1.id, t1.list
having 0 = count(t2.vals);


SQL Server - Index Including PK Lets assume we have a table as follows:

PK -- nonclustered primary key
Col1 -- unique clustered
Col2
Col3

Is this index:

nonclustered index on (Col2, Col3)

any different from this index:

nonclustered index on (Col2, Col3) include (PK)

What if my PK is clustered instead of nonclustered? Now does it make/not make a difference?

Edit to add:

I suppose this is a round-about way of asking: Is the nonclustered index's pointer back to the table based on the clustered index or the primary key? I assume the clustered index RID is what is used.

The clustered index key is included in every non-clustered index as the row identifier.

SQL Server just ignores an INCLUDE of the clustered index key.

If your PK index is NONCLUSTERED then it will not be included in every row of all other NC indexes.

You can test this yourself by making two identical indexes, one with INCLUDE(ClusterKey) and one without, and compare the sizes.  They will be identical, even on hundreds of millions of rows.


Grouping records with with consecutive start and end dates -Postgresql We are migrating to a new record management system and I'm weeding through the issues with the data migration. Source data and destination data are all in Postgresql

In the source database we have multiple rows for the same vendor for each authorization code for that vendor and the start and end date of that authorization.

+----------+---------------+------------+-----------+
|  Vendor  | Authorization | Start Date | End Date  |
+----------+---------------+------------+-----------+
| Vendor 1 | #####         | 1/1/2017   | 2/28/2017 |
| Vendor 1 | #####         | 3/1/2017   | 4/15/2017 |
| Vendor 2 | #####         | 4/16/2017  | 5/31/2017 |
| Vendor 2 | #####         | 6/1/2017   | 7/12/2017 |
| Vendor 1 | #####         | 7/13/2017  | 9/30/2017 |
+----------+---------------+------------+-----------+

In the destination database we have records for the active vendor relationship at a given time with a start date and an end date. (Auth # start and end dates are tracked separately) After migration the example listed previously should look like this.

+----------+------------+-----------+
|  Vendor  | Start Date | End Date  |
+----------+------------+-----------+
| Vendor 1 | 1/1/2017   | 4/15/2017 |
| Vendor 2 | 4/16/2017  | 7/12/2017 |
| Vendor 1 | 7/13/2017  | 9/30/2017 |
+----------+------------+-----------+

I think I should be able to accomplish this joining the table back on it self n-1 number of times where n is the largest number of consecutive authorization numbers for the same vendor which is probably around 10, which is doable but definitely not preferable.

Is there a more common method to group data by consecutive start and end dates?

You could use a GROUPING AND WINDOW solution in this way:

create table tbl (vendor text, auth text, StartDate Date, EndDate Date);

✓

insert into tbl values
('Vendor 1', '#####', '20170101', '20170228'),
('Vendor 1', '#####', '20170301', '20170415'),
('Vendor 2', '#####', '20170416', '20170531'),
('Vendor 2', '#####', '20170601', '20170712'),
('Vendor 1', '#####', '20170703', '20170930');

5 rows affected

select vendor, min(startdate) as startdate, max(enddate) as enddate, grp
from (
      select vendor, auth, startdate, enddate,
             sum(rst) over (order by vendor, startdate) as grp
      from (
             select vendor, auth, startdate, enddate,
                    case when coalesce(lag(enddate) over (partition by vendor order by vendor, startdate), startdate) + 1 <> startdate then 1 end rst
             from   tbl
           ) t1
     ) t2
group by grp, vendor
order by startdate

vendor   | startdate  | enddate    | grp
:------- | :--------- | :--------- | --:
Vendor 1 | 2017-01-01 | 2017-04-15 |   1
Vendor 2 | 2017-04-16 | 2017-07-12 |   3
Vendor 1 | 2017-07-03 | 2017-09-30 |   2

db<>fiddle here


Is it worth to update to an x64 environment? Situation

We have 10 to 20 Tomcat web applications with hundreds of concurrent users per application on a server.

Each Tomcat application connects using its own user to a database on Microsoft SQL Server 2008 R2 server.  This database contains loads of Stored Procedures that are executed by the Tomcat application. The Stored Procedures usually execute large joins between multiple tables (usually joins between 4-5 tables with each 100k+ records)

Currently this runs on 32-bit Windows 2008 R2 (Enterprise) with 32-bit Tomcat and 32-bit SQL Server 2008 R2. The Servers have 32 - 64GB of RAM on a multi-core processor.

Question

My gut feeling tells me we should update to x64. This will future-proof and prevent some memory issues now that might be better handled in an x64 environment. I can't find benchmarks or proof for this though. Using flags like /AWE and /PAE feels like SQL Server is artificially trying to perform better than it would out of the box, whereas this artificial layer is not necessary in the x64 environment. I can't find any "proof" for this though.

Is it worth updating this configuration to x64? Will we see a performance gain? How much? Are there benchmarks supporting this?

Well, memory management will be much better in a 64bit platform.  This is because all the memory is real memory and there's no concept of extended memory any more.

This means that you can now have much more than 1 Gig of execution plans in memory at any one time.  If you've got lots of stored procedures you are probably generating new plans for queries all the time because you can't store all the plans in memory.

Granted this ability to grow the plan cache beyond 1 Gig will take away from the buffer pool, so that's something to keep in mind (no you can't control how big the plan cache is).

Joins of 4-5 tables with 100k records each isn't all that scary.  When you get into the 100M range that's when things start to get more interesting.


Load Balancer for MySQL MASTER MASTER Replication We are having typical requirement.

We are having MySQL MASTER-MASTER Replication setup in place. We require to setup Load Balancer between these 2 MySQL servers.

I am aware of setting up WEB based Load Balancer, but we require load balancer for MySQL servers.

Requirement is there are one Application server and 2 DB servers, Application server will connect to Load Balancer, Load balancer will connect to DB servers by sharing the load.

Please let me know the best approach for this, also let me know if there are any pros and cons.

As you already have a running Master-Master replication, here a tip for the load balancer: I use HAProxy

It is a free software balancer, needs very less system resources and is quite easy to configure.

A tip: Install 2 loadbalancers with keepalived or keep in mind that you just created a critical bottleneck.

I use the setup keepalived + 2x haproxy + 2x master-mysql for 9 months now and I only noticed one problem with pdo_mysql - you can see my problem here: https://stackoverflow.com/questions/26871221/slow-pdo-mysql-mysqli-with-haproxy

There is also a small example config (in the example it is a hot standby master)


Need help to build SQL query for a complex scenario I need help to build an SQL query for a slightly complex scenario. I have an interface with the following dropdowns.

Cuisine: Any, Chinese, Indian, Italian, French

Delivery type: Any, Delivery, Pickup, Dine-In

Meal type: Any, Breakfast, Lunch, Dinner

Cuisine Table

CuisineId  | Type
-------------------
     1     | Chinese
     2     | Indian
     3     | Italian
     4     | French

Delivery type Table

DeliveryTypeId | Type
--------------------------
         1     | Delivery
         2     | Pickup
         3     | Dine In

Meal type Table

    MealTypeId | Type
--------------------------
         1     | Breakfast
         2     | Lunch
         3     | Dinner

Food Table

    Food            CuisineId   MealTypeId      DeliveryTypeId
---------           -------     ---------       -------------
Cakes               NULL         NULL            NULL
Pizza               3 (Italian)  NULL            NULL
Noodles             1 (Chinese)  2 (Lunch)       NULL
Butter Chicken      2 (Indian)   3 (Dinner)      1 (Delivery)
Ice cream           NULL         2 (Lunch)       2 (Pickup)
Soup                NULL        NULL             2 (Pickup)
Drinks              NULL        NULL             1 (Delivery)

Please note: In the above table, NULL represents the selection of Any from the dropdown.

The logic here is as follows:

Cuisine: if the option is selected as Any or if the selected option doesn't have a matching value in the corresponding column in Food table, then show items that have NULL in Cuisine column.

Delivery Type: default should be Delivery / NULL unless Pickup or Dine In is selected

Meal type: Show only NULL (Any) items unless any specific option is selected.

For example, please find the sample output based on the selections.

Cuisine     Meal type       Delivery Type       Output
-------     ----------      -------------       ------
Any         Any             Any                 Cakes,Drinks
Any         Any             Delivery            Cakes,Drinks
Any         Lunch           Any                 NO RECORDS
Any         Any             Pickup              Soup
French      Any             Any                 Cakes,Drinks
French      Any             Delivery            Cakes,Drinks
French      Lunch           Any                 NO RECORDS
French      Any             Pickup              Soup
Chinese     Any             Any                 NO RECORDS
Chinese     Lunch           Any                 Noodles
Indian      Any             Any                 NO RECORDS
Indian      Dinner          Any                 Butter Chicken

How can I build an SQL query for the above scenario. Any help is highly appreicated. Thanks in advance.

The query will be used in a stored proc and the parameters passed will be @cusineType (like Chinese or French), @deliveryType (like Delivery or Pickup) and @mealType (like Breakfast or Lunch) or NULL for any of these.

Script for the above tables with data here: script.sql

Exactly, How you will pass  parameter value ?

In my example, I am passing parameter value in variable.

Mention it clearly.I understand that above output is only for explanation.

create table #Cuisine(CuisineId int,CuisineType varchar(50))
insert into #Cuisine values
( 1 ,'Chinese'),( 2 ,'Indian'),( 3 ,'Italian'),( 4 ,'French')

create table #DeliveryType (DeliveryTypeId int, DeliveryType varchar(50))
insert into #DeliveryType values
 (  1,'Delivery') ,(  2,'Pickup') ,(  3,'Dine In')

  create table #MealType (MealTypeId int, MealType varchar(50))
 insert into #MealType values
(1,'Breakfast')
,(2,'Lunch')
,(3,'Dinner')

 create table #Food(Food  varchar(50),CuisineId int,MealTypeId int,DeliveryTypeId int)
insert into #Food values
('Cakes',               NULL ,        NULL,          NULL)
,('Pizza',               3 ,  NULL,            NULL)
,('Noodles',             1 ,  2 ,       NULL)
,('Butter Chicken',      2  ,  3,       1 )
,('Ice cream',           NULL,         2 ,      2 )
,('Soup',                NULL,        NULL,             2 )
,('Drinks',              NULL,        NULL,             1 )

Parameter value,

Declare @Cuisine int,@Mealtype int,@DeliveryType int=2

Since you have condition like if delivery is Any then consider both null and Delivery=1 and vice versa.

So Create one local temp table to define value.It is easier and cost effective to join than write complicated and expensive where condition.

Create table #DeliveryParameter (DeliveryTypeId int)

if(@DeliveryType is null or @DeliveryType=1)
insert into #DeliveryParameter values(-1),(1)
else
insert into #DeliveryParameter values(@DeliveryType)

select * from #Food F
inner join #DeliveryParameter DP on f.DeliveryTypeId=dp.DeliveryTypeId
where ((@Cuisine is null and CuisineId is null) or(CuisineId=@Cuisine))
and ((@Mealtype is null and MealTypeId is null) or(MealTypeId=@Mealtype))

drop table #Cuisine,#DeliveryType,#MealType,#Food,#DeliveryParameter

Notice : ((@Cuisine is null and CuisineId is null) or(CuisineId=@Cuisine))

This type of condition is very expensive if you records are in millions .

So if you treat Null or Any as -1 or 0 for example, then condition will be simplified to

CuisineId=@Cuisine

Like in #Food table store -1 instead of Null and in variABLE pass -1 instead of null.

If you need output in some other format then mention it.


Why is dbo the only grantor in Microsoft SQL Server? I can't understand why is dbo the only grantor in SQL Server? Or am I wrong?

I mean it as follow:
No matter as which user I am currently loged in in SQL Server Management Studio, when I go to properties of a database under Securables and grant some permissions to a user and save it and come back to Securables: dbo is shown as Grantor for those permissions, which I granted before and not the current loged in user.

Why is it so?

Under which scenario could I see another user than dbo as grantor?

Thanks

Probably because all your logins are sysadmin, and sysadmin always connects as dbo.

Try something like:

create user fred without login

create user joe without login

grant select to fred with grant option

execute as user='fred'
  grant select to joe
revert

select * from sys.database_permissions


Why does selecting top 1 from composite index DESC also used to partition by month not select the top value? We have very large database table that I need to select the value of the max id. Given the table size SELECT MAX(id) FROM tableA will not perform well enough.

Table definition (with columns omitted for clarity here)

CREATE TABLE [dbo].[TableA](
    [id] [numeric](19, 0) NOT NULL,
    [timeSampled] [datetime] NOT NULL
 CONSTRAINT [PK_TableA] PRIMARY KEY NONCLUSTERED
(
    [id] DESC,
    [timeSampled] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

The table is partitioned by month on the timesampled, and because we switch partitions out to another table for archiving data the timeSampled column must be included in the Primary Key.

So, the PK is

[id] DESC,
[timeSampled] DESC

The table contains rows for May 2013 and June 2013, and the ids increment in date order (i.e. the max id is in June)

When I select the top 1 id from the table as follows

SELECT top 1 id
FROM dbo.TableA
WITH (INDEX (PK_TableA))

I don't get the max id, instead I get the max id from May (i.e. the very last entry in the May partition).

Any ideas why this is the case? Basically seems to be picking the MAX id from a partition, not max across all partitions

Basically seems to be picking the MAX id from a partition, not max across all partitions

Writing TOP (1) without an ORDER BY clause to define which row is 'top' means the query processor is logically free to return any row from the set. The query plan selected by the optimizer happens to return a particular row (highest id from the first partition) but you cannot rely on this, even if it were a useful result.

Whenever you use TOP you should always specify an ORDER BY at the same scope to produce deterministic behaviour - unless you really do not care which row(s) come back.

  Given the table size SELECT MAX(id) FROM tableA will not perform well enough

The optimizer is lacking some logic to transform a scalar MAX or MIN aggregate over a partitioned index to a global aggregate over per-partition aggregates. Itzik Ben-Gan explains the limitation and provides a general workaround in this article.

If the highest partition number is known and guaranteed not to change, the workaround to specify a literal partition using the $partition function will work, though it may fail in a non-obvious way if the partitioning strategy changes in future.

This 'solution' works by eliminating all but one partition, resulting in a simple seek on one partition of the index.

  Adding an order by id does not improve performance for some reason

The same optimizer limitation broadly applies to TOP (1) ... ORDER BY. The ORDER BY makes the result deterministic, but does not help produce a more efficient plan in this particular case (but see below).

Implied Index Keys

Your index is on id DESC, timeSampled DESC. In SQL Server 2008 and later, partitioning introduces an extra implied leading key on $partition ASC (it is always ascending, it is not configurable) making the full index key $partition ASC, id DESC, timeSampled DESC.

Since id and timeSampled increase together (though there is nothing in the schema to guarantee this) you could rewrite the query as TOP (1) ... ORDER BY $partition DESC, id DESC. Unfortunately, the DESC keys on your index and ASC implied leading key $partition means the index could not be used to scan just one row from the index in order.

If your index keys were instead id ASC, timeSampled ASC the whole index key would be $partition ASC, id ASC, timeSampled ASC. This all-ASC index could be scanned backward, returning just the first row in key order. This row would be guaranteed to have the highest id value in the highest-numbered partition. Given the (unenforced) relationship between id and partition id, this would produce the correct result with an optimal execution plan that reads just a single row.

This 'solution' lacks integrity because the id-timeSampled relationship is not enforced, and you probably do not want to rebuild the nonclustered primary key anyway. Nevertheless, I mention it because it may enhance your understanding of how partitioning interacts with indexes.


Optimizing single-row DELETEs from a table with many FOREIGN KEY references In a database I am working on, there is a table called persons with about one million rows, and 60 FK (FK) constraints from other tables (some with many millions rows) pointing to it.
If I delete one row from persons, it takes many minutes, which would not be a problem, but it also keeps the table locked, thus blocking the database for all processes. In the past, this has caused users to report that the system is down.
Of course the situation would greatly improve if I added supporting indexes for all the FKs (currently, only 20 of the 60 tables have it). But many of these FKs are for columns like modified_by, so all the indexes would have no other purpose, and would degrade performance of the system in the daily operations, only to gain an improvement in an exceptional case.
Before running the DELETE, I already make sure that all the referencing rows have been deleted or updated. I do it manually, since I am strongly against the use of CASCADE.
I am not considering soft-delete because otherwise I would have to change all software that reads the persons table to skip deleted rows.
Question
Is there a way to (possibly temporarily) change the locking mechanism of the persons table, so that even if the DELETE takes an hour, it does not impact concurrent processes?
Here's the anonymized execution plan.
Considerations

Disabling FKs might be a possibility. The risk is that someone else creates inconsistency while I delete the row, then I cannot re-enable the FKs.

Rows to delete: typically one at a time. Either manual operation or scheduled.

Interesting point: I did not check the execution plan immediately, but apparently, almost all operations cost 0%, except 7 cases of "Clustered index Scan (Clustered)" on the PK of referencing tables; one of them costs 57%, the other from 1% to 16%. I still don't understand why it should scan a clustered index.

the situation would greatly improve if I added supporting indexes for all the FK (currently, only 20 of the 60 tables have it)

You should always index fields used as keys, foreign or otherwise.
This gives the database far more options when analysing how to execute the query and adding them may actually increase the overall performance of your application.

  many of these FK are for columns like "modified by", so all the indexes would have no other purpose

As with [pretty much] everything on Planet Database, it's a compromise.
Here, you're balancing data integrity - knowing with certainty who changed something - against speed - not having those extra indexes that slow down inserts.
Does it really matter if you don't know (with certainty) the user that changed these records?  If not, you're OK as you are.  If it does matter - and in lots of cases, it really, really does - then you're stuck with having the overhead of the indexes.

  Before running the DELETE, I already make sure that all the referencing records have been deleted ...

With the FK's in place, you would not be able to delete them anyway, so the values of this activity is dubious.

The question really should be, why is the delete taking an hour?
If you're only deleting one record out of a million row table and there are no rows in the tables with FK's back to this table, then what's taking the time?  It should be practically instantaneous.

Slow queries, even deletes, are often caused by missing indexes - are you performing the delete based on the record's Primary Key field?  Or some other combination of [not-indexed] fields?


How to rename an index in informix using numbering I am currently facing issue of renaming an index to a number.

I have tried the below:

RENAME INDEX 584_1973_tmp TO '584_1973';
RENAME INDEX 584_1973_tmp TO ' 584_1973';
RENAME INDEX 584_1973_tmp TO "584_1973";
RENAME INDEX 584_1973_tmp TO " 584_1973";

None of the above seems to work. Is it possible to rename an index to a number within Informix?

I realized that once you rename an index like the below:

RENAME INDEX 584_1973 TO 584_1973_tmp;

You cannot revert, you will need to drop and recreate. If it is a unique index constraint, then it will auto recreate with the original numbering after dropped.


mysql fine tuning: open_tables, opened_tables I'm a novice to database administration and have been depending on forum suggestions and answers to help resolve my database hang ups and too many connections errors;  I've managed to reduce the frequency of too many connections errors but I'd like to eliminate them at this level.

According to an answer by RolandoMySQLDBA on  this post, "If Open_tables climbs above (Opened_tables / Uptime), I have a cause for concern.."

My Server specs is 2.0Mhz and 484Mem; I've had a client whose having a lot of hits upto 1000 requests per hour and with processing mainly of their 2 InnoDB tables, that translates to roughly 3000 connections made to both tables in an hour.

From advise on another forum, I've made changes to my.cnf and it translates to this:

[mysqld]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock
skip-locking
key_buffer_size = 30M
max_allowed_packet = 1M
table_cache = 300
sort_buffer_size = 256K
read_buffer_size = 1M
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
net_buffer_length = 12K
thread_stack = 1M
innodb_buffer_pool_size=60M
interactive_timeout=300
wait_timeout=300

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 48M
sort_buffer_size = 48M
#key_buffer_size = 256M
#sort_buffer_size = 256M
#read_buffer = 2M
#write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

When I checked the parameters of opened_tables, open_tables and uptime as per the post, I get these values:

Uptime = 78018
Opened_tables = 1606999
Open tables = 300

therefore: Opened_tables / Uptime=1606999/78018= 20.6

However, my Opened_tables variable has not changed in 3 hours; It has been the same.

After these changes, the server load had occurred twice in the past 24Hours (at night) and I didn't record the value for Opened_tables at this period; But as you'll note from the calculation, the Opened_tables value increased drastically last night during when many client requests came through.

Output of thread variables are:

 Threads_cached                    | 7          |
| Threads_connected                 | 14         |
| Threads_created                   | 306        |
| Threads_running                   | 2

and i note from the mysql documentation (http://dev.mysql.com/doc/refman/5.5/en/server-status-variables.html#statvar_Threads_connected) that I need to increase the thread_cache_size .

my max_connections is the default;

So basically:

I need to increase thread_cache_size;
I need to increase the
table_open_cache (but a very large value also causes
unreliability)

I've just had a "too many connections" error at the time of posting this and can't get results for Opened_tables and Open_tables; Also, I can't login with another session to the server; I automatically get disconnected.

QUESTIONS:

What method should I use to approximate the values of
thread_cache_size and table_open_cache?
Is my max_connections too low? How can I calculate the best max_connections setting?
How can I reduce the "too many connections" error (without creating another user and having that user be the one making the connections rather than root) ?

NOTE: No I can't upgrade memory as at now. Thanks.
       Right now the only way I deal with this is to close the port form incoming messages (which is not exactly ethical) or to restart server to pending messages in system can be processed and memory freed.

After my now too many connections errors - which I've resolved by blocking the receiving port; my variables are now:

Opened_tables = 1970498
Open_tables =300

| Threads_cached                    | 6          |
| Threads_connected                 | 15         |
| Threads_created                   | 437        |
| Threads_running                   | 2          |

Any advise i dealing with this wisely and efficiently will be much appreciated. Thanks in Advance.

[3]:

Three points:

I don't see the immediate connection between your "too many connections" and the table cache. There might be such connection, but you have not provided info which suggests the two relate.
I disagree with the advice about "If Open_tables climbs above (Opened_tables / Uptime), I have a cause for concern.."
You haven't listed the table_open_cache & table_definition_cache settings. The table_cache variable is an old one.

Some suggestions:

With regard "too many connections", try the following:
Set max_connections to some reasonable value, like 300, throwing around a number.
Set open_files_limit to a very high values, like 8192. By default you are only allowed 1024 open files on linux. Sockets, threads, table descriptors -- all map to files.
Do you have some connection pool which holds many open (idle) connections, with some keepalive?
How many processes do you get on a typical SHOW PROCESSLIST?
With regard table cache: I suggest set a high value. With 3,000 queries per hour you're not remotely near the problem of having a problem with that. Why don't you set:
table_open_cache=2048
table_definition_cache=512 (even though you may only have a couple tables)
With regard the advice about open tables, you should be more interested in the number of tables opened per second (or minute, whatever) in any given time period, rather than looking at the overall number of tables opened since the server started to run. So you should monitor your opened_tables status variable every few minutes, and do the math. You would typically want to see less than 1 table opened in a second. Again, I'm throwing numbers around, but just to give you proportion.
The open_tables is nice to know, as well. On good usage it can be very high, and that means your server is actively reading from tables. Fine! As long as the number of opened_tables/sec does not rise too high, this is no worry.
There could be other reasons for "too many connections". Are the queries very long? Such that they just accumulate? Are some background tasks failing to complete? (You do have a wait_timeout=300 which is good; however some PHP job might be issuing new queries every few seconds, failing to close properly?
Noting again: watch over SHOW PROCESSLIST and see what comes up.


Error "the system cannot find the file specified" when opening a large SQL Server file I have generated the script of my whole SQL Server database into a file and it's more than 36 GB. When I try to open it, I get the following error:

The system cannot find the file specified.

At first I intended to create the script in a new window, but every time my SQL instance got crashed. Also I have all the necessary permissions and I even tried opening it in the server, but I keep getting this error.
The server's RAM is 16 GB.
My initial goal is to change the whole database's collation (including every field and every table) and since it's a time-consuming process, I decided to generate the script of the database in order to create a new one with the right collation. I couldn't create a new window in the "Generate Script" process; therefore I had to create a new file in order to generate the script. The file is 36 GB.
When trying to open the file, I just get the mentioned error and SQL doesn't crash.

If no code page conversions are necessary (i.e. either not changing code pages or changing code pages but no characters being used are different between the code pages; and this only pertains VARCHAR data), then there is an undocumented option that might be much quicker, both in terms of time spent working out the solution, and time spent moving data. In fact, this undocumented approach is incredibly fast because it does not move any data at all. It merely updates the internal collation_id of each string column in the metadata (for system tables that should be updated and all user tables). The only real time spent is in the rebuilding of all affected indexes. But there is no data export and import. And it bypasses all of the restrictions that prevent updating a databases collation (in many, but not all, cases). Plus it changes the system DBs and the instance-level collation.
The major issue is that by changing the collation_id, any existing VARCHAR characters in the range of 128 - 255 might be misinterpreted in the new collation. However, this is a non-issue if the code page isn't changing. Also, there are two areas that are currently not updated by this approach, though I have requested that they be added, so please vote for this suggestion: Update Collation of Partition Functions and User-Defined Table Types via SQLSERVR -Q.
The method I speak of is running sqlservr.exe with the -q switch. You need to stop the SQL Server engine service, and run that command passing in the new collation and a couple of specific trace flags. I have a detailed description of this approach in the following post (please be sure to read all of the details about what this method does and does not do):
Changing the Collation of the SQL Server Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?


Difference between database vs user vs schema I'm really confused with the terms database, user and schema. Can anyone explain how they are different from each other (if they are)?

If they are same then, what are the similarities between them? How do we use them? And how do we create them?

In Oracle, users and schemas are essentially the same thing. You can consider that a user is the account you use to connect to a database, and a schema is the set of objects (tables, views, etc.) that belong to that account.

See this post on Stack Overflow: difference between a User and a Schema in Oracle? for more details and extra links.

You create users with the create user statement. This also "creates" the schema (initially empty) - you cannot create a schema as such, it is tied to the user. Once the user is created, an administrator can grant privileges to the user, which will enable it to create tables, execute select queries, insert, and everything else.

The database is the thing that contains all the users you've created, and their data (and a bunch of predefined system users, tables, views, etc. that make the whole thing work). You should look at the Oracle Database Architecture documentation in the Concepts Guide (actually, that whole page is worth a read - there's a section about users and schemas higher up in that page) to get an introduction to what a database is, and what a database instance is - two important concepts.

You can create a database with the create database statement, once you've installed the Oracle software stack. But using dbca (database creation assistant) is easier to get started.


Database Growth in a table - MySQL I would like to store the database size for a particular database on daily basis on to a table which would help in understand the growth of the database over a period of time.

SELECT table_schema AS "Database", ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" FROM information_schema.TABLES where table_schema = 'testdb' GROUP BY table_schema;

I get the output for the above query say 10456.3.

I need to insert this number on to a table which should contain three columns.
DB name, Date and DB size.

How can I do this on daily basis by automating this?

If you're storing it into the same database you're measuring you could use a MySQL Scheduled Event to run the query at a certain time.

an example being:

DELIMITER $$
CREATE
    EVENT IF NOT EXISTS db_measurement
    ON SCHEDULE EVERY 1 DAY
    STARTS '2016-11-15 00:00:00'
    ENABLE
    DO
    INSERT INTO tableName
    SELECT
        table_schema AS "Database",
        ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
    FROM information_schema.TABLES
    where table_schema = 'testdb'
    GROUP BY table_schema$$
DELIMITER ;

If it's a different database you'll need to use a shell script of some sort.

Bearing in mind I'm far from an expert on Linux (and ignoring the discussions around permissions, passwords in scripts, SSL etc etc), you could do something along the lines of:

mysql -hxxxx -Pxxxx -uxxxx -pxxxx -se"SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS Size FROM information_schema.TABLES where table_schema = 'dbtest' GROUP BY table_schema;" | { read dbSizeVal; mysql -hxxxx -Pxxxx -uxxxx -pxxxx -e"INSERT INTO dbname.tablename VALUES(${dbSizeVal})"}

Which should get the size value from the remote database, and load it into a table in the local database.

I just used:

CREATE TABLE `tableName` (
    `dbsize` varchar(45) NOT NULL
);

for testing this, but you would probably want a decimal, integer, float datatype depending on the data you return and how accurate you need it.

Also this doesn't include the dbname or time fields. You could use timeVal TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP which would give you the timestamp field.

The database name is tricky, as you would need to carry two variables across, and my script (above) only allows for one (if you are only looking at one database you could just type it directly into the query / set a default value).


How to restore mysql DB from only myi files? Our server's hdds crashed and we didn't have a current backup, so we sent our hdds to a data recovery company. They have been working on the hdds for over a week and they have finally managed to save some files from the hdds.

My problem is the saved files are only myi files and they are named numerically, so I don't know which one belongs to which mysql table. Is it possible to recreate myd and frm files from a myi file? Or is there anything else I can do to get current data from myi?

No, it's not possible - you need the MYD file, too.  The MYI file is only the indexes and does not contain any data.  It is like trying to restore the contents of the book High Performance MySQL from only the index pages.


Restore database with zero size log file SQL Server backups contain meta-data about the size of the original log (LDF) file.

So if you backup a database that has a 5GB LDF file, when you restore it, you will get an (empty) 5GB LDF file. This often isn't needed (because the log file of the restored database contains practically nothing)

Often this causes problems with database restores on busy dev servers with not much space left if people *cough* haven't been too careful about shrinking log files before making the backup.

Is there any way to restore a SQL Server backup but tell it "Dont bother making that empty 5GB LDF file, thanks" ?

No, there is no way. Restores really is meant to recreate the original database that was backed up. There are tools that allow you to query directly a backup w/o actually restoring it (Idera's SQL Virtual Database) but those are horrendously slow.


Selecting first N rows for each group Assume I have a table like this:

+----+-----------+-----------+
| id | file_name | file_type |
+----+-----------+-----------+
|  1 | file1     | 1         |
|  2 | file2     | 1         |
|  3 | file3     | 1         |
|  4 | file4     | 2         |
|  5 | file5     | 2         |
|  6 | file6     | 2         |
|  7 | file7     | 3         |
|  8 | file8     | 3         |
|  9 | file9     | 3         |
+----+-----------+-----------+

I'm trying to get 2 rows for each file_type, so I'm following this hints:

https://blog.sqlauthority.com/2014/03/08/mysql-generating-row-number-for-each-row-using-variable/
http://www.sqlines.com/mysql/how-to/get_top_n_each_group

I'm trying the following query:

SELECT file_name, file_type,
  @type_number := IF(@current_type = file_type, @type_number + 1, 1) AS type_number,
  @current_type := file_type
FROM test
ORDER BY file_type

But I'm getting the following results:

+-----------+-----------+-------------+----------------------------+
| file_name | file_type | type_number | @current_type := file_type |
+-----------+-----------+-------------+----------------------------+
| file1     | 1         |           1 | 1                          |
| file2     | 1         |           1 | 1                          |
| file3     | 1         |           1 | 1                          |
| file4     | 2         |           1 | 2                          |
| file5     | 2         |           1 | 2                          |
| file6     | 2         |           1 | 2                          |
| file7     | 3         |           1 | 3                          |
| file8     | 3         |           1 | 3                          |
| file9     | 3         |           1 | 3                          |
+-----------+-----------+-------------+----------------------------+

As you can see the type_number column should have values from 1 to 3, but it is 1 on every rows.

Where I am wrong?

It's a little bit more complicated, you must assign each variable @type_number and @file_type again.

SELECT file_type, file_name, id
FROM (
      SELECT  @type_number := IF(@file_type = file_type, @type_number + 1, 1) AS type_number,
              @file_type := file_type as file_type,
              file_name, id
      FROM
          (SELECT @type_number := 1) x,
          (SELECT id, file_name, @file_type := file_type as file_type FROM ttest ORDER BY file_type) y
    ) z
WHERE type_number <= 2;

Check it here: http://rextester.com/IUPWT52140


Starting up sql server after hard-drive recovery So I'm in a bit of a pickle. My primary work computer has recently crashed and some of the windows files were corrupted I tried repairing but did not work. This computer was running a program that stores all my customer information and invoice data(so pretty critical). This application had a Microsoft SQL server back end. I managed to recover the data and put it on a new computer, I can launch the application but it wont work since SQL server is not running.

When ever I try to start up SQL I get the following error "SQL Server could not fine the default instance(MSSQLSERVER)" which makes sense since its not actuality installed on this new computer. Is there anyway I would be able to transfer the data over to a new instance of SQL server if I cant start up the original SQL server ?

Asking the company that made the application is not possible since they went out of business a while back.

You can try attaching the MDF file to the new instance, but there is no guarantee that it will work. If the machine crashed while SQL Server was running, the files may actually be corrupt. If you don't have proper backups, you can try:

CREATE DATABASE tms ON
(
  name = tms_Data,
  filename = 'e:\path\tms_data.mdf' -- <-- move this file to the data location on new box
)
FOR ATTACH_REBUILD_LOG;

If that doesn't work, this will probably be a very expensive lesson about why we want to take backups regularly and store them in a different location.


Extremely slow MyISAM slave updates I have 2 slaves where one is working as expected, but the other slave is extremely slow when executing update statements. The insert and delete statements are fast.

I used the command check table tbl_name to see if any MyISAM table was corrupt. Both the slaves are MyISAM only.

I am using (AWS ec2 instance) ext3 for the fast slave and xfs for the the other. Is that the reason why the updates are taking time?

You can compare both slaves performance using Percona utilities mentioned below:

pt-heartbeat measures replication lag on a MySQL server. You can use it to update a master or monitor a replica. If possible, MySQL connection options are read from your .my.cnf file.
pt-table-checksum performs an online replication consistency check by executing checksum queries on the master, which produces different results on replicas that are inconsistent with the master.


How to select top 3 vendors without duplicate entries? Here is my tables

I want to select Top 3 lowest quoted rate vendors without duplicate Entry. Expected output will be V1, V4, V3 without using while loop. is this possible?

Table details

CREATE TABLE #Header(id int identity,HNo varchar(10),vendor varchar(50))
insert into #Header values('H1','V1')
insert into #Header values('H2','V2')
insert into #Header values('H3','V3')
insert into #Header values('H4','V4')
insert into #Header values('H5','V1')
insert into #Header values('H6','V6')
insert into #Header values('H7','V4')

CREATE TABLE #Detail(id int identity,DNo varchar(10),RATE MONEY )
insert into #Detail values('H1', 10)
insert into #Detail values('H2', 40)
insert into #Detail values('H3', 30)
insert into #Detail values('H4', 20)
insert into #Detail values('H5', 20)
insert into #Detail values('H6', 60)
insert into #Detail values('H7', 70)

SELECT * FROM #Header
SELECT * FROM #Detail

I have the result like below but vendors are duplicated, i can achieve using while loop and check eveytime vendor exist or not. is this any other way?

SELECT top 3 RATE,vendor FROM #Header,#Detail
where DNo = HNo
order by RATE

SELECT TOP(3) --WITH TIES
    h.vendor
    ,MIN(d.Rate) as Rate
FROM #Header as h
    inner join #Detail as d
        on  H.HNo = D.DNo
GROUP BY h.vendor
order by RATE

or option 2

SELECT TOP(3)
    a.vendor
    ,a.RATE
FROM
(
    SELECT
        h.vendor
        ,d.RATE
        ,ROW_NUMBER() OVER(PARTITION BY h.Vendor ORDER BY d.Rate ASC) as RN
    FROM #Header as h
        inner join #Detail as d
            on  H.HNo = D.DNo
)a
WHERE a.rn = 1
ORDER BY a.RATE ASC

output:

vendor  RATE
V1  10.00
V4  20.00
V3  30.00


Truncate text encoding safe What would be the most efficient way, within Postgres, to truncate a text field to a maximum number of bytes, in such a way that the encoding is preserved?
I.e. how to keep at most N bytes while keeping a valid UTF8 string representation.

E.g. assuming UTF8, if a field contains abc€, that's 4 characters for 5 bytes, if I want to truncate this field to at most 4 bytes, but keep a valid UTF8 string, I'd need to actually keep only the first 3 bytes.

I feel like I need the following, but instead of 'escape', I'd like to ignore the invalid bytes to only keep abc:

select encode(substring('abc€'::bytea, 0, 5), 'escape');
 encode
---------
 abc\342

What's the best way to achieve this?

Use this function:

CREATE OR REPLACE FUNCTION get_prefix (
   string text,
   max_bytes bigint
) RETURNS text
   LANGUAGE sql STRICT AS
$$SELECT p
FROM (SELECT p.p, octet_length(p.p) AS len
      FROM generate_series(0, length($1)) AS len
         CROSS JOIN LATERAL substr($1, 1, len.len) AS p) AS q
WHERE len <= $2
ORDER BY len DESC
LIMIT 1$$;

It works like this:

SELECT get_prefix('abc€', 4);
 get_prefix
------------
 abc
(1 row)


Additional space required for INDEX REORGANIZE MSDN states:

  ALTER INDEX REORGANIZE; however, log space is required.

The database recovery mode is Simple, does that implies the log space is actually NOT required ?.

To answer more concisely than the existing answers:

REORGANIZE produces the same amount of log with SIMPLE and FULL. It's just that SIMPLE almost immediately makes that space available again.

Except if something is preventing log truncation such as an open transaction (unrelated to the session the REORGANIZE runs in).


What are the factors to consider or to worry when we migrate to SQL Server 2017 from 2012 We have been asked to migrate to SQL Server 2017 from 2012. Only coding experience as background we are not so aware of what are the crucial points to consider or to worry when migration.

Some of the points I'm concerned are:

Is there is anything missing in 2017 which was working fine in 2012.
Missing in the sense of an upgrade in 2017.
Can we restore a backup
from 2012 in 2017
Also is there is any chance of any kind of data truncation or something like that if we restore DB backup from 2017 into 2012 (If restoration possible)

https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2017 as a list of deprecated features for 2017.
2012 to 2017 yes. 2017 to 2012 no.
No, because you can't restore from 2017 to 2012.

The main thing to look at with your current database is what Compatibility Level is it set to in the 2012 instance? If it's below 110, and needs to stay below 110, then it won't function correctly, or at all.

The only way you'll be able to definitively determine what won't work is install a copy of SQL Server 2017 somewhere (with the appropriate licensing), restore your database to that new instance, and then start testing your application connecting to that new database.


Trying to restore SQL file from mysqldump, results in syntax error I ran the following sqldump call in a MySQL 5.7 instance (Windows OS):

mysqldump --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=1 --routines -A > ~/dump.sql

I copied the result in a MySQL 5.7 instance (Linux OS), and executed it via:

mysql -uroot -pMyPwd < dump.sql

But I got the following error:

  ERROR 1064 (42000) at line 813: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '917,0.31000000,267.21000000,'2015-07-08 17:44:39',88758,'BK1','BK1',49208,4921' at line 1

There are some characters in the error message that I haven't been able to copy, so I paste an image here:

So I open the (big) file in nano, and I find the fragment which has this weird characters:

...(49171,0.87000000,267.28000000,'2015-07-08 17:42:48',88671,'BK1','BK1',49167,49170,63,64,'BK11432145524','BK11432145533','xxxxxx',0,0.00000000,0.00000000,0.00000000,0.00000000),(^T9^R17,0.31000000,267.21000000,'2015-07-08 17:44:39',88758,'BK1','BK1',49208,49216,63,64,'BK11432145637','BK11432145661','xxxxxx',0,0.00000000,0.00^P00000,0.0000000^P,0.00000000),(4^Y^R^W6,0.19000000,267.14000000,'2015-07-08 17:47:02',88875,'BK1','BK1'...

As you can see, it seems the first value of a row seems to have a weird element ^T9^R17 instead of a normal integer like the row before (49171).

Tried to run dos2unix on the file as recommended by @Verace, and this is the result:

$ dos2unix dump.sql
dos2unix: Binary symbol 0x14 found at line 813
dos2unix: Skipping binary file dump.sql

Why is this happening? The type of the first column is bigint(20) NOT NULL.

I got 2 comments that hinted me that:

This problem might not be reproducible again if I do a second mysqldump.
The copy of the file from one place to another might have corrupted the file.

So then to ensure (1) was not the case, I tried to mysqldump again, and to make sure (2) didn't happen, I compiled the file before transferring it to another computer (so that if it gets corrupted, at least I would know when trying to decompress it).

The result was: success. It works now. So I suspect it was a glitch in the matrix.
Thanks for all the people that helped.


Point in time restore MySQL I want to perform a point in time restore on MySQL.

I want to change log_bin value and ensure that log files are created.

I am following this article and am working on a Windows platform.

Log files are not created in the directory I specified? What is wrong?

I figured it out. Location on Windows for my.ini C:\ProgramData\MySQL\MySQL Server 5.6\my.ini


ORA-00942: table or view does not exist- Can it be solved by granting privileges to user? I am creating a Java application that connects to an Oracle Database.

In the application I connect to the DB as User A, which works as intended.

I then try to access tables in Schema 1 and Schema 2.

However when I do so I get the error:

ORA-00942: table or view does not exist

I looked in SQLDeveloper and in the "other users" section of User A I cannot see any tables for Schemas 1 and 2.

How can I solve this error so that I can see and access tables in Schemas 1 and 2?

Can I do this by assigning GRANT SELECT privileges on User A for schemas 1 and 2?

Take a look at what Oracle says about this type of error.

  ORA-00942 table or view does not exist

  Cause: The table or view specified does not exist, a synonym that is not allowed here was used, or a view was referenced where a table is required. If you are trying to access tables or views of another schema then you might not have required privileges to do it.
  Action: You can-
      check the spelling of the table or view name.
      check that an existing table or view name exists.
      contact the DBA if the table needs to be created or if user or application privileges are required to access the table.

The following is the example to address your situation. It shows how grant SELECT object privilege and how to create synonym.

SQL> create user userA identified by userA;

User created.

SQL> grant connect, resource to usera;

Grant succeeded.

SQL> conn userA/userA
Connected.
SQL> create table tab (id number);

Table created.

SQL> conn / as sysdba
Connected.
SQL> create user schema1 identified by schema1;

User created.

SQL> grant connect, resource to schema1;

Grant succeeded.

SQL> conn schema1/schema1
Connected.
SQL> create table tab1(id number);

Table created.

SQL> select * from usera.tab;
select * from usera.tab
                    *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> conn usera/userA
Connected.
SQL> grant select on tab to schema1;

Grant succeeded.

SQL> conn schema1/schema1
Connected.
SQL> select * from usera.tab;

no rows selected

SQL> create synonym for usera.tab;
create synonym for usera.tab
               *
ERROR at line 1:
ORA-00995: missing or invalid synonym identifier

SQL> create synonym usera_tab for usera.tab;
create synonym usera_tab for usera.tab
*
ERROR at line 1:
ORA-01031: insufficient privileges

SQL> conn / as sysdba
Connected.

SQL> grant create synonym to schema1;

Grant succeeded.

SQL> conn schema1/schema1
Connected.
SQL> create synonym usera_tab for usera.tab;

Synonym created.

SQL> select * from usera_tab;

no rows selected

SQL>


Does the order of fields in SELECT query matter when using composite indexing? I understand that the order of columns in the index itself matters immensely; however, what about the order of columns in the subsequent SELECT queries that make use of that index?

For example, if I have a multi-column index on [:col_1, :col_2, :col_3], does my SELECT statement need to look like "SELECT * FROM my_table WHERE col_1 = (?), col_2 = (?), col_3 = (?)" in order for the query to be optimized? Or can I specify the params in any order and the query optimizer will handle it?

I've read a few similar answers, but there didn't seem to be a definitive answer that pointed to documentation, and answered relative to indexes. Some answers say "it shouldn't matter" or "effect is negligible".

I'm working with RoR/ActiveRecord on PostgreSQL, but the question is really for any SQL/relational DB.

No, you can specify the 'params' (the parts of the where clause) in any order and the query optimizer will handle it. The optimizer will do the filtering in the order that it estimates is most efficient, but note that this is more complex than just choosing which order to filter: filtering might be done before or after joining for example.

You can't exactly prove this, but you can demonstrate it is true for a particular query by experimenting and seeing if the plan changes. It may even be true that there are edge cases where the order does matter, but my advice would be to ignore the possibility and assume it never happens as otherwise you will expend a lot of effort trying different permutations. Much better to focus on the kind of tuning which you know can pay dividends (eg correct indexing).


Setting the SMTP_OUT_PARAMETER in 10G at runtime I am writing a procedure that will send mail from oracle 10g. I have installed the UTL_MAIL and UTL_SMTP packages and configured the SMTP_OUT_SERVER parameter. However the requirement is that the SMTP server address be chosen at runtime. How do I resolve the issue.??

Looking at the docs, you cannot selectively choose from multiple SMTP_OUT_SERVER values. Here's how that parameter works:
http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams230.htm#REFRN10241

SMTP_OUT_SERVER specifies the SMTP host and port to which UTL_MAIL delivers out-bound E-mail. Multiple servers may be specified, separated by commas.
If the first server in the list is unavailable, then UTL_MAIL tries the second server, and so on.

It looks like you will need to manage this requirement externally.


xtrabackup backup grants? I'm using MySQL 5.5 + Opensuse 12.3 .
After face an issue with mysqldump (appear be a bug with OpenSuse Mysql distribution) I have looking for others backup solution.

I found the xtrabackup from percona which is very nice solution .
I tested it with successfully to backup and restore .

But, it don't appear to be complete as I expected , they don't backup the users/grants and the configuration file (my.cnf)...
With mysqlpdump I user this shell script:

mygrants()
{
  mysql -B -N $@ -e "SELECT DISTINCT CONCAT(
    'SHOW GRANTS FOR ''', user, '''@''', host, ''';'
    ) AS query FROM mysql.user" | \
  mysql $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
}

and with xtrabackup I believe I will keep using this "complement" .

Looking at xtrabackup documentation, I found nothing about this subject.

What I need to know:

There is some way to include this (grant and my.cnf) into xtrabackup automatically?
If not, running my script to get the grants and copy of my.cnf is enougth as backup from my database? or I missing something here?

Your backup solution for MySQL User Grants is fine.

I have been advocating doing that for years:

Mar 24, 2013 : MySQL export user with semi-colon ";" on the end
Dec 24, 2012 : Backup and restore "mysql" database
Jun 13, 2012 : Fastest way to move a database from one server to another
Apr 12, 2012 : Cannot GRANT privileges as root
Apr 09, 2012 : Mysql users deleted
Jun 26, 2011 : Restoring an old backup to latest MySQL release

Also, please notice that Percona created pt-show-grants and xtrabackup. It is curiously funny that pt-show-grants has not been integrated into xtrabackup already (Hey I could be wrong. After all, a physical backup of the mysql schema should be very adequate for a Backup of Grants). Perhaps, one of the following is the reason:

Business Decision to keep Text version of Grants separate from the Backup
Potential Security Reasons (should anyone get a hold of the Backup)
No one ever thought of it before

Going back to you original question, copies of the User Grants in SQL and the my.cnf are sufficient.


When and how would MyISAM concurrent insert = always be a problem? I wish to set concurrent_insert to always on a MyISAM log table, so that inserts wouldn't have to wait for selects to perform when there are some holes in the table.

We're talking about 10-20 inserts per second and quite rare but yet some selects on the log that could take around 10s. I don't want my 100-200 inserts wait for these selects.

My question is quite simple actually. Taken aside the hole not-always-being-filled concern, when and how would it do any harm ?

Nota Bene: I wouldn't use DELAYED INSERT as they are of the INSERT ... ON DUPLICATE KEY UPDATE form.

The only immediate harm I can think of is dealing with contention from the UPDATE.

Normal INSERT, UPDATE, and DELETE commands execute a full table lock. Doing ON DUPLICATE KEY UPDATE should work the same.

In addition, what if the row's size changes to the point that the row needs to be bigger that the row's original allocation because of increasing the length of a VARCHAR column? That would require writing the row to a new part of the MyISAM table.

The only exception to this concern is when the MyISAM table

has no TEXT or VARCHAR fields
has no TEXT fields, has VARCHAR fields but ROW_FORMAT=Fixed

The MyISAM table would probably have some contention issues if you have TEXT fields because of the lack of length control or possible overflow of data to handle no matter what the table's ROW_FORMAT is set to.


statement to find at least a year with register users > 1 i need to find find the years for which have at least 2 users register.

the table is

CREATE TABLE IF NOT EXISTS `rotef`.`users` (
  `id` INT(11) NULL,
  `name` VARCHAR(100) NULL,
  `year` INT(11) NULL,
  PRIMARY KEY (`id`))
ENGINE = InnoDB;

and the data

INSERT INTO `rotef`.`users` (`id`, `name`,`year`) VALUES (1,"john", 2016);
INSERT INTO `rotef`.`users` (`id`, `name`,`year`) VALUES (2,"jim", 2016);
INSERT INTO `rotef`.`users` (`id`, `name`,`year`) VALUES (3,"eric", 2014);
INSERT INTO `rotef`.`users` (`id`, `name`,`year`) VALUES (4,"jesi", 2012);
INSERT INTO `rotef`.`users` (`id`, `name`,`year`) VALUES (5,"jef", 2011);

the statement based on the above data must return 2016

You need to GROUP BY year, and use a HAVING clause to further limit only the grups where the count or rows (count(*)) is greater than 1:

SELECT
    year
FROM
    users
GROUP BY
    year
HAVING
    count(*) > 1
ORDER BY
    year ;

You get:

| year |
|------|
| 2016 |

You can check it at SQLFiddle


Name for table with metric_name and metric_value columns A typical database table might look something like this:

date-------geography---clicks---cost---conversions
___________________________________________________
1/1/2010---Kansas------56-------12-----1

When transposed, it looks like this.

date-----geography---metric_name---metric_value
____________________________________________
1/1/2010-Kansas------clicks--------56
1/1/2010-Kansas------cost----------12
1/1/2010-Kansas------conversions---1

The transposed table works better for some use cases where the number of metrics being collected changes frequently.

Is there a standard name for a table structured like that in the transposed table?

Unpivot Table

Being the product of a transformation, your desired form here is called an "Unpivot table". The form you're starting from here is either normalized data, or the result of a transformation itself called a "pivot."

You can pivot and unpivot without losing data. Pivoting and operations like this emerge from OLAP functionality, where they're rotations. In PostgreSQL we pivot with the tablefunc module. We can easily unpivot with a CROSS JOIN LATERAL on a VALUES list,

SELECT id, cat, value
FROM ( VALUES (1,1,2,3), (2,10,20,30) ) AS t1(id,x,y,z)
CROSS JOIN LATERAL ( VALUES ('X',x), ('Y',y), ('Z',z) )
  AS t2(cat, value);

Or, though slower, we use the unnest(ARRAY[]) idiom and parallel unnest

SELECT id, cat, value
FROM ( VALUES (1,1,2,3), (2,10,20,30) ) AS t1(id,x,y,z)
CROSS JOIN LATERAL unnest(ARRAY['X','Y','Z'], ARRAY[x,y,z])
  AS t2(cat, value);

Either way you'll produce,

 id | cat | value
----+-----+-------
  1 | X   |     1
  1 | Y   |     2
  1 | Z   |     3
  2 | X   |    10
  2 | Y   |    20
  2 | Z   |    30
(6 rows)

See also,

Unpivoting data in PostgreSQL (older method than used above)
Microsoft Technet docs
Oracle Pivot and Unpivot article


Simple use of IF inside procedure I'm creating a simple query, a simple select like select x,y,z from etc etc but in the software, there are start date and end date.

If the user fills in only the first date, then it will use a where clause like this:

where....
and...
and x.date >= Startdate

If the user fills in both dates, it will use:

where....
    and...
    and x.date between @Startdate and @enddate

If the user fills in only end date:

 where....
    and...
    and x.date < @enddate

I'm getting a syntax error with everything I'm trying to do.

 where....
        and...
        case when @startdate is not null and @enddate is null
           and x.date >= Startdate

Or even using if. What is the correct syntax for this? Or this is something programmers need to fix inside the software's code?

Try like this

WHERE....
AND...
AND (x.DATE>= @Startdate OR @Startdate IS NULL)
AND (x.DATE<= @enddate OR @enddate IS NULL)

Possibly with OPTION (RECOMPILE).

See Dynamic Search Conditions in T‑SQL by Erland Sommarskog.


A large percentage of unused space in the table SQL Server 2005.

I have a table size 16GB.  Table has 14 GB of free space.

sp_spaceused 'FREECLIENTDOC'

name            rows   reserved      data          index_size  unused
--------------- ------ ------------- ------------- ----------- ------------
FREECLIENTDOC   72407  16168232 KB   1742544 KB    30696 KB    14394992 KB

Indexes do not take up much space:

DBCC SHOWCONTIG scanning 'FREECLIENTDOC' table...
Table: 'FREECLIENTDOC' (659533433); index ID: 1, database ID: 7
TABLE level scan performed.
- Pages Scanned................................: 3508
- Extents Scanned..............................: 457
- Extent Switches..............................: 2555
- Avg. Pages per Extent........................: 7.7
- Scan Density [Best Count:Actual Count].......: 17.18% [439:2556]
- Logical Scan Fragmentation ..................: 71.18%
- Extent Scan Fragmentation ...................: 69.80%
- Avg. Bytes Free per Page.....................: 1981.6
- Avg. Page Density (full).....................: 75.52%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Perhaps the problem in the fields of image types.
How to determine what is in an unused space?
I'm interested in the general case definition table space.
This table - a example.

If you have deleted a lot of data, this isn't recovered until you rebuild.

ALTER INDEX PK_PrimaryKeyName ON dbo.FREECLIENTDOC REBUILD;

Please see all the options in the documentation before just running the default.

Also, IMAGE is deprecated - if you are storing files in your database tables, you should look into VARBINARY(MAX) - on newer versions of SQL Server you can look at FILESTREAM or FILETABLE.


Find the second highest salary of employees Imagine there is a table with these fields:

Employee: E-Number
          E-Name
          Department
          Salary

We need to write a query in order to find the second highest salary in this table.

I found this query :

select distinct salary
from Employee e1
where 2 = ( select count(distinct salary)
            from Employee e2
            where e1.salary < e2.salary)

I'm a bit confused by this part where 2 = because I have not seen something like that before.

Can anyone here explain more about the query and the part where 2 =? What is the result of the subquery and in what way are table rows (e1, e2) being compared with each other?

Actually this is the execution plan can anyone explain the steps more?

I'm going to address this part of the question:

  ...this is the execution plan can anyone explain the steps more?

Assuming the table definition looks something like this:

CREATE TABLE dbo.Employee
(
    [E-Number]  integer NOT NULL,
    [E-Name]    nvarchar(50) NOT NULL,
    Department  nvarchar(30) NOT NULL,
    Salary      smallmoney NOT NULL
);

Empty table

The following query (not my recommendation, just an example):

SELECT DISTINCT
    E1.Salary
FROM dbo.Employee AS E1
WHERE
    1 =
    (
        SELECT
            COUNT(DISTINCT E2.Salary)
        FROM dbo.Employee AS E2
        WHERE
            E2.Salary > E1.Salary
    );

Gives this execution plan (with no data in the table):

The numbers correspond to the Node ID property of each operator.

The E1 Table Scan (4) returns all rows (in no particular order). For each row:

The Nested Loops join (3) executes the inner side, passing the current E1.Salary value as a parameter.
The E2 Table Scan (8) finds rows matching E2.Salary > E1.Salary
The Distinct Sort (7) groups rows by E2.Salary.
The Stream Aggregate (6) counts the number of rows (1 per distinct E2.Salary). The new count column is given the label Expr1008.
The Compute Scalar (5) converts the native count data type (bigint) to integer because the query specifies COUNT rather than COUNT_BIG. The new column is given the label Expr1004.

As rows emerge from the Nested Loops join, the Filter (2) applies the predicate Expr1004 = 1
The final Distinct Sort returns the distinct E1.Salary values.

With duplicate Salary values

If we add a few rows to the table, with some duplicate Salary values:

INSERT dbo.Employee
    ([E-Number], [E-Name], Department, Salary)
VALUES
    (1, 'A', 'D1', $1),
    (2, 'B', 'D1', $1),
    (3, 'C', 'D1', $2),
    (4, 'D', 'D1', $2),
    (5, 'E', 'D1', $3),
    (6, 'F', 'D1', $3);

The query above produces a plan with a couple of new operators (highlighted):

The new Sort orders rows by E1.Salary. The ensures that any duplicate Salary values are presented to the join sequentially.

The Lazy Index Spool saves the output from the operators below it as they arrive. These are the Stream Aggregate, Distinct Sort, and E2 Table Scan (with predicate), which as described before filter E2 (using the current value of E1.Salary), remove duplicate E2.Salary values, and count the resulting rows. The Lazy Index Spool saves recomputing the result in case the same Salary value from table E1 is presented on the next iteration of the Nested Loops join.

Because the Sort ordered rows from E1 by E1.Salary, any duplicates are presented to the join one after the other, so the saved result in the spool can be reused instead of re-running the Stream Aggregate, Distinct Sort, and E2 Table Scan each time.

The index on the spool is keyed by Salary, making it quick to find the saved COUNT(DISTINCT E2.Salary) result for the current E1.Salary value.

If you look at the Actual Executions value for each operator in the plan, you will see that the Lazy Index Spool executes 6 times (once per outer row), whereas the Stream Aggregate, Distinct Sort, and E2 Table Scan execute only 3 times (once per distinct salary value).

The optimizer introduces the extra Sort and Spool as a performance optimization, based on automatically collected statistical information about the data in the table.