主页 / user-5121

shantanuo's questions

Martin Hope
shantanuo
Asked: 2012-07-21 20:59:33 +0800 CST
  • 3

以下 2 个查询按预期工作并显示计数。是否可以将它们合并为单个查询？我需要 sum_hits 列作为第二个查询中显示的其余列的一部分。

SELECT date_time, SUM(diac.requests) AS sum_hits
FROM ox_data_summary_ad_hourly diac
JOIN ox_zones z ON (z.zoneid = diac.zone_id)
JOIN ox_affiliates p ON (p.affiliateid = z.affiliateid AND p.agencyid = '3')
WHERE diac.date_time BETWEEN '2012-07-01 00:00:00' AND '2012-07-01 23:59:59'
AND p.agencyid = '3'
AND z.zone_type in ('application')
AND z.delivery in (8)
GROUP BY date_time

SELECT s.date_time AS date_time,
SUM(s.requests) AS sum_requests,
SUM(s.impressions) AS sum_views,
SUM(s.clicks) AS sum_clicks,
SUM(s.conversions) AS sum_conversions,
SUM(if(d.network_type like '%_user' and d.is_network=1,impressions,0)) AS sum_effimp,
SUM(s.total_revenue) AS sum_revenue,
SUM(s.total_revenue) AS sum_spend,
SUM(s.total_basket_value) AS sum_bv,
SUM(s.total_num_items) AS sum_num_items,
SUM(s.total_pubrevenue) AS sum_pubrevenue
FROM ox_data_summary_ad_hourly AS s
INNER JOIN ox_banners AS d ON (d.bannerid=s.ad_id)
INNER JOIN ox_campaigns AS m ON (m.campaignid=d.campaignid)
INNER JOIN ox_clients AS a ON (a.clientid=m.clientid)
LEFT JOIN ox_zones AS z ON (z.zoneid=s.zone_id)
LEFT JOIN ox_affiliates AS p ON (p.affiliateid=z.affiliateid)
WHERE a.agencyid = 3
AND z.zone_type = 'application'
AND z.delivery = 8
AND s.date_time>='2012-07-01 00:00:00' AND s.date_time<='2012-07-01 23:59:59'
GROUP BY date_time
mysql subquery
Martin Hope
shantanuo
Asked: 2012-07-13 21:47:34 +0800 CST
  • 1

我有一个 19GB 的存档表，它在一台服务器上按预期工作。我将以下 2 个文件复制到另一台服务器。该表在新服务器上不工作。

r_mi_raw_20120712.ARZ
r_mi_raw_20120712.frm

第一个服务器版本是 Remi 的 5.5.22-log MySQL Community Server (GPL) 第二个服务器版本是：5.5.20 MySQL Community Server (GPL)

可能是什么原因？当我使用 mysqlshow db_name 时，它​​会列出表，但是当我使用 --count 时，我没有得到计数。相反，我得到命令字段列表的“打开表”状态。

更新：

mysql 似乎正在写入 ARN 文件...

r_mi_raw_20120712.ARN

这是一次性优化吗？如何提高 ARN 文件的创建速度？

mysql
Martin Hope
shantanuo
Asked: 2012-07-11 02:36:07 +0800 CST
  • 1

当我在 master 上使用“load data infile”时，数据被正确地传输到 slave。但是当我从远程服务器使用“加载数据本地文件”到主服务器时，数据不会传递给从服务器。

为什么slave读取不到远程服务器的数据？

mysql replication
Martin Hope
shantanuo
Asked: 2012-06-07 20:54:00 +0800 CST
  • 2

当我尝试在分区表中插入记录时，出现“打开的文件过多”错误。

mysql> 插入 test.ox_data_geo_span 选择 * from vserv.ox_data_geo_span 限制 100；

错误 1016 (HY000): 无法打开文件: './test/ox_data_geo_span.frm' (errno: 24)

错误 24

操作系统错误代码 24：打开的文件过多

如果我执行“刷新表”，那么我可以将数据插入到其他表中。但我需要一种方法一次插入许多表。

mysql partitioning
Martin Hope
shantanuo
Asked: 2012-05-27 06:50:47 +0800 CST
  • 1

由于主键错误，从站无法正常工作，如下所示：

      Last_SQL_Errno: 1062
       Last_SQL_Error: Error 'Duplicate entry '550696860' for key 'PRIMARY'' on query.
 Default database: 'vserv'. 
 Query: 
  'INSERT INTO ox_data_intermediate_ad
        (date_time, ad_id, zone_id, clicks, impressions, 
         requests, operation_interval, operation_interval_id, 
         interval_start, interval_end, creative_id, updated)
    SELECT
        date_time, ad_id, zone_id, SUM(clicks) AS clicks, 
        SUM(impressions) AS impressions, SUM(requests) AS requests, 
        5 AS operation_interval, 1871 AS operation_interval_id, 
        '2012-05-26 11:55:00' AS interval_start, 
        '2012-05-26 11:59:59' AS interval_end, 
        0 AS creative_id, '2012-05-26 12:04:01' AS updated
    FROM
        (
        SELECT
            interval_start AS date_time, creative_id AS ad_id, zone_id AS zone_id,
            count AS clicks, 0 AS impressions, 0 AS requests
        FROM
            ox_data_bkt_c
        WHERE
            interval_start >= '2012-05-26 11:55:00'
            AN

提到的查询不会尝试插入自动递增的列 data_intermediate_ad_id。它期望 mysql 自动添加下一个 ID。查看slave的时候发现有一条主键550696860的记录。

Master 是 5.0，slave 是 5.5.24

第二个版本为 5.5.21 的奴隶工作正常。这是与 5.5.24 版本相关的错误吗？

有问题的查询如下：

INSERT INTO
                ox_data_intermediate_ad
                (date_time, ad_id, zone_id, clicks, impressions, requests, operation_interval, operation_interval_id, interval_start, interval_end, creative_id, updated)
            SELECT
                date_time, ad_id, zone_id, SUM(clicks) AS clicks, SUM(impressions) AS impressions, SUM(requests) AS requests, 5 AS operation_interval, 1591 AS operation_interval_id, '2012-05-25 12:35:00' AS interval_start, '2012-05-25 12:39:59' AS interval_end, 0 AS creative_id, '2012-05-25 12:43:01' AS updated
            FROM
                (
                SELECT
                    interval_start AS date_time, creative_id AS ad_id, zone_id AS zone_id, count AS clicks, 0 AS impressions, 0 AS requests
                FROM
                    ox_data_bkt_c
                WHERE
                    interval_start >= '2012-05-25 12:35:00'
                    AND
                    interval_start <= '2012-05-25 12:39:59' UNION ALL 
                SELECT
                    interval_start AS date_time, creative_id AS ad_id, zone_id AS zone_id, 0 AS clicks, count AS impressions, 0 AS requests
                FROM
                    ox_data_bkt_m
                WHERE
                    interval_start >= '2012-05-25 12:35:00'
                    AND
                    interval_start <= '2012-05-25 12:39:59' UNION ALL 
                SELECT
                    interval_start AS date_time, creative_id AS ad_id, zone_id AS zone_id, 0 AS clicks, 0 AS impressions, count AS requests
                FROM
                    ox_data_bkt_r
                WHERE
                    interval_start >= '2012-05-25 12:35:00'
                    AND
                    interval_start <= '2012-05-25 12:39:59'
                ) AS virtual_table
            GROUP BY
                date_time, ad_id, zone_id

我想知道为什么奴隶对insert into ... select声明有疑问。

mysql replication
Martin Hope
shantanuo
Asked: 2012-05-26 19:34:16 +0800 CST
  • 1

我有 2 个奴隶，其中一个按预期工作，但另一个奴隶在执行update语句时非常慢。和语句很快insert。delete

我使用该命令check table tbl_name查看是否有任何 MyISAM 表已损坏。两个奴隶都只是 MyISAM。

我正在使用（AWS ec2 实例）ext3 作为快速从站，而 xfs 作为另一个。这就是更新需要时间的原因吗？

mysql myisam
Martin Hope
shantanuo
Asked: 2012-05-03 19:27:07 +0800 CST
  • 1

我需要编写一个触发器，使一个表与其他 2 个表保持同步。我在奴隶上有 table_a、table_b 和 table_c。主服务器只有 table_a，每当主服务器上更新此表时，从服务器上的其他两个表也应该使用触发器或任何其他方式进行更新。

换句话说，我只需要从这个从站复制一个表。由于没有多一台服务器的预算，我将使用相同的从属架构。我需要在 table_a 上更新、插入和删除触发器后编写吗？最好的方法是什么？

mysql replication
Martin Hope
shantanuo
Asked: 2012-05-03 08:43:21 +0800 CST
  • 1

这是我的表结构：

create table table_a (emp_name varchar(100), dept_name varchar(100));
insert into table_a values ('amar', 'finance');
insert into table_a values ('akbar', 'human_resource');
insert into table_a values ('amar', 'finance');
insert into table_a values ('anthony', 'information_technology');

create table table_b (emp_name varchar(100), dept_name varchar(100));
insert into table_b values ('amar', 'finance');
insert into table_b values ('akbar', 'human_resource');

mysql> select * from table_a as a left join table_b as b on a.emp_name = b.emp_name and a.dept_name = b.dept_name where b.dept_name is null;
+----------+------------------------+----------+-----------+
| emp_name | dept_name              | emp_name | dept_name |
+----------+------------------------+----------+-----------+
| anthony  | information_technology | NULL     | NULL      |
+----------+------------------------+----------+-----------+
1 row in set (0.00 sec)

上面提到的左连接按预期工作。

但是，我还需要一条“amar”记录，因为表 A 中有 2 个条目，而表 B 中只有 1 个条目。所以，结果应该如下所示......

+----------+------------------------+----------+-----------+
| emp_name | dept_name              | emp_name | dept_name |
+----------+------------------------+----------+-----------+
| anthony  | information_technology | NULL     | NULL      |
| amar     | finance                | NULL     | NULL      |
+----------+------------------------+----------+-----------+
2 row in set (0.00 sec)

在计数准确性比数据完整性更重要的环境中，我需要这个。B表记录号（2）+查询结果（2）要与A表记录号（4）匹配

mysql join
Martin Hope
shantanuo
Asked: 2012-03-11 19:16:16 +0800 CST
  • 4

我想找到当前时间之前的五分钟间隔。因此，如果当前时间是 8:43，我想返回 8:35 和 8:40。这是我写的查询，它按预期工作。我想知道有没有更好的方法...

mysql> select now() ;
+---------------------+
| now()               |
+---------------------+
| 2012-03-11 08:43:06 |
+---------------------+
1 row in set (0.00 sec)

mysql> SELECT date_sub(from_unixtime(unix_timestamp(curdate()) + floor(TIME_TO_SEC(now())/300)*300), interval 5 minute) as ten_minute_interval,  from_unixtime(unix_timestamp(curdate()) + floor(TIME_TO_SEC(now())/300)*300) as five_minute_interval;
+---------------------+----------------------+
| ten_minute_interval | five_minute_interval |
+---------------------+----------------------+
| 2012-03-11 08:35:00 | 2012-03-11 08:40:00  |
+---------------------+----------------------+
1 row in set (0.00 sec)


mysql> SELECT some better query...
mysql timestamp
Martin Hope
shantanuo
Asked: 2012-03-10 00:08:43 +0800 CST
  • 4

我在主从上有一个完全相同的 myisam 表。索引未在主服务器上使用。

mysql> explain select count(date_time) from ox_data_summary_ad_hourly where  date_time = '2012-03-08 00:00:00' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ox_data_summary_ad_hourly
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 32212292
        Extra: Using where
1 row in set (0.00 sec)

从站的解释计划：

mysql> explain select count(date_time) from ox_data_summary_ad_hourly where  date_time = '2012-03-08 00:00:00' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ox_data_summary_ad_hourly
         type: ref
possible_keys: ox_data_summary_ad_hourly_date_time
          key: ox_data_summary_ad_hourly_date_time
      key_len: 8
          ref: const
         rows: 156769
        Extra: Using index
1 row in set (0.00 sec)

master 是 5.0，而 slave 是 5.5 版本（如果重要的话）。

这是否意味着 master 上的索引文件已损坏？

mysql index
Martin Hope
shantanuo
Asked: 2012-03-07 22:29:48 +0800 CST
  • 2

以下是按预期工作的创建表语句。但是如果我有一个没有任何分区的表并且如果我尝试添加分区，似乎我不能在其中添加超过 16 个值。

   CREATE TABLE `mypart` (
      `part_id` int(11) DEFAULT NULL
    ) ENGINE=MyISAM 
    PARTITION BY LIST (part_id)
    (
    PARTITION Jan10 VALUES IN (1,2,3,4,5) ENGINE = MyISAM,
    PARTITION Mar10 VALUES IN (734201,734202,734203,734204,734205,734206,734207,734208,734209,734210,734211,734212,734213,734214,734215,734216, 734217) ENGINE = MyISAM
    );

以下所有语句都应成功执行并创建如上所示的表。

drop table mypart;
create table mypart (`part_id` int) ENGINE=MyISAM ;
alter table mypart partition by list(part_id) (PARTITION Jan10 VALUES IN (1, 2, 3, 4, 5));

mysql> alter table mypart add PARTITION (PARTITION Mar10 VALUES IN (734201, 734202, 734203, 734204, 734205, 734206, 734207, 734208, 734209, 734210, 734211, 734212, 734213,  734214, 734215,734216, 734217  ) ) ;
ERROR 1657 (HY000): Cannot have more than one value for this type of LIST partitioning

此时，第二个 alter table 语句不起作用，因此我无法获得第一个示例中所示的 Mar10 分区的 17 个值。如果我删除最后一个值 734217 那么 alter table 语句就成功了！

mysql> alter table mypart add PARTITION (PARTITION Mar10 VALUES IN (734201, 734202, 734203, 734204, 734205, 734206, 734207, 734208, 734209, 734210, 734211, 734212, 734213,  734214, 734215,734216));
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

alter table 语句中是否有某些不适用于 create table 语句的限制？

mysql partitioning
Martin Hope
shantanuo
Asked: 2012-02-02 07:07:18 +0800 CST
  • 11

我在 my.cnf 文件中将最大连接数设置为 2000 左右。

max_connections=2048

我已升级到 5.5.20，现在我在错误日志中看到以下行。

120201 19:40:24 [Warning] Changed limits: max_open_files: 1024  max_connections: 214  table_cache: 400

为什么 mysql 将最大连接值更改为 214 ？

# mysqladmin variables | grep max_connections
| max_connections | 214

操作系统限制的软硬打开文件为1024

# ulimit -Sa | grep "open files"
open files                      (-n) 1024

# ulimit -Ha | grep "open files"
open files                      (-n) 1024

实际使用的最大连接数：

# mysql -e"show status like '%used_connections%'"
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Max_used_connections | 95     |
+----------------------+-------+
mysql